14.3.3 Locks Set by Different SQL Statements in InnoDB 不同的SQL语句在InnoDB里的锁设置

14.3.3 Locks Set by Different SQL Statements in InnoDB  不同的SQL语句在InnoDB里的锁设置locking read, 一个UPDATE,或者一个DELETE 通常设置record locks 在每个index record它不关心 是否语句里的WHERE条件会排除记录InnoDB 不记准确的WHERE 条件,只是知道哪个index ranges 会被扫描。锁通常是next-key locks  会堵塞插入的区间。然而, gap locking如果一个secondary index 是用于搜索,index record locks 设置为排它锁InnoDB 也检索相应的clustered index records 在它们上面加锁 shared and exclusive locks  的区别 在14.3.1, “InnoDB Locking”.有描述如果没有合适的索引对于你的语句,MySQL必须扫描整个表来处理语句,表的每行记录都会被锁住,从而阻止所有行的插入。有必要创建合适的索引,因此你的查询不必要扫描很多的行。对于SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE,locks对于扫描的行是需要的,期望被释放对于这些记录,不符合包含在结果集内的(比如,如果它们不符合WHERE条件的标准)然而, 在有些情况下,记录不会马上解锁 因为结果行和它的源头之间的关系在查询执行间丢失了,比如, 在一个UNION里,扫描的记录(或者被锁定的)记录可能被插入到一个临时表 InnoDB 设置指定的锁类型如下:SELECT ... FROM 是一个一致性读,读取一个数据快照,不加锁 除非事务隔离级别设置为SERIALIZABLE.对于SERIALIZABLE 级别,SELECT ... FROM ... LOCK IN SHARE MODE  设置shared next-key locks 在所有的搜索的记录上。对于搜索遇到的index recods,SELECT ... FROM ... FOR UPDATE  堵塞其他会话进行SELECT ... FROM ... LOCK IN SHARE MODE 一致性读忽略任何的locks 设置在records上UPDATE ... WHERE ... 设置一个排它的next-key lock 在搜索遇到的每条记录:插入设置一个排它锁 在插入的行, lock是一个index-record 锁,不是一个 next-key lock(that is, there is no gap lock) ,不阻止其他会话插入到这个区间(
在插入行前的区间)Session 1:mysql> mysql> show index from t5;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t5    |          0 | PRIMARY  |            1 | sn          | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| t5    |          0 | t5_idx1  |            1 | id          | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)mysql> insert into t5(id,info) values(50,'a50');
Query OK, 1 row affected (0.00 sec)Session 2:mysql>  insert into t5(id,info) values(50,'a50'); --Hang在插入该行之前, gap锁类型被称为一个插入间隙锁 被设置。这种锁发出信号意向插入以这样的方式, 多个事务插入到相同的index gap 不需要相互等待 如果它们不是插入相同的gap位置。假设 这里有index records 值为4和7,单独的事务尝试插入值5和6 每个都lock 4到7的区间 使用insert 意向锁来得到排它锁在插入的行,但是不会互相堵塞 因为记录是不冲突的如果一个重复键错误发生, 一个共享锁在重复index record 是被设置。使用一个共享锁 可以导致死锁 有多个会话尝试插入相同的记录如果其他的会话已经有一个排它锁。这个可能发生在如果session 删除了记录,假设InnoDB 表t1 有下面的结构；CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;夹着 3个会话按顺序执行下面的操作:Session 1:START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 2:START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 3:START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 1:ROLLBACK;Session 1的操作需要一个记录的排它锁,  Session 2和Session 3的操作都会导致重复键错误,都需要一个记录的共享锁。当Session 1回滚,它释放它的记录上排它锁,排队的共享锁请求对于session 2和session 3被授权。在这点上,session 2和3 死锁:没有一个能获得排它锁 因为被其他会话获得了共享锁类似的情况发生 如果表已经包含 键值为1的记录, 3个会话按顺序执行下面的操作:Session 1:START TRANSACTION;
DELETE FROM t1 WHERE i = 1;
Session 2:START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 3:START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 1:COMMIT;session 1的第一个操作需要一个记录的排它锁, session 2和3的操作都会导致一个重复键错误,它们都请求一个记录的共享锁。当Session 1提交时,它释放太的排它锁等待共享锁请求的对于session 2和3是被授权,在这个时间点,session 2和3死锁，没有一个能获得排它锁 因为共享锁被其他会话占有1.INSERT ... ON DUPLICATE KEY UPDATE  不同于简单的插入 一个排它 next-key lock 而不是一个共享锁 被放置在记录上2.REPLACE 是像一个INSERT 如果没有冲突在一个唯一键上,否则,一个排它的next-key lock 是被放置在记录上: