InnoDB锁冲突案例演示

 

參考http://blog.csdn.net/zbszhangbosen/article/details/7434637\#reply

Preface

这里补充一些:

 

(1)InnoDB默认加锁方式是next-key locking

    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.

(2)在聚集索引中,假设主键有唯一性约束(unique,auto
increment),next-key locking 会自己主动降级为record locking。

 

(3)因为事务的隔离性和一致性要求,会对全部扫描到的record加锁。

Procedure

比方:update … where/delete .. where/select …from…lock in share
mode/ select .. from .. for update这都是next-key lock。

 

(4)注意优化器的选择。

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)

record lock:记录锁,也就是只锁着单独的一行

 

gap
lock:区间锁。只锁住一个区间(注意这里的区间都是开区间。也就是不包含边界值,至于为什么这么定义?innodb官方定义的)
next-key lock:record lock+gap lock,所以next-key
lock也就半开半闭区间,且是下界开,上界闭。(为什么这么定义?innodb官方定义的)
以下来举个手冊上的样例看什么是next-key lock。

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

假如一个索引的行有10,11,13,20
那么可能的next-key lock的包含:
(无穷小, 10]
(10,11]
(11,13]
(13,20]
(20, 无穷大) (这里无穷大为什么不是闭合?你数学不到家~~)
好了如今通过举样例说明:

 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
mysql> show create table test;
+——-+——————————————————————————————————–+
| Table | Create Table                                                  
                                        |
+——-+——————————————————————————————————–+
| test  | CREATE TABLE `test` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+——-+——————————————————————————————————–+
1 row in set (0.00 sec)
mysql> select * from test;
+—-+
| a  |
+—-+
| 11 |
| 12 |
| 13 |
| 14 |
+—-+
4 rows in set (0.00 sec)
開始实验:
(一)
session 1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

 

mysql> delete from test where a=11;
Query OK, 1 row affected (0.00 sec)

**Test 2.
session1 executes “select …  for update” and session2
executes ordinary query.(**compatible**)**

session 2:
mysql> start transaction;
Query OK, 0 rows affected (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]>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.

mysql> insert into test values(10);
Query OK, 1 row affected (0.00 sec)

 

mysql> insert into test values(15);
Query OK, 1 row affected (0.00 sec)

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

mysql> insert into test values(9);
Query OK, 1 row affected (0.00 sec)

 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.

mysql> insert into test values(16);
Query OK, 1 row affected (0.01 sec)

 

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

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

ok,上面的情况是预期的。由于a上有索引。那么当然就仅仅要锁定一行,所以其它行的插入不会被堵塞。

 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.

那么接下来的情况就有意思了
(二)
session 1(跟上一个session 1同样):
delete from test where a=22;
Query OK, 0 rows affected (0.01 sec)

 

session 2:
mysql> insert into test values (201);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
transaction
mysql> insert into test values (20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
transaction
mysql> insert into test values (19);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
transaction
mysql> insert into test values (18);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
transaction
mysql> insert into test values (16);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
transaction
mysql> insert into test values (9);
Query OK, 1 row affected (0.00 sec)

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

从上面的结果来看,在a=11后面全部的行,也就是区间(11,无穷大)都被锁定了。先不解释原因,再来看一种情况:
(三)
session 1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from test;
+—-+
| a  |
+—-+
|  7 |
|  9 |
| 10 |
| 12 |
| 13 |
| 14 |
| 15 |
| 22 |
| 23 |
| 24 |
| 25 |
+—-+
11 rows in set (0.00 sec)

 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.

mysql> delete from test where a=21;
Query OK, 0 rows affected (0.00 sec)

 

session 2:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

***Test

mysql> insert into test values (20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
transaction
mysql> insert into test values (26);
Query OK, 1 row affected (0.00 sec)

  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.

mysql> insert into test values (21);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
transaction
mysql> insert into test values (16);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
transaction
mysql> insert into test values (6);
Query OK, 1 row affected (0.01 sec)

 

从这里能够看出。如今被锁住的区间就仅仅有[16,21)了。
有了前面对三种类型的加锁解释,如今能够来解释为什么会这样了,在innodb表中
delete from where
..针对扫描到的索引记录加next-key锁(详细的什么语句加什么锁能够查看手冊,另外须要说明一下。行锁加锁对象永远是索引记录,由于innodb中表即索引)

在(一)中。实际上加的next-key lock就是(11,11]
因此也仅仅有a=11这一条记录被锁住。其它全部插入都没有关系。
在(二)中,由于a=22这条记录不存在,并且22比表里全部的记录值都大,所以在innodb看来锁住的区间就是(14,
无穷大)。

***Test

所以在插入14以后的值都提示被锁住,而14之前的则能够。

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

在(三)种。a=21也是不存在,可是在表里面21前后都有记录。因此这里next-key
lock的区间也就是(15,21],因此不在这个区间内的都能够插入。
那么为什么next-key
lock都是下界开区间。上界闭区间呢?这个倒不重要,管它呢,可是有一点我个人却认为比較怪,比方说
delete test where a > 11           #——- 1
它的next-key lock是(11, 无穷大) 
delete test where a < 11           #——- 2
它的next-key lock是(无穷小, 10]
这样给人的感觉就非常怪。由于在手冊上对next-key lock的定义:
Next-key lock: This is a combination of a record lock on the index
record and a gap lock on the gapbefore the index record.
而在1那种情况下,如果依照手冊上的解释,记录锁和它之前的gap那么就会有些牵强。

 

[今天再次看了一遍官方手冊,是之前自己的理解不到位,这个before是对的,由于innodb在加锁时是全部扫描过程中遇到的记录都会被加锁,那么对于1那种情况,实际上是从12開始扫描,可是由于要保证a>11的都被delete掉。因此得一直扫描下去那自然最大值就是无穷大,由于这个next-key
lock就是无穷大这条记录(这是如果的一条记录,表示一个边界)加上它之前的gap
lock (11, 无穷大),所以在不论什么时候next-lock都是record
lock加上这个record之前的一个gap lock]
可是仅仅要我们自己能理解即可了:记录锁—锁单条记录。区间锁—锁一个开区间。next-key
锁—前面两者的结合,而不要管什么before。
另外next-key
lock尽管在非常多时候是锁一个区间,但要明确一个区间也可能仅仅有一个元素。因此在称delete
from tb where key=x 这样的情况下加next-key锁也是全然正确的。

***Test

另外还提两点:
1.假设我们的SQL语句里面没有利用到索引。那么加锁对象将是全部行(但不是加表锁)。所以建索引是非常重要的
2.next-key
lock是为防止幻读的发生,而仅仅有repeatable-read以及以上隔离级别才干防止幻读。所以在read-committed隔离级别以下没有next-key
lock这一说法。

  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.