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

INVENTORY RECORDS
IN DATABASE

Order Entry --
Boston

Order Entry --
Chicago

D

Order : Database textbook
60 copies

Order: Database textbook
70 copies
1. REQUEST LOCK (D)
2. LOCK (D)
3. READ (D)
5. (D) :

200

4. REQUEST LOCK (D)

130

(D) - 70
6. WRITE (D)
7. UNLOCK (D)

=

8. LOCK (D)
9. READ (D)
10. (D) :

70

(D) - 60
11. WRITE (D)
12. UNLOCK (D)

=

Figure 15-19 Example of simple locking with binary locks.

Interleaved Schedule with
S and X Locks

Serial Schedule: T1, T2

T1 T2

Initial Values
(D1) 100, (D2) 150

T1 T2

LOCK-S (D2)
READ (D2)
UNLOCK (D2)
LOCK-X (D1)
READ (D1)
(D1) :

LOCK-S (D2)
READ (D2)
UNLOCK (D2)

Operations in T1 and T2

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

= (D1) + (D2)

WRITE (D1)
UNLOCK (D1)

T1 T2

LOCK-S (D1)
READ (D1)
UNLOCK (D1)
LOCK-X (D2)
READ (D2)
(D2) :

LOCK-S (D2) LOCK-S (D1)

READ (D2) READ (D1) (D2) (D1)

+

UNLOCK (D2) UNLOCK (D1) WRITE (D2)

UNLOCK (D2) LOCK-X (D1) LOCK-X (D2)

READ (D1) READ (D2) LOCK-X (D1)

READ (D1)
(D1) : = (D1) + (D2)
WRITE (D1)
UNLOCK (D1)

= (D2) + (D1)

(D1) : = (D1) + (D2) (D2) : = (D2) + (D1)

WRITE (D2)
UNLOCK (D2)

WRITE (D1) WRITE (D2)

UNLOCK (D1) UNLOCK (D2)

Final Values
(D1) 250, (D2) 400

Final Values
(D1) 250, (D2) 250

Serial Schedule: T2, T1
Final Values
(D1) 350, (D2) 250

Figure 15-20 Example of locking with S- and X-locks.

Locking with S- and X-Locks Let us consider another example, already men-
tioned in earlier sections, about two transactions accessing the inventory records of
database and data warehousing textbooks. In this example, each of the two trans-
actions makes adjustments to inventory numbers. Figure 15-20 presents the appli-
cation of S- and X-locks to this example.