Synchronization
provides Consistency and Isolation, i.e., two of four ACID properties ensured during transaction management in databases. Synchronization
is achieved using locking protocols or time-stamping. Below is an exercise
problem and its solution using locking protocols (S2PL and CS2PL) and timestamps-ordering.
Problem: The company Miller Real Estates
transfers the monthly salary of its employee Henry Robertson from their bank
account at bank A to his bank account at bank B on December 25th. On the
following day the company also transfers the outstanding travel expenses to
Henry Robertson’s account. At bank A a transfer requires 6 days, i.e. debit on
day one, 4 days processing time, and credit entry on day 6. On December 27th
Henry Robertson transfers the monthly rent for his house to his landlord, which
has a bank account at bank C. At bank B a transfer only requires 4 days.
Assuming that the three banks A, B, and C commonly use a
Distributed DBMS, What would be the processing order, if the synchronization
would be based on:
a) S2PL,
b) CS2PL, or
c) Timestamp Ordering?
Solution:
S2PL
Date
|
Transaction 1
|
Transaction 2
|
Transaction 3
|
25 December
|
xlock A
Debit A->Salary |
||
26 December
|
Waiting A
|
||
27 December
|
Waiting A
|
xlock B
Debit B->Rent |
|
28 December
|
Waiting A
|
||
29 December
|
Waiting A
|
||
30 December
|
Waiting B
|
Waiting A
|
xlock C
Credit C->Rent ulock B ulock C Commit |
31 December
|
xlock B
Credit B->Salary ulock A ulock B Commit |
Waiting A
|
|
01 January
|
xlock A
Debit A->OTExp |
||
02 January
|
|||
03 January
|
|||
04 January
|
|||
05 January
|
|||
06 January
|
xlock B
Credit B->OTExp ulock A ulock B Commit |
CS2PL
Date
|
Transaction 1
|
Transaction 2
|
Transaction 3
|
25 December
|
xlock A
xlock B
Debit A->Salary |
||
26 December
|
Waiting A
|
||
27 December
|
Waiting A
|
Waiting B
|
|
28 December
|
Waiting A
|
Waiting B
|
|
29 December
|
Waiting A
|
Waiting B
|
|
30 December
|
Credit B->Salary
ulock A ulock B Commit |
Waiting A
|
Waiting B
|
31 December
|
xlock A
xlock B
Debit A->OTExp |
Waiting B
|
|
01 January
|
Waiting B
|
||
02 January
|
Waiting B
|
||
03 January
|
Waiting B
|
||
04 January
|
Waiting B
|
||
05 January
|
Credit B->OTExp
ulock A ulock B Commit |
Waiting B
|
|
06 January
|
xlock B
xlock C Debit B->Rent |
||
07 January
|
|||
08 January
|
|||
09 January
|
Credit C->Rent
ulock B ulock C Commit |
Timestamp-ordering
Date
|
Transaction 1
TS = 25 |
Transaction 2
TS = 26 |
Transaction 3
TS = 27
|
A (R)
|
A (W)
|
B (R)
|
B (W)
|
C (R)
|
C (W)
|
25 Dec.
|
Debit A->Salary
|
0
|
25
|
0
|
0
|
0
|
0
|
||
26 Dec.
|
Debit A->OTExp
|
0
|
26
|
0
|
0
|
0
|
0
|
||
27 Dec.
|
Debit B->Rent
|
0
|
26
|
0
|
27
|
0
|
0
|
||
28 Dec.
|
0
|
26
|
0
|
27
|
0
|
0
|
|||
29 Dec.
|
0
|
26
|
0
|
27
|
0
|
0
|
|||
30 Dec.
|
Credit B->Salary
Rollback |
Credit C->Rent
Commit
|
0
|
26
|
0
|
27 (25)
|
0
|
27
|
|
31 Dec.
|
Debit A->Salary
TS = 31 |
Credit B->OTExp
Rollback |
0
|
31
|
0
|
27 (26)
|
0
|
27
|
|
01 Jan.
|
Debit A->OTExp
TS = 32 |
0
|
32
|
0
|
27
|
0
|
27
|
||
02 Jan.
|
0
|
32
|
0
|
27
|
0
|
27
|
|||
03 Jan.
|
0
|
32
|
0
|
27
|
0
|
27
|
|||
04 Jan.
|
0
|
32
|
0
|
27
|
0
|
27
|
|||
05 Jan.
|
Credit B->Salary
Commit |
0
|
32
|
0
|
31
|
0
|
27
|
||
06 Jan.
|
Credit B->OTExp
Commit |
0
|
32
|
0
|
32
|
0
|
27
|
