DATA INTEGRITY - Database Design and Development: An Essential Guide for IT Professionals - page 464

T1 and T2 following 2PL

T1 T2

LOCK-S (D2) LOCK-S (D1)
READ (D2) READ (D1)
LOCK-X (D1) LOCK-X (D2)
UNLOCK (D2) UNLOCK (D1)
READ (D1) READ (D2)
(D1) := (D1) + (D2) (D2) := (D2) + (D1)
WRITE (D1) WRITE (D2)

UNLOCK (D1) UNLOCK (D2)

Figure 15-21 Transactions following 2PL.

TIME T1 -- Chicago Order: T2 -- Boston Order: DB

DB 70 copies DB 60 copies

t1
t2
t3
t4
t5
t6
t7
t8
t9
t10
t11
t12

200
200
200
200
130
130
130
130
70
70
70
70

BEGIN T1
LOCK-X (DB) BEGIN T2

READ (DB) LOCK-X (DB)

(DB) := (DB) - 70 …. wait ….

WRITE (DB) …. wait ….

UNLOCK (DB) …. wait ….

COMMIT READ (DB)

END (DB) := (DB) - 60

WRITE (DB)
UNLOCK (DB)
COMMIT

END

Figure 15-22 Two-phase locking: resolution of lost update problem.

alizability. Carefully inspect the lock and unlock operations in T1 and T2 to ensure
that each of these transactions follows 2PL.
Before we proceed further, go back and review Figures 15-9, 15-10, and 15-11,
which presented the three major problems of concurrent transactions—lost update,
uncommitted dependency, and inconsistent summary. You can address these three
problems by applying the two-phase locking protocol. Figure 15-22 illustrates how
2PL resolves the lost update problem presented earlier in Figure 15-9. 2PL also
resolves the other two problems of uncommitted dependency and inconsistent
summary.

Deadlock: Prevention and Detection

Review lock-based concurrency control protocols including 2PL. Although 2PL
ensures serializability, it is still subject to a different problem. Refer back to Figure
15-21 showing the two transactions T1 and T2, each of which follows 2PL. In the
first phase, each transaction acquires all the locks it needs and performs its data-
base operations. While each transaction is acquiring the necessary locks, it is possi-
ble that the first transaction may have to wait for a lock held by a second transaction
to be released and vice versa. Refer to Figure 15-23 portraying a situation in which
transactions T1 and T2 are deadlocked although they attempt to conform to 2PL.