User's Guide
PART 2. Relational Database Concepts
CHAPTER 15. Using Transactions and Locks
When the database engine processes a transaction, it can lock one or more rows of a table. The locks maintain the reliability of information stored in the database by preventing concurrent access by other transactions. They also improve the accuracy of result queries by identifying information which is in the process of being updated.
The Adaptive Server Anywhere engine places these locks automatically and needs no explicit instruction. It holds all the locks acquired by a transaction until the transaction is completed, for example by either a COMMIT or ROLLBACK statement, with a single exception noted below.
The transaction that has access to the row is said to hold the lock. Depending on the type of lock, other transactions may have limited access to the locked row, or none at all.
Adaptive Server Anywhere allows users to determine the amount of locking employed in each connection. The amount of locking controls the correctness of the schedules. Unfortunately it also affects concurrency because locks placed by one transaction may delay or obstruct the progress of another.
Amounts of locking will be described further in Isolation levels and consistency.
Objects that can be lockedAdaptive Server Anywhere uses row-level locking, meaning that each lock locks a row, rather than always locking an entire page or range of rows. Some transactions may require locks on many rows, or rows that meet a specific criterion. To meet these requirements, locks can be placed on the flowing objects.
rows in tables A transaction can lock a particular row, for example, to prevent another transaction from changing it.
scan positions in either indexed or sequential scans Transactions typically scan rows using the ordering imposed by an index, or scan rows sequentially. In either case, a lock can be placed on the scan position. For example, placing a lock in an index can prevent another transaction from inserting a row with a specific value or range of values.
table schemas A transaction can lock the schema of a table, preventing other transactions from modifying the table's structure.
Of these objects, the most intuitive are likely rows. It is understandable that a transaction reading, updating, deleting, or inserting a row should limit the simultaneous access to other transactions. Similarly, a transaction changing the structure of a table, perhaps inserting a new column, could greatly impact other transactions. In such a case, it is essential to limit the access of other transactions to prevent errors.
A scan position is a location in an ordering of rows of a table. In this case, there are only two possible types of ordering affected. First, rows can be ordered through use of an index, based on a particular criterion established when the index was constructed. Secondly, when performing a sequential scan of a table, Adaptive Server Anywhere must select an order in which to process the rows.
In the case of a sequential scan, the specific ordering is defined by the internal workings of the database engine. You should not rely on the order of rows in a sequential scan. From the point of view of scanning the rows, however, Adaptive Server Anywhere treats the request similarly to an indexed scan, albeit using an ordering of its own choosing. It can place locks on positions in the scan as it would were it using an index.
Through locking a scan position, a transaction prevents some actions by other transactions relating to a particular range of values in that ordering of the rows. Phantom and anti-phantom locks are always placed on scan positions.
For example, a transaction might delete a row, hence deleting a particular primary key value. Until this transaction either commits the change or rolls it back, it must protect its right to do either. In the case of a deleted row, it must ensure that no other transaction can insert a row using the same primary key value, hence making a rollback operation impossible. A lock on the scan position this row occupied reserves this right while having the least impact on other transactions.
The four types of locksAdaptive Server Anywhere uses four distinct types of locks to implement its locking scheme and ensure appropriate levels of isolation between transactions:
read lock (shared)
phantom lock (shared)
write lock (exclusive)
anti-phantom lock (shared)
Each of these locks has a separate purpose. They work together and all are needed. Each addresses a particular set of inconsistencies which would occur in their absence. Depending on the isolation level you select, the database server will use some or all of them to maintain the degree of consistency you require.
These four types of locks each fall into one of two categories:
Exclusive locks Only one transaction can hold an exclusive lock on the row of a table at one time. No transaction can obtain an exclusive lock while any other transaction holds a lock of any type on the same row. Once a transaction acquires an exclusive lock, requests to lock the row by other transactions will be denied.
Write locks are exclusive.
Shared locks Any number of transactions may acquire shared locks on any one row at the same time. Shared locks are sometimes referred to as non-exclusive locks.
Read locks, phantom locks, and anti-phantom locks are shared.
Only one transaction should change any one row at one time. Otherwise, two simultaneous transaction might try to change one value to two different new ones. Hence, a write lock should be, and is, exclusive.
By contrast, no difficulty arises if more than one transaction wants to read a row. Since neither is changing it, there is no conflict of interest. Hence, read locks may be shared.
You may apply similar reasoning to phantom and anti-phantom locks. Many transactions can prevent the insertion of a row in a particular scan position by each acquiring an phantom lock. Similar logic applies for anti-phantom locks. When a particular transaction requires exclusive access, it can easily achieve exclusive access by obtaining both a phantom and an anti-phantom lock on the same row. These locks to not conflict when they are held by the same transaction.
The following table identifies the combination of locks that conflict.
read |
write |
phantom |
anti-phantom |
|
|---|---|---|---|---|
read |
|
conflict |
|
|
write |
conflict |
conflict |
|
|
phantom |
|
|
|
conflict |
anti-phantom |
|
|
conflict |
|
These conflicts arise only when the locks are held by different transactions. For example, one transaction can obtain both phantom and anti-phantom locks on a single scan position to obtain exclusive access to a location.
The above types of locks have the following uses:
A transaction acquires a write lock whenever it inserts, updates, or deletes a row. No other transaction can obtain either a read or a write lock on the same row when a write lock is set. A write lock is an exclusive lock.
A transaction can acquire a read lock when it reads a row. Several transactions can acquire read locks on the same row (a read lock is a shared or nonexclusive lock). Once a row has been read locked, no other transaction can obtain a write lock on it. Thus, a transaction can ensure that no other transaction modifies or deletes a row by acquiring a read lock.
A phantom lock is a shared lock placed on an indexed scan position to prevent phantom rows. It prevents other transactions from inserting a row into a table immediately before the row which is phantom locked. Phantom locks for lookups using indexes require a read lock on each row that is read, and one extra read lock to prevent insertions into the index at the end of the result set. Phantom rows for lookups that do not use indexes require a read lock on all rows in a table to prevent insertions from altering the result set, and so can have a bad effect on concurrency.
An anti-phantom lock is a shared lock placed on an indexed scan position to reserve the right to insert a row. Once one transaction acquires an anti-phantom lock on a row, no other transaction can acquire a phantom lock on the same row. A read lock on the corresponding row is always acquired at the same time as an anti-phantom lock to ensure that no other process can update or destroy the row, thereby bypassing the anti-phantom lock.
Adaptive Server Anywhere uses these four types of locks as necessary to ensure the level of consistency that you require. You need not explicitly request the use of a particular lock. You control the level of consistency, as is explained in the next section. Knowledge of the types of locks will guide you in choosing isolation levels and understanding the impact of each level on performance.