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



  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. This ends to be engine independent. MyISAM and InnoDB and others recognize this.

TEST : make two connections, connection1 and 2.

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. When row locks occur, the following seems to happen.


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.