MySQL InnoDB synthetic lock

mysql_lock.md

Table-level lock type compatibility is summarized in the following matrix

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

• A shared (S) lock permits the transaction that holds the lock to read a row.
• An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.
• An intention shared lock (IS) indicates that a transaction intends to set a shared lock on individual rows in a table.
• An intention exclusive lock (IX) indicates that a transaction intends to set an exclusive lock on individual rows in a table.

---

№1 Lock by updating cross tables

Transaction 1

# 1 > Start a first transaction
begin;
# 3 > Get a X-lock on a row of a first table
update Account a set a.active = 1 where a.id = 2;
# 6 > Try to get a X-lock on a row of the second table but acquire a dead lock at the first or second transaction either 
update AccountBonus ab set ab.amount = 100 where ab.id = 1;
# 7 > Commit changes if the deadlock was acquire at the second transaction
commit;

Transaction 2

# 2 > Start a second transaction
begin;
# 4 > Get a X-lock on a row of a second table
update AccountBonus ab set ab.amount = 100 where ab.id = 1;
# 5 > Try to get a X-lock on a row of the first table but acquire a wait lock
update Account a set a.active = 0 where a.id = 2;
# 7 > Commit changes if the deadlock was acquire at the first transaction
commit;

№2 Lock by updating a row with the shared lock

Transaction 1

# 1 > Start a first transaction
begin;
# 3 > Get a S-lock on a row of a table
select * from Account a where a.id = 2 for share;
# 5 > Try to get a X-lock on the same row of the table but acquire a wait lock
update Account a set a.active = 1 where id = 2;
# 7 > Do commit because the second transaction released the S-lock and the update was done on the fifth step.
commit;

Transaction 2

# 2 > Start a second transaction
begin;
# 4 > Get a S-lock on the same row of a table
select * from Account a where a.id = 2 for share;
# 6 > Try to get a X-lock on the same row of the table but acquire a dead lock
update Account a set a.active = 1 where a.id = 2;
# There is no commit because the dead lock is happened here
# commit;

№3 Lock by inserting a row at a same index gap then a rollback.

Insert Intention Gap Lock

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row.

Locks Set by Different SQL Statements in InnoDB

Schema

create table Account (
    id bigint primary key,
    userId bigint,
    currency char(3),
    unique index uniqUserIdCurrency(userId, currency)
) engine = InnoDB;

Transaction 1

# 1 > start a first transaction
begin;
# 2 > Get "insert intention gap lock" on a gap then immediately get the X-lock on a inserting record.
insert into Account values(uuid_short(), '123', 'USD');
# 7 > The transaction is rollbacked therefore the X-lock is released
rollback;

Transaction 2

# 3 > start a second transaction
begin;
# 4 > Get "insert intention gap lock" on a gap index then a duplicate-key error occurs on a inserting record.
# Cause is "insert intention gap lock" shares information about changes of the index gap even before any commits in that gap.
# The duplicate-key error occurs thus try to get the S-lock on a inserting record.
insert into Account values(uuid_short(), '123', 'USD');
# 8 > The S-lock is get because the X-lock was released and immediately try to get the IX-lock (intention exclusive lock) on a inserting record.
# It's going to a deadlock because the third transaction have already the IS-lock (intention shared lock).
# 9 > Commit changes if the deadlock was acquire at the third transaction
commit;

Transaction 3

# 5 > start a third transaction
begin;
# 6 > Get "insert intention gap lock" on a gap index then a duplicate-key error occurs on a inserting record.
# Cause is "insert intention gap lock" shares information about changes of the index gap even before any commits in that gap.
# The duplicate-key error occurs thus try to get the S-lock on a inserting record.
insert into Account values(uuid_short(), '123', 'USD');
# 8 > The S-lock is get because the X-lock was released and immediately try to get the IX-lock (intention exclusive lock) on a inserting record.
# It's going to a deadlock because the second transaction have already the IS-lock (intention shared lock).
# 9 > Commit changes if the deadlock was acquire at the second transaction
commit;