innodb notes

Below are some notes regarding the innodb transactions and locks. Most of them are just a copy-paste from innodb docs with my notes. Also there are some examples of custom-maid deadlocks.

In the examples of MySQL operations different sessions are marked as (1) and (2), for example:

create table child (id int(11) NOT NULL) ENGINE=InnoDB;
- common operation, do it from any session
(1) start transaction;
- do it in session #1
(2) start transaction;
- do it in session #2

To get two (or more) sessions just launch several instances of mysql in different console windows. Most of information about deadlocks can be retrieved by SHOW ENGINE INNODB STATUS statement. Its output can be improved by creating special tables:

CREATE TABLE innodb_monitor(a int) ENGINE=INNODB;
CREATE TABLE innodb_lock_monitor(a int) ENGINE=INNODB;

transaction isolation levels

REPEATABLE READ (default)
- in transaction sequential selects will get the same data
READ COMMITTED
- in transaction sequential selects can get different (new) data
READ UNCOMMITTED
- dirty read, read even not commited data
SERIALIZABLE
- all SELECTSs are done as SELECT … LOCK IN SHARE MODE
- so no one can modify data we selected (all selected data becomes read-only)

lock modes

S (shared)
- row level, others can get S, IS (select data, but not modify it)
X (exclusive)
- row level, others can do nothing
IS (Intention shared)
- table-level, others can S, IS, IX (can not modify record, but can do IX)
IX (Intention exclusive)
- table-level, others can IS, IX

Note: information about IS/IX locks is (at least for me) a bit confusing. Especially following statement was very unclear:

"For example, SELECT ... LOCK IN SHARE MODE sets an IS lock and SELECT ... FOR UPDATE sets an IX lock."

While in other chapter: “SELECT … LOCK IN SHARE MODE sets a shared mode lock on any rows that are read… (S-lock)” and “SELECT … FOR UPDATE locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. … (X-lock)”

Actually it seems that innodb set both IS and S (or IX and X) where IS is set on the table and S is set on the row, see wikipedia and blog post.

Example 1. Insert a new row into a table child, and make sure that the child row has a parent row in table parent:

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
Now you can safely add the child record to the CHILD table and commit the transaction.
Any transaction that tries to read or write to the applicable row in the PARENT table waits until you are finished, that is, the data in all tables is in a consistent state.

Example 2. Consider an integer counter field in a table CHILD_CODES, used to assign a unique identifier to each child added to table CHILD:

We can not use here:
- SELECT - two users could see the same value for the counter, and a duplicate-key error occurs if two transactions attempt to add rows with the same identifier to the CHILD table.
- SELECT ... LOCK IN SHARE MODE - if wo users read the counter at the same time, at least one of them ends up in deadlock when it attempts to update the counter.
Solution 1:
- First update the counter by incrementing it by 1, then read it and use the new value in the CHILD table. Any other transaction that tries to read the counter waits until your transaction commits. If another transaction is in the middle of this same sequence, your transaction waits until the other one commits.
  - (update will set X-lock)
Solution 2:
- First perform a locking read of the counter using FOR UPDATE, and then increment the counter:
- SELECT counter_field FROM child_codes FOR UPDATE;
  - (read + set X-lock)
- UPDATE child_codes SET counter_field = counter_field + 1;
Both solutions are only examples for SELECT ... FOR UPDATE. In MySQL, the specific task of generating a unique identifier actually can be accomplished using only a single access to the table:
- UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
- here “LAST_INSERT_ID(counter_field + 1)” makes the following “LAST_INSERT_ID()” to return an inserted value (counter_field + 1), so we can know it and use in other statements

Record, Gap, and Next-Key Locks

Record lock
- This is a lock on an index record.
- It lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.
Gap lock
- This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.
Next-key lock
- This is a combination of a record lock on the index record and a gap lock on the gap before the index record.
- This lock type is used by default in REPEATABLE READ isolation level and prevents selecting phantom rows.
- If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.
- Suppose that an index contains the values 10, 15 and 20. The possible next-key locks for this index cover the following intervals, where ( or ) denote exclusion of the interval endpoint and [ or ] denote inclusion of the endpoint:
  - (negative infinity, 10]
  - (10, 15]
  - (15, 20]
  - (20, positive infinity)
- For the last interval, the next-key lock locks the gap above the largest value in the index and the supremum pseudo-record having a value higher than any value actually in the index.
- The ‘supremum’ is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value.
  - Note: there is no much information about “supremum”, but it acts as always existing record at the end of the table
  - Example:
    - create table child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
    - insert into child (id) values (90),(102);
      - (1) start transaction;
      - (1) select * from child where id > 100 for update;
      - (2) start transaction;
      - (2) insert into child (id) values (101);
        wait
    - The select above blocked record with id=102, gap before record 102 and also gap before record supremum, so other sessions can not insert something after the record 102 and will wait.
- Gap locking is not needed for statements that lock rows using a unique index to search for a unique row.
  - This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.
  - For example, if the id column has a unique index, the following statement uses only an index-record lock for the row having id value 100 and it does not matter whether other sessions insert rows in the preceding gap:
    - SELECT * FROM child WHERE id = 100;
    - If id is not indexed or has a nonunique index, the statement does lock the preceding gap.
- INSERT operations set a special gap lock called isertion intention gap lock prior to row insertion.
  - 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.
- Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

Locks and statements

A locking read (SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE), an UPDATE, or a DELETE
- next-key locks (index record + gap before, take into account there is “supremum” record)
  - Note: next-key locks are used by default is a default. This can be changed by setting a transaction isolation level or with innodb settings
- It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.
- If a secondary index is used in a search and index record locks to be set are exclusive (X), InnoDB also retrieves the corresponding clustered index records and sets locks on them.
- If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily scan many rows.
  - while here: Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.
  - Does it mean that hidden index is used only for record locks, but not in the case of next-key locks?
  - Check it:
    - create table child (id int(11) NOT NULL) ENGINE=InnoDB;
    - insert into child (id) values (90),(102);
    - (1) start transaction;
    - (1) select * from child where id > 100 for update;
    - (2) start transaction;
    - (2) insert into child (id) values (80);
      - wait
    - so it seems that whole table was locked
SELECT ... FROM
- consistent read, no locks
SELECT ... FROM ... LOCK IN SHARE MODE
- sets shared next-key locks on all index records the search encounters.
SELECT ... FROM ... FOR UPDATE
- blocks other sessions from doing SELECT ... FROM ... LOCK IN SHARE MODE or from reading in certain transaction isolation levels. Consistent reads will ignore any locks set on the records that exist in the read view.
- Note: it blocks everything except plain SELECT
UPDATE ... WHERE ...
- sets an exclusive next-key lock on every record the search encounters.
DELETE FROM ... WHERE ...
- sets an exclusive next-key lock on every record the search encounters.
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, an insertion 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.
- Check it:
  - create table child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
  - insert into child (id) values (90),(102);
  - (1) start transaction;
  - (1) insert into child (id) values (93);
    - insert intention lock on gap 90-102
    - X-lock on 93
  - (2) start transaction;
  - (2) insert into child (id) values (97);
    - insert intention lock on gap 90-102
    - X-lock on 97
    - no wait
  - both inserted into the same gap whithout waiting
  - –
  - (1) start transaction;
  - (1) delete from child where id > 100;
    - X `lock on gap 90-100 (gap before 100), 100, gap 100-supremum (“supremum” > 100 and fits to condition in ‘where’)
  - (2) start transaction;
  - (2) insert into child (id) values (105);
    - insert intention on gap 100-supremum;
    - wait
  - (1) insert into child (id) values (107);
    - OK
  - (1) commit;
    - (2) OK
- If a duplicate-key error occurs, a shared lock on the duplicate index record is set.
  - no error returned now because because other transaction can be rolled back, so it will be no error for current transaction
  - 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.
  - see deadlock example1 and example2
INSERT ... ON DUPLICATE KEY UPDATE
- differs from a simple INSERT in that an exclusive next-key lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs.
REPLACE
- is done like an INSERT if there is no collision on a unique key. Otherwise, an exclusive next-key lock is placed on the row to be replaced.
INSERT INTO T SELECT ... FROM S WHERE ...
- sets an exclusive index record without a gap lock on each row inserted into T, sets shared next-key locks on rows from S
CREATE TABLE ... SELECT ...
- performs the SELECT with shared next-key locks or as a consistent read, as for INSERT ... SELECT.
When a SELECT is used in the constructs REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB sets shared next-key locks on rows from table s.
While initializing a previously specified AUTO_INCREMENT column on a table, InnoDB sets an exclusive lock on the end of the index associated with the AUTO_INCREMENT column.
In accessing the auto-increment counter, InnoDB uses a specific AUTO-INC table lock mode where the lock lasts only to the end of the current SQL statement, not to the end of the entire transaction. Other sessions cannot insert into the table while the AUTO-INC table lock is held;
If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint. InnoDB also sets these locks in the case where the constraint fails.
LOCK TABLES sets table locks, but it is the higher MySQL layer above the InnoDB layer that sets these locks.InnoDB is aware of table locks if innodb_table_locks = 1 (the default) and autocommit = 0, and the MySQL layer above InnoDB knows about row-level locks.
- Otherwise, InnoDB’s automatic deadlock detection cannot detect deadlocks where such table locks are involved. Also, because in this case the higher MySQL layer does not know about row-level locks, it is possible to get a table lock on a table where another session currently has row-level locks. However, this does not endanger transaction integrity, see here and here.

Deadlock Detection and Rollback

InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock.
- InnoDB tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted, updated, or deleted.
InnoDB is aware of table locks if innodb_table_locks = 1 (the default) and autocommit = 0, and the MySQL layer above it knows about row-level locks. Otherwise, InnoDB cannot detect deadlocks where a table lock set by a MySQL LOCK TABLES statement or a lock set by a storage engine other than InnoDB is involved. Resolve these situations by setting the value of the innodb_lock_wait_timeout system variable.
When InnoDB performs a complete rollback of a transaction, all locks set by the transaction are released.
- However, if just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved.
- This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.
- Note: statement above looks a bit strange.
If a SELECT calls a stored function in a transaction, and a statement within the function fails, that statement rolls back. Furthermore, if ROLLBACK is executed after that, the entire transaction rolls back.

How to Cope with Deadlocks

InnoDB uses automatic row-level locking.
- You can get deadlocks even in the case of transactions that just insert or delete a single row. That is because these operations are not really “atomic”; they automatically set locks on the (possibly several) index records of the row inserted or deleted.
Techniques:
- Use SHOW ENGINE INNODB STATUS to determine the cause of the latest deadlock. That can help you to tune your application to avoid deadlocks.
  - This is most important tool to resolve deadlocks. See also how to enable additional lock monitor output
- Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.
  - Note: Usually it is easy to say so, but not so easy to do.
- Commit your transactions often. Small transactions are less prone to collision.
  - Note: Yes, take this into account, but often having a long transaction is necessary to keep the system in a consistent state.
- If you are using locking reads (SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE), try using a lower isolation level such as READ COMMITTED.
  - Note: this depends on situation. Take into account that READ COMMITED is less safe and can you can get phantom records because next-key locking is not used in this case.
- Access your tables and rows in a fixed order. Then transactions form well-defined queues and do not deadlock.
  - Note: See deadlock example 3 and 4 - these are produced with data access in a different order
- Add well-chosen indexes to your tables. Then your queries need to scan fewer index records and consequently set fewer locks. Use EXPLAIN SELECT to determine which indexes the MySQL server regards as the most appropriate for your queries.
- Use less locking.
  - If you can afford to permit a SELECT to return data from an old snapshot, do not add the clause FOR UPDATE or LOCK IN SHARE MODE to it. Using the READ COMMITTED isolation level is good here, because each consistent read within the same transaction reads from its own fresh snapshot.
- If nothing else helps, serialize your transactions with table-level locks.
  - The correct way to use LOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly.
  - For example, if you need to write to table t1 and read from table t2, you can do this:
    - SET autocommit=0;
    - LOCK TABLES t1 WRITE, t2 READ, ...; … do something with tables t1 and t2 here …
    - COMMIT;
    - UNLOCK TABLES;
  - Table-level locks make your transactions queue nicely and avoid deadlocks.
- Create an auxiliary “semaphore” table that contains just a single row. Have each transaction update that row before accessing other tables. In that way, all transactions happen in a serial fashion.

Deadlock examples

Example 1 (deadlock on insert):

create table t1 (i int, primary key (i)) engine = InnoDB;
(1) start transaction;
(1) insert into t1 values(1);
- X lock
(2) start transaction;
(2) insert into t1 values(1);
- duplicate-key error -> S lock
(3) start transaction;
(3) insert into t1 values(1);
- duplicate-key error -> S lock
(1) rollback;
(2) & (3) - deadlock - both hold S-lock and can not get X-lock

Example 2 (deadlock on delete / insert):

create table t1 (i int, primary key (i)) engine = InnoDB;
(1) start transaction;
(1) delete from t1 where i = 1;
- X lock
(2) start transaction;
(2) insert into t1 values(1);
- duplicate-key error -> S lock
(3) start transaction;
(3) insert into t1 values(1);
- duplicate-key error -> S lock
(1) rollback;
(2) & (3) - deadlock - both hold S-lock and can not get X-lock

Example 3 (deadlock on access in different order):

Example is from this blog post. But I was unable to reproduce a deadlock with example from the post (maybe because mysql improved since then).

Here is original case:

create table vegetable (id bigint(10) NOT NULL auto_increment, name varchar(255) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_name (name)) ENGINE=InnoDB;
insert into vegetable (id, name) values (10, "ggg"), (5, "jjj");
(1) start transaction;
(1) insert into vegetable values(null, "ppp");
- expected: lock the gap between “jjj” and “ppp”
- actually: insert intention lock / X-row lock
(2) `start transaction;
(2) insert into vegetable values(null, "iii");
- expected: locking the gap after “ggg”, upto “iii”
- actually: insert intention lock / X-row lock
(2) insert into vegetable values (null, "mmm");
- expected: lock the gap after “jjj” upto “mmm”. Since connection 1 has a lock between “jjj” and “ppp”, effectively spanning the lock connection 2 is attempting to take, this will block
- actually: no block, OK
(1) insert into vegetable values (null, "hhh");
- expected: This requires the gap lock between “ggg” and “hhh”. This will block as it spans the the lock [“ggg” to “iii”] held by connection 2.
- actually: OK OK because insert do not do next-key blocking, but sets insert intention lock, so such inserts work ok.

Modified example with deadlock - inserts are combined with updates which actually do next-key locking:

create table vegetable (id bigint(10) NOT NULL auto_increment, name varchar(255) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_name (name)) ENGINE=InnoDB;
insert into vegetable (id, name) values (10, "ggg"), (5, "jjj");
(1) start transaction;
(1) update vegetable set name="jjj1" where name > 'jjj';
- lock the gap between “jjj” and “supremum” (pseudo record beyond the table end)
(2) start transaction;
(2) update vegetable set name="ggg1" where name < 'jjj';
- locking the gap after “ggg”, upto “jjj”
(2) insert into vegetable values (null, "mmm");
- insert intention lock - gap after “jjj”, wait for lock set by (1)
- wait
(1) insert into vegetable values (null, "hhh");
- insert intention lock - gap after “ggg”, wait for lock set by (2)
- deadlock

Deadlock info:

show engine innodb status\G;
------------------------
LATEST DETECTED DEADLOCK
------------------------
120924 13:14:03
*** (1) TRANSACTION:
TRANSACTION 3EDB62, ACTIVE 13 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 320, 6 row lock(s), undo log entries 2
MySQL thread id 50, OS thread handle 0xa6a9db40, query id 512 localhost root update
insert into vegetable values (null, "mmm")
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1476 n bits 80 index `uk_name` of table `test_dl`.`vegetable` trx id 3EDB62 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 3EDB61, ACTIVE 22 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, 2 row lock(s), undo log entries 1
MySQL thread id 51, OS thread handle 0xa6aceb40, query id 513 localhost root update
insert into vegetable values (null, "hhh")
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1476 n bits 80 index `uk_name` of table `test_dl`.`vegetable` trx id 3EDB61 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1476 n bits 80 index `uk_name` of table `test_dl`.`vegetable` trx id 3EDB61 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 6a6a6a; asc jjj;;
1: len 8; hex 8000000000000005; asc         ;;

*** WE ROLL BACK TRANSACTION (2)

Example 4 (deadlock on access in different order):

CREATE TABLE ... SELECT conflicts with INSERT and causes a deadlock because of opposite order of data
(1) INSERT a record at the end, got X-lock
(2) CREATE TABLE ... SELECT scans the table from the beginning and sets S-locks on all records. When it reaches the last record it waits.
(1) INSERT a record in the middle - try to get X-lock, but there is an S-lock set by (2) which waits for (1)
- deadlock

Example 5 (deadlock on access in different order with complex primary key):

create table complex (id1 int(11) not null, id2 int(11) not null, primary key (id1,id2)) engine=InnoDB;
insert into complex (id1, id2) values (90, 5),(90,7),(90,9),(102, 5),(102,7),(102,9);
(1) start transaction;
(1) delete from complex where id1 = 102;

Now (1) locks gap before 102/5 and records up to 102/9:

+-----+-----+
| id1 | id2 |
+-----+-----+
|  90 |   5 |
|  90 |   7 |
|  90 |   9 |   ___
|     |     |      |
| 102 |   5 |      |
| 102 |   7 |      |  (1)
| 102 |   9 |  ____|
+-----+-----+

(2) start transaction;
(2) delete from complex where id1 = 90;

Now (2) locks records until 102/5. Because key is complex it seems that gap after 90/9 is also locked when table was scanned by delete operation.

+-----+-----+
| id1 | id2 |
+-----+-----+ ___
|  90 |   5 |    |
|  90 |   7 |    |  (2)
|  90 |   9 |    |
|     |     | ___|
| 102 |   5 |
| 102 |   7 |
| 102 |   9 |
+-----+-----+

So now the gap between 90/9 and 102/5 is not available for both sessions:

(1) insert into complex (id1, id2) values (102,1);
- insert intension on gap between 90/9 and 102/5;
- wait because it is hold by (2)
- while insertion of (102,5) would be OK now
(2) insert into complex (id1, id2) values (90,10);
- insert intension on gap between 90 and 102;
- conflicts with lock hold by (1) which starts with 90/9
- now (2) continues and (1) aborted with deadlock:
(1) ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Deadlock info:

show engine innodb status/G;
------------------------
LATEST DETECTED DEADLOCK
------------------------
120924 12:15:44
*** (1) TRANSACTION:
TRANSACTION 3EDB4B, ACTIVE 25 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 320, 5 row lock(s), undo log entries 3
MySQL thread id 51, OS thread handle 0xa6aceb40, query id 440 localhost root update
insert into complex (id1, id2) values (102,1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1472 n bits 80 index `PRIMARY` of table `test_dl`.`complex` trx id 3EDB4B lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 80000066; asc    f;;
1: len 4; hex 80000005; asc     ;;
2: len 6; hex 0000003edb4b; asc    > K;;
3: len 7; hex 3c000005e61ccd; asc <      ;;

*** (2) TRANSACTION:
TRANSACTION 3EDB4C, ACTIVE 16 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 320, 5 row lock(s), undo log entries 3
MySQL thread id 50, OS thread handle 0xa6a9db40, query id 441 localhost root update
insert into complex (id1, id2) values (90,10)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1472 n bits 80 index `PRIMARY` of table `test_dl`.`complex` trx id 3EDB4C lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 80000066; asc    f;;
1: len 4; hex 80000005; asc     ;;
2: len 6; hex 0000003edb4b; asc    > K;;
3: len 7; hex 3c000005e61ccd; asc <      ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1472 n bits 80 index `PRIMARY` of table `test_dl`.`complex` trx id 3EDB4C lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 80000066; asc    f;;
1: len 4; hex 80000005; asc     ;;
2: len 6; hex 0000003edb4b; asc    > K;;
3: len 7; hex 3c000005e61ccd; asc <      ;;

*** WE ROLL BACK TRANSACTION (1)

(1) failed to insert (102,1) while it could insert (102,5) without any problem. In this case can be not that easy to re-order statements to avoid a deadlock. Possible solution can be first insert new records and only then do delete:

(1) start transaction;
(1) insert into complex (id1, id2) values (102,1);
(2) start transaction;
(2) insert into complex (id1, id2) values (90,10);
(1) delete from complex where id1 = 102 and id2 not in (1);
- X-lock on 102 and gap before it
(2) delete from complex where id1 = 90 and id2 not in (10);
- wait
(1) commit;
(2) commit;

Example 6 (deadlock in empty table):

See initial post and explanation.

create table T (C int not null primary key) engine=InnoDB;
(1) start transaction;
(1) select * from T where C = 42 for update;
- gap lock … supremum (whole table)
(2) start transaction;
(2) select * from T where C = 42 for update;
- gap lock … supremum (whole table)
- why it does not wait? It seems that it gets a lock for the same gap.
(1) insert into T set C = 42;
- insert intention
- wait for gap lock (2)
(2) insert into T set C = 42;
- insert intention
- wait for gap lock (1)
- deadlock

Deadlock info:

------------------------
LATEST DETECTED DEADLOCK
------------------------
120924 14:51:18
*** (1) TRANSACTION:
TRANSACTION 3EDB7A, ACTIVE 22 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 320, 2 row lock(s)
MySQL thread id 51, OS thread handle 0xa6aceb40, query id 567 localhost root update
insert into T set C = 42
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1489 n bits 72 index `PRIMARY` of table `test_dl`.`T` trx id 3EDB7A lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 3EDB7B, ACTIVE 13 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, 2 row lock(s)
MySQL thread id 50, OS thread handle 0xa6a9db40, query id 568 localhost root update
insert into T set C = 42
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1489 n bits 72 index `PRIMARY` of table `test_dl`.`T` trx id 3EDB7B lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1489 n bits 72 index `PRIMARY` of table `test_dl`.`T` trx id 3EDB7B lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

What seems to be not logical is that both sessions seems to get an X lock on the gap at the end of the table:

create table innodb_monitor (id int);
create table innodb_lock_monitor (id int);
(1) start transaction;
(1) select * from T where C = 42 for update;
(2) start transaction;
(2) select * from T where C = 42 for update;
(3) show engine innodb status\G;

Transactions info:

------------
TRANSACTIONS
------------
Trx id counter 3EDBDF
Purge done for trx's n:o < 3EDBCD undo n:o < 0
History list length 675
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 54, OS thread handle 0xa6affb40, query id 1067 localhost root
show engine innodb status
---TRANSACTION 3EDBDE, ACTIVE 4 sec
2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 50, OS thread handle 0xa6a9db40, query id 1066 localhost root
TABLE LOCK table `test_dl`.`T` trx id 3EDBDE lock mode IX
RECORD LOCKS space id 0 page no 1513 n bits 72 index `PRIMARY` of table `test_dl`.`T` trx id 3EDBDE lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

---TRANSACTION 3EDBDD, ACTIVE 15 sec
2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 51, OS thread handle 0xa6aceb40, query id 1064 localhost root
TABLE LOCK table `test_dl`.`T` trx id 3EDBDD lock mode IX
RECORD LOCKS space id 0 page no 1513 n bits 72 index `PRIMARY` of table `test_dl`.`T` trx id 3EDBDD lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

Both transactions hold the same X lock. See my post with question.

Example 7 (deadlock after delete non-existing data), similar to example 6:

create table d (id int not null primary key) engine=InnoDB;
insert into d values (1),(2),(3);
(1) start transaction;
(1) delete from d where id = 4;
- lock on gap at the end of table
(2) start transaction;
(2) delete from d where id = 5;
- lock on gap at the end of table
(1) insert into d values (4);
- wait
(2) insert into d values (5);
- deadlock

Deadlock info:

------------------------
LATEST DETECTED DEADLOCK
------------------------
120924 17:44:05
*** (1) TRANSACTION:
TRANSACTION 3EDBD2, ACTIVE 40 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 320, 2 row lock(s)
MySQL thread id 51, OS thread handle 0xa6aceb40, query id 1001 localhost root update
insert into d values (4)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1507 n bits 72 index `PRIMARY` of table `appceo`.`d` trx id 3EDBD2 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 3EDBD3, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, 2 row lock(s)
MySQL thread id 50, OS thread handle 0xa6a9db40, query id 1002 localhost root update
insert into d values (5)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1507 n bits 72 index `PRIMARY` of table `appceo`.`d` trx id 3EDBD3 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1507 n bits 72 index `PRIMARY` of table `appceo`.`d` trx id 3EDBD3 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

With lock monitor enabled we can check transactions state before producing a deadlock:

create table innodb_monitor (id int);
create table innodb_lock_monitor (id int);
(1) start transaction;
(1) delete from d where id = 4;
(2) start transaction;
(2) delete from d where id = 5;
(3) show engine innodb status\G;

Transactions info:

------------
TRANSACTIONS
------------
Trx id counter 3EDBDC
Purge done for trx's n:o < 3EDBCD undo n:o < 0
History list length 675
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 54, OS thread handle 0xa6affb40, query id 1028 localhost root
show engine innodb status
---TRANSACTION 3EDBDB, ACTIVE 11 sec
2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 50, OS thread handle 0xa6a9db40, query id 1027 localhost root
TABLE LOCK table `appceo`.`d` trx id 3EDBDB lock mode IX
RECORD LOCKS space id 0 page no 1507 n bits 72 index `PRIMARY` of table `db`.`d` trx id 3EDBDB lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

---TRANSACTION 3EDBDA, ACTIVE 17 sec
2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 51, OS thread handle 0xa6aceb40, query id 1025 localhost root
TABLE LOCK table `appceo`.`d` trx id 3EDBDA lock mode IX
RECORD LOCKS space id 0 page no 1507 n bits 72 index `PRIMARY` of table `db`.`d` trx id 3EDBDA lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

vim, git, aws and other three-letter words

Search

Tags

transaction isolation levels

lock modes

Record, Gap, and Next-Key Locks

Locks and statements

Deadlock Detection and Rollback

How to Cope with Deadlocks

Deadlock examples

Example 1 (deadlock on insert):

Example 2 (deadlock on delete / insert):

Example 3 (deadlock on access in different order):

Example 4 (deadlock on access in different order):

Example 5 (deadlock on access in different order with complex primary key):

Example 6 (deadlock in empty table):

Example 7 (deadlock after delete non-existing data), similar to example 6: