Concurrency Control The major disadvantage of locking a data item corresponding to the relation, or locking an entire index,is the low degree of concurrency—two transactions that insert different tuples into a relation are prevented from executing concurrently.73807

A better solution is an index-locking technique that avoids locking the whole index.Any transaction that inserts a tuple into a relation must insert information into every index maintained on the relation.We eliminate the phantom phenomenon by imposing a locking protocol for indices. For simplicity we shall consider only B+-tree indices. 

As we saw in Chapter11, every search-key value is associated withan index leaf node. A query will usually use one or more indices to access a relation. An insert must insert the new tuple in all indices on the relation.Inour example,we assume that there is an index on instructor for dept name. Then, T31 must modify the leaf containing the key “Physics”. If T30 reads the same leaf node to locate all tuples pertaining to the Physics department,then T30 and T31 conflict on thatleaf node. 

The index-locking protocol takes advantage of the availability of indices on a  relation,by turning instances of the phantom phenomenon into conflicts on locks on index leaf nodes. The protocol operates as follows:

• Every relation must have at least one index. 

• A transaction Ti can access tuples of a relation only after first finding them through one or more of the indices on the relation. For the purpose of the index-locking protocol, a relation scan is treated as a scan through all the leavesof one of the indices. 

• A transaction Ti that performs a lookup (whether a range lookup or a point lookup)must acquire a shared lock on all the index leaf nodes that it accesses. 

• A transaction Ti may not insert, delete, or update a tuple ti in a relation r without updating all indices on r. The transaction must obtain exclusive locks on all index leaf nodes that are affected by the insertion, deletion, or update. For insertion and deletion, the leaf nodes affected are those that contain (after insertion) or contained (before deletion) the search-key value of the tuple. For updates, the leaf nodes affected are those that (before the modification)contained the old value of the searchkey,and nodes that(after the modification) contain the new value of the search key. 

• Locks are obtained on tuples as usual. 

• The rules of the two-phase locking protocol must be observed.

Note that the index-locking protocol does not address concurrency controlon internal nodes of an index; techniques for concurrency control on indices, which minimize lock conflicts, are presentedin Section 15.10. 

Locking an index leaf node prevents any update to the node, even if the update did not actually conflict with the predicate. A variant called key-value. locking, which minimizes such false lock conflicts, is presented in Section 15.10 as part of index concurrency control. 

As noted in Section 14.10, it would appear that the existence of a conflict between transactions depends on a low-level query-processing decision by the system that is unrelated to a user-level view of the meaning of the two transactions. An alternative approach to concurrency control acquires shared locks on predicates in a query, such as the predicate “salary > 90000” on the instructor relation. Inserts and deletes of the relation must then be checked to see if they satisfy the predicate;if they do,there is a lock conflict,forcing the insert or delete to wait till the predicate lock is released. For updates, both the initial value and the final value of the tuple must be checked against the predicate. Such conflicting inserts, deletes and updates affect the set of tuples selected by the predicate, and cannot be allowed to execute concurrently with the query that acquired the (shared) predicate lock. We call the above protocol predicate locking;3 predicate locking is not used in practice since it is more expensive to implement than the index-locking protocol, and does not give significant additional benefits.