14.3.1 InnoDB Locking  InnoDB 锁

本章节描述InnoDB 使用的锁类型:

Shared and Exclusive Locks

Intention Locks

Record Locks

Gap Locks

Next-Key Locks

Insert Intention Locks

AUTO-INC Locks

Shared and Exclusive Locks  共享锁和排他锁:

InnoDB 实现 标准的row-level locking  这里有两种类型的lcoks,

 shared (S) locks and exclusive (X) locks.

 1.共享锁(S锁) 允许事务持有lock来读取一条记录

 2.一个exclusive (X) lock 允许事务持有锁来更新或者删除一条记录

 如果事务T1持有一个共享锁在记录r上, 

 那么另外一个事务T2 请求一个lock 在记录r上是如下处理的:

 T2 请求一个S锁会被立即授予,因此,T1和T2 都持有S锁在记录r上

 T2请求一个X锁 不能被立即授予

 如果一个事务T1持有1个排他锁在记录r上, 那么另外一个事务T2请求任何类型的锁在记录r上

 都不能立即被授权,代替的是 事务T2 需要等待事务T1释放记录r上的锁。

 Intention Locks 意向锁:

 InnoDB 支持多种丽都的锁 允许row-level locks and locks 在整个表上并存。

 为了使锁在多个粒度级别可用,

 额外类型的锁被称为意向锁被使用。意向锁是表级锁 在InnoDB里

 表明哪种类型的锁(共享或者排他) 一个事务将随后需要对于表里的一行记录。

 这里有两种类型的意向锁用于InnoDB(假设事务T 已经请求了一个lock)

 1. Intention shared (IS): 事务T想要设置S锁在表t的单独行上

 2.Intention exclusive (IX):  事务T想要设置X锁在那些行上。

 比如,SELECT ... LOCK IN SHARE MODE 设置一个IS 锁和

 SELECT ... FOR UPDATE 设置一个IX锁

 意向锁协议如下:

 1.在一个事务能获取一个S锁 在表t的一条记录上,它必须首选获得一个IS或者更强的锁

 2. 在一个事务可以获得一个X锁 在一条记录上,它必须首选获得一个IX锁在表t上

 这些规则可以很方便地通过以下的锁类型兼容性矩阵。

 	   X	            IX	             S	           IS

X	Conflict	Conflict	Conflict	Conflict

IX	Conflict	Compatible	Conflict	Compatible

S	Conflict	Conflict	Compatible	Compatible

IS	Conflict	Compatible	Compatible	Compatible

一个锁被授权来请求事务如果它是和存在的锁兼容的, 如果它和存在的锁冲突就不会授权。

一个事务等待直到冲突存在的锁被释放。

如果一个lock 和一个存在的锁冲突,那么不能被授权 因为它会导致死锁

因此,意向锁不会堵塞任何除非全表请求(比如 LOCK TABLES ... WRITE)

IX 和IS 锁的主要目的是显示 有人锁定了一行,或者将锁定一行。

MySQL官网上有个死锁的例子，但分析得过于概括，这里我们详细分析一下。

首先，会话S1以SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE查询，该语句首先会对t表加IS锁，接着会对数据（i = 1）

加S锁。

mysql>  select * from test where id=1 LOCK IN SHARE MODE;

+------+------+

| id   | name |

+------+------+

|    1 | b    |

+------+------+

1 row in set (0.00 sec)

接着，会话S2执行DELETE FROM t WHERE i = 1，该语句尝试对t表加IX锁，由于IX锁与IS锁是兼容的，所以成功对t表加IX锁。

接着继续对数据（i = 1）加X锁，但数据已经被会话S1事务加了S锁了，所以会话S2等待。

mysql> delete from test where id=1; --HANG

接着，会话S1也执行DELETE FROM t WHERE i = 1，该语句首先对t表加IX锁，虽然会话S2已经对t表加了IX锁，但IX锁与IX锁之间

是兼容的，所以成功对t表加上IX锁。

接着会话S1会对数据（i = 1）加X锁，此时发现会话S2占有的IX锁与X锁不兼容，所以会话S1也等待。就这样，会话S1等S2释放IX

锁，而会话S2等S1释放S锁，从而死锁发生。

会话S2 产生死锁:

mysql> delete from test where id=1;

ERROR 1213 (40001): Deadlock found when trying to get lock; 

Record Locks  记录锁:

一个record lock 是lock在一个Index record.比如,

SELECT c1 FOR UPDATE FROM t WHERE c1 = 10;

阻止任何其他事物来插入,更新或者删除t1.c1=10的记录

Record locks  总是lock index 记录,尽管一个表时定义为没有索引。

对于这样的请求, InnoDB 创建一个隐含的 clustered index和使用这个index 

用于record locking.

Gap Locks  区间锁

一个gap lock 是在一个区间在Index records 之间,

或者一个区间在第一个之前或者最后一个之后。

比如,

SELECT c1 FOR UPDATE FROM t WHERE c1 BETWEEN 10 and 20;

阻止其他事物插入值15到 c1列,

不管时候已经有这样的值在列里,因为Gap在存在的值之间 会被锁定

CREATE TABLE `t1` (

  `sn` int(11) NOT NULL AUTO_INCREMENT,

  `id` int(11) DEFAULT NULL,

  `info` varchar(40) DEFAULT NULL,

  PRIMARY KEY (`sn`)

) ENGINE=InnoDB AUTO_INCREMENT=235 DEFAULT CHARSET=utf8

session 1:

mysql> select * from t1;

+-----+------+------+

| sn  | id   | info |

+-----+------+------+

| 235 |    1 | a1   |

| 236 |    2 | a2   |

| 237 |    3 | a3   |

| 238 |    4 | a4   |

| 239 |    5 | a5   |

| 240 |    6 | a6   |

| 241 |    7 | a7   |

| 242 |    8 | a8   |

| 243 |    9 | a9   |

| 244 |   10 | a10  |

+-----+------+------+

10 rows in set (0.00 sec)

mysql> select * from t1 where id BETWEEN 10 and 20 for update

    -> ;

+-----+------+------+

| sn  | id   | info |

+-----+------+------+

| 244 |   10 | a10  |

+-----+------+------+

1 row in set (0.00 sec)

session 2:

mysql> insert into t1(id ,info) values(15,'a15');--hang

/*******************************

session 1:

mysql> select * from t1;

+-----+------+------+

| sn  | id   | info |

+-----+------+------+

| 235 |    1 | a1   |

| 236 |    2 | a2   |

| 237 |    3 | a3   |

| 238 |    4 | a4   |

| 239 |    5 | a5   |

| 240 |    6 | a6   |

| 241 |    7 | a7   |

| 242 |    8 | a8   |

| 243 |    9 | a9   |

| 244 |   10 | a10  |

| 245 |   15 | a15  |

+-----+------+------+

11 rows in set (0.00 sec)

mysql> select * from t1 where id BETWEEN 10 and 20 for update;

+-----+------+------+

| sn  | id   | info |

+-----+------+------+

| 244 |   10 | a10  |

| 245 |   15 | a15  |

+-----+------+------+

2 rows in set (0.00 sec)

session 2:

Database changed

mysql> insert into t1(id,info) values(15,'a15'); --hang

一个区间 可以跨越一个单独的index value,多个index value,甚至是空的。

区间锁 是在性能和并发折中的一部分,用于一些事务隔离级别而不是其他

区间锁对于那些语句 锁定行实用一个唯一索引来搜索唯一的记录是不需要的

(这个不包括这种情况 查询条件只包含了多列唯一索引的部分列,在这种情况下,

区间锁确实会发生0

比如,如果id列有一个唯一索引,下面的语句使用一个 index-record lock 对于ID=100的行

它没有关系 是否其他session 插入行 在前面的区间)

/***********************************

Session 1:

mysql> select * from s100;

+-----+------+------+

| sn  | id   | info |

+-----+------+------+

| 227 |    1 | 1a   |

| 228 |    3 | 3a   |

| 229 |    6 | 6a   |

| 230 |    9 | 9a   |

| 231 |   12 | 12a  |

| 232 |   15 | 15a  |

| 233 |   18 | 18a  |

+-----+------+------+

7 rows in set (0.00 sec)

mysql> show index from s100;

+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| s100  |          0 | PRIMARY   |            1 | sn          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |

| s100  |          1 | s100_idx1 |            1 | id          | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |

+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

2 rows in set (0.00 sec)

mysql> show variables like '%tx_isolation%';

+---------------+-----------------+

| Variable_name | Value           |

+---------------+-----------------+

| tx_isolation  | REPEATABLE-READ |

+---------------+-----------------+

1 row in set (0.00 sec)

mysql> update s100 set info='bbb' where id=12;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

Session 2:

mysql> insert into s100(id,info) select 13,'xxxxxxx';

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql>

mysql>

mysql>

mysql> insert into s100(id,info) select 12,'xxxxxxx';

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into s100(id,info) select 14,'xxxxxxx';

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into s100(id,info) select 15,'xxxxxxx';

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

会从12 锁到14

改成unique index 呢？

Session 1:

mysql> show index from s100;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| s100  |          0 | PRIMARY  |            1 | sn          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set (0.00 sec)

mysql> create unique index s100_idx1 on s100(id);

Query OK, 0 rows affected (0.05 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql>  update s100 set info='bbb' where id=12;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1  Changed: 0  Warnings: 0

mysql> show index from s100;

+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| s100  |          0 | PRIMARY   |            1 | sn          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |

| s100  |          0 | s100_idx1 |            1 | id          | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |

+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

2 rows in set (0.00 sec)

Session 2:

Database changed

mysql>  insert into s100(id,info) select 13,'xxxxxxx';

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

此时正常

如果没有索引或者只是 a nonunique index, 这个语句会lock 前区间

同样值得注意的是 冲突的锁可以通过不同的事务被持有在一个区间。

比如,事务A 可以持有一个共享的区间锁(gap S-lock)

在一个区间 当事务B持有一个排他的lock 在同样的区间。

冲突的gap锁可以被允许的原因是如果一个记录是从index 被清除,

区间锁持有的记录被不同的事务必须被合并

Gap locks 在InnoDB 是纯粹的抑制,这意味着只有停止其他事务插入到区间.

它们不阻止不同的事务占据区间锁在相同的区间,因此 a gap X-lock has the same effect as a gap S-lock.

Gap locking 可以被显示的禁用，这个发生在如果你改变了事务的隔离级别

到 READ COMMITTED 或者启用 innodb_locks_unsafe_for_binlog system variable

在这种情况下，gap locking 是禁用的 对于查询和index scans 

只用于外键约束检查和重复键检查

Next-Key Locks