mysql 事务及隔离级别,锁

big muscle

墨衍会员 · AI 创作全网分发码龄11年

关注

原创 2023-09-21 20:23:25 581 阅读 AI 写同款· GEO 优化 ›

MySQL事务隔离级别 本文主要探讨MySQL事务隔离级别的使用，具体什么场景使用啥，实战验证出现的并发事务问题阅读详情

1. 事务特性：ACID

原子性：回滚日志（undo log）
持久性：重做日志（redo log）
隔离性：锁+mvcc（当前读）
一致性：（通过AID及用户自定义完整性保证）
一致性是事务追求的最终目标：前面提到的原子性、持久性和隔离性，都是为了保证数据库状态的一致性。此外，除了数据库层面的保障，一致性的实现也需要应用层面进行保障（转账）。

一致性是指事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。数据库的完整性约束包括但不限于：实体完整性（如行的主键存在且唯一）、列完整性（如字段的类型、大小、长度要符合要求）、外键约束、用户自定义完整性（如转账前后，两个账户余额的和应该不变）。不同于CAP的一致性，CAP的一致性指的是多个节点数据保持一致，

2. 事务隔离级别

在这里插入图片描述

未提交读（问题：脏读）-> 提交读（问题：不可重复读）-> 可重复读（默认）（问题：幻读）-> 串行化或者mvcc（读）及X锁+gap锁（写）

幻读
指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行(Phantom Row). —高性能mysql（权威）

不可重复度
一个事务内两次读之间，另一个事务修改了数据，两次读的内容不一样

可重复读
一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见

mysql如何实现避免幻读

在快照读读情况下，mysql通过mvcc来避免幻读
在当前读读情况下，mysql通过next-key来避免幻读

mvcc

InnoDB 的MVCC ，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间(或删除时间)。当然存储的并不是实际的时间值，而是系统版本号(system version number) 。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。下面看一下在REPEATABLE READ 隔离级别下， MVCC 具体是如何操作的。

SELECT
InnoDB 会根据以下两个条件检查每行记录：
a. InnoDB只查找版本早于当前事务版本的数据行(也就是，行的系统版本号小于或等于事务的系统版本号)这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
b.行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
只有符合上述两个条件的记录，才能返回作为查询结果。
INSERT
InnoDB 为新插入的每一行保存当前系统版本号作为行版本号。
DELETE
InnoDB 为删除的每一行保存当前系统版本号作为行删除标识。
UPDATE
InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

什么是next-key锁

可以简单的理解为记录锁（X锁（排他锁））+GAP锁

什么是快照读和当前读

快照读：一致性读，当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果。
简单的select操作，属于快照读，不加锁

select * from table where ?;

当前读：当前读就是读取最新数据，而不是历史版本的数据
特殊的读操作，插入/更新/删除操作，DML 操作，属于当前读，需要加锁。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;

行锁

在这里插入图片描述

间隙锁

间隙锁之间都不存在冲突关系，间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。可重复读隔离级别下才会生效的

3. 锁

锁类别

表级锁（alter table，LOCK TABLES users READ），myisam仅支持表锁
行级锁（Record Lock）
间隙锁（Gap Lock）
Next-key Lock：（Record Lock + Gap Lock）
共享锁
排它锁：普通select不会加锁，所以某个事务在一行加排它锁，其他事务的普通select能执行，只是查询的是老数据
意向共享/排它锁