InnoDB锁冲突案例演示

时间:2019-05-11 09:40来源:计算机教程
  參考 http://blog.csdn.net/zbszhangbosen/article/details/7434637#reply Preface 这里补充一些:   (1)InnoDB默认加锁方式是next-key locking     As weknow,InnoDB is index organized table.InnoDB engine supports row-levell

 

參考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)

编辑:计算机教程 本文来源:InnoDB锁冲突案例演示

关键词: