MySQL-锁的学习

2021-01-20 约 1694 字预计阅读 4 分钟

SQL标准规定不同隔离级别

READ UNCOMMITTED :脏读、不可重复读、幻读都可能发生。 READ COMMITTED:隔离级别下，不可重复读、幻读可能发生，脏读不可以发生。 REPEATABLE READ:隔离级别下，幻读可能发生，脏读和不可重复读不可以发生。 SERIALIZABLE:隔离级别下，上述问题都不可以发生。 MySQL在REPEATABLE READ隔离级别实际上就已经解决了幻读问题。

幻读
幻读问题的产生是因为某个事务读了一个范围的记录，之后别的事务在该范围内插入了新记录，该事务再次读取该范围的记录时，可以读到新插入的记录，所以幻读问题准确的说并不是因为读取和写入一条相同记录而产生的，这一点要注意一下。

解决脏读、不可重复读、幻读

我们说过普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录。在READ COMMITTED隔离级别下，一个事务在执行过程中每次执行SELECT操作时都会生成一个ReadView，ReadView的存在本身就保证了事务不可以读取到未提交的事务所做的更改，也就是避免了脏读现象；REPEATABLE READ隔离级别下，一个事务在执行过程中只有第一次执行SELECT操作才会生成一个ReadView，之后的SELECT操作都复用这个ReadView，这样也就避免了不可重复读和幻读的问题。

加锁

表级别的锁

1
2
3
4
5
LOCK TABLES xxx READ：这是加表级共享锁 
LOCK TABLES xxx WRITE：这是加表级独占锁
系统默认加的MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。
所以改表结构要注意点，会阻塞这个表的所有操作
其实走mvcc基本都够了没必要加锁。除非特定的业务需要独占某条数据。

锁定语句

1
2
3
4
5
6
对读取的记录加S锁： 共享锁相互不互斥
SELECT ... LOCK IN SHARE MODE;
对读取的记录加X锁： 排它锁与锁都互斥
SELECT ... FOR UPDATE;

增删改的语句或多或少在获取数据时或者在更新记录时都是会获取X锁的，具体细化看后面

降低锁时间

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

假设你负责实现一个电影票在线交易业务，顾客A要在影院B购买电影票。我们简化一点，这个业务需要涉及到以下操作：

从顾客A账户余额中扣除电影票价；
给影院B的账户余额增加这张电影票价；
记录一条交易日志。

根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果你把语句2安排在最后，比如按照3、1、2这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

死锁检测策略

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

间隙锁与临键锁(next-key lock)

MVCC不能完美的解决幻读问题

我们都知道幻读是数据条数的改变，那意味着表里面增加记录的锁时未知的。即使把所有的记录都加上锁，还是阻止不了新插入的记录

这样，当你执行 select * from t where d=5 for update的时候，就不止是给数据库中已有的6个记录加上了行锁，还同时加了7个间隙锁。这样就确保了无法再插入新的记录。

跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。 间隙锁之间都不存在冲突关系
间隙锁是在可重复读隔离级别下才会生效的，不可重复读那没必要加锁了

间隙锁和行锁合称next-key lock:每个next-key lock是前开后闭区间。

也就是说，我们的表t初始化以后。如果用select * from t for update要把整个表所有记录锁起来，

就形成了7个next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +suprenum]。

可以看看之前文章间隙锁

间隙锁案例、规则、死锁

目录