User's Guide
PART 2. Relational Database Concepts
CHAPTER 15. Using Transactions and Locks
To process transactions concurrently, the database engine must execute some component statements of one transaction, then some from other transactions, before continuing to process further operations from the first. The order in which the component operations of the various transactions are interwoven is called the schedule.
Applying transactions concurrently in this manner can result in many possible outcomes, including the three particular inconsistencies described in the previous section. Sometimes, the final state of the database also could have been achieved had the transactions been executed sequentially, meaning that one transaction was always completed in its entirety before the next was started. A schedule is called serializable whenever executing the transactions sequentially, in some order, could have left the database in the same state.
Serializability is the commonly accepted criterion for correctness. A serializable schedule is accepted as correct because the database is not influenced by the concurrent execution of the transactions.
Even when transactions are executed sequentially, the final state of the database can depend upon the order in which these transactions are executed. For example, if one transaction sets a particular cell to the value 5 and another sets it to the number 6, then the final value of the cell is determined by which transaction executes last.
Knowing a schedule is serializable does not settle which order transactions would best be executed, but rather states that concurrency has added no effect. Outcomes which may be achieved by executing the set of transactions sequentially in some order are all assumed correct.
The three types of inconsistencies introduced in the previous section are typical of the types of problems which appear when the schedule is not serializable. In each case, the inconsistency appeared because the statements were interleaved in such a way as to produce a result that would not be possible if all transactions were executed sequentially. For example, a dirty read can only occur if one transaction can select rows while another transaction is in the middle of inserting or updating data.
Two-phase lockingTwo-phase locking is important in the context of ensuring that schedules are serializable. The two-phase locking protocol specifies a procedure each transaction should follow.
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 theorem |
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. Thus, Adaptive Server Anywhere affords you control the correctness of each transaction through the use of various types of locking.