InnoDB鎖衝突案例演示

 1 zlm@192.168.56.100:3306 [zlm]>create table t1(
 2     -> c1 int unsigned not null default '0',
 3     -> c2 int unsigned not null default '0',
 4     -> c3 int unsigned not null default '0',
 5     -> c4 int unsigned not null default '0',
 6     -> primary key(c1),
 7     -> key(c2)
 8     -> ) engine=innodb;
 9 Query OK, 0 rows affected (0.02 sec)
10 
11 zlm@192.168.56.100:3306 [zlm]>insert into t1(c1,c2,c3,c4) values(0,0,0,0),(1,1,1,0),(3,3,3,0),(4,2,2,0),(6,2,5,0),(8,6,6,0),(10,4,4,0);
12 Query OK, 7 rows affected (0.01 sec)
13 Records: 7  Duplicates: 0  Warnings: 0
14 
15 zlm@192.168.56.100:3306 [zlm]>select * from t1;
16 +----+----+----+----+
17 | c1 | c2 | c3 | c4 |
18 +----+----+----+----+
19 |  0 |  0 |  0 |  0 |
20 |  1 |  1 |  1 |  0 |
21 |  3 |  3 |  3 |  0 |
22 |  4 |  2 |  2 |  0 |
23 |  6 |  2 |  5 |  0 |
24 |  8 |  6 |  6 |  0 |
25 | 10 |  4 |  4 |  0 |
26 +----+----+----+----+
27 7 rows in set (0.01 sec)
28 
29 zlm@192.168.56.100:3306 [(none)]>select @@transaction_isolation;
30 +-------------------------+
31 | @@transaction_isolation |
32 +-------------------------+
33 | REPEATABLE-READ         | //Make surej in RR transaction isolation level.
34 +-------------------------+
35 1 row in set (0.00 sec)
36 
37 zlm@192.168.56.100:3306 [(none)]>show variables like 'innodb_status_output_locks';
38 +----------------------------+-------+
39 | Variable_name              | Value |
40 +----------------------------+-------+
41 | innodb_status_output_locks | ON    |
42 +----------------------------+-------+
43 1 row in set (0.00 sec)

 1 //Session1：
 2 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1=3 for update;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 +----+----+----+----+
 6 | c1 | c2 | c3 | c4 |
 7 +----+----+----+----+
 8 |  3 |  3 |  3 |  0 |
 9 +----+----+----+----+
10 1 row in set (0.00 sec)
11 
12 //Session2:
13 monitor@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1=3 lock in share mode;
14 Query OK, 0 rows affected (0.00 sec)
15 
16 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
17 
18 //Session2 requested a "S" record lock on the primary key column where c1=3 while session1 has holded the "X" record lock on the same position,so session2 was blocked util lock timeout.

 1 //Session1：
 2 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1=3 for update;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 +----+----+----+----+
 6 | c1 | c2 | c3 | c4 |
 7 +----+----+----+----+
 8 |  3 |  3 |  3 |  0 |
 9 +----+----+----+----+
10 1 row in set (0.00 sec)
11 
12 //Session2:
13 monitor@192.168.56.100:3306 [zlm]>select * from t1 where c1=3;
14 +----+----+----+----+
15 | c1 | c2 | c3 | c4 |
16 +----+----+----+----+
17 |  3 |  3 |  3 |  0 |
18 +----+----+----+----+
19 1 row in set (0.00 sec)
20 
21 //Session1 didn't change this time and session2 request for non-lock consistent read.It read records from a consistent snapshop without locking.

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c3=7 lock in share mode;
 3 Query OK, 0 rows affected (0.01 sec)
 4 
 5 Empty set (0.00 sec)
 6 
 7 //Session2:
 8 monitor@192.168.56.100:3306 [zlm]>begin;select * from t1 where c3=10 for update;
 9 Query OK, 0 rows affected (0.00 sec)
10 
11 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
12 
13 //Although there's no record satisfied with c3=7 but notice that there's no index on c3 column.Therefore,the session1 has holded a "S" record for all the records on column c1 in table t1.Then session2 asked for the "X" record lock for "c3=10"(even it does not exixt),it was blocked.

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c3=7 lock in share mode;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Empty set (0.00 sec)
 6 
 7 //Session2:
 8 monitor@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1=6 for update;
 9 Query OK, 0 rows affected (0.00 sec)
10 
11 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
12 
13 //This is similar with "test 3".Session1 has holded a "S" record lock of all records on column c3.The record where c1=6 means c3=5,it's also in the range of all records.So session2 was blocked.

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c2=2 and c3=5 for update;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 +----+----+----+----+
 6 | c1 | c2 | c3 | c4 |
 7 +----+----+----+----+
 8 |  6 |  2 |  5 |  0 |
 9 +----+----+----+----+
10 1 row in set (0.00 sec)
11 
12 //Session2:
13 monitor@192.168.56.100:3306 [zlm]>begin;select * from t1 where c2=2 and c3=7 for update;
14 Query OK, 0 rows affected (0.00 sec)
15 
16 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
17 
18 //Because of the secondary index key on column c2,it generated a "X" record lock and a gap lock(record + gap = next key lock).Although the gap lock between two sessions can be coexistent,but record locks do not.So session2 was blocked.

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c2=2 and c3=5 for update;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 +----+----+----+----+
 6 | c1 | c2 | c3 | c4 |
 7 +----+----+----+----+
 8 |  6 |  2 |  5 |  0 |
 9 +----+----+----+----+
10 1 row in set (0.00 sec)
11 
12 //Session2:
13 monitor@192.168.56.100:3306 [zlm]>begin;select * from t1 where c2=3 and c3=7 for update;
14 Query OK, 0 rows affected (0.00 sec)
15 
16 Empty set (0.00 sec)
17 
18 //This time session2 was not blocked.They've requested a different "X" record lock individually even they still hold the gap lock.

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c2=2 and c3=2 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 +----+----+----+----+
10 1 row in set (0.00 sec)
11 
12 //Session2:
13 monitor@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1=4 and c3=10 for update;
14 Query OK, 0 rows affected (0.00 sec)
15 
16 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
17 
18 //The query condition in session2 is c1=4.It means c2=2,this is similar with test 5(asked for the equal line).

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;update t1 set c4=20 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 //Session2:
 9 monitor@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1=7 for update;
10 Query OK, 0 rows affected (0.00 sec)
11 
12 Empty set (0.00 sec)
13 
14 //The records according to the query condition c2>=4 were c1=8 and c1=10.
15 //Even though there's a index key on c1 but it's a primary key which doesn't generate gap lock.So session2 's asking for "X" record lock of c1=7 was not blocked.

• We should pay more attention to innodb row-level locks.If there's no key on the relevant column,the locks will be escalated to "table-level"(all records will be locked) locks.
• In the RR transaction isolation level,Secondary index generates gap locks(LOCK_ORDINARY) to prevent phantom read while primary index and unique index do not.They only hold record locks(LOCK_REC_NOT_GAP).
• In the RC transaction isolation level,there're no gap locks.Therefore,it's concurrency is better than that in RR mode,but the consistency is poor as well.
• As for which transaction isolation level we should choose is depend on your purpose:for more consistency or for more concurrency.