Answer:
If two-phase locking is used:
- A delete operation may be performed only if the transaction deleting the tuple has an exclusive lock on the tuple to be deleted.
- A transaction that inserts a new tuple into the database is given an X-mode lock on the tuple.
Insertions and deletions can lead to the phantom phenomenon.
- A transaction that scans a relation (e.g., find all accounts in Bombay) and a transaction that inserts a tuple in the relation (e.g., insert a new account at Bombay) may be in conflict with each other, despite the fact that the two transactions are not accessing any tuple in common.
- If only tuple locks are used, non-serialisable schedules may be the result: the scan than transaction may not see the new account, yet may be serialised before the insert transaction.
The transaction scanning the relation reads the information that indicates what tuples the relation contains, while a transaction inserting a tuple updates the same information.
The information should be locked.
One solution to such a problem may be:
- Associating a data item with the relation helps represent the information about the tuples the relation contains.
- Transactions scanning the relation acquires a shared lock in the data item.
- Transactions inserting or deleting a tuple acquires an exclusive lock on the data item.
The above mentioned protocol provides very low concurrency for insertions/deletions. Index locking protocols provide higher concurrency while preventing the phantom phenomenon, (by requiring locks on certain index buckets).
Index Locking Protocol: Every relation must have at least one index. Access to a relation must be made only through one of the indices on the relation. A transaction T i that performs a lookup must lock all the index buckets that it accesses, in S-mode. A transaction Ti may not insert a tuple ti into a relation r without updating all indices to r. T i must perform a lookup on every index to find all index buckets that could have possibly contained a pointer to tuple t i, had it existed already, and obtain locks in X-mode on all these index buckets. T i must also obtain locks in X-mode on all index buckets that it modifies. The rules of the two-phase locking protocol must be observed for index locking protocols to be effective.
