MySQL锁和隔离级别的关系

以你之姓@ 2022-12-16 13:28 143阅读 0赞

# 锁的分类 #

*  共享锁（S锁）：多个事务可以同时加共享锁，但加了共享锁的数据不能再加排它锁。由于不可重复读的原因即为A事务开启期间B事务修改了数据，而B修改数据之前需要加排它锁，如果这条数据有共享锁排它锁就加不上，所以共享锁可用于解决不可重复读问题。开启共享锁的sql：`SELECT * FROM xx WHERE id=1 LOCK IN SHARE MODE ;`
 *  排它锁（X锁）：加了排他锁的数据既不能加共享锁也不能加排他锁。由于脏读即A事务读取到了B事务没有提交的数据，而如果B事务有排它锁，A事务就没法开启共享锁读取数据，故避免了脏读的问题。那么是否说B事务在修改未提交之前A事务无法查询呢？其实不是的，由于MVCC机制的存在，A事务采用`SELECT * FROM xx WHERE id=1`这种方式查询实际上查询的是快照，只有当A事务尝试采用共享锁的方式查询才会堵塞。排它锁的开启SQL：`SELECT * FROM XXX WHERE id = 1 for update`
 *  意向共享锁（IS锁）：没搞懂，说是获取共享锁之前需要先获取意向共享锁
 *  意向排它锁（IX）：没搞懂，说是获取排它锁之前需要先获取意向排它锁
 *  临键锁：当数据库ID记录为（10，15，20，25），查询语句为`SELECT * FROM xx WHERE id>15 and id<20 for update`，然后在另一个会话中插入id为11-25都是没法插入的，但是插入10以下（含10）或者大于25（不含）是可以插入的，因为锁的是索引，索引会把相邻的键放在一个节点中，锁的就是这个节点，这就叫临键锁。
 *  间隙锁：当数据库ID记录为（10，15，20，25），查询语句为`SELECT * FROM xx WHERE id>16 and id<17 for update`，这里锁住的是不存在的记录的时候，此时15-20的之间都是不允许插入的，这即间隙锁。由于幻读即A事务进行范围查询时，B事务往这个范围新增了记录，导致A事务多次查询到的数据量不一致，所以使用间隙锁即可避免这个问题。

# 行锁和表锁 #

有数据列为如下的表  
![在这里插入图片描述][20201020234202391.png]

## 当事务的隔离级别为`READ-COMMITTED`时\*\* ##

**abc均无索引的情况**  
事务A查询语句为

select * from t_test where a=15 for update;

事务B执行语句为如下

update t_test t set c=10 where a=1; --不堵塞
    update t_test t set c=10 where a=15; --堵塞
    update t_test t set a=15 where a=9; --不堵塞

当给a列加了一个任意索引的情况，我们来执行上诉语句，发现与上诉情况一致  
说明：当事务的隔离级别为`READ-COMMITTED`时，不管有没有索引，锁住的都是具体的数据行

## 当事务的隔离级别为`repeatable read`时 ##

**abc均无索引的情况**  
事务A查询语句为

select * from t_test where a=15 for update;

事务B执行语句为如下