Lock Conversions Two-phase locking protocol with lock conversions: Growing Phase: can acquire a lock-S on item can acquire a lock-X on item can convert a lock-S to a lock-X(upgrade) Shrinking Phase: 。can release a lock-S 。can release a lock-X can convert a lock-X to a lock-S (downgrade) This protocol ensures serializability Database System Concepts-7th Edition 18.13 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 18.13 ©Silberschatz, Korth and Sudarshan th Edition Lock Conversions ▪ Two-phase locking protocol with lock conversions: – Growing Phase: • can acquire a lock-S on item • can acquire a lock-X on item • can convert a lock-S to a lock-X (upgrade) – Shrinking Phase: • can release a lock-S • can release a lock-X • can convert a lock-X to a lock-S (downgrade) ▪ This protocol ensures serializability
Automatic Acquisition of Locks A transaction T issues the standard read/write instruction,without explicit locking calls. The operation read(D)is processed as: if T;has a lock on D then read(D) else begin if necessary wait until no other transaction has a lock-X on D grant T a lock-Son D; read(D) end Database System Concepts-7th Edition 18.14 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 18.14 ©Silberschatz, Korth and Sudarshan th Edition Automatic Acquisition of Locks ▪ A transaction Ti issues the standard read/write instruction, without explicit locking calls. ▪ The operation read(D) is processed as: if Ti has a lock on D then read(D) else begin if necessary wait until no other transaction has a lock-X on D grant Ti a lock-Son D; read(D) end
Automatic Acquisition of Locks(Cont.) The operation write(D)is processed as: if T;has a lock-Xon D then write(D) else begin if necessary wait until no other trans.has any lock on D, if T;has a lock-S on D then upgrade lock on D to lock-X else grant T;a lock-X on D write(D) end; All locks are released after commit or abort Database System Concepts-7th Edition 18.15 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 18.15 ©Silberschatz, Korth and Sudarshan th Edition Automatic Acquisition of Locks (Cont.) ▪ The operation write(D) is processed as: if Ti has a lock-Xon D then write(D) else begin if necessary wait until no other trans. has any lock on D, if Ti has a lock-S on D then upgrade lock on D to lock-X else grant Ti a lock-X on D write(D) end; ▪ All locks are released after commit or abort
Implementation of Locking A lock manager can be implemented as a separate process Transactions can send lock and unlock requests as messages The lock manager replies to a lock request by sending a lock grant messages (or a message asking the transaction to roll back,in case of a deadlock) The requesting transaction waits until its request is answered The lock manager maintains an in-memory data-structure called a lock table to record granted locks and pending requests Database System Concepts-7th Edition 18.16 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 18.16 ©Silberschatz, Korth and Sudarshan th Edition Implementation of Locking ▪ A lock manager can be implemented as a separate process ▪ Transactions can send lock and unlock requests as messages ▪ The lock manager replies to a lock request by sending a lock grant messages (or a message asking the transaction to roll back, in case of a deadlock) • The requesting transaction waits until its request is answered ▪ The lock manager maintains an in-memory data-structure called a lock table to record granted locks and pending requests
Lock Table Dark rectangles indicate granted 123 locks,light colored ones indicate waiting requests T23 T8 T2 Lock table also records the type of lock granted or requested New request is added to the end of I912 the queue of requests for the data item,and granted if it is compatible with all earlier locks T23 Unlock requests result in the request being deleted,and later requests are checked to see if they can now be granted If transaction aborts,all waiting or granted requests of the transaction are deleted lock manager may keep a list of granted locks held by each transaction,to waiting implement this efficiently T8 Database System Concepts-7th Edition 18.17 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 18.17 ©Silberschatz, Korth and Sudarshan th Edition Lock Table ▪ Dark rectangles indicate granted locks, light colored ones indicate waiting requests ▪ Lock table also records the type of lock granted or requested ▪ New request is added to the end of the queue of requests for the data item, and granted if it is compatible with all earlier locks ▪ Unlock requests result in the request being deleted, and later requests are checked to see if they can now be granted ▪ If transaction aborts, all waiting or granted requests of the transaction are deleted • lock manager may keep a list of locks held by each transaction, to implement this efficiently