2012-09-24

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:

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:

transaction isolation levels

lock modes

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.

SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE Locking Reads - Usage Examples

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

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:

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 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:

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

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):

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

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 |  ____|
+-----+-----+

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:

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:

Example 6 (deadlock in empty table):

See initial post and explanation.

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:

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:

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:

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;;
profile for Boris Serebrov on Stack Exchange, a network of free, community-driven Q&A sites