The Two-Phase Locking Protocol (Cont.) There can be conflict serializable schedules that cannot be obtained if two-phase locking is used. However,in the absence of extra information (e.g.,ordering of access to data),two-phase locking is needed for conflict serializability in the following sense: Given a transaction Ti that does not follow two-phase locking,we can find a transaction T that uses two-phase locking,and a schedule for T;and T;that is not conflict serializable. Database System Concepts-6th Edition 15.7 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 15.7 ©Silberschatz, Korth and Sudarshan th Edition The Two-Phase Locking Protocol (Cont.) There can be conflict serializable schedules that cannot be obtained if two-phase locking is used. However, in the absence of extra information (e.g., ordering of access to data), two-phase locking is needed for conflict serializability in the following sense: Given a transaction Ti that does not follow two-phase locking, we can find a transaction Tj that uses two-phase locking, and a schedule for Ti and Tj that is not conflict serializable
Lock Conversions Two-phase locking with lock conversions: First 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) -Second Phase: can release a lock-S can release a lock-X can convert a lock-X to a lock-S (downgrade) This protocol assures serializability.But still relies on the programmer to insert the various locking instructions. Database System Concepts-6th Edition 15.8 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 15.8 ©Silberschatz, Korth and Sudarshan th Edition Lock Conversions Two-phase locking with lock conversions: – First 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) – Second Phase: can release a lock-S can release a lock-X can convert a lock-X to a lock-S (downgrade) This protocol assures serializability. But still relies on the programmer to insert the various locking instructions
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 Tia lock-S on D; read(D) end Database System Concepts-6th Edition 15.9 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 15.9 ©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-S on D; read(D) end
Automatic Acquisition of Locks (Cont.) write(D)is processed as: if T;has a lock-X on D then write(D) else begin if necessary wait until no other transaction has any lock on D, if T;has a lock-S on D then upgrade lock on D to lock-X else grant Tia lock-X on D write(D) end; All locks are released after commit or abort Database System Concepts-6th Edition 15.10 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 15.10 ©Silberschatz, Korth and Sudarshan th Edition Automatic Acquisition of Locks (Cont.) write(D) is processed as: if Ti has a lock-X on D then write(D) else begin if necessary wait until no other transaction 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
Deadlocks Consider the partial schedule Ta Ta lock-x (B) read (B) B=B-50 write (B) lock-s (A) read (A) lock-s (B) lock-x (A) Neither T3 nor T4 can make progress-executing lock-S(B) causes T4 to wait for T3 to release its lock on B,while executing lock-X(A)causes T3 to wait for T4 to release its lock on A. Such a situation is called a deadlock. To handle a deadlock one of T3 or T4 must be rolled back and its locks released. Database System Concepts-6th Edition 15.11 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 6 15.11 ©Silberschatz, Korth and Sudarshan th Edition Deadlocks Consider the partial schedule Neither T3 nor T4 can make progress — executing lock-S(B) causes T4 to wait for T3 to release its lock on B, while executing lock-X(A) causes T3 to wait for T4 to release its lock on A. Such a situation is called a deadlock. To handle a deadlock one of T3 or T4 must be rolled back and its locks released