Page 162 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 9: Transaction Management

RULE 2: All locks held by a transaction are released when the transaction is completed. Notes
The locking protocol can have two cases as follows:
1. If two transactions access completely different parts of the database, then they proceed
without interruption on their ways.
2. If two transactions access same object of the database, then their actions are ordered
serially i.e., all actions of locked transaction are completed first, then this lock is released
and the other transaction can now proceed.
Thus, the locking protocol only helps the second case i.e., interleaving of transactions.
Moreover, the shared lock on database object A is denoted as S(A) and the exclusive lock on
database object A is denoted as X(A).

Example: Suppose that both transactions T and T read the same data objects A and B
1 2
perform the following operations:
1. T deducts $100 from account A.
1
2. T reads accounts of A and B and adds 6% interest to each.
2
3. T adds $100 to account B.
1
This example, may use interleaving of transactions and produce incorrect results. But, if the
Strict 2PL Protocol is used, such interleaving is disallowed and performs both transactions as
follows:
First T would obtain an exclusive lock on A and then read and write A. Then, T would request
2
a lock A, but, this request cannot be granted until T releases its exclusive lock on A, and the
1
DBMS therefore suspends T as follows:
2
T1 T2
X(A)
R(A)
A: = A – 100;
W(A)

Now, T proceeds to obtain an exclusive lock on B reads and writes B, then finally commits and
1
the locks on A and B are released. Now, T ’s lock request is granted and it proceeds. As a result
2
the Strict 2PL Protocol results in a serial execution of the two transactions as follows:
T1 T2
X(A)
R(A)
A: = A-100;
W(A)
X (B)
R(B)
B: = B + 100;
W (B)
Commit
X(A) Contd...
R(A)
A: = A + 0.06 A;
W(A)
X(B)
LOVELY PROFESSIONAL UNIVERSITY 155
R(B)
B: = B + 0.06B;
W(B)
Commit