Understanding MySQL InnoDB Locks: Optimistic, Pessimistic, Shared, Exclusive, Row/Table Locks and Deadlock Handling

MySQL/InnoDB locking is a frequently asked interview topic; the article introduces how to maintain data integrity under high concurrency, how to investigate and resolve deadlocks, and demonstrates various lock types with practical SQL examples.

Optimistic Lock

Implemented by adding a numeric version column to a table; each read fetches the version, and updates increment the version. When committing, the current version is compared to the original; if they match, the update proceeds, otherwise the data is considered stale.

Example

Table design includes three fields: id, value, version.

select id, value, version from TABLE where id=#{id}

Update statement with optimistic lock:

update TABLE set value=2, version=version+1 where id=#{id} and version=#{version};

Pessimistic Lock

Pessimistic lock assumes a conflict will occur, so each operation acquires a lock before proceeding, similar to Java's synchronized. It is implemented by the database itself.

Shared lock and exclusive lock are two implementations of pessimistic locking.

Using Exclusive Lock (Example)

Disable MySQL autocommit to use explicit transactions: set autocommit=0; Typical transaction flow:

begin; -- start transaction
select status from TABLE where id=1 for update; -- lock row
insert into TABLE (id, value) values (2,2);
update TABLE set value=2 where id=1;
commit;

Shared Lock

Also called a read lock; multiple transactions can read the locked rows, but no transaction can modify them until all shared locks are released.

Example of acquiring a shared lock:

SELECT * FROM TABLE where id = 1 lock in share mode;

Attempting to update the same row while a shared lock is held will block or timeout unless the transaction is committed.

Exclusive Lock

Also known as a write lock; the transaction that holds the exclusive lock can read and write the row, while all other transactions are blocked from acquiring any lock on that row.

Use FOR UPDATE at the end of a SELECT statement to acquire an exclusive lock.

Row Lock

Row‑level locks can be either shared or exclusive and are applied to individual records, typically based on indexed columns. Non‑indexed queries fall back to table‑level locks.

Table Lock

InnoDB uses row locks when an index is used; otherwise, it locks the entire table.

Deadlock

A deadlock occurs when two or more transactions wait indefinitely for each other’s resources. The article lists two main ways to resolve deadlocks: manually killing blocking sessions or inspecting InnoDB internal tables.

Common commands for deadlock analysis:

show open tables where In_use > 0;

show processlist;

kill <span style="color:#F79557"><process_id></span>;

To view current transactions and locks:

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

Best practices to minimize deadlocks include accessing objects in a consistent order, keeping transactions short, using lower isolation levels, and using bound connections.