Master MySQL Locking: Table vs Row Locks, InnoDB Algorithms & Transaction Isolation

1. What Is a Lock

In database systems, locks protect shared resources to ensure data integrity and consistency during concurrent access, such as operations on the buffer pool LRU list.

2. InnoDB Lock Types

InnoDB implements two primary lock granularities:

Table‑level lock : simple, fast to acquire and release, prevents deadlocks but can cause high contention.

Row‑level lock : fine‑grained, reduces contention and improves concurrency, but acquisition is costlier and deadlocks are more likely.

InnoDB also defines standard row‑level lock modes:

Shared lock (S) : allows reading but not modifying the row.

Exclusive lock (X) : permits both reading and modifying the row.

To support mixed granularity, InnoDB uses intention locks :

Intention Shared (IS) : transaction intends to acquire shared locks on some rows.

Intention Exclusive (IX) : transaction intends to acquire exclusive locks on some rows.

3. Lock Algorithms

InnoDB provides several lock algorithms:

Record Lock : locks a single index record; if no index exists, a hidden clustered index is used.

Gap Lock : locks the interval between index records, preventing inserts into the gap (used for phantom‑read protection).

Next‑Key Lock : combination of record lock and gap lock; locks the record and the preceding gap.

Insert Intention Lock : a special gap lock set before inserting a row, allowing concurrent inserts into different gaps.

Example of a record lock:

create table t (c1 int primary key);
insert into t select 1;
insert into t select 3;
insert into t select 10;

Concurrent sessions attempting conflicting updates will encounter lock wait timeouts:

# Session A
start transaction;
update t set c1=12 where c1 = 10;
# Session B
update t set c1=11 where c1=10; -- ERROR 1205 (HY000): Lock wait timeout exceeded

Gap lock example (range condition):

create table keme1 (id int primary key, name varchar(10));
insert into keme1 values (1,'a'),(3,'c'),(4,'d'),(5,'e'),(6,'f');
-- Session 1
start transaction;
update keme1 set name='bb' where id between 1 and 3; -- acquires gap lock
-- Session 2
start transaction;
insert into keme1 values (2,'bb'); -- waits for gap lock

4. Lock‑Related Issues

Locks enable transaction isolation but introduce three classic problems:

Dirty read : reading uncommitted changes from another transaction.

Non‑repeatable read : the same query returns different results within a transaction because another transaction modified the data.

Lost update : two concurrent transactions overwrite each other's changes without proper synchronization.

Deadlocks occur when two or more transactions wait for each other’s locks. MySQL resolves deadlocks by rolling back one victim transaction. Example:

# Create table
define temp(id int primary key, name varchar(10));
insert into temp values (1,'a'),(2,'b'),(3,'c');
-- Transaction 1
start transaction;
update temp set name='aa' where id=1;
-- Transaction 2
start transaction;
update temp set name='bb' where id=2;
-- Transaction 1 attempts to lock id=2
update temp set name='aaa' where id=2; -- waits
-- Transaction 2 attempts to lock id=1
update temp set name='bbb' where id=1; -- deadlock detected

To reduce deadlocks, keep transactions short, commit or rollback promptly, access tables in a consistent order, and use appropriate indexes.

5. Transactions

MySQL transactions follow the ACID properties:

Atomicity : all statements succeed or all are rolled back.

Consistency : data remains valid according to constraints.

Isolation : achieved through locking and MVCC.

Durability : committed changes survive crashes via redo logs.

Key transaction statements:

START TRANSACTION | BEGIN

COMMIT

ROLLBACK

SAVEPOINT name

ROLLBACK TO SAVEPOINT name

SET TRANSACTION ISOLATION LEVEL ...

Isolation levels supported by InnoDB:

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ (default)

SERIALIZABLE

Example of setting the isolation level:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

6. Log Management and Group Commit

InnoDB uses redo and undo logs. Redo logs ensure durability; undo logs enable rollback and MVCC. The parameter innodb_flush_log_at_trx_commit controls when redo logs are flushed (values 0, 1, 2).

Group commit batches multiple transaction commits into a single fsync operation, reducing disk I/O and improving throughput, especially in MySQL 5.6 and later.

Two‑phase commit flow (simplified):

InnoDB Prepare – generate redo/undo in memory.

Binlog Flush – write binlog to OS cache.

Binlog Sync – fsync binlog to disk.

InnoDB Commit – flush redo log according to innodb_flush_log_at_trx_commit.

7. Bad Transaction Practices

Committing inside loops creates many small transactions, causing excessive redo‑log writes. A better approach is to start a single transaction, perform all inserts/updates, then commit once.

# Inefficient loop commit
START TRANSACTION;
INSERT ...;
COMMIT; -- repeated many times

# Efficient batch
START TRANSACTION;
INSERT ...;
INSERT ...;
...;
COMMIT;

Long‑running transactions (e.g., bulk account‑balance updates) hold locks for extended periods, increasing contention and rollback cost. Splitting such work into smaller batches mitigates these issues.

8. Summary

Understanding MySQL's lock hierarchy, lock modes, and transaction mechanics is essential for building high‑performance, reliable applications. Proper use of table‑ and row‑level locks, intention locks, and isolation levels, combined with disciplined transaction design and awareness of group‑commit behavior, helps avoid deadlocks, phantom reads, and performance bottlenecks.