Exclusive Lock
Usually, write. It’s like a mutex lock
Lifetime
In a single transaction
Only one transaction can get exclusive lock.
Shared Lock
Usually, read.
Lifetime
In multiple transactions
Multiple transactions can get shared locks.
Dead Lock
1
2
3
4
5
6
7
BEGIN TRANSACTION;
INSERT INTO test
VALUES 20
INSERT INTO test
VALUES 30
1
2
3
4
5
6
7
8
BEGIN TRANSACTION;
INSERT INTO test
VALUES 30
INSERT INTO test
VALUES 20
-- DeadLock Occurs
DB Rejects transaction and fail when deadlock occurs.
Two Phase Lock
Usually used with snapshot isolation level. It resolves Lost update and Phantom Read.
Why to use
Avoid race condition Booking problem
Two phase locking is robust enough to block all possible race conditions among concurrent transactions including phantom reads, lost updates and write skew. Readers don’t block writers and Writers don’t block readers.
Example
A book seat 1 which is empty in SELECT transaction B book seat 1 empty in SELECT transaction
A succeed book seat 1 in transaction and COMMIT! B succeed book seat 1 in transaction and COMMIT!
=> Lost Update “A”
References
- https://medium.com/double-pointer/transactions-for-system-design-interview-8-two-phase-locking-fcb74458785a
- https://medium.com/@hnasr/postgres-locks-a-deep-dive-9fc158a5641c