Pitfalls of lock-Based protocols Consider the partial schedule T lock-X (B) read(B) B:=B-50 write (B) ock-S(A) read(a) lock-S(B) lock-X(A) Such a situation is called a deadlock(死锁) To handle a deadlock one of T3 or Tg must be rolledback and its locks released Database System Concepts 3rd Edition 16.6 OSilberschatz, Korth and Sudarshan
Database System Concepts 3 16.6 ©Silberschatz, Korth and Sudarshan rd Edition Pitfalls of Lock-Based Protocols Consider the partial schedule Such a situation is called a deadlock（死锁）. To handle a deadlock one of T3 or T4 must be rolledback and its locks released
Pitfalls of Lock-Based Protocols(Cont) he potential for deadlock exists in most locking protocols Deadlocks are a necessary evil Starvation(53t)is also possible if concurrency control manager is badly designed. For example T5 T6 T7 T8 lock-S(Q) Lock×(Q) lock-S(Q) unlock(Q) lock-S(Q) unlock(Q) unlock(Q) Database System Concepts 3rd Edition 16.7 OSilberschatz, Korth and Sudarshan
Database System Concepts 3 16.7 ©Silberschatz, Korth and Sudarshan rd Edition Pitfalls of Lock-Based Protocols (Cont.) The potential for deadlock exists in most locking protocols. Deadlocks are a necessary evil. Starvation（饿死） is also possible if concurrency control manager is badly designed. For example: T5 T6 T7 T8 lock-S(Q) Lock-X(Q) lock-S(Q) unlock(Q) lock-S(Q) unlock(Q) unlock(Q)
The Two-Phase Locking Protocol 两段侦协议) This is a protocol which ensures conflict-serializable schedules Phase1: Growing Phase(增长阶段) x transaction may obtain locks x transaction may not release locks Phase2: Shrinking Phase(缩减阶段) x transaction may release locks x transaction may not obtain locks The protocol assures serializability. It can be proved that the transactions can be serialized in the order of their lock points封锁点(e. the point where a transaction acquired its final lock) Database System Concepts 3rd Edition 16.8 OSilberschatz, Korth and Sudarshan
Database System Concepts 3 16.8 ©Silberschatz, Korth and Sudarshan rd Edition The Two-Phase Locking Protocol （两段锁协议） This is a protocol which ensures conflict-serializable schedules. Phase 1: Growing Phase（增长阶段） transaction may obtain locks transaction may not release locks Phase 2: Shrinking Phase（缩减阶段） transaction may release locks transaction may not obtain locks The protocol assures serializability. It can be proved that the transactions can be serialized in the order of their lock points 封锁点 (i.e. the point where a transaction acquired its final lock)
The Two-Phase Locking Protocol(Cont) TWO-phase locking does not ensure freedom from deadlocks Cascading roll-back is possible under two-phase locking To avoid this follow a modified protocol called strict two- phase locking(严格两段锁). Here a transaction must hold all its exclusive locks till it commits/aborts Rigorous two- phase locking(强两段锁) is even stricter: here all locks are held till commit/abort. In this protocol transactions can be serialized in the order in which they commit 标 Database System Concepts 3rd Edition 16.9 OSilberschatz, Korth and Sudarshan
Database System Concepts 3 16.9 ©Silberschatz, Korth and Sudarshan rd Edition The Two-Phase Locking Protocol (Cont.) Two-phase locking does not ensure freedom from deadlocks Cascading roll-back is possible under two-phase locking. To avoid this, follow a modified protocol called strict twophase locking（严格两段锁）. Here a transaction must hold all its exclusive locks till it commits/aborts. Rigorous two-phase locking（强两段锁） is even stricter: here all locks are held till commit/abort. In this protocol transactions can be serialized in the order in which they commit
The Two-Phase Locking Protocol(Cont) T lock-X(A) read(a) lock-S(B) read ( B write(A) unlock(A) lock-X(A) read(A) write(A) unlock(A) lock-S(A) ead(A) 标 Database System Concepts 3rd Edition 16.10 OSilberschatz, Korth and Sudarshan
Database System Concepts 3 16.10 ©Silberschatz, Korth and Sudarshan rd Edition The Two-Phase Locking Protocol (Cont.)