Other Concurrency Control Issues

Other Concurrency Control Issues

In this section we discuss some other issues relevant to concurrency control. In Section 1, we discuss problems associated with insertion and deletion of records and the so-called phantom problem, which may occur when records are inserted. This problem was described as a potential problem requiring a concurrency control measure in Section 21.6. In Section 2 we discuss problems that may occur when a transaction outputs some data to a monitor before it commits, and then the transaction is later aborted.

1. Insertion, Deletion, and Phantom Records

When a new data item is inserted in the database, it obviously cannot be accessed until after the item is created and the insert operation is completed. In a locking environment, a lock for the item can be created and set to exclusive (write) mode; the lock can be released at the same time as other write locks would be released, based on the concurrency control protocol being used. For a timestamp-based protocol, the read and write timestamps of the new item are set to the timestamp of the creating transaction.

Next, consider a deletion operation that is applied on an existing data item. For locking protocols, again an exclusive (write) lock must be obtained before the trans-action can delete the item. For timestamp ordering, the protocol must ensure that no later transaction has read or written the item before allowing the item to be deleted.

A situation known as the phantom problem can occur when a new record that is being inserted by some transaction T satisfies a condition that a set of records accessed by another transaction T must satisfy. For example, suppose that transaction T is inserting a new EMPLOYEE record whose Dno = 5, while transaction T is accessing all EMPLOYEE records whose Dno = 5 (say, to add up all their Salary values to calculate the personnel budget for department 5). If the equivalent serial order is T followed by T , then T must read the new EMPLOYEE record and include its Salary in the sum calculation. For the equivalent serial order T followed by T, the new salary should not be included. Notice that although the transactions logically con-flict, in the latter case there is really no record (data item) in common between the two transactions, since T may have locked all the records with Dno = 5 before T inserted the new record. This is because the record that causes the conflict is a phantom record that has suddenly appeared in the database on being inserted. If other operations in the two transactions conflict, the conflict due to the phantom record may not be recognized by the concurrency control protocol.

One solution to the phantom record problem is to use index locking, as discussed in Section 22.6. Recall from Chapter 18 that an index includes entries that have an attribute value, plus a set of pointers to all records in the file with that value. For example, an index on Dno of EMPLOYEE would include an entry for each distinct Dno value, plus a set of pointers to all EMPLOYEE records with that value. If the index entry is locked before the record itself can be accessed, then the conflict on the phantom record can be detected, because transaction T would request a read lock on the index entry for Dno = 5, and T would request a write lock on the same entry before they could place the locks on the actual records. Since the index locks conflict, the phantom conflict would be detected.

A more general technique, called predicate locking, would lock access to all records that satisfy an arbitrary predicate (condition) in a similar manner; however, predicate locks have proved to be difficult to implement efficiently.

2. Interactive Transactions

Another problem occurs when interactive transactions read input and write output to an interactive device, such as a monitor screen, before they are committed. The problem is that a user can input a value of a data item to a transaction T that is based on some value written to the screen by transaction T , which may not have committed. This dependency between T and T cannot be modeled by the system concurrency control method, since it is only based on the user interacting with the two transactions.

An approach to dealing with this problem is to postpone output of transactions to the screen until they have committed.

3. Latches

Locks held for a short duration are typically called latches. Latches do not follow the usual concurrency control protocol such as two-phase locking. For example, a latch can be used to guarantee the physical integrity of a page when that page is being written from the buffer to disk. A latch would be acquired for the page, the page written to disk, and then the latch released.