Understanding MySQL Locks: Types, Engines, and Deadlock Prevention

Why MySQL Needs Locks

Lock mechanisms manage concurrent access to shared resources, protecting data consistency and integrity during transactions. Without locks, concurrent updates can cause lost updates, as illustrated by a typical scenario where two transactions modify the same salary field, resulting in one update being overwritten.

| Transaction A | Transaction B |
|--|--|
| begin A | |
| | begin B |
| select salary from tb where id=1 (result 1000) | select salary from tb where id=1 (result 1000) |
| update tb set salary=1100 where id=1 | |
| | update tb set salary=1200 where id=1 |
| commit A | |
| | commit B |

The final salary becomes 1200 because Transaction B commits after A, overwriting A’s update; locks are needed to prevent this.

Lock Types

Table lock : low overhead, fast acquisition, no deadlocks, but coarse granularity leads to high lock‑conflict probability and low concurrency.

Row lock : higher overhead and slower acquisition, can cause deadlocks, but fine granularity reduces lock conflicts and improves concurrency.

MyISAM Storage Engine

Table‑level lock modes : shared read lock allows concurrent reads but blocks writes; exclusive write lock blocks both reads and writes, making all operations serial.

Concurrent Inserts : controlled by the concurrent_insert variable (0, 1, 2). Setting it to 2 permits concurrent inserts at the table tail regardless of gaps, useful for reducing lock contention.

Lock scheduling : write locks have higher priority than read locks. Low‑priority updates can be enabled via the low_priority_updates setting or the LOW_PRIORITY clause on DML statements. Adjusting max_write_lock_count can also balance read‑write conflicts.

InnoDB Storage Engine

Optimistic vs. Pessimistic Locks : Optimistic locks assume conflicts are rare and check version numbers at commit; pessimistic locks acquire exclusive locks before data access.

Row‑level lock types :

Shared lock (S lock): read lock allowing other transactions to read.

Exclusive lock (X lock): write lock preventing other reads or writes.

## Transaction 1
MariaDB [test]> show variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+

MariaDB [test]> begin;
MariaDB [test]> update tb1 set name="aaa" where id=1;
MariaDB [test]> commit;

## Transaction 2
MariaDB [test]> show variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+

MariaDB [test]> begin;
MariaDB [test]> update tb1 set name="haha" where id=1;
MariaDB [test]> rollback;
MariaDB [test]> select * from tb1 where id=1;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+

Implementation of InnoDB Row Locks : InnoDB locks index records, not whole rows. Row locks are used only when queries use an index; otherwise, a table lock is applied. Multiple indexes allow different transactions to lock different rows, but locking the same index key can cause conflicts.

Intention Locks :

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

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

To view InnoDB lock information:

MariaDB [test]> show engine innodb status\G;
MariaDB [test]> show full processlist;
MariaDB [test]> select * from information_schema.innodb_trx\G;
MariaDB [test]> select * from information_schema.innodb_locks\G;
MariaDB [test]> select * from information_schema.innbrd_lock_waits\G;

Consistent Non‑Locking Read

InnoDB uses multi‑version concurrency control (MVCC) to read a snapshot of a row without waiting for locks. The snapshot is created from the undo log and incurs no extra cost. Under REPEATABLE READ, the snapshot reflects the data as of the transaction start.

Consistent Locking Read

Explicit locking reads ensure logical consistency: SELECT ... FOR UPDATE: acquires an X lock on selected rows. SELECT ... LOCK IN SHARE MODE: acquires an S lock, allowing other shared locks but blocking exclusive locks.

## Transaction 1
MariaDB [test]> begin;
MariaDB [test]> select * from tb1 where id=1 for update;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
MariaDB [test]> rollback;

## Transaction 2
MariaDB [test]> begin;
MariaDB [test]> select * from tb1 where id=1 lock in share mode;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
MariaDB [test]> rollback;

Lock Algorithms

Deadlocks

A deadlock occurs when two or more transactions wait for each other’s resources. MyISAM is deadlock‑free because it acquires all needed locks at once. InnoDB can deadlock because locks are acquired incrementally; the engine usually detects and rolls back one transaction automatically. Setting innodb_lock_wait_timeout helps avoid long‑lasting lock waits, but does not solve deadlocks themselves.

## Transaction 1
MariaDB [test]> begin;
MariaDB [test]> update tb1 set name="jyy" where id=1;
MariaDB [test]> update tb1 set name="xixi" where id=2;
MariaDB [test]> commit;
MariaDB [test]> select * from tb1 where id in(1,2);
+----+------+
| id | name |
+----+------+
|  1 | jyy  |
|  2 | xixi |
+----+------+

## Transaction 2
MariaDB [test]> begin;
MariaDB [test]> update tb1 set name="haha" where id=2;
MariaDB [test]> update tb1 set name="heihei" where id=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Common Deadlock Prevention Methods :

Access tables in a consistent order across all applications.

Sort data before batch processing so each thread handles records in the same sequence.

Acquire the appropriate lock level (e.g., exclusive) directly when updating records.

Use READ COMMITTED isolation instead of REPEATABLE READ for patterns that may cause lock‑wait cycles.

When using SELECT ... FOR UPDATE under READ COMMITTED, ensure only one thread attempts the insert after a missing record check.

Analyze deadlock information via SHOW ENGINE INNODB STATUS or related queries to identify and fix the root cause.