图片 1

MySQL · 引擎特性 · InnoDB 事务锁系统简介

 

MYSQL原文链接  本文链接扩展  

Preface

作者: 淘宝阿里数据库组

 

 

    I’ve demontstrated several InnoDB
locking cases in my previous blog.I’m gonna do the rest tests about
InnoDB locks.

事务锁管理

InnoDB
所有的事务锁对象都是挂在全局对象lock_sys上,同时每个事务对象上也维持了其拥有的事务锁,每个表对象(dict_table_t)上维持了构建在其上的表级锁对象。

如下图所示:

图片 1

 

This section describes lock types used by InnoDB.

 

Shared and Exclusive Locks

InnoDB implements standard row-level locking where there are two types
of locks, shared (S)
locks
 and exclusive
(X)
locks
.

  • 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.

If transaction T1 holds a shared (S) lock on row r, then
requests from some distinct transaction T2 for a lock on row r are
handled as follows:

  • A request by T2 for an S lock can be granted immediately. As a
    result, both T1 and T2 hold an S lock on r.

  • A request by T2 for an X lock cannot be granted immediately.

If a transaction T1 holds an exclusive (X) lock on row r, a
request from some distinct transaction T2 for a lock of either type
on rcannot be granted immediately. Instead, transaction T2 has to
wait for transaction T1 to release its lock on row r.

Procedure

Intention Locks

InnoDB supports multiple granularity
locking
 which permits coexistence of row-level locks and locks on
entire tables. To make locking at multiple granularity levels practical,
additional types of locks called intention
locks
 are
used. Intention locks are table-level locks in InnoDB that indicate
which type of lock (shared or exclusive) a transaction requires later
for a row in that table. There are two types of intention locks used
in InnoDB (assume that transaction T has requested a lock of the
indicated type on table t):

  • Intention
    shared
     (IS):
    Transaction T intends to set S locks on individual rows in
    table t.

  • Intention
    exclusive
     (IX):
    Transaction T intends to set X locks on those rows.

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

The intention locking protocol is as follows:

  • Before a transaction can acquire an S lock on a row in
    table t, it must first acquire an IS or stronger lock on t.

  • Before a transaction can acquire an X lock on a row, it must
    first acquire an IX lock on t.

These rules can be conveniently summarized by means of the
following lock type compatibility
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 lock is granted to a requesting transaction if it is compatible with
existing locks, but not if it conflicts with existing locks. A
transaction waits until the conflicting existing lock is released. If a
lock request conflicts with an existing lock and cannot be granted
because it would
cause deadlock,
an error occurs.

Thus, intention locks do not block anything except full table requests
(for example, LOCK TABLES ... WRITE). The main purpose
of IX and IS locks is to show that someone is locking a row, or
going to lock a row in the table.

Transaction data for an intention lock appears similar to the following
in SHOW ENGINE INNODB STATUS and InnoDB
monitor
output:

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

 

Record Locks

A record lock is a lock on an index record. For
example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other
transaction from inserting, updating, or deleting rows where the value
of t.c1 is 10.

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. See Section 14.8.2.1,
“Clustered and Secondary
Indexes”
.

Transaction data for a record lock appears similar to the following
in SHOW ENGINE INNODB STATUS and InnoDB
monitor
 output:

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

Test table information and the relevant
variables.

Gap Locks

A gap lock is a lock on a gap between index records, or a lock on the
gap before the first or after the last index record. For
example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents
other transactions from inserting a value of 15 into column t.c1,
whether or not there was already any such value in the column, because
the gaps between all existing values in the range are locked.

A gap might span a single index value, multiple index values, or even be
empty.

Gap locks are part of the tradeoff between performance and concurrency,
and are used in some transaction isolation levels and not others.

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.

It is also worth noting here that conflicting locks can be held on a gap
by different transactions. For example, transaction A can hold a shared
gap lock (gap S-lock) on a gap while transaction B holds an exclusive
gap lock (gap X-lock) on the same gap. The reason conflicting gap locks
are allowed is that if a record is purged from an index, the gap locks
held on the record by different transactions must be merged.

Gap locks in InnoDB are “purely inhibitive”, which means they only stop other
transactions from inserting to the gap. They do not prevent different
transactions from taking gap locks on the same gap. Thus, a gap X-lock
has the same effect as a gap S-lock.

Gap locking can be disabled explicitly. This occurs if you change the
transaction isolation level
to READ COMMITTED or
enable
theinnodb_locks_unsafe_for_binlog system
variable (which is now deprecated). 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.

There are also other effects of using
the READ COMMITTED isolation
level or
enabling innodb_locks_unsafe_for_binlog.
Record locks for nonmatching rows are released after MySQL has evaluated
the WHERE condition. For UPDATE statements, InnoDB does a “semi-consistent” read, such that it
returns the latest committed version to MySQL so that MySQL can
determine whether the row matches the WHERE condition of
the UPDATE.

 1 zlm@192.168.56.100:3306 [zlm]>show create table t1\G
 2 *************************** 1. row ***************************
 3        Table: t1
 4 Create Table: CREATE TABLE `t1` (
 5   `c1` int(10) unsigned NOT NULL DEFAULT '0',
 6   `c2` int(10) unsigned NOT NULL DEFAULT '0',
 7   `c3` int(10) unsigned NOT NULL DEFAULT '0',
 8   `c4` int(10) unsigned NOT NULL DEFAULT '0',
 9   PRIMARY KEY (`c1`),
10   KEY `c2` (`c2`)
11 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
12 1 row in set (0.00 sec)
13 
14 zlm@192.168.56.100:3306 [zlm]>select * from t1;
15 +----+----+----+----+
16 | c1 | c2 | c3 | c4 |
17 +----+----+----+----+
18 |  0 |  0 |  0 |  0 |
19 |  1 |  1 |  1 |  0 |
20 |  3 |  3 |  3 |  0 |
21 |  4 |  2 |  2 |  0 |
22 |  6 |  2 |  5 |  0 |
23 |  8 |  6 |  6 |  0 |
24 | 10 |  4 |  4 |  0 |
25 +----+----+----+----+
26 8 rows in set (0.01 sec)
27 
28 zlm@192.168.56.100:3306 [zlm]>select @@transaction_isolation;
29 +-------------------------+
30 | @@transaction_isolation |
31 +-------------------------+
32 | REPEATABLE-READ         |
33 +-------------------------+
34 1 row in set (0.00 sec)
35 
36 zlm@192.168.56.100:3306 [(none)]>show variables like 'innodb_status_output_locks';
37 +----------------------------+-------+
38 | Variable_name              | Value |
39 +----------------------------+-------+
40 | innodb_status_output_locks | ON    |
41 +----------------------------+-------+
42 1 row in set (0.00 sec)

Next-Key Locks

A next-key lock is a combination of a record lock on the index record
and a gap lock on the gap before the index record.

InnoDB performs row-level locking in such a way that when it searches
or scans a table index, it sets shared or exclusive locks on the index
records it encounters. Thus, the row-level locks are actually
index-record locks. A next-key lock on an index record also affects
the “gap” before that index
record. That is, a next-key lock is an index-record lock plus a gap lock
on the gap preceding the index record. 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, 11, 13, and 20. The
possible next-key locks for this index cover the following intervals,
where a round bracket denotes exclusion of the interval endpoint and a
square bracket denotes inclusion of the endpoint:

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 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.

By default, InnoDB operates
in REPEATABLE READ transaction
isolation level. In this case, InnoDB uses next-key locks for searches
and index scans, which prevents phantom rows (see Section 14.5.4,
“Phantom
Rows”
).

Transaction data for a next-key lock appears similar to the following
in SHOW ENGINE INNODB STATUS and InnoDB
monitor
 output:

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10080 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;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

 

Insert Intention Locks

An insert intention lock is a type of gap lock set
by INSERT operations
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,
respectively, 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.

The following example demonstrates a transaction taking an insert
intention lock prior to obtaining an exclusive lock on the inserted
record. The example involves two clients, A and B.

Client A creates a table containing two index records (90 and 102) and
then starts a transaction that places an exclusive lock on index records
with an ID greater than 100. The exclusive lock includes a gap lock
before record 102:

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

Client B begins a transaction to insert a record into the gap. The
transaction takes an insert intention lock while it waits to obtain an
exclusive lock.

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

Transaction data for an insert intention lock appears similar to the
following
in SHOW ENGINE INNODB STATUS and InnoDB
monitor
output:

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

Test 1: session1 update while session2
insert.

AUTO-INC Locks

An AUTO-INC lock is a special table-level lock taken by transactions
inserting into tables with AUTO_INCREMENT columns. In the simplest
case, if one transaction is inserting values into the table, any other
transactions must wait to do their own inserts into that table, so that
rows inserted by the first transaction receive consecutive primary key
values.

The innodb_autoinc_lock_mode configuration
option controls the algorithm used for auto-increment locking. It allows
you to choose how to trade off between predictable sequences of
auto-increment values and maximum concurrency for insert operations.

For more information, see Section 14.8.1.5, “AUTO_INCREMENT Handling
in
InnoDB”
.

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;update t1 set c4=20 where c2>=4;select * from t1 where c2>=4;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Query OK, 2 rows affected (0.00 sec)
 6 Rows matched: 2  Changed: 2  Warnings: 0
 7 
 8 +----+----+----+----+
 9 | c1 | c2 | c3 | c4 |
10 +----+----+----+----+
11 | 10 |  4 |  4 | 20 |
12 |  8 |  6 |  6 | 20 |
13 +----+----+----+----+
14 2 rows in set (0.00 sec)
15 
16 //Lock information of session1.
17 TABLE LOCK table `zlm`.`t1` trx id 2997544 lock mode IX
18 RECORD LOCKS space id 178 page no 4 n bits 80 index c2 of table `zlm`.`t1` trx id 2997544 lock_mode X
19 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
20  0: len 8; hex 73757072656d756d; asc supremum;;
21 
22 Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
23  0: len 4; hex 00000006; asc     ;;
24  1: len 4; hex 00000008; asc     ;;
25 
26 Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
27  0: len 4; hex 00000004; asc     ;;
28  1: len 4; hex 0000000a; asc     ;;
29 
30 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997544 lock_mode X locks rec but not gap
31 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
32  0: len 4; hex 00000008; asc     ;;
33  1: len 6; hex 0000002dbd28; asc    - (;;
34  2: len 7; hex 3a0000012727bb; asc :   '' ;;
35  3: len 4; hex 00000006; asc     ;;
36  4: len 4; hex 00000006; asc     ;;
37  5: len 4; hex 00000014; asc     ;;
38 
39 Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
40  0: len 4; hex 0000000a; asc     ;;
41  1: len 6; hex 0000002dbd28; asc    - (;;
42  2: len 7; hex 3a000001272799; asc :   '' ;;
43  3: len 4; hex 00000004; asc     ;;
44  4: len 4; hex 00000004; asc     ;;
45  5: len 4; hex 00000014; asc     ;;
46 
47 //Session2:
48 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 7,5,10,10;
49 Query OK, 0 rows affected (0.00 sec)
50 
51 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
52 
53 //Session2 was block because of the gap lock(c2>=4 hold a supremum lock) which was holed by session 1.The value c2=5 which session2 want to insert is conficted with the range lock.

Predicate Locks for Spatial Indexes

InnoDB supports SPATIAL indexing of columns containing spatial
columns (see Section 11.5.8, “Optimizing Spatial
Analysis”
).

To handle locking for operations involving SPATIAL indexes, next-key
locking does not work well to
support REPEATABLE READ orSERIALIZABLE transaction
isolation levels. There is no absolute ordering concept in
multidimensional data, so it is not clear which is the “next” key.

To enable support of isolation levels for tables
with SPATIAL indexes, InnoDB uses predicate locks. A SPATIAL index
contains minimum bounding rectangle (MBR) values, so InnoDB enforces
consistent read on the index by setting a predicate lock on the MBR
value used for a query. Other transactions cannot insert or modify a row
that would match the query condition.

 

Test 2: session1 update while session2
insert.

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;update t1 set c4=20 where c2>=4;select * from t1 where c2>=4;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Query OK, 2 rows affected (0.00 sec)
 6 Rows matched: 2  Changed: 2  Warnings: 0
 7 
 8 +----+----+----+----+
 9 | c1 | c2 | c3 | c4 |
10 +----+----+----+----+
11 | 10 |  4 |  4 | 20 |
12 |  8 |  6 |  6 | 20 |
13 +----+----+----+----+
14 2 rows in set (0.00 sec)
15 
16 //Session2:
17 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 7,2,10,10;
18 Query OK, 0 rows affected (0.00 sec)
19 
20 Query OK, 1 row affected (0.00 sec)
21 Records: 1  Duplicates: 0  Warnings: 0
22 
23 //This time the transaction in session2 was committed immediately.The value c2=2 didn't conflict with the range lock in session1.

 

Test 3: session1  update while
session2 insert.

  1 //Session1:
  2 zlm@192.168.56.100:3306 [zlm]>begin;update t1 set c4=20 where c1>=6;select * from t1 where c1>=6;
  3 Query OK, 0 rows affected (0.00 sec)
  4 
  5 Query OK, 3 rows affected (0.00 sec)
  6 Rows matched: 3  Changed: 3  Warnings: 0
  7 
  8 +----+----+----+----+
  9 | c1 | c2 | c3 | c4 |
 10 +----+----+----+----+
 11 |  6 |  2 |  5 | 20 |
 12 |  8 |  6 |  6 | 20 |
 13 | 10 |  4 |  4 | 20 |
 14 +----+----+----+----+
 15 3 rows in set (0.00 sec)
 16 
 17 //Lock information of session1.
 18 ---TRANSACTION 2997551, ACTIVE 3 sec
 19 3 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 3
 20 MySQL thread id 103, OS thread handle 140181164484352, query id 4187 zlm1 192.168.56.100 zlm
 21 Trx read view will not see trx with id >= 2997552, sees < 2997552
 22 TABLE LOCK table `zlm`.`t1` trx id 2997551 lock mode IX
 23 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997551 lock_mode X locks rec but not gap
 24 Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 25  0: len 4; hex 00000006; asc     ;;
 26  1: len 6; hex 0000002dbd2f; asc    - /;;
 27  2: len 7; hex 3e0000012d180e; asc >   -  ;;
 28  3: len 4; hex 00000002; asc     ;;
 29  4: len 4; hex 00000005; asc     ;;
 30  5: len 4; hex 00000014; asc     ;;
 31 
 32 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997551 lock_mode X
 33 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 34  0: len 8; hex 73757072656d756d; asc supremum;;
 35 
 36 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 37  0: len 4; hex 00000008; asc     ;;
 38  1: len 6; hex 0000002dbd2f; asc    - /;;
 39  2: len 7; hex 3e0000012d1830; asc >   - 0;;
 40  3: len 4; hex 00000006; asc     ;;
 41  4: len 4; hex 00000006; asc     ;;
 42  5: len 4; hex 00000014; asc     ;;
 43 
 44 Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 45  0: len 4; hex 0000000a; asc     ;;
 46  1: len 6; hex 0000002dbd2f; asc    - /;;
 47  2: len 7; hex 3e0000012d1852; asc >   - R;;
 48  3: len 4; hex 00000004; asc     ;;
 49  4: len 4; hex 00000004; asc     ;;
 50  5: len 4; hex 00000014; asc     ;;
 51 
 52 //Session2:
 53 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 9,9,9,9;
 54 Query OK, 0 rows affected (0.00 sec)
 55 
 56 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 57 
 58 //Locks information of session2.
 59 ---TRANSACTION 2997604, ACTIVE 5 sec inserting
 60 mysql tables in use 1, locked 1
 61 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
 62 MySQL thread id 255, OS thread handle 140181022336768, query id 10108 zlm1 192.168.56.100 zlm executing
 63 insert into t1 select 9,9,9,9
 64 ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
 65 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997604 lock_mode X locks gap before rec insert intention waiting
 66 Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 67  0: len 4; hex 0000000a; asc     ;;
 68  1: len 6; hex 0000002dbd63; asc    - c;;
 69  2: len 7; hex 400000012f17f3; asc @   /  ;;
 70  3: len 4; hex 00000004; asc     ;;
 71  4: len 4; hex 00000004; asc     ;;
 72  5: len 4; hex 00000014; asc     ;;
 73 
 74 ------------------
 75 TABLE LOCK table `zlm`.`t1` trx id 2997604 lock mode IX
 76 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997604 lock_mode X locks gap before rec insert intention waiting
 77 Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 78  0: len 4; hex 0000000a; asc     ;;
 79  1: len 6; hex 0000002dbd63; asc    - c;;
 80  2: len 7; hex 400000012f17f3; asc @   /  ;;
 81  3: len 4; hex 00000004; asc     ;;
 82  4: len 4; hex 00000004; asc     ;;
 83  5: len 4; hex 00000014; asc     ;;
 84 
 85 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 11,9,9,9;
 86 Query OK, 0 rows affected (0.00 sec)
 87 
 88 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 89 
 90 //Locks information of session2.
 91 Trx id counter 2997606
 92 Purge done for trx's n:o < 2997603 undo n:o < 0 state: running but idle
 93 History list length 35
 94 LIST OF TRANSACTIONS FOR EACH SESSION:
 95 ---TRANSACTION 421656271875696, not started
 96 0 lock struct(s), heap size 1136, 0 row lock(s)
 97 ---TRANSACTION 2997605, ACTIVE 3 sec inserting
 98 mysql tables in use 1, locked 1
 99 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
100 MySQL thread id 255, OS thread handle 140181022336768, query id 10237 zlm1 192.168.56.100 zlm executing
101 insert into t1 select 11,9,9,9
102 ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
103 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997605 lock_mode X insert intention waiting
104 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
105  0: len 8; hex 73757072656d756d; asc supremum;;
106 
107 ------------------
108 TABLE LOCK table `zlm`.`t1` trx id 2997605 lock mode IX
109 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997605 lock_mode X insert intention waiting
110 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
111  0: len 8; hex 73757072656d756d; asc supremum;;
112  
113 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 10,9,9,9;
114 Query OK, 0 rows affected (0.00 sec)
115 
116 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
117 
118 //locks information of session2.
119 ---TRANSACTION 2997609, ACTIVE 5 sec inserting
120 mysql tables in use 1, locked 1
121 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
122 MySQL thread id 271, OS thread handle 140181022738176, query id 10784 zlm1 192.168.56.100 zlm executing
123 insert into t1 select 10,9,9,9
124 ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
125 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997609 lock mode S waiting
126 Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
127  0: len 4; hex 0000000a; asc     ;;
128  1: len 6; hex 0000002dbd68; asc    - h;;
129  2: len 7; hex 4400000da22824; asc D    ($;;
130  3: len 4; hex 00000004; asc     ;;
131  4: len 4; hex 00000004; asc     ;;
132  5: len 4; hex 00000014; asc     ;;
133 
134 ------------------
135 TABLE LOCK table `zlm`.`t1` trx id 2997609 lock mode IX
136 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997609 lock mode S waiting
137 Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
138  0: len 4; hex 0000000a; asc     ;;
139  1: len 6; hex 0000002dbd68; asc    - h;;
140  2: len 7; hex 4400000da22824; asc D    ($;;
141  3: len 4; hex 00000004; asc     ;;
142  4: len 4; hex 00000004; asc     ;;
143  5: len 4; hex 00000014; asc     ;;
144 
145 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 8,9,9,9;
146 Query OK, 0 rows affected (0.00 sec)
147 
148 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
149 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 7,9,9,9;
150 Query OK, 0 rows affected (0.00 sec)
151 
152 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
153 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 6,9,9,9;
154 Query OK, 0 rows affected (0.00 sec)
155 
156 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
157 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 5,9,9,9;
158 Query OK, 0 rows affected (0.00 sec)
159 
160 Query OK, 1 row affected (0.00 sec)
161 Records: 1  Duplicates: 0  Warnings: 0
162 
163 //Only c1=5 in session2 can be executed immediately.Because c1=5 didn't conflict with the range lock.

 

Test 4: session1 select for update while
session2 delete.

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1<=4 for update;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 +----+----+----+----+
 6 | c1 | c2 | c3 | c4 |
 7 +----+----+----+----+
 8 |  0 |  0 |  0 |  0 |
 9 |  1 |  1 |  1 |  0 |
10 |  3 |  3 |  3 |  0 |
11 |  4 |  2 |  2 |  0 |
12 +----+----+----+----+
13 4 rows in set (0.00 sec)
14 
15 //Locks information of session1.
16 ---TRANSACTION 2997623, ACTIVE 4 sec
17 2 lock struct(s), heap size 1136, 5 row lock(s)
18 MySQL thread id 295, OS thread handle 140181022537472, query id 11851 zlm1 192.168.56.100 zlm
19 TABLE LOCK table `zlm`.`t1` trx id 2997623 lock mode IX
20 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997623 lock_mode X
21 Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
22  0: len 4; hex 00000000; asc     ;;
23  1: len 6; hex 0000002dbb07; asc    -  ;;
24  2: len 7; hex a7000002690110; asc     i  ;;
25  3: len 4; hex 00000000; asc     ;;
26  4: len 4; hex 00000000; asc     ;;
27  5: len 4; hex 00000000; asc     ;;
28 
29 Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
30  0: len 4; hex 00000001; asc     ;;
31  1: len 6; hex 0000002dbb07; asc    -  ;;
32  2: len 7; hex a700000269011d; asc     i  ;;
33  3: len 4; hex 00000001; asc     ;;
34  4: len 4; hex 00000001; asc     ;;
35  5: len 4; hex 00000000; asc     ;;
36 
37 Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
38  0: len 4; hex 00000003; asc     ;;
39  1: len 6; hex 0000002dbb07; asc    -  ;;
40  2: len 7; hex a700000269012a; asc     i *;;
41  3: len 4; hex 00000003; asc     ;;
42  4: len 4; hex 00000003; asc     ;;
43  5: len 4; hex 00000000; asc     ;;
44 
45 Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
46  0: len 4; hex 00000004; asc     ;;
47  1: len 6; hex 0000002dbb07; asc    -  ;;
48  2: len 7; hex a7000002690137; asc     i 7;;
49  3: len 4; hex 00000002; asc     ;;
50  4: len 4; hex 00000002; asc     ;;
51  5: len 4; hex 00000000; asc     ;;
52 
53 Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
54  0: len 4; hex 00000006; asc     ;;
55  1: len 6; hex 0000002dbd6c; asc    - l;;
56  2: len 7; hex 470000025802f4; asc G   X  ;;
57  3: len 4; hex 00000002; asc     ;;
58  4: len 4; hex 00000005; asc     ;;
59  5: len 4; hex 00000014; asc     ;;
60 
61 //Session2:
62 zlm@192.168.56.100:3306 [zlm]>begin;delete from t1 where c1=6;
63 Query OK, 0 rows affected (0.00 sec)
64 
65 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
66 
67 //The lock session2 requested for where c1=6 was locked either.So it was blocked.Even though primary key does not contains the gap lock,but when the condition contains a range like c1<=4,it will block the next-key of c1=6 here.

 

Test 5: session1 select for update while
session2 delete.

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1>=4 for update;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 +----+----+----+----+
 6 | c1 | c2 | c3 | c4 |
 7 +----+----+----+----+
 8 |  4 |  2 |  2 |  0 |
 9 |  6 |  2 |  5 | 20 |
10 |  8 |  6 |  6 | 20 |
11 | 10 |  4 |  4 | 20 |
12 +----+----+----+----+
13 4 rows in set (0.00 sec)
14 
15 //Lock information of session1.
16 ---TRANSACTION 2997627, ACTIVE 4 sec
17 3 lock struct(s), heap size 1136, 5 row lock(s)
18 MySQL thread id 338, OS thread handle 140181164685056, query id 14154 zlm1 192.168.56.100 zlm
19 TABLE LOCK table `zlm`.`t1` trx id 2997627 lock mode IX
20 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997627 lock_mode X locks rec but not gap
21 Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
22  0: len 4; hex 00000004; asc     ;;
23  1: len 6; hex 0000002dbb07; asc    -  ;;
24  2: len 7; hex a7000002690137; asc     i 7;;
25  3: len 4; hex 00000002; asc     ;;
26  4: len 4; hex 00000002; asc     ;;
27  5: len 4; hex 00000000; asc     ;;
28 
29 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997627 lock_mode X
30 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
31  0: len 8; hex 73757072656d756d; asc supremum;;
32 
33 Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
34  0: len 4; hex 00000006; asc     ;;
35  1: len 6; hex 0000002dbd6c; asc    - l;;
36  2: len 7; hex 470000025802f4; asc G   X  ;;
37  3: len 4; hex 00000002; asc     ;;
38  4: len 4; hex 00000005; asc     ;;
39  5: len 4; hex 00000014; asc     ;;
40 
41 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
42  0: len 4; hex 00000008; asc     ;;
43  1: len 6; hex 0000002dbd6c; asc    - l;;
44  2: len 7; hex 47000002580316; asc G   X  ;;
45  3: len 4; hex 00000006; asc     ;;
46  4: len 4; hex 00000006; asc     ;;
47  5: len 4; hex 00000014; asc     ;;
48 
49 Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
50  0: len 4; hex 0000000a; asc     ;;
51  1: len 6; hex 0000002dbd6c; asc    - l;;
52  2: len 7; hex 47000002580338; asc G   X 8;;
53  3: len 4; hex 00000004; asc     ;;
54  4: len 4; hex 00000004; asc     ;;
55  5: len 4; hex 00000014; asc     ;;
56  
57  //Session2:
58  zlm@192.168.56.100:3306 [zlm]>delete from t1 where c1=3;
59 Query OK, 1 row affected (0.00 sec)
60 
61 //Session2 was not blocked this time.Because c1=3 was not in the gap lock here.

 

Test 5: session1 update while session2
select for update.

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;update t1 set c1=40 where c1=4;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Query OK, 1 row affected (0.00 sec)
 6 Rows matched: 1  Changed: 1  Warnings: 0
 7 
 8 //lock information of session1.
 9 ---TRANSACTION 2997652, ACTIVE 49 sec
10 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
11 MySQL thread id 366, OS thread handle 140181022738176, query id 15652 zlm1 192.168.56.100 zlm
12 TABLE LOCK table `zlm`.`t1` trx id 2997652 lock mode IX
13 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997652 lock_mode X locks rec but not gap
14 Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
15  0: len 4; hex 00000004; asc     ;;
16  1: len 6; hex 0000002dbd94; asc    -  ;;
17  2: len 7; hex 6000000dab26f6; asc `    & ;;
18  3: len 4; hex 00000002; asc     ;;
19  4: len 4; hex 00000002; asc     ;;
20  5: len 4; hex 00000000; asc     ;;
21  
22 //Session1 holded only 1 record lock with no gap of record c1=4.
23 
24 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c2=2 for update;
25 Query OK, 0 rows affected (0.00 sec)
26 
27 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
28 
29 //Lock information of session2.
30 ---TRANSACTION 2997657, ACTIVE 39 sec fetching rows
31 mysql tables in use 1, locked 1
32 LOCK WAIT 3 lock struct(s), heap size 1136, 4 row lock(s)
33 MySQL thread id 365, OS thread handle 140181022537472, query id 15743 zlm1 192.168.56.100 zlm Sending data
34 select * from t1 where c2=2 for update
35 ------- TRX HAS BEEN WAITING 39 SEC FOR THIS LOCK TO BE GRANTED:
36 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997657 lock_mode X waiting
37 Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
38  0: len 4; hex 00000004; asc     ;;
39  1: len 6; hex 0000002dbd94; asc    -  ;;
40  2: len 7; hex 6000000dab26f6; asc `    & ;;
41  3: len 4; hex 00000002; asc     ;;
42  4: len 4; hex 00000002; asc     ;;
43  5: len 4; hex 00000000; asc     ;;
44 
45 ------------------
46 TABLE LOCK table `zlm`.`t1` trx id 2997657 lock mode IX
47 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997657 lock_mode X
48 Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
49  0: len 4; hex 00000000; asc     ;;
50  1: len 6; hex 0000002dbb07; asc    -  ;;
51  2: len 7; hex a7000002690110; asc     i  ;;
52  3: len 4; hex 00000000; asc     ;;
53  4: len 4; hex 00000000; asc     ;;
54  5: len 4; hex 00000000; asc     ;;
55 
56 Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
57  0: len 4; hex 00000001; asc     ;;
58  1: len 6; hex 0000002dbb07; asc    -  ;;
59  2: len 7; hex a700000269011d; asc     i  ;;
60  3: len 4; hex 00000001; asc     ;;
61  4: len 4; hex 00000001; asc     ;;
62  5: len 4; hex 00000000; asc     ;;
63 
64 Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
65  0: len 4; hex 00000003; asc     ;;
66  1: len 6; hex 0000002dbd8b; asc    -  ;;
67  2: len 7; hex db0000019f0110; asc        ;;
68  3: len 4; hex 00000003; asc     ;;
69  4: len 4; hex 00000003; asc     ;;
70  5: len 4; hex 00000000; asc     ;;
71 
72 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997657 lock_mode X waiting
73 Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
74  0: len 4; hex 00000004; asc     ;;
75  1: len 6; hex 0000002dbd94; asc    -  ;;
76  2: len 7; hex 6000000dab26f6; asc `    & ;;
77  3: len 4; hex 00000002; asc     ;;
78  4: len 4; hex 00000002; asc     ;;
79  5: len 4; hex 00000000; asc     ;;
80  
81  //Session2 holded 4 record locks on c1 but only c1=4 was conflicted with session1.It was still blocked.
82  //I'm a little baffled why the session2 holded so many record locks on the primary key column(c1=0,c1=1,c1=3).

 

Test 6: session1 delete then insert
while session2 insert.

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;delete from t1 where c1=8;insert into t1 select 9,9,9,9;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Query OK, 1 row affected (0.00 sec)
 6 
 7 Query OK, 1 row affected (0.00 sec)
 8 Records: 1  Duplicates: 0  Warnings: 0
 9 
10 //Lock information of session1.
11 ---TRANSACTION 2997659, ACTIVE 12 sec
12 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
13 MySQL thread id 414, OS thread handle 140181164484352, query id 17399 zlm1 192.168.56.100 zlm
14 TABLE LOCK table `zlm`.`t1` trx id 2997659 lock mode IX
15 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997659 lock_mode X locks rec but not gap
16 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
17  0: len 4; hex 00000008; asc     ;;
18  1: len 6; hex 0000002dbd9b; asc    -  ;;
19  2: len 7; hex 64000001b9298c; asc d    ) ;;
20  3: len 4; hex 00000006; asc     ;;
21  4: len 4; hex 00000006; asc     ;;
22  5: len 4; hex 00000014; asc     ;;
23 
24 //Session2:
25 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 7,7,7,7;
26 Query OK, 0 rows affected (0.00 sec)
27 
28 Query OK, 1 row affected (0.00 sec)
29 Records: 1  Duplicates: 0  Warnings: 0
30 
31 //Session2 did not conflict with the "X" record locks holded by session1.So it was executed immediately.Because there was no gap lock on c1 column(primary key).

 

Test 7: session1 insert while session2
insert.

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 9,9,9,9;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Query OK, 1 row affected (0.00 sec)
 6 Records: 1  Duplicates: 0  Warnings: 0
 7 
 8 //Lock information of session1.
 9 ---TRANSACTION 2997666, ACTIVE 3 sec
10 1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
11 MySQL thread id 440, OS thread handle 140181022738176, query id 18609 zlm1 192.168.56.100 zlm
12 TABLE LOCK table `zlm`.`t1` trx id 2997666 lock mode IX
13 
14 //Notice that the c1=9 is not exist in primary key.It only generate a "IX" lock(intention lock).
15 
16 //Session2:
17 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 9,9,9,9;
18 Query OK, 0 rows affected (0.01 sec)
19 
20 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
21 
22 //Lock information of session2.
23 ---TRANSACTION 2997671, ACTIVE 9 sec inserting
24 mysql tables in use 1, locked 1
25 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
26 MySQL thread id 443, OS thread handle 140181022537472, query id 18663 zlm1 192.168.56.100 zlm executing
27 insert into t1 select 9,9,9,9
28 ------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
29 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997671 lock mode S waiting
30 Record lock, heap no 10 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
31  0: len 4; hex 00000009; asc     ;;
32  1: len 6; hex 0000002dbda2; asc    -  ;;
33  2: len 7; hex e8000001b00110; asc        ;;
34  3: len 4; hex 00000009; asc     ;;
35  4: len 4; hex 00000009; asc     ;;
36  5: len 4; hex 00000009; asc     ;;
37 
38 ------------------
39 TABLE LOCK table `zlm`.`t1` trx id 2997671 lock mode IX
40 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997671 lock mode S waiting
41 Record lock, heap no 10 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
42  0: len 4; hex 00000009; asc     ;;
43  1: len 6; hex 0000002dbda2; asc    -  ;;
44  2: len 7; hex e8000001b00110; asc        ;;
45  3: len 4; hex 00000009; asc     ;;
46  4: len 4; hex 00000009; asc     ;;
47  5: len 4; hex 00000009; asc     ;;
48 
49 ---TRANSACTION 2997666, ACTIVE 57 sec
50 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
51 MySQL thread id 440, OS thread handle 140181022738176, query id 18609 zlm1 192.168.56.100 zlm
52 TABLE LOCK table `zlm`.`t1` trx id 2997666 lock mode IX
53 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997666 lock_mode X locks rec but not gap
54 Record lock, heap no 10 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
55  0: len 4; hex 00000009; asc     ;;
56  1: len 6; hex 0000002dbda2; asc    -  ;;
57  2: len 7; hex e8000001b00110; asc        ;;
58  3: len 4; hex 00000009; asc     ;;
59  4: len 4; hex 00000009; asc     ;;
60  5: len 4; hex 00000009; asc     ;;
61  
62  //Session2 was waiting for the "S" record lock and it also request for "X" record lock on record c1=9 in primary key.Although the record c1=9 is not exist in primary key,but they were inserting into the same row.As the session2 cannot get the lock for inserting.It was blocked.

 

Test 8: session1 select for update while
session2 insert.**
(modify the same
row)**

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1=7 for update;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Empty set (0.00 sec)
 6 
 7 //Lock information of session1.
 8 ---TRANSACTION 2997672, ACTIVE 7 sec
 9 2 lock struct(s), heap size 1136, 1 row lock(s)
10 MySQL thread id 464, OS thread handle 140181164484352, query id 19707 zlm1 192.168.56.100 zlm
11 TABLE LOCK table `zlm`.`t1` trx id 2997672 lock mode IX
12 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997672 lock_mode X locks gap before rec
13 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
14  0: len 4; hex 00000008; asc     ;;
15  1: len 6; hex 0000002dbd6c; asc    - l;;
16  2: len 7; hex 47000002580316; asc G   X  ;;
17  3: len 4; hex 00000006; asc     ;;
18  4: len 4; hex 00000006; asc     ;;
19  5: len 4; hex 00000014; asc     ;;
20 
21 //Session1 holded a gap lock(or we can call it next-key lock either) of c1=8.Even though the record of c1=7 was not exist.
22 
23 //Session2:
24 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 7,7,7,7;
25 Query OK, 0 rows affected (0.00 sec)
26 
27 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
28 
29 //Lock information of session2.
30 ---TRANSACTION 2997673, ACTIVE 3 sec inserting
31 mysql tables in use 1, locked 1
32 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
33 MySQL thread id 465, OS thread handle 140181022537472, query id 19772 zlm1 192.168.56.100 zlm executing
34 insert into t1 select 7,7,7,7
35 ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
36 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997673 lock_mode X locks gap before rec insert intention waiting
37 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
38  0: len 4; hex 00000008; asc     ;;
39  1: len 6; hex 0000002dbd6c; asc    - l;;
40  2: len 7; hex 47000002580316; asc G   X  ;;
41  3: len 4; hex 00000006; asc     ;;
42  4: len 4; hex 00000006; asc     ;;
43  5: len 4; hex 00000014; asc     ;;
44 
45 ------------------
46 TABLE LOCK table `zlm`.`t1` trx id 2997673 lock mode IX
47 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997673 lock_mode X locks gap before rec insert intention waiting
48 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
49  0: len 4; hex 00000008; asc     ;;
50  1: len 6; hex 0000002dbd6c; asc    - l;;
51  2: len 7; hex 47000002580316; asc G   X  ;;
52  3: len 4; hex 00000006; asc     ;;
53  4: len 4; hex 00000006; asc     ;;
54  5: len 4; hex 00000014; asc     ;;
55  
56 //Session2 generated a insert intention lock and request for the next-key lock holded by session1.So it blocked.

 

**Test 9: session1 insert while session2
select for update.*(modify the same
row)***

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 7,7,7,7;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Query OK, 1 row affected (0.00 sec)
 6 Records: 1  Duplicates: 0  Warnings: 0
 7 
 8 //Lock information of session1.
 9 ---TRANSACTION 2997674, ACTIVE 4 sec
10 1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
11 MySQL thread id 493, OS thread handle 140181022738176, query id 20721 zlm1 192.168.56.100 zlm
12 TABLE LOCK table `zlm`.`t1` trx id 2997674 lock mode IX
13 
14 //Session1 generated only a "IX" lock.
15 
16 //Session2:
17 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1=7 for update;
18 Query OK, 0 rows affected (0.00 sec)
19 
20 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
21 
22 //Lock information of session2.
23 ---TRANSACTION 2997675, ACTIVE 2 sec starting index read
24 mysql tables in use 1, locked 1
25 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
26 MySQL thread id 494, OS thread handle 140181164685056, query id 20752 zlm1 192.168.56.100 zlm statistics
27 select * from t1 where c1=7 for update
28 ------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
29 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997675 lock_mode X locks rec but not gap waiting
30 Record lock, heap no 10 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
31  0: len 4; hex 00000007; asc     ;;
32  1: len 6; hex 0000002dbdaa; asc    -  ;;
33  2: len 7; hex ee000001bd0110; asc        ;;
34  3: len 4; hex 00000007; asc     ;;
35  4: len 4; hex 00000007; asc     ;;
36  5: len 4; hex 00000007; asc     ;;
37 
38 ------------------
39 TABLE LOCK table `zlm`.`t1` trx id 2997675 lock mode IX
40 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997675 lock_mode X locks rec but not gap waiting
41 Record lock, heap no 10 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
42  0: len 4; hex 00000007; asc     ;;
43  1: len 6; hex 0000002dbdaa; asc    -  ;;
44  2: len 7; hex ee000001bd0110; asc        ;;
45  3: len 4; hex 00000007; asc     ;;
46  4: len 4; hex 00000007; asc     ;;
47  5: len 4; hex 00000007; asc     ;;
48 
49 //Lock information of session1.
50 ---TRANSACTION 2997674, ACTIVE 31 sec
51 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
52 MySQL thread id 493, OS thread handle 140181022738176, query id 20721 zlm1 192.168.56.100 zlm
53 TABLE LOCK table `zlm`.`t1` trx id 2997674 lock mode IX
54 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997674 lock_mode X locks rec but not gap
55 Record lock, heap no 10 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
56  0: len 4; hex 00000007; asc     ;;
57  1: len 6; hex 0000002dbdaa; asc    -  ;;
58  2: len 7; hex ee000001bd0110; asc        ;;
59  3: len 4; hex 00000007; asc     ;;
60  4: len 4; hex 00000007; asc     ;;
61  5: len 4; hex 00000007; asc     ;;
62  
63  //when session2 intended to update the same row,session1 generated a "X" record lock.Then session2 had to wait for the release of the lock by session1.It was blocked.That means InnoDB adds locks only if it detects multiple concurrent transactions are modifying the same rows no mater whether the record is exist or not.

 

Test 10: session1 select for update
while session2 select for update.(modify the same row)

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1=7 for update;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Empty set (0.00 sec)
 6 
 7 //Lock information of session1.
 8 ---TRANSACTION 2997677, ACTIVE 3 sec
 9 2 lock struct(s), heap size 1136, 1 row lock(s)
10 MySQL thread id 520, OS thread handle 140181164885760, query id 21845 zlm1 192.168.56.100 zlm
11 TABLE LOCK table `zlm`.`t1` trx id 2997677 lock mode IX
12 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997677 lock_mode X locks gap before rec
13 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
14  0: len 4; hex 00000008; asc     ;;
15  1: len 6; hex 0000002dbd6c; asc    - l;;
16  2: len 7; hex 47000002580316; asc G   X  ;;
17  3: len 4; hex 00000006; asc     ;;
18  4: len 4; hex 00000006; asc     ;;
19  5: len 4; hex 00000014; asc     ;;
20  
21 //Session2:
22 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1=7 for update;
23 Query OK, 0 rows affected (0.00 sec)
24 
25 Empty set (0.00 sec)
26 
27 //Lock information of session2.
28 ---TRANSACTION 2997678, ACTIVE 14 sec
29 2 lock struct(s), heap size 1136, 1 row lock(s)
30 MySQL thread id 519, OS thread handle 140181022738176, query id 21890 zlm1 192.168.56.100 zlm
31 TABLE LOCK table `zlm`.`t1` trx id 2997678 lock mode IX
32 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997678 lock_mode X locks gap before rec
33 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
34  0: len 4; hex 00000008; asc     ;;
35  1: len 6; hex 0000002dbd6c; asc    - l;;
36  2: len 7; hex 47000002580316; asc G   X  ;;
37  3: len 4; hex 00000006; asc     ;;
38  4: len 4; hex 00000006; asc     ;;
39  5: len 4; hex 00000014; asc     ;;
40  
41 //Session1 together with session2 both hold the gap lock(next-key lock) of c1=8.Because gap lock does not block each other.They are actually coexistent util there's a actual inserting operation.Therefore,session2 was not blocked in the case.

 

Summary

  • In RR
    transaction isolation level,the InnoDB locking seems more
    complicated than that in RC transaction isolation level.
  • InnoDB
    gap locks in differtent transactions are compatible only if the
    insert intention appears.
  • Generally,primary
    index and unique index does not generate gap locks like secondary
    index does.The exception is that when the condition contains a range
    of scanned indexes.The gap lock will appear according to the range
    of condition of query.
  • As for
    the “select … for update” statement,if the records are in table,it
    adds “LOCK_REC_NOT_GAP”(secondary index is “LOCK_ORDINARY”)
    otherwise it adds “LOCK_GAP”.