DEV Community

Cover image for MySQL Locks Explained: Preventing Deadlocks and Improving Performance
Kenta Takeuchi
Kenta Takeuchi

Posted on • Originally published at bmf-tech.com

MySQL Locks Explained: Preventing Deadlocks and Improving Performance

#mysql #transaction #lock

This article was originally published on bmf-tech.com.

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
  1. Start a transaction in TX2 and perform WRITE 
// TX2
mysql> START TRANSACTION;
mysql> UPDATE users SET name = 'bar' WHERE id = 1;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
  1. 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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
  1. Start a transaction in TX2 and perform WRITE 
// TX2
mysql> START TRANSACTION;
mysql> INSERT INTO users(id, name) VALUES(3, 'baz');
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
  1. Start a transaction in TX2 and perform WRITE 
// TX2
mysql> START TRANSACTION;
mysql> INSERT INTO users(id, name) VALUES(5, 'quux');
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)