SQL语句的加锁方式 - Mysql 锁机制

本是古典何须时尚 2023-10-04 22:57 21阅读 0赞

SQL语句的加锁方式 - Mysql锁机制  
SELECT ... FROM  
SELECT ... FOR UPDATE / SELECT ... FOR SHARED MODE  
SELECT ... LOCK IN SHARE MODE  
SELECT ... FOR UPDATE  
UPDATE ... WHERE ...  
DELETE FROM ... WHERE ...  
INSERT  
INSERT ... ON DUPLICATE KEY UPDATE  
REPLACE  
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相同。如果有唯一键冲突，则在被替换的行上添加排它临界锁。