InnoDB中不同SQL语句设置的锁

锁定读，update，或delete一般会在SQL语句处理过程中扫描的每个索引记录上加记录锁。其并不关心排除该数据行的语句中是否有where条件。InnoDB并不记得准确的where条件，但仅知道被扫描的索引范围。
锁定通常为下一键值锁，其将阻塞往锁定记录前的间隙插入数据的操作。然而，间隙锁能被显式关闭，从而引起下一键值锁不被使用。
如果二级索引被用于查找且索引记录锁被设置为排他锁，InnoDB也会获取相应的簇索引记录并在其上加锁。
如果SQL语句没有合适的索引可用，mysql必须扫描整张表来处理该语句，那么该表的每个数据行将被锁定，其反过来阻塞其他会话的所有对该表的插入操作。创建好的索引非常重要，这样，查询不必扫描很多数据行。
InnoDB设置如下特定类型的锁。
1）SELECT ... FROM为一致性读，其读取数据库快照并不加锁，除非事务隔离级别设置为serializable。对serializable级别，查找过程将对遇到的索引记录加共享下一键值锁。然而，对于通过唯一索引查找唯一数据行的语句，仅需加一个索引记录锁。
2）SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE，会对扫描的数据行加锁，且会释放不符合结果集条件的数据行的锁（例如：如果数据行并不符合where子句设定的标准）。然而，某些场景下，因为查询执行期间结果集和行源间的关系丢失，导致数据行锁也许并不会被立即释放。例如：在UNION中，扫描（且被锁定）的表数据行被评估是否符合结果集标准前，也许被插入一张临时表。这种场景下，临时表中数据行与原表中数据行的关系丢失，因此，直到查询结束，原表中的数据行锁不会被释放。
3）SELECT ... FOR UPDATE会对查找遇到的每个记录加下一键值锁。然而，通过唯一索引查找唯一数据行的语句只要求加一个索引记录锁。
对查找遇到的索引记录，SELECT ... FOR UPDATE会阻塞其他会话运行LECT ... LOCK IN SHARE MODE或特定事务级别读数据。一致性读会忽略加在读视图中记录上的任何锁。
4）UPDATE ... WHERE ...会对查找遇到的每个记录加排他下一键值锁。然而，通过唯一索引查找唯一数据行的语句只需一个索引记录锁。
5）当UPDATE修改簇索引记录时，受影响的二级索引记录也会被加隐式锁。UPDATE执行插入新二级索引记录前的重复数据检查扫描和插入新的二级索引记录时，将也会对受影响的二级索引记录加共享锁。
6）DELETE FROM ... WHERE ...会对查找遇到的每个记录加排他下一键值锁。然而，通过唯一索引查找唯一数据行的语句只需一个索引记录锁。
7）INSERT会对插入的数据行加排他锁。这些锁为索引记录锁，而非下一键值锁（也就是，没有间隙锁），且不会阻止其他会话往插入数据行前的间隙插入数据。
插入数据行前，一种叫做插入意向间隙锁（insert intention gap lock）的间隙锁被加。该锁表示往同一索引间隙但不同位置插入数据的多个事务无需相互等待。假设有值为4和7的索引记录。分别想插入值为5和6数据行的单独事务，在得到插入行的排他锁前，都将用插入意向锁锁定4和7间的间隙，因为数据行不冲突，所以事务间不会互相阻塞。
如果重复键值错误发生，将在重复索引记录上加共享锁。当有多个会话试图插入相同数据行但一个会话已经持有排他锁时，这种共享锁的使用能导致死锁。当另一个会话删除了该数据行时这种死锁将会发生。假设InnoDB表t1有下述结构：
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发生死锁：因为两个会话都持有该数据行上的共享锁，因此，两个会话都不能获取该数据行的排他锁。
如果该表已经包含键值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会发生死锁：因为两个会话都持有共享锁，所以，都不能获取该数据行上的排他锁。
8）INSERT ... ON DUPLICATE KEY UPDATE与简单的INSERT不同，当重复键错误发生时，该语句将在特定数据行上加一个排他锁而不是共享锁。重复主键值发生时会加一个排他索引记录锁。重复唯一键值发生时会加一个排他下一键值锁。
9）如果唯一键上没有冲突，REPLACE像INSERT一样被处理。否则，替换数据行上将加一个排他下一键值锁。
10）INSERT INTO T SELECT ... FROM S WHERE ...在每个插入到T表中的数据行上加一个排他索引记录锁（不加间隙锁）。如果事务隔离级别为read committed，或innodb_locks_unsafe_for_binlog被开启且事务隔离级别不为serializable,InnoDB将在S上做一致性读（没有锁）。否则，InnoDB将为S表中的数据行加共享下一键值锁。InnoDB必须对后一种场景加锁：在基于语句二进制日志的前滚恢复期间，每个SQL语句必须像最初那样被执行。
CREATE TABLE ... SELEC ...通过共享下一键值锁或作为一致性读运行，就像INSERT ... SELECT那样。
当SELECT用于REPLACE INTO t SELECT ... FROM s WHERE ...或UPDATE t ... WHERE col IN(SELECT ... FROM s ...)结构时，InnoDB为s表中的数据行加共享下一键值锁。
11）当初始化一个表的先前确定的AUTO_INCREMENT列时，InnoDB会在该AUTO_INCREMENT列相关索引的末端加一个排他锁。访问自增计数器时，InnoDB用一个确定的自增表锁模式，该模式下锁将仅持续到当前SQL语句结束，而非整个事务结束。当持有该自增表锁时，其他会话不能向该表中插入数据。InnoDB获取之前初始化的AUTO_INCREMENT列值时无需加任何锁。
12）如果表上定义一个外键约束，任何需要检查约束条件的insert,update或delete都将在查看检查该约束的记录上加共享记录级锁。当约束失败时，InnoDB也会加这些锁。
13）LOCK TABLES加表锁，但由InnoDB之上的较高mysql层加这些锁。如果innodb_table_locks=1（默认）和autocommit=0，InnoDB会知道表锁，而InnoDB之上的mysql层会知道行锁的情况。
否则，当涉及这些表锁时，InnoDB自动死锁探测机制并不能探测到死锁。同时，因为这种情况下较高mysql层并不清楚行锁情况，有可能在另一个会话目前持有行锁的表上获得一个表锁。然而，这并不能危机事务的完整性。