每种sql使用的锁类型
2019-02-28 本文已影响17人
骁兵
锁定读、update和delete通常会在sql运行时，对扫描到的行设置记录锁，而不管where语句是否匹配该行，锁通常是Next-Key锁（记录锁+间隙锁），以防止其他事务在记录索引之前插入数据。
如果查询使用二级索引，InnoDB会获取相应的聚簇索引并对其加锁。
如果查询中没有索引，Mysql必须扫描全表，表中的每一行都将被锁，以防止其他事务插入数据。
- SELECT ... FROM是一致性读，读取的是快照版本，所以无设置锁（除非使用序列化隔离级别），如果是序列化隔离级别，查询会对涉及到的数据加共享Next-Key锁，但是，对于使用唯一索引搜索唯一行的语句，只需要索引记录锁。
- SELECT ... FOR UPDATE 和 SELECT ... FOR SHARE两个语句会对扫描的行加锁，并释放不符合where语句的行上面的锁。
- 锁定读（使用for update和for share的SELECT）、UPDATE、DELETE语句，这几个语句使用什么锁，取决于他们使用唯一索引（x=y）搜索还是范围搜索
- 唯一索引：只使用记录锁，没有间隙锁
- 范围搜索：使用间隙锁或next-key锁
- 对于扫描到索引记录，select for update会阻塞其他事务的select for share，一致性读会忽略所有锁
- UPDATE ... WHERE ...在搜索遇到的每条记录上设置一个排他的next-key锁，但使用唯一索引（x=y），只使用记录锁，没有间隙锁
- 当update修改聚簇索引记录，一个隐式的锁会被加到二级索引上
- DELETE FROM ... WHERE ...在搜索遇到的每条记录上设置一个排他的next-key锁，但使用唯一索引（x=y），只使用记录锁，没有间隙锁
- INSERT设置一个排他记录锁在被添加的行上面，没有加间隙锁。但有一个特殊的地方，在插入行之前，一个被称作的插入意向gap锁的gap锁会被设置。这个锁表示，当多个事务插入同一个gap的不同位置时，他们不会相互阻塞。
如果发生重复键错误，一个共享记录锁将被设置在重复键上，当其他事务拥有了这个键的排它锁时，这个共享锁可能会导致死锁。比如：
CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
假设有三个事务按以下顺序执行
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;
事务1获取一个排它锁在新添加的行上，事务2和3会发生重复键错误，并都请求获取一个新行的共享锁，等到事务1回退，它释放排它锁，然后事务2和3都获取到共享锁，接着事务2和事务3会请求排它锁，但因为他们都拥有共享锁，这时会发生死锁，它们任何一个都无法获取排它锁。
假设数据库已经包含了新添加的key为1的行，三个事务执行以下操作
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;
事务1获取一个排它锁在要删除的行上，事务2和3会发生重复键错误，并都请求获取一个新行的共享锁，等到事务1提交后，它释放排它锁，然后事务2和3都获取到共享锁，接着事务2和事务3会请求排它锁，但因为他们都拥有共享锁，这时会发生死锁，它们任何一个都无法获取排它锁。
- INSERT ... ON DUPLICATE KEY UPDATE，跟普通的insert不同，当发生重复键错误时，它会获取排它锁而不是共享锁，对于主键重复错误，获取排他记录锁，对于唯一列重复错误，获取排他next-key锁。
- 没有唯一键冲突时，REPLACE跟INSERT一样，否则会在替换的行上加一个排他next-key锁
- INSERT INTO T SELECT ... FROM S WHERE ... 设置一个排他记录锁（没有gap锁）在每一个新添加的行上。如果隔离级别是读已提交，select S表的操作是一致性读，否则InnoDB设置一个共享next-key锁在S表被查找的行上。
- CREATE TABLE ... SELECT ...的SELECT语句用到的锁跟INSERT INTO T SELECT ... FROM S WHERE ...的SELECT一样
- 在初始化表上的先前指定的AUTO_INCREMENT列时，InnoDB在与AUTO_INCREMENT列关联的索引的末尾设置独占锁。在访问自动增量计数器时，InnoDB使用特定的AUTO-INC表锁定模式，其中锁定仅持续到当前SQL语句的末尾就释放，而不是等到事务结束才释放。在持有AUTO-INC表锁时，其他事务无法执行插入操作。
- 如果存在外键，任何需要检查约束条件的插入，更新或删除都会在它查看的记录上设置共享记录锁定以检查约束。
- LOCK TABLES 加表锁
