InnoDB锁冲突案例演示

Posted by

 

Preface

 

    As we
know,InnoDB is index organized table.InnoDB engine supports row-level
lock base on indexes,if there’re no indexes on a certain table the
record locks will upgrade to “table-level”(not really table lock,just
locks all the records in the table) locks.Furthe more,in RR transaction
isolation mode,It’s more complicated.’cause there’re gap locks(together
with record locks,we call them next key locks) to prevent phantom read
between multiple tansactions.Let’s do some test watch the locking
conflicts.

 

Procedure

 

Crete a
test table as below.

 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)

 

**Test 1.
session1 executes “select …  for update” and session2 executes “select
… lock in share mode”.(conflict)**

 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.

 

**Test 2.
session1 executes “select …  for update” and 365bet平台,session2
executes ordinary query.(**compatible**)**

 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.

 

**Test 3.
session1 executes “select …  lock in share mode” and session2
executes “select … for update”.(conflict)**

 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.

 

**Test 4.
session1 executes “select …  lock in share mode” and session2
executes “select … for update”.(conflict)**

 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.

 

**Test 5.
session1 executes “select …  for update” and session2
executes “select … for update”.(conflict)**

 

 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.

 

***Test

  1. session1 executes “select …  for update” and session2
    executes “select … for
    update”.(compatible*)**

    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.

 

***Test

  1. session1 executes “select …  for update” and session2
    executes “select … for update”.*(**conflict**)

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

 

***Test

  1. session1 executes “select …  for update” and session2
    executes “select … for update”.(**compatible***)

    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.

 

Summary

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

 

相关文章

Leave a Reply

电子邮件地址不会被公开。 必填项已用*标注