ASA SQL User's Guide
Using Transactions and Isolation Levels
How locking works
Often, the general information about locking provided in the earlier sections will suffice to meet your needs. There are times, however, when you may benefit from more knowledge of what goes on inside the database server when you perform basic types of operations. This knowledge will provide you with a better basis from which to understand and predict potential problems that users of your database may encounter.
Two-phase locking is important in the context of ensuring that schedules are serializable. The two-phase locking protocol specifies a procedure each transaction follows.
This protocol is important because, if observed by all transactions, it will guarantee a serializable, and thus correct, schedule. It may also help you understand why some methods of locking permit some types of inconsistencies.
Before operating on any row, a transaction must acquire a lock on that row.
After releasing a lock, a transaction must never acquire any more locks.
In practice, a transaction normally holds locks until it terminates with either a COMMIT or ROLLBACK statement. Releasing locks before the end of the transaction disallows the operation of rolling back the changes whenever doing so would necessitate operating on rows to return them to an earlier state.
The two-phase locking protocol allows the statement of the following important theorem:
The two-phase locking theoremIf all transactions obey the two-phase locking protocol, then all possible interleaved schedules are serializable. |
In other words, if all transactions follow the two-phase locking protocol, then none of the inconsistencies mentioned above are possible.
This protocol defines the operations necessary to ensure complete consistency of your data, but you may decide that some types of inconsistencies are permissible during some operations on your database. Eliminating all inconsistency often means reducing the efficiency of your database.
Write locks are placed on modified, inserted, and deleted rows regardless of isolation level. They are always held until commit and rollback.
| Isolation level | Read locks |
|---|---|
| 0 | None |
| 1 | On rows that appear in the result set; they are held only when a cursor is positioned on a row. |
| 2 | On rows that appear in the result set; they are held until the user executes a COMMIT or a ROLLBACK. |
| 3 | On all rows read and all insertion points crossed in the computation of a result set |
For more information, see Serializable schedules
The details of locking are best broken into two sections: what happens during an INSERT, UPDATE, DELETE or SELECT and how the various isolation levels affect the placement of read, anti-insert, and insert locks.
Although you can control the amount of locking that takes place within the database server by setting the isolation level, there is a good deal of locking that occurs at all levels, even at level 0. These locking operations are fundamental. For example, once one transaction updates a row, no other transaction can modify the same row before the first transaction completes. Without this precaution, you could not rollback the first transaction.
The locking operations that the database server performs at isolation level 0 are the best to learn first exactly because they represent the foundation. The other levels add locking features, but do not remove any present in the lower levels. Thus, moving to higher isolation level adds operations not present at lower levels.