目录
Mysql锁机制 - 各种SQL语句的加锁方式
MySQL加锁读、更新、删除操作通常会给处理过程中扫描到的行添加记录锁,不管语句中是否存在排除该行的where条件。InnoDB不知道具体的WHERE条件是什么,而只清楚扫描了哪些索引范围。加锁通常是临界锁,会阻塞行数据前间隙的插入。
如果在搜索中使用了二级索引,并且要设置的索引记录锁是排他的,InnoDB也会检索相应的聚类索引记录并对其设置锁
如果你的语句不能使用合适的索引,MySQL不得不进行全表扫描,导致所有的行都会被锁住。那么其他的所有插入语句将被阻塞。因此给你的查询语句加上合适的锁是非常重要的
InnoDB的加锁方式如下:
SELECT … FROM
SELECT ... FROM
是一致性读,读取数据库的快照并不加任何锁(除非是“串行化”的隔离级别)。
SELECT … FOR UPDATE / SELECT … FOR SHARED MODE
SELECT ... FOR UPDATE
or SELECT ... FOR SHARED MODE
对扫描的行获取锁,并期望对不符合包含在结果集中条件的行释放锁(例如,如果它们不满足WHERE子句中给定的条件)。但是,在某些情况下,可能不会立即解锁行,因为在查询执行期间会丢失结果行与其原始源之间的关系。例如,在UNION中,从表中扫描(和锁定)的行可能会在评估它们是否符合结果集之前插入临时表。
SELECT … LOCK IN SHARE MODE
SELECT ... LOCK IN SHARE MODE
给扫描到的所有行添加共享临界锁。但是,对于使用唯一索引,并搜索唯一行的语句,只需要添加索引记录锁
SELECT … FOR UPDATE
SELECT ... FOR UPDATE
给扫描到的所有行添加排它临界锁。但是,对于使用唯一索引,并搜索唯一行的语句,只需要添加索引记录锁
UPDATE … WHERE …
UPDATE ... WHERE ...
给扫描到的所有行添加排它临界锁。但是,对于使用唯一索引,并搜索唯一行的语句,只需要添加索引记录锁
当update
语句修改了聚簇索引的行,其关联的二级索引也会被添加隐式锁。在二级索引插入新数据前进行重复数据校验、和插入新数据时,update
操作同样会给相关的二级索引添加共享锁
DELETE FROM … WHERE …
DELETE FROM ... WHERE ....
给扫描到的所有行添加排它临界锁。但是,对于使用唯一索引,并搜索唯一行的语句,只需要添加索引记录锁
INSERT
INSERT
在插入的行上插入排它锁,该锁仅是索引记录锁,非临界锁(即不锁住间隙),不会阻止其他会话在该行前的间隙插入行
在插入行之前,InnoDB会设置插入意向锁。该锁表示要以这样一种方式插入,即插入到同一索引间隙的多个事务如果不在间隙内的同一位置插入,则不需要相互等待。假设有值为4和7的索引记录。尝试插入值5和6的独立事务在获得插入行上的排它锁之前,每个事务都用插入意图锁锁定4和7之间的间隙,但不会阻塞彼此,因为行是不冲突的
如果出现重复键错误(duplicate-key error)
,则对重复索引记录添加共享锁(shared lock)
。 如果有多个会话试图插入同一行,而另一个会话已经拥有独占锁,那么使用共享锁可能会导致死锁。如果另一个会话删除了该行,就会发生这种情况。假设有一个表t1:
CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
有三个会话分别执行插入语句:
Session1:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session2:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session3:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
此时会话1回滚:
Session1:
ROLLBACK;
会话1的第一个操作为行获取排他锁。会话2和会话3的操作都导致重复密钥错误,它们都请求行共享锁。当会话1回滚时,它释放对该行的排他锁,并为会话2和会话3授予排队的共享锁请求。此时,会话2和会话3会死锁:由于另一方持有共享锁,任何一方都不能获得行独占锁
INSERT … ON DUPLICATE KEY UPDATE
INSERT ... ON DUPLICATE KEY UPDATE
与简单INSERT的不同之处在于,当重复键错误发生时,将排他锁而不是共享锁放在要更新的行上。对重复的主键值使用排他索引记录锁。如果是重复主键索引,加排它记录锁;如果是重复唯一键索引,加排它临界锁
REPLACE
REPLACE
在没有唯一键冲突的情况下,表现和INSERT相同。如果有唯一键冲突,则在被替换的行上添加排它临界锁。