In the InnoDB storage engine, transaction concurrency is managed through two primary mechanisms: Multi-Version Concurrency Control (MVCC) and locking strategies. MVCC maintains version chains to serve snapshot reads, effectively handling dirty reads, non-repeatable reads, and phantom reads for standard queries without explicit locking. However, MVCC does not apply to current reads where locks are explicitly requested. In such cases, the engine relies on locking mechanisms to block conflicting operations.

Lock Modes

InnoDB supports several lock modes, primarily Shared (S) and Exclusive (X) locks, along with Intention locks.

Shared Locks (S)

A shared lock allows multiple transactions to read the same resource simultaneously. Syntax: SELECT ... LOCK FOR SHARE Behavior:

1. Transaction A acquires a shared lock on a specific row. This succeeds.
2. Transaction B attempts to acquire a shared lock on the same row. This also succeeds, demonstrating compatibility between shared locks.
3. Transaction C attempts to acquire an exclusive lock on the same row. This request blocks until Transaction A releases its lock via commit or rollback. Rule: Shared locks are compatible with other shared locks but conflict with exclusive locks.

Exclusive Locks (X)

An exclusive lock ensures that only one transaction can modify or lock a resource at a time. Syntax: SELECT ... FOR UPDATE Behavior:

1. Transaction A obtains an exclusive lock on a row.
2. Transaction B attempts to obtain either a shared or exclusive lock on the same row. Both attempts will block. Rule: Exclusive locks are incompatible with both shared and other exclusive locks.

Lock Granularity

Locks can be applied at different levels:

• Row Lock: Restricts access to specific rows within a table.
• Table Lock: Restricts access to the entire table.

Intention Locks

Intention locks are table-level locks that signal a transaction's intent to acquire row-level locks within that table. They optimize performance by preventing the need to scan every row when checking for table-level lock conflicts.

• Intention Shared (IS): Indicates a transaction intends to place shared locks on rows.
• Intention Exclusive (IX): Indicates a transaction intends to place exclusive locks on rows.

Intention locks do not conflict with eachother. They only conflict with actual table-level locks (S or X).

Compatibility Matrix

Lock Algorithms

InnoDB utilizes specific algorithms to apply locks based on the query structure. Note: Row-level locks are only utilized when the query conditions involve indexed columns. If no index is used, InnoDB escalates to table locks.

1. Record Lock

This algorithm locks a specific index record. Condition: Triggered when querying via a Primary Key or Unique Index with an equality condition. Example:

SELECT * FROM product_stock WHERE sku_id = 101 FOR UPDATE;

This locks the specific row where sku_id is 101.

2. Gap Lock

Gap locks secure the range between index records, preventing other transactions from inserting new rows into that gap. This helps prevent phantom reads. The lock range is open on both ends, e.g., (1, 5). Condition: Typically occurs under the Repeatable Read isolation level when using secondary indexes or range queries. Scenarios:

• Range Query on Secondary Index:

  SELECT * FROM product_stock WHERE category_code = 'electronics' FOR UPDATE;
  

  InnoDB locks the gap around the matching records to prevent new inserts in that range.
• Unique Index with Multiple Rows: Even with a unique index, if the query matches multiple rows or uses a range, gap locks may apply.
• Multi-Column Unique Index: Queries using a prefix of a multi-column unique index may generate gap locks.

3. Next-Key Lock

A Next-Key Lock is a combination of a Record Lock and a Gap Lock. It locks the index record itself and the gap before it. The range is left-open and right-closed, e.g., (1, 5]. Purpose: This is the default locking strategy in Repeatable Read isolation to fully prevent phantom reads by ensuring no new records can appear in the scanned range during the transaction.