0 / 3
Lock Compatibility
Shared · Shared
If a newly requested lock can be held together with a lock already held, the later transaction runs right away. If not, it waits.
See whether shared locks can be held together when both transactions only read.
1 / 3
T1 takes a shared lock and reads
T1 takes a shared lock on X and reads 10.
2 / 3
T2 reads right away too
T2 also takes a shared lock on X. Shared locks can be held together, so T2 does not wait.
3 / 3
Result: shared locks can be held together
Reads do not change the value, so shared locks may be held together.
1 / 5
T1 takes a shared lock and reads
T1 takes a shared lock on X and reads 10.
2 / 5
T2 waits
T2 requests an exclusive lock to write. X holds T1’s shared lock, so the two locks cannot be held together.
3 / 5
T1 releases its lock
T1 finishes reading and releases its lock on X. The waiting T2 can now take its lock.
4 / 5
T2 writes now
T2 takes an exclusive lock on X and writes 9.
5 / 5
Result: cannot be held together
T2 waits until T1 releases its lock, so the value does not change while T1 is reading.
1 / 5
T1 takes an exclusive lock and writes
T1 takes an exclusive lock on X and writes 9.
2 / 5
T2 waits
T2 requests a shared lock to read. X holds T1’s exclusive lock, so even reading is blocked.
3 / 5
T1 finishes
T1 runs Commit and releases its lock on X. The waiting T2 can now take its lock.
4 / 5
T2 reads now
T2 takes a shared lock on X and reads the final 9.
5 / 5
Result: cannot be held together
An exclusive lock keeps other transactions from reading a value that is still being written.
1 / 5
T1 takes an exclusive lock and writes
T1 takes an exclusive lock on X and writes 9.
2 / 5
T2 waits
T2 also requests an exclusive lock to write but waits because of T1’s exclusive lock.
3 / 5
T1 finishes
T1 runs Commit and releases its lock on X. The waiting T2 can now take its lock.
4 / 5
T2 writes now
T2 takes an exclusive lock on X, subtracts 1 from 9, and writes 8.
5 / 5
Result: cannot be held together
The two writes do not mix and run one at a time.
Summary:Only shared locks can be held together. If even one exclusive lock is involved, the later transaction waits.
1 / 5
T1 locks the whole table
To update Row 1, T1 takes an exclusive lock on the entire Orders table.
2 / 5
T2 waits
T2 wants to update a different row, order 3. But the whole table is locked, so it waits.
3 / 5
T1 finishes
T1 runs Commit and releases its lock on the table.
4 / 5
T2 updates now
T2 takes an exclusive lock on the table and updates Row 3.
5 / 5
Result: low concurrency
T1 and T2 updated different rows but still ran one after the other.
Upside:Only one lock needs to be managed.
1 / 5
T1 locks only Row 1
T1 takes an exclusive lock only on Row 1, the row it will update.
2 / 5
T2 updates right away too
T2 takes an exclusive lock on Row 3. T1’s lock covers only Row 1, so T2 does not wait.
3 / 5
T1 finishes
T1 runs Commit and releases its lock on Row 1.
4 / 5
T2 finishes
T2 runs Commit and releases its lock on Row 3.
5 / 5
Result: high concurrency
Transactions that update different rows ran at the same time.
Cost:Each row gets its own lock, so there are more locks to manage.Note:Some DBMSs, such as SQL Server, replace row locks with a single table lock when there are too many of them. This is called lock escalation. Other DBMSs, such as Oracle and MySQL (InnoDB), do not escalate.
1 / 6
T1 reads A
T1 takes a shared lock on A and reads 100.
2 / 6
T1 releases its lock on A
T1 still has to lock B after this. But it has already released its lock on A, so the moment it locks B, it breaks the 2PL rule.
3 / 6
T2 cuts in
The lock on A is released, so T2 takes an exclusive lock on A and subtracts 50.
4 / 6
T2 finishes the transfer
T2 takes an exclusive lock on B, adds 50, then releases all its locks and finishes.
5 / 6
T1 reads B
T1 takes a shared lock on B and reads it. The transfer is already done, so B is 150. T1 took a new lock after releasing one, so it broke 2PL.
6 / 6
Result: the total is wrong even with locks
T1 released its lock on A too early, and T2’s transfer cut in through that gap.
1 / 7
Growing phase: lock A
T1 takes a shared lock on A and reads 100. It has not locked B yet, so it does not release its lock on A.
2 / 7
T2 waits
T2 requests an exclusive lock on A but waits because of T1’s shared lock. When locks were released early, this is where T2 cut in.
3 / 7
Growing phase: lock B
T1 also takes a shared lock on B and reads 100. It now holds every lock it needs.
4 / 7
Shrinking phase: release locks
T1 gets a total of 200 and releases its locks. From here on, T1 cannot take any new lock.
5 / 7
T2 transfers now
The waiting T2 takes an exclusive lock on A and subtracts 50.
6 / 7
T2 finishes the transfer
T2 takes an exclusive lock on B and adds 50.
7 / 7
Result: serializable
When every transaction follows 2PL, running them in turns gives the same result as running them one at a time. Such a schedule is called serializable.
Growing phase:Up to Read(B), T1 only takes locks.Shrinking phase:From unlock(A, B) on, T1 only releases locks.
1 / 5
T1 writes 9
T1 takes an exclusive lock on X and writes 9. It has not run Commit yet.
2 / 5
T1 unlocks before Commit
T1 has no more locks to take, so the 2PL rule lets it release its lock on X. But it has not run Commit yet.
3 / 5
T2 reads an intermediate value
T2 takes a shared lock on X and reads the 9 that is not final yet.
4 / 5
T1 cancels
The payment fails, so T1 runs Rollback and X goes back to 10. The 9 that T2 read becomes invalid.
5 / 5
Result: basic 2PL cannot prevent cascading rollback
The 2PL rule only fixes the order: once any lock is released, no new lock is taken. It does not say whether locks are released before or after Commit.
1 / 5
T1 writes 9
T1 takes an exclusive lock on X and writes 9. It keeps this lock until it runs Commit or Rollback.
2 / 5
T2 waits
T2 tries to read X but waits because of T1’s exclusive lock. In basic 2PL, this is where T2 read 9.
3 / 5
T1 cancels and unlocks
T1 runs Rollback, sets X back to 10, and only then releases its lock.
4 / 5
T2 reads the final value
The waiting T2 takes a shared lock on X and reads the final 10.
5 / 5
Result: strict 2PL prevents cascading rollback
T2 read only final values, so T1’s Rollback does not affect it.
Rigorous 2PL:Rigorous 2PL keeps every lock, shared locks included, until Commit or Rollback. Like strict 2PL, it prevents cascading rollback, and its result matches running the transactions one at a time in Commit order. Most real DBMSs use this approach.
1 / 5
T1 locks A
T1 takes an exclusive lock on A and updates A.
2 / 5
T2 locks B
T2 takes an exclusive lock on B and updates B.
3 / 5
T1 waits for T2
T1 tries to update B but waits because of T2’s exclusive lock. T1 waits without releasing its lock on A.
4 / 5
T2 waits for T1
T2 also tries to update A but waits because of T1’s exclusive lock. Now each one only waits for the other to release its lock.
5 / 5
Result: deadlock
Unless the DBMS steps in, neither transaction releases its locks, so both wait forever.