Overview

This post summarizes MySQL locks, assuming version 8.

Test Environment

The environment used for testing is prepared with docker-compose. (Although it's just one container, so you don't necessarily need to use compose...)

.
├── docker-compose.yml
└── initdb.d
    └── 1_schema.sql

docker-compose.yml

version: '3'

services:
  mysql:
    image: mysql:8.0.33
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: example
      TZ: "Asia/Tokyo"
    command: mysqld
    ports:
      - 3306:3306
    volumes:
      - ./initdb.d:/docker-entrypoint-initdb.d

1_schema.sql

CREATE DATABASE IF NOT EXISTS example;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(255) NOT NULL UNIQUE
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

You can set up a MySQL 8 container with docker compose up.

Locks

Internal Level Locks

In MySQL, there are row-level locks and table-level locks as methods of exclusive control.

cf. dev.mysql.com - 8.11.1 Internal Locking Methods

• Row-level locks
  • Locks targeting individual rows in a table
  • Narrow lock targets reduce lock contention and rollback changes
  • Allows long-term locking of a single row
• Table-level locks
  • Locks targeting the entire table
  • Requires relatively less memory (row locks need memory for each locked row or group of rows)
  • Fast when used for most of the table as only a single lock is needed
  • Fast when frequently executing GROUP BY on most of the data or scanning the entire table

InnoDB Locks

cf. dev.mysql.com - 15.7.1 InnoDB Locking

Shared (READ) Lock

A shared lock allows READ but not WRITE. Shared lock (IS).

Verification

1. Start a transaction in TX1 and apply a shared lock

// TX1
mysql> INSERT INTO users(name) VALUES('foo'); // Initial data input
mysql> START TRANSACTION;
mysql> SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

2. Start a transaction in TX2 and perform WRITE

// TX2
mysql> START TRANSACTION;
mysql> UPDATE users SET name = 'bar' WHERE id = 1;

TX2's update is locked until TX1 commits.

Exclusive (WRITE) Lock

An exclusive lock prevents both READ and WRITE. Exclusive lock (IX).

Verification

1. Start a transaction in TX1 and apply an exclusive lock

// TX1
mysql> INSERT INTO users(name) VALUES('foo'); // Initial data input
mysql> START TRANSACTION;
mysql> SELECT * FROM users WHERE id = 1 FOR UPDATE;

2. Start a transaction in TX2 and perform READ and WRITE

// TX2
mysql> START TRANSACTION;
mysql> SELECT * FROM users WHERE id = 1; // Allowed
mysql> SELECT * FROM users WHERE id = 1 FOR UPDATE; // Not allowed
mysql> UPDATE users SET name = 'bar' WHERE id = 1; // Not allowed

TX2 cannot perform READ (other than simple SELECT) or WRITE until TX1's lock is released.

Intention Locks

Table-level locks indicating the type of lock (shared or exclusive) a transaction requires on a table's rows. They support coexistence of row and table locks.

There are two types of intention locks:

• Intention shared lock
• Intention exclusive lock

Verification

Not explicitly operable via SQL and generally managed internally by the database, so verification is omitted.

Various verification patterns are available, as explored in the following article.

cf. qiita.com - Exploring MySQL Locks with Official Documentation

Record Locks

Locks on index records, which include clustered and secondary indexes. Locks the scanned indexes.

Verification

Omitted as it's an internal database operation.

Gap Locks

Locks the gaps between index records or before/after index records.

Verification

1. Start a transaction in TX1 and perform READ

// TX1
mysql> INSERT INTO users(id, name) VALUES(1, 'foo'), (2, 'bar'), (4, 'qux'), (5, 'quux'), (6, 'corge'); // Initial data input
mysql> START TRANSACTION;
mysql> SELECT * FROM users WHERE ID between 1 AND 5 FOR UPDATE;

2. Start a transaction in TX2 and perform WRITE

// TX2
mysql> START TRANSACTION;
mysql> INSERT INTO users(id, name) VALUES(3, 'baz');

It appears to be row-level locking, but it's confirmed to be range-locked.

Next-Key Locks

A combination of record locks on index records and gap locks on the gap before the index record.

Verification

1. Start a transaction in TX1 and perform READ

// TX1
mysql> INSERT INTO users(id, name) VALUES(1, 'foo'), (2, 'bar'), (3, 'baz'), (4, 'qux'); // Initial data input
mysql> START TRANSACTION;
mysql> SELECT * FROM users WHERE ID < 5 FOR UPDATE;

2. Start a transaction in TX2 and perform WRITE

// TX2
mysql> START TRANSACTION;
mysql> INSERT INTO users(id, name) VALUES(5, 'quux');

It's confirmed that not only rows with id less than 5 are locked, but also the gap after the row with the highest index value.

Insert Intention Locks

A type of gap lock set by an INSERT before inserting a row. Insert intention lock.

Verification

Omitted as it's an internal database operation.

Refer to this article for verification. cf. Exploring MySQL Locks with Official Documentation

AUTO-INC Locks

Table locks acquired by transactions inserting into a table with an AUTO_INCREMENT column. Prevents TX2 from acquiring AUTO_INCREMENT values while TX1 is acquiring them for INSERT.

Verification

Omitted due to internal operation and lack of reproduction method.

Predicate Locks for Spatial Indexes

Refer to the documentation. (I'm not familiar with spatial indexes, so I didn't fully understand...)

cf. Predicate Locks for Spatial Indexes

Checking Locks

Locks can be checked with the following queries.

// Check lock status
SELECT * FROM performance_schema.data_locks;

// Check lock count + thread ID
SHOW ENGINE INNODB STATUS;

// Check lock count
SELECT trx_id, trx_rows_locked, trx_mysql_thread_id FROM information_schema.INNODB_TRX;

To check for deadlocks, execute SHOW ENGINE INNODB STATUS and look for the section labeled LATEST DETECTED DEADLOCK.

Summary

MySQL has patterns of explicit and implicit locks.

It's beneficial to first focus on what is being locked (row or table) and the extent of the range.

References

• dev.mysql.com - 8.11.1 Internal Locking Methods
• dev.mysql.com - 15.7.1 InnoDB Locking
• zenn.dev - Basics of Database Locks to Deadlocks
• qiita.com - Exploring MySQL Locks with Official Documentation
• qiita.com - Supplement on MySQL Locks (Note: Already well-discussed content)
• qiita.com - Exploring MySQL Locks with Official Documentation
• qiita.com - Exploring MySQL Locks with Official Documentation
• techblog.cartaholdings.co.jp - Knowledge on Locks You Should Know
• www.wakuwakubank.com - Exclusive Lock (FOR UPDATE) and Shared Lock (LOCK IN SHARE MODE)
• saekis.hatenablog.com - Verifying MySQL Exclusive Lock Behavior
• bizstation.hatenablog.com - Detailed InnoDB Lock Control in MySQL/MariaDB and Transactd
• devsakaso.com - About MySQL Locks and Deadlocks
• nishinatoshiharu.com - Overview and Behavior Verification of InnoDB Shared and Exclusive Locks
• www.wakuwakubank.com - Exclusive Lock (FOR UPDATE) and Shared Lock (LOCK IN SHARE MODE)
• free-engineer.life - MySQL (InnoDB) Shared, Exclusive, and Intention Locks
• free-engineer.life - MySQL (InnoDB) Row Locks
• github.com - Investigation of InnoDB Lock Behavior in MySQL
• github.com - Deadlock Analysis Method Using Thread ID in MySQL