Gap 锁

14.3.1 InnoDB Locking  InnoDB 锁本章节描述InnoDB 使用的锁类型:Shared and Exclusive LocksIntention LocksRecord LocksGap LocksNext-Key LocksInsert Intention LocksAUTO-INC LocksShared 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           ISXConflictConflictConflictConflictIXConflictCompatibleConflictCompatibleSConflictConflictCompatibleCompatibleISConflictCompatibleCompatibleCompatible一个锁被授权来请求事务如果它是和存在的锁兼容的, 如果它和存在的锁冲突就不会授权。一个事务等待直到冲突存在的锁被释放。如果一个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=utf8session 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 changedmysql> 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: 0Session 2:mysql> insert into s100(id,info) select 13,'xxxxxxx';ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> mysql> mysql> mysql> insert into s100(id,info) select 12,'xxxxxxx';ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> insert into s100(id,info) select 14,'xxxxxxx';ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> 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: 0mysql>  update s100 set info='bbb' where id=12;Query OK, 0 rows affected (0.00 sec)Rows matched: 1  Changed: 0  Warnings: 0mysql> 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 changedmysql>  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