OdenData

MySQL: InnoDB Locks

by Mark Nielsen
Copyright August 2023

In time, we will adding more sections.

1. Links
2. Setup
3. Table locks
4. Row level locks: Shared and Exclusive
5. Intention locks
6. Other locks
7. Lock Functions

---

Links

• https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
• https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html
• https://dev.mysql.com/doc/refman/8.0/en/internal-locking.html
• https://severalnines.com/blog/understanding-lock-granularity-mysql/ https://simon-ninon.medium.com/dont-break-production-learn-about-mysql-locks-297671ec8e73
• https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html
• https://tkstoneblog.wordpress.com/2019/06/27/mysql-gap-lock-next-key-lock-by-example/
• https://devpress.csdn.net/mysqldb/62ebe8ab89d9027116a0fb9d.html
• https://minervadb.com/index.php/2018/02/12/how-innodb-handles-locks/
• https://dev.mysql.com/doc/refman/8.0/en/performance-schema-data-locks-table.html

---

Setup

  create database if not exists mark;
use mark
drop table if exists test1;

create table test1 ( i int, PRIMARY KEY (i));

insert into  test1 values (1),(2),(3),(4);

---

Table Locks

Table level locks are read or write.

• A table with read lock allows other connections to read rows from the table. Everything else will be locked until the table lock is released.
• A write lock locks everything. Everything is locked from reading, writing, or altering the table until the table lock is released.

This ends to be engine independent. MyISAM and InnoDB and others recognize this.

TEST : make two connections, connection1 and 2.

• conn1: lock tables test1 read;
• conn2: select * from test1;
  This should work.
• conn2 : insert into test1 values (5);
  This should hang until conn1 releases the lock.
• conn1 : unlock tables;
  When this happens, conn2 should be able to finish the insert.
• conn1: lock tables test1 write;
  This will lock everything from happening on all other connections.
• conn2: select * from test1;
  This will hang until conn1 releases the lock. Inserts will also stall.
• conn1 : unlock tables;
  conn2 select will finish, or any other command on the table.

---

Row Locks

We will be talking only about the Innodb engine. Row level locks come in read and write. Similar to table locks, row level locks are for specific rows. Read locks on rows allows other connections to read the rows. Write row locks prevent all action on the rows, including DDL changes on the table.

• In read mode, specified rows are locked into read only mode.
  ex: select * from name where name_id = 10 for share
• In write mode, all actions on the rows are blocked.
  ex: select * from name where name_id = 10 for update

When row locks occur, the following seems to happen.

• An intention lock is requested, IS for read, IX for write.
• If rejected, query fails.
• If accepted, a read lock is granted S, or a write lock is granted X.
• For writes, GAP and Next-locks, or insert intention locks are made. Insert intention locks might be made earlier.
• Query finishes.

---

Intention

Intention locks are internal. Intention locks are locks a query will need. The system then calculates if it can get the final locks.
Intention Share locks (read locks) are IS.
Intention SELECT RELEASE_LOCK('lock1'); -- now other connections or applications connections will work that try to use the same named lock.

---