locking read、 an
UPDATE
或
generally set 记录锁定在 SQL 语句处理中扫描
的DELETE
每个索引记录。WHERE
语句中是否存在排除该行的条件并不重要
。InnoDB
不记得确切的WHERE
情况,只知道扫描了哪些索引范围。这些锁通常是
next-key 锁,它也会阻止插入记录之前的“间隙” 。但是,
可以显式禁用间隙锁定,这会导致不使用下一键锁定。有关详细信息,请参阅
第 14.7.1 节,“InnoDB 锁定”。事务隔离级别也会影响设置的锁;请参阅
第 14.7.2.1 节,“事务隔离级别”。
如果在搜索中使用二级索引并且要设置的索引记录锁是独占的,InnoDB
还检索相应的聚簇索引记录并对其设置锁。
如果您没有适合您的语句的索引,并且 MySQL 必须扫描整个表来处理该语句,那么表的每一行都会被锁定,这反过来会阻止其他用户对该表的所有插入。创建良好的索引很重要,这样您的查询就不会扫描不必要的行。
InnoDB
如下设置特定类型的锁。
SELECT ... FROM
是一致读取,读取数据库的快照并且不设置锁,除非事务隔离级别设置为SERIALIZABLE
. 对于SERIALIZABLE
级别,搜索在它遇到的索引记录上设置共享的下一键锁。但是,对于使用唯一索引锁定行以搜索唯一行的语句,只需要索引记录锁。对于
SELECT ... FOR UPDATE
orSELECT ... LOCK IN SHARE MODE
,为扫描的行获取锁,并期望为不符合包含在结果集中的行释放锁(例如,如果它们不符合WHERE
子句中给出的条件)。但是,在某些情况下,行可能不会立即解锁,因为结果行与其原始源之间的关系在查询执行期间丢失了。例如,在一个UNION
,在评估它们是否符合结果集之前,可能会将来自表的扫描(和锁定)行插入到临时表中。在这种情况下,临时表中的行与原始表中的行的关系丢失,并且后面的行直到查询执行结束才解锁。SELECT ... LOCK IN SHARE MODE
在搜索遇到的所有索引记录上设置共享的下一键锁。但是,对于使用唯一索引锁定行以搜索唯一行的语句,只需要索引记录锁。SELECT ... FOR UPDATE
在搜索遇到的每条记录上设置排他的下一键锁。但是,对于使用唯一索引锁定行以搜索唯一行的语句,只需要索引记录锁。对于搜索遇到的索引记录,
SELECT ... FOR UPDATE
阻止其他会话执行SELECT ... LOCK IN SHARE MODE
或读取某些事务隔离级别。一致读取忽略读取视图中存在的记录上设置的任何锁。UPDATE ... WHERE ...
在搜索遇到的每条记录上设置排他的下一键锁。但是,对于使用唯一索引锁定行以搜索唯一行的语句,只需要索引记录锁。修改聚簇索引记录时
UPDATE
,会对受影响的二级索引记录进行隐式锁定。UPDATE
在插入新的二级索引记录之前执行重复检查扫描时,以及插入新的二级索引记录时, 该 操作还会对受影响的二级索引记录使用共享锁。DELETE FROM ... WHERE ...
在搜索遇到的每条记录上设置排他的下一键锁。但是,对于使用唯一索引锁定行以搜索唯一行的语句,只需要索引记录锁。INSERT
在插入的行上设置排他锁。这个锁是index-record lock,不是next-key lock(即没有gap lock),不会阻止其他session插入插入行之前的gap。在插入行之前,设置一种称为插入意图间隙锁的间隙锁。这个锁表示插入的意图,这样插入到同一个索引间隙中的多个事务如果没有插入到间隙中的相同位置,则不需要相互等待。假设有值为 4 和 7 的索引记录。尝试插入值 5 和 6 的单独事务在获得对插入行的排他锁之前分别使用插入意向锁锁定 4 和 7 之间的间隙,但不相互阻塞,因为行不冲突。
如果发生重复键错误,则会在重复索引记录上设置共享锁。如果另一个会话已经具有独占锁,那么如果有多个会话试图插入同一行,那么使用共享锁可能会导致死锁。如果另一个会话删除该行,就会发生这种情况。假设一个
InnoDB
表t1
有如下结构:CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
现在假设三个会话依次执行以下操作:
第 1 节:
START TRANSACTION; INSERT INTO t1 VALUES(1);
第 2 节:
START TRANSACTION; INSERT INTO t1 VALUES(1);
第 3 节:
START TRANSACTION; INSERT INTO t1 VALUES(1);
第 1 节:
ROLLBACK;
会话 1 的第一个操作获取该行的独占锁。会话 2 和会话 3 的操作都导致重复键错误,并且它们都请求该行的共享锁。当会话 1 回滚时,它会释放对该行的独占锁,并授予会话 2 和 3 的排队共享锁请求。此时,会话 2 和会话 3 死锁:由于对方持有共享锁,因此无法获取该行的排他锁。
如果表中已经包含键值为 1 的行,并且三个会话依次执行以下操作,则会出现类似情况:
第 1 节:
START TRANSACTION; DELETE FROM t1 WHERE i = 1;
第 2 节:
START TRANSACTION; INSERT INTO t1 VALUES(1);
第 3 节:
START TRANSACTION; INSERT INTO t1 VALUES(1);
第 1 节:
COMMIT;
会话 1 的第一个操作获取该行的独占锁。会话 2 和会话 3 的操作都导致重复键错误,并且它们都请求该行的共享锁。当会话 1 提交时,它会释放对该行的独占锁,并授予会话 2 和 3 的排队共享锁请求。此时,会话 2 和会话 3 死锁:由于对方持有共享锁,因此无法获取该行的排他锁。
INSERT ... ON DUPLICATE KEY UPDATE
与 simpleINSERT
的不同之处在于,当发生重复键错误时,将排他锁而不是共享锁放在要更新的行上。对重复的主键值采用独占索引记录锁。对重复的唯一键值采用独占下一键锁。INSERT INTO T SELECT ... FROM S WHERE ...
在插入到的每一行上设置排他索引记录锁(没有间隙锁)T
。如果事务隔离级别为READ COMMITTED
,或innodb_locks_unsafe_for_binlog
已启用且事务隔离级别不是SERIALIZABLE
,InnoDB
则搜索S
作为一致读取(无锁)进行。否则,InnoDB
对来自 的行设置共享的下一键锁S
。InnoDB
在后一种情况下必须设置锁:在使用基于语句的二进制日志进行前滚恢复期间,每个 SQL 语句都必须以与最初相同的方式执行。CREATE TABLE ... SELECT ...
使用共享的下一键锁或作为一致读取执行SELECT
,至于INSERT ... SELECT
.当
SELECT
在结构REPLACE INTO t SELECT ... FROM s WHERE ...
or中使用 a 时UPDATE t ... WHERE col IN (SELECT ... FROM s ...)
,InnoDB
对表中的行设置共享的下一键锁s
。InnoDB
AUTO_INCREMENT
在初始化表上先前指定的列时,在与列关联的索引的末尾设置排他锁AUTO_INCREMENT
。使用
innodb_autoinc_lock_mode=0
,InnoDB
使用特殊的AUTO-INC
表锁模式,在访问自增计数器时获取锁并保持到当前 SQL 语句的末尾(而不是整个事务的末尾)。AUTO-INC
当持有表锁时,其他客户端无法插入到表中。相同的行为发生在“批量插入” 中innodb_autoinc_lock_mode=1
。表级AUTO-INC
锁不与 一起使用innodb_autoinc_lock_mode=2
。有关更多信息,请参阅 第 14.6.1.6 节,“InnoDB 中的 AUTO_INCREMENT 处理”。InnoDB
AUTO_INCREMENT
在不设置任何锁 的情况下获取先前初始化的列的值。如果
FOREIGN KEY
在表上定义了约束,则任何需要检查约束条件的插入、更新或删除都会在它查看以检查约束的记录上设置共享记录级锁。InnoDB
在约束失败的情况下也会设置这些锁。LOCK TABLES
InnoDB
设置表锁,但它是设置这些锁 的层之上的更高 MySQL 层 。InnoDB
如果innodb_table_locks = 1
(默认)和 知道表锁autocommit = 0
,并且上面的 MySQL 层InnoDB
知道行级锁。否则,
InnoDB
的自动死锁检测无法检测到涉及此类表锁的死锁。此外,因为在这种情况下,较高的 MySQL 层不知道行级锁,所以有可能在另一个会话当前具有行级锁的表上获得表锁。但是,这不会危及事务完整性,如 第 14.7.5.2 节“死锁检测”中所述。LOCK TABLES
innodb_table_locks=1
如果(默认),则在每个表上获取两个锁。除了MySQL层的表锁,它还获取了InnoDB
表锁。为避免获取InnoDB
表锁,请设置innodb_table_locks=0
. 如果没有InnoDB
获取表锁,LOCK TABLES
即使表的某些记录被其他事务锁定,也会完成。在 MySQL 5.7 中,
innodb_table_locks=0
对使用 显式锁定的表没有影响LOCK TABLES ... WRITE
。LOCK TABLES ... WRITE
它确实对通过隐式(例如,通过触发器)或通过锁定以供读取或写入的表有影响LOCK TABLES ... READ
。InnoDB
当事务提交或中止时,事务持有的 所有锁都会被释放。因此,在mode中调用表 没有多大意义,LOCK TABLES
因为 获取的表锁将立即释放。InnoDB
autocommit=1
InnoDB
您不能在事务中间锁定其他表,因为
LOCK TABLES
执行隐式COMMIT
andUNLOCK TABLES
。