Database Locks

1. What They Are

• When multiple transactions concurrently access the same data, conflicts will definitely occur. How should such conflicts be handled?
• There are mainly two ideas:
  1. Pessimistic locking mechanism: avoid conflicts from occurring, such as Read/Write Locks and Two-Phase Locking.
  2. Optimistic locking mechanism: allow conflicts to occur and detect them afterward, such as MVCC.
• Described using Java
  • When doing multithreaded programming in Java, locks are needed.
  • The simplest is ReentranLock. With this, reads and writes are mutually exclusive, and reads are also mutually exclusive.
  • Replace it with ReentrantReadWriteLock. With this, reads are not mutually exclusive, but reads and writes are mutually exclusive.
  • Finally replace it with CopyOnWriteList. With this, reads are not mutually exclusive, and reads and writes are also not mutually exclusive.
  • MVCC is somewhat similar to CopyOnWriteList, except that when reading, instead of simply copying the current data, it preserves a series of snapshot versions.

2. Pessimistic Locking

Lock before modifying data, and release the lock after the modification.

2.1. Two-Phase Locking

• Within a transaction, there are locking and unlocking phases. At the beginning of the transaction, the number of locks increases; all locks are released only when the transaction ends.

2.1.1. Disadvantages of Two-Phase Locking

• The locking granularity is too coarse, which has an extremely large impact on concurrency performance.
  • Database Lock Granularity.md
• It may cause deadlocks.
  • Database Deadlocks.md

2.2. Read/Write Locks

• A read operation requires a shared lock, and a write operation requires an exclusive lock.
  • A shared lock blocks writes, but allows other readers to acquire the same shared lock.
• Shared locks block writes and allow concurrent reads. Exclusive locks block both reads and writes.
  • An exclusive lock blocks both readers and writers.

2.2.1. Disadvantages of Read/Write Locks

• Readers block Writers, Writers block Readers

3. Optimistic Locking

3.1. MVCC

• Reads do not acquire locks, and reads and writes do not conflict.
• Writes acquire locks, and writes are mutually exclusive.

3.1.1. Common Implementation Methods

3.1.2. Store Old Data in a Rollback Segment

When writing new data, move the old data to a separate place, such as a rollback segment. When others read the data, read the old data from the rollback segment. Oracle Database and the InnoDB engine in MySQL use this approach.

3.1.3. Do Not Delete Old Data

When writing new data, do not delete the old data; insert the new data instead. PostgreSQL uses this implementation method.

• Advantages
  • No matter how many operations a transaction has performed, transaction rollback can complete immediately.
  • Data can be updated many times. Unlike Oracle and MySQL’s InnoDB engine, there is no need to constantly ensure that the rollback segment will not be exhausted, and there is no frequent trouble with errors such as Oracle’s “ORA-1555”.
• Disadvantages
  • Old versions of data need to be cleaned up. Of course, PostgreSQL 9.x already added an automatic cleanup auxiliary process to clean them periodically.
  • Old versions of data may increase the number of data blocks that a query needs to scan, thereby making queries slower.

3.2. PostgreSQL Implementation

• PostgreSQL MVCC.md

3.3. MySQL InnoDB Implementation

• InnoDB MVCC.md

4. References

• A Brief Analysis of MySQL’s Locking Mechanism - bilibili
• How does MVCC (Multi-Version Concurrency Control) work - Vlad Mihalcea
• PgSQL · Feature Analysis · A Brief Analysis of the MVCC Mechanism
• PostgreSQL Series: Understanding the MVCC Mechanism and Locking Mechanism - Jianshu
• PostgreSQL’s MVCC Concurrency Processing | Piglei
• MYSQL MVCC Implementation Principle - Jianshu
• Two Phase Locking Protocol - GeeksforGeeks
• Two-Phase Locking Protocol - Mission Call - cnblogs
• Three-Level Locking Protocol - Baidu Baike
• Detailed Explanation of MySQL’s Two-Phase Locking Protocol - SegmentFault