《MySQL高级》锁和事务笔记

学习《MySQL高级》锁和事务,并参考其他优秀相关文章整理的笔记

表锁

读锁会阻塞写,但是不会堵塞读;写锁会把读锁和写锁都阻塞。

详解:
会话s1给表A加读锁,s1、s2……都不可写表A,可读表A;
会话s1给表A加写锁,s1可对表A增删改查,s2、s3等未拿到锁的会话,既不能查也不能写;
会话s1不管是表A加读锁还是写锁,需要等表A的锁释放后才能操作其他没有加锁的表(可以给多个表加锁,同时操作多个表)。

PS:读锁,又叫共享锁(S锁);写锁,又叫排他锁(X锁)。

行锁

行锁支持事务

事务

什么是事务?事务是数据库操作的最小工作单元,组合一组操作一起提交,要么执行都成功(commit),要么执行都不成功(rollback)。

事务的特性(ACID)

  • Atomicity:原子性
  • Consistency:一致性
  • Isolation:隔离性
  • Durability:持久性

原子性

事务被视为不可分割的最小单元,事务的所有操作要么全部成功,要么全部失败回滚。

一致性

数据库在事务执行前后都保持一致性状态(数据前后一致)。

隔离性

一个事务所做的修改在最终提交以前,对其他事务是不可见的。

持久性

一旦事务提交,则其所做的修改将会永远保存到数据库中。即使系统发生崩溃,事务执行的结果也不能丢。

并发事务处理带来了哪些问题

  • 更新丢失 (Lost Update)
  • 脏读 (Dirty Reads)
  • 不可重复读 (Not-Repeatable Reads)
  • 幻读 (Phantom Reads)

更新丢失

当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题。最后的更新覆盖了由其他事务所做的更新。

脏读

一句话:事务A读取到了事务B已修改但尚未提交的数据,还在这个数据基础上做了操作。此时,如果B事务回滚,A读取的数据无效,不符合一致性隔离性要求。

不可重复读

一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现其读出的数据已经发生了改变、或某些记录已经被删除了!这种现象就叫做“不可重复读”。

一句话:事务A读取到了事务B已修改且已提交的数据,不符合隔离性一致性。

幻读

一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就称为“幻读”。

一句话:事务A读取到了事务B新增且已提交的数据,不符合隔离性一致性。

PS: 仔细理解加黑的地方,脏读、不可重复读、幻读的区别一目了然。

事务的隔离级别

读未提交(READ UNCOMMITTED)

事务中的修改,即使没有提交,对其他事务也是可见的。

读已提交(READ COMMITTED)

一个事务只能读取已经提交的事务所做的修改。换句话说,一个事务所做的修改在提交之前对其他事务是不可见的。

可重复读(REPEATABLE READ)(默认级别)

保证在同一个事务中多次读取同样数据的结果是一样的。

可串行化(SERIALIZABLE)

强制事务串行执行。

每种隔离级别以及会出现的问题汇总表

隔离级别 脏读 不可重复读 幻影读
未提交读
提交读 ×
可重复读 × ×
可串行化 × × ×

查看当前数据库的事务隔离级别:

show variables like 'tx_isolation';

《MySQL实战45讲》03 | 事务隔离:为什么你改了我还看不见?一文解析的更加清晰明了

行锁升级表锁

假如A事务根据条件 a = 1 更新时,索引失效,事务未提交前;B事务根据条件 a = 10 更新时,会被阻塞。由此可见,插入时索引失效导致行锁升级为表锁。

总结:没有索引或者索引失效更新数据时,InnoDB 的行锁升级为表锁。

间隙锁(Gap Lock)

什么是间隙锁?

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB 会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”。

InnoDB 也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Gap Lock) 。

Next-Key Lock = Record Lock(行锁)+ Gap Lock(间隙锁),它不仅锁定一个记录上的索引,也锁定索引之间的间隙。

危害

因为Query执行过程中通过范围查找的话,他会锁定整个范围内所有的索引键值,即使这个键值并不存在。

间隙锁有一个比较致命的弱点,就是当锁定一个范围键值之后,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

共享锁和排它锁

  • SELECT ... LOCK IN SHARE MODE 共享锁
  • SELECT ... FOR UPDATE 排他锁

SELECT ... LOCK IN SHARE MODE:查询时,增加共享锁(共享锁查询)。一个事务开启查询并增加共享锁,在当前事务提交之前,其他事务中可以进行查询共享锁查询;但是不能排他锁查询删改

SELECT ... FOR UPDATE:查询时,增加排他锁(排他锁查询)。一个事务开启查询并增加排他锁,当前事务独占执行权,在当前事务提交之前,其他事务中只能等待排他锁的释放。

PS:MySQL 8.0 中 FOR UPDATE 新特性 Nowait 和 Skip Locked。 见你可能不知道的MySQL支持的nowait和skip locked特点

如何给某一行数据加锁?

# session 1
> begin; # 开启手动事务
> select * from test where a = 8 for update; # 锁住 a = 8 的这行记录

// 此时其他 session 是不能更新此行数据的,直到 session1 会话提交事务,才会将此行的锁释放

> commit; # 手动提交事务

补充:悲观锁和乐观锁

悲观锁和乐观锁并不是仅限于数据库的锁,它们是两种思想,用于解决并发场景下的数据竞争问题。

悲观锁

悲观锁:在操作数据时比较“悲观”,总认为会有其他人同时修改数据,所以在操作数据时会先把数据加锁,直到操作完毕才释放锁,加锁期间其他人不能修改数据。

乐观锁

乐观锁:在操作数据时比较“乐观”,总认为不会有人同时修改数据,所以在操作数据时不会加锁。更新数据的时候,判断一下在查询后到更新这段时间数据有没有被其他人修改,若别人没有修改数据,则自己正常执行。若别人修改了数据,则自己放弃操作。

乐观锁不加锁并不意味着对数据不负责。它有两种实现方式:CAS机制版本号机制

1.CAS机制(Compare And Swap)

CAS操作包括了3个操作数:

  • 需要读写的内存位置(V)
  • 进行比较的预期值(A)
  • 拟写入的新值(B)

CAS操作逻辑:如果内存位置V的值等于预期的A值,则将该位置更新为新值B,否则不进行任何操作。
许多CAS的操作是自旋的:如果操作不成功,会一直重试,直到操作成功为止。

CAS包含了CompareSwap两个操作,它是由CPU支持的原子操作,通过硬件保持原子性

2.版本号机制

实现思路:在数据记录中增加一个 version (版本号)字段,每次数据被修改时版本号增加1。

当一个会话操作中,先把数据查出,执行操作,对数据进行修改时,
比对版本号是否发生变化,若相同则证明没有人修改,正常执行修改操作;
若版本号不相同,则表示这段时间有其他会话操作修改了数据,当前会话需要放弃操作。

悲观锁和乐观锁的优缺点和适用场景?CAS有哪些缺点?

详见【BAT面试题系列】面试官:你了解乐观锁和悲观锁吗?

总结

Innodb 存储引擎实现了行锁,支持事务;MyISAM 存储引擎是表锁。

行锁的性能损耗要比表锁更大,但整体并发处理能力要比表锁更强。

[参考]

MySQL高级_行锁理论
MySQL高级_索引失效行锁变表锁
MySQL高级_间隙锁危害
MySQL高级_如何锁定一行
打工四年总结的数据库知识点
【BAT面试题系列】面试官:你了解乐观锁和悲观锁吗?
MySQL :: MySQL 5.7 Reference Manual :: 14.7.2.4 Locking Reads