mysql对表查询不加锁

转载

jowvid 2024-12-05 06:21:36

文章标签 mysql对表查询不加锁 mysql 网络加锁意向锁 文章分类 MySQL 数据库

获取锁的流程

锁包含**行级锁**或是**表级锁**，当程序要进行读或写操作时，需要**先获取到对应的锁**，才能继续进行操作。
数据行在**一开始时没有任何锁**与其绑定
如果想要对数据行进行操作，需要先生成一个锁，锁中的主要内容有事务的id，以及该锁是否在等待。
如果此时数据行没有锁与其绑定，则该锁会与数据行绑定，并且标志位为没有在等待。
此时如果有其他想来操作这个数据行，则也需要生成一个自己的锁，因为该数据行此时已经绑定了一把锁，因此这个锁也会绑定在数据行上，但是此时该锁的状态是等待中，不能对数据行进行操作。
只有当第一个锁完成释放后，后面的锁才会进行竞争。

按照数据操作类型划分读写锁

SELECT操作

**读锁** 称之为 **S锁**，可以通过SELECT … **IN SHARE MODE** 进行添加，**多个S锁之间不会阻塞**
**写锁** 称之为 **X锁**，可以通过SELECT… **FOR UPDATE** 进行添加，**S锁和X锁之间会阻塞**

DELETE操作

对于**删除一条记录**的时候，会**先获取到这个记录的X锁**，再进行操作
当删除**范围之间**的记录时，会先获取到**区间内所有记录的X锁**，并且会对记录之前加上**Next-key**锁

INSERT操作

插入不需要提前获取锁，只需要在插入后增加一个**隐性锁**，保证该条记录不被其他事务所访问。
隐性锁的本质也是临键锁，

UPDATE操作

如果**修改不涉及主键**，并且修改前后的**数据类型长度**没有发生改变，则获取到该行的**X锁**后，进行修改。
如果修改**涉及主键**，或者是修改前后的**数据类型发生**改变，则需要获取**X锁**，将该行记录的**删除标志位进行标记**，然后再**重新插入一条**新的记录。

按照锁的维度划分表锁、页锁、行锁

表锁

表级别读锁（X锁）和写锁（S锁）

LOCK TABLES t READ 获取表级别读锁
LOCK TABLES t WRITE 获取表级别写锁

意向锁

当需要**给表添加读锁或写锁时**，因为读锁和写锁冲突的关系，**需要检查表中的所有页、所有行是否持有读锁和写锁**，这是一个比较耗时的操作。
因此为了改善这种情况，引入**意向锁**。在事务对其中的**行列进行操作**之前，需要先获取到**读写的意向锁**。
**意向锁**和**表级的读写锁**之间是**不兼容的**，**表级读锁**和**读意向锁**兼容，其余的都不兼容。如果事务A获取了意向锁，事务B想要对表进行加锁，此时会被阻塞。
**意向锁**与**行级锁**是**兼容的**，事务A在操作某个数据行前先获取了意向锁，此时事务B想要对某个数据行进行操作，也会先获取意向锁，此时不会被阻塞。事务之间可以同时持有属于自己的表的读写意向锁。

自增锁

每当有一个 **auto increment**的字段需要获取值时，都需要先获取到这个自增锁。

innodb_autoinc_lock_mode = 0(“传统”锁定模式) 每个INSERT语句会获取锁
innodb_autoinc_lock_mode = 1(“传统”锁定模式) 批量只获取一个锁
innodb_autoinc_lock_mode = 2(“交错”锁定模式) 可以同时进行 **仅保证总体增长**

元数据锁

在访问表时，需要加上元数据锁，读锁之间不阻塞，而读写锁之间冲突

行锁

mysql对表查询不加锁_意向锁

记录锁

仅锁一条记录
在聚簇索引中，只锁一条记录 SELECT * FROM xx WHERE id = 1 FOR UPDATE

间隙锁
间隙锁和临键锁是可重复读隔离级别中用于解决幻读问题的方法
因此在读已提交隔离级别中 只存在记录锁
锁范围，两边开区间
例子：1. id=5这条记录在列表中不存在，因此会对（3，8）的记录进行加锁
SELECT * FROM xx WHERE id = 5 FOR UPDATE
2. 指定范围
SELECT * FROM xx WHERE id > 3 AND id < 8 FOR UPDATE
3. 对于末尾的空间可以显示指定进行加锁
SELECT * FROM xx WHERE id > 20 FOR UPDATE
在加上间隙锁后，如果此时有一条id=4的记录想要插入，则会被阻塞。
阻塞的原理是会在插入时，会生成**插入意向锁**，本质也是一个**间隙锁**，此时会阻塞等待。临键锁
特殊的间隙锁，包含记录本身与间隙
SELECT * FROM xx WHERE id > 3 AND id <= 8 FOR UPDATE

死锁

mysql对表查询不加锁_加锁_02

死锁出现的条件

两个事务都**持有对方所需要的锁**
锁不会被**强占**
**环路等待**
锁只能被一个事务所拥有

死锁如何解决

**等待超时时间** 设定属性 **innodb_lock_wait_timeout** 如果事务等待获取锁的时间过长，则主动释放锁，但是对于在线服务来说，如果设置过长的时间，则会造成用户体验差，设置时间过短，则容易误伤到正常的锁。
**主动判断逻辑是否有环路等待**

但是每个阻塞等待的锁都会主动判断是否存在死锁，因此不开启

**通过合理的代码避免死锁**

使用**读已提交**隔离级别代替**可重复读**，**没有临键锁和间隙锁**可以减少很多的死锁情况发生
合理的**使用索引**，通过索引能够大幅减少需要加锁的行数，同时也减少了SQL的运行时间，让锁更快释放
在一个事务内同时要执行**update和insert**操作，在不影响顺序的前提下，可以**先执行insert**操作，**后执行update**操作。因为update操作获取的**锁需要在事务结束后才会释放**，后进行update操作可以使得update操作持有锁的时间更短。

查看死锁

innodb_status_output_locks

加锁的规则

加锁规则

默认加锁加入的锁都是**临键锁**，临键锁是**前开后闭区间**。从左边的节点开始（不包含该节点），右边则会继续查询，直到找到第一个不等于该值的才会停止。
查询过程**只有访问到的对象**会进行加锁，其他索引上加的锁，如果有**回表**操作，最终也**会给主键索引加上锁**，如果普通索引的内容不需要进行回表操作，则不需要给主键索引加锁。
**唯一索引**上的**等值查询**，临键锁会退化成记录锁。
普通索引上进行的查询，如果一直向右遍历找到的记录不相等，临键锁会退化成间隙锁。

加锁案例

在读已提交隔离级别下只存在记录锁不存在间隙锁和临键锁

在**主键索引**的情况下，只会对**条件记录中**的**主键索引**记录进行加锁
在**唯一索引**的情况下，会对**二级索引的记录**和**主键索引**进行加锁
在**普通索引**的情况下，会对**扫描出的所有记录**和这些记录对应的**主键索引**进行加锁
在没有索引的情况下，因为会进行**全表扫描**，因此会对所有记录进行加锁，但是MySQL有进行优化，在加锁后会判断是否符合条件，检查到不符合条件后**会进行解锁**。

在可重复读隔离级别下存在间隙锁和临键锁

在主键索引、唯一索引情况下与**读已提交**相同。
在普通索引的情况下，会对该条记录加上X锁，同时会对左右两边加上间隙锁
如果查询的条件没有索引的情况下，会进行全表扫描，会对所有记录加上X锁，并且给每个加上间隙锁

CREATE TABLE t5 ( id int(11) NOT NULL, c int(11) DEFAULT NULL, d int(11) DEFAULT NULL, PRIMARY KEY (id), KEY c (c)) ENGINE=InnoDB;
insert into t5 values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);