Understanding Database Locks: Shared, Exclusive, Mutex, Pessimistic, Optimistic, Row, Table, and Page Locks, and Common Concurrency Issues

The article begins with a humorous anecdote before diving into technical explanations of database locking mechanisms.

Shared lock (S lock) : Also called a read lock, it allows transactions to read data but prevents any modification; multiple transactions can hold shared locks simultaneously.

Exclusive lock (X lock) : Also known as a write or exclusive lock, it grants a transaction both read and write rights on a data object, blocking all other transactions from acquiring any lock on that object until it is released.

Mutex lock : In programming, a mutex ensures that only one thread can access a shared object at a time, preserving data integrity.

Pessimistic lock : Assumes the worst case; a lock is acquired before data is accessed, causing other transactions to wait until the lock is released. Examples include row locks, table locks, and the Java synchronized keyword.

Optimistic lock : Assumes conflicts are rare; no lock is taken when reading data, but the transaction verifies that the data has not been changed before committing, often using version numbers or CAS (compare‑and‑swap) mechanisms.

Row‑level lock : The finest granularity in MySQL, locking only the specific rows involved in a transaction, which reduces conflict but incurs higher overhead.

Table‑level lock : Locks the entire table, offering lower overhead and faster lock acquisition but higher potential for conflicts.

Page‑level lock : An intermediate granularity between row and table locks, locking a group of adjacent records (a page) to balance performance and contention.

Lost update : Occurs when two concurrent transactions read the same data, both modify it, and the later commit overwrites the earlier one, causing the first update to be lost.

Non‑repeatable read : A transaction reads the same row twice and gets different results because another transaction modified or deleted the row in between.

Dirty read : Happens when a transaction reads data that has been modified by another transaction that later rolls back, resulting in inconsistent data.

Deadlock : A situation where two or more transactions are each waiting for resources held by the other, causing all to block indefinitely.

Four necessary conditions for deadlock are: 1) Mutual exclusion – a resource can be held by only one transaction at a time. 2) Hold and wait – a transaction holds at least one resource while requesting additional ones. 3) No preemption – resources cannot be forcibly taken away. 4) Circular wait – a closed chain of transactions each waiting for a resource held by the next.

Breaking any of these conditions can prevent deadlocks.

Reference: www.cnblogs.com/qjjazry/p/6581568.html