Mysql事务

techzealot
mysql, 事务, 锁
目录

mysql事务详解

本文所有讨论皆是针对innodb存储引擎

锁

锁分类

image-20230412165006968

image-20230412165120820

表锁模式兼容矩阵

image-20230412165240014

InnoDB锁分类

image-20230412165348218

InnoDB锁关系矩阵

image-20230412170012526

行锁算法

image-20230412170207658

不同索引加锁行为分析

image-20230412170342551

image-20230412170745081

image-20230412170820430

image-20230412170858462

image-20230412171026995

InnoDB存储引擎的行锁是通过锁住索引实现的，而不是记录

由于InnoDB特殊的索引机制，数据库操作使用主键索引时，InnoDB会锁住主键索引；使用非主键索引时，InnoDB会先锁住非主键索引，再锁定主键索引

死锁

死锁产生的四个条件

image-20230412171650533

如何避免死锁的产生

image-20230412171806769

InnoDB死锁优化

image-20230412173905750

MVCC

只在RC和RR隔离级别生效

读不加锁，读写不冲突

在MVCC并发控制下，读操作分为快照读与当前读

实现方式：

隐藏字段+undolog链

image-20230412163111895

image-20230412163207214

image-20230412163230785

DB_TRX_ID: 6字节事务id字段，表示最后更新的事务id(update,delete,insert)。 此外，删除在内部被视为更新，其中行中的特殊位被设置为将其标记为已软删除。

DB_ROLL_PTR: 7字节回滚指针，指向前一个版本的undolog记录，组成undo链表。如果更新了行，则撤消日志记录包含着更新行之前重建行内容所需的信息。

DB_ROW_ID: 6字节的DB_ROW_ID字段，包含一个随着新行插入而单调递增的行ID, 当由innodb自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。

当前读

当前读，读取的是最新版本，并且对读取的记录加锁，阻塞其他事务同时改动相同记录，避免出现安全问题

属于当前读的SQL形式：

  • select…for share (共享读锁)
  • select…for update
  • update , delete , insert

关于for update：

利用select * for update 可以锁表/锁行。 自然锁表的压力远大于锁行。所以我们应尽量采用锁行。 FOR UPDATE仅适用于InnoDB，且必须在事务处理模块(BEGIN/COMMIT)中才能生效 那么什么时候锁表呢？

  • 例1: (明确指定主键，并且有此记录，row lock) SELECT * FROM xxx WHERE id=3 FOR UPDATE;
  • 例2: (明确指定主键，若查无此记录，无lock) SELECT * FROM xxx WHERE id=-1 FOR UPDATE;
  • 例3: (无索引列查询，table lock) SELECT * FROM xxx WHERE name=‘xxx’ FOR UPDATE;
  • 例4: (主键不明确，table lock) SELECT * FROM xxx WHERE id <> 3 FOR UPDATE;

RR模式下实现方式：

当前读使用next-key锁(行记录锁+Gap间隙锁)实现

  • 对主键或唯一索引，如果当前读时，where条件全部精确命中(=或者in)，这种场景本身就不会出现幻读，所以只会加行记录锁

  • 没有索引的列，当前读操作时，会加全表gap锁和记录锁

  • 非唯一索引列，则除记录锁外会额外加附近Gap间隙锁

快照读

单纯的select操作(不加锁)，不包括上述 select … lock in share mode, select … for update

一致性视图(consistent read view)

img

定义

一致性读视图是InnoDB在实现MVCC用到的虚拟结构，用于读提交（RC）和可重复度（RR）隔离级别的实现。

一致性视图没有物理结构，主要是在事务执行期间用来定义该事物可以看到什么数据

获取方式及时机

  1. 一致性视图是在第执行第一个快照读语句时创建的

  2. 一致性视图是在执行start transaction with consistent read时创建

在 MySQL 中，READ COMMITTED 和 REPEATABLE READ 隔离级别的区别就是它们生成 ReadView 的时机不同

读已提交：每次查询重新获取一致性视图

可重复读：事务第一次查询时获取一致性视图

对可重复读来说，事务只有在第一次进行读操作时才会生成一个ReadView，后续的读操作都会重复使用这个ReadView。

也就是说，如果在此期间有其他事务提交了，那么对于可重复读来说也是不可见的，因为对它来说，事务活跃状态在第一次进行读操作时就已经确定下来，后面不会修改了。

对读已提交来说，事务中的每次读操作都会生成一个新的ReadView。

也就是说，如果这期间某个未提交事务Commit了，那么它就会从ReadView中移除，添加到已提交事务中，这样确保RC级别下事务每次读操作都能读到已经提交的数据

工作原理

事务在正式启动的时候我们会创建一致性视图，该一致性视图是基于整个库的

InnodDB 的每个事务都有一个唯一的事务 ID，叫做 transaction id，该 ID 在事务开始的时候向 InnoDB 申请，并且按照申请顺序严格递增。

每行数据都会有多个版本，每次事务更新数据的时候都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 id，称为 row trx_id。

img

上图是一条行数据的多个版本，最新的版本是 V4。

其中 U3、U2、U1 代表的是 undo log，V1、V2、V3 在物理上并不真实存在，而是在需要的时候通过 V4 配合 undo log 计算获得。

ReadView 中主要包含 4 个比较重要的内容：

  • m_ids：表示在生成 ReadView 时当前系统中活跃的读写事务的事务 id 列表。
  • min_trx_id：表示在生成 ReadView 时当前系统中活跃的读写事务中最小的事务 id，也就是 m_ids 中的最小值。
  • max_trx_id：表示生成 ReadView 时系统中应该分配给下一个事务的 id 值。
  • creator_trx_id：表示生成该 ReadView 的事务的事务 id。

img

在访问某条记录时，按照下边的步骤判断记录的某个版本是否可见：

  • 如果被访问版本的 trx_id 属性值与 ReadView 中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
  • 如果被访问版本的 trx_id 属性值小于 ReadView 中的 min_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问。
  • 如果被访问版本的 trx_id 属性值大于 ReadView 中的 max_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。
  • 如果被访问版本的 trx_id 属性值在 ReadView 的 min_trx_id 和 max_trx_id 之间，那就需要判断一下 trx_id 属性值是不是在 m_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问

在 InnoDB 中，为每个事务构造了一个 当前事务 ID 数组的快照，就是记录事务开启时，当前正在执行的事务 ID 的集合。数组里面 trx_id 最小的记为 低水位，trx_id 最大的 + 1 记为高水位。如下图所示：

img

对于一个新事务而言，所读取到的记录版本的 trx_id 可能有以下几种情况：

  1. 在绿色区域：说明数据版本在事务开始前已提交，当前版本是可见的

  2. 在红色区域：说明数据版本在事务开始后变更的，当前版本是不可见的

  3. 在橙色区域：包含 2 种情况

    1. 如果 数据版本的 trx_id 在数组中，说明是正在执行的事务，不可见
    2. 如果 数据版本的 trx_id 不在数组中，说明是已经提交的事务，可见

总之，一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

  1. 版本未提交，不可见；
  2. 版本已提交，但是是在视图创建后提交的，不可见；
  3. 版本已提交，而且是在视图创建前提交的，可见

更新数据都是先读后写的，而这个读，只能读当前的值，称为当前读

可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待

可以看出，InnoDB 利用了 UndoLog 数据多版本的特点，实现了快速创建快照的能力

事务定义

事务指作为单个逻辑工作单元执行的一系列操作，这些操作要么全做要么全不做，是一个不可分割的工作单元

事务特性：ACID

原子性

事务的所有操作要么全部完成，要么全部不完成，不会结束在某个中间状态

一致性

事务开始之前和事务结束之后，数据库的完整性约束未被破坏

隔离性

当多个事务并发访问数据库中同一数据时所表现出来的相互关系

持久性

事务完成之后，事务所做的修改持久化保存，不会丢失

image-20230412142653184

事务开启与提交模式

  • 若参数autocommit=0，事务则在用户本次对数据进行操作时自动开启，在用户执行commit命令时提交，用户本次对数据库开始进行操作到用户执行commit命令之间的一系列操作为一个完整的事务周期。若不执行commit命令，系统则默认事务回滚。总而言之，当前情况下事务的状态是自动开启手动提交。
  • 若参数autocommit=1（系统默认值），事务的开启与提交又分为两种状态： ①手动开启手动提交：当用户执行start transaction命令时（事务初始化），一个事务开启，当用户执行commit命令时当前事务提交。从用户执行start transaction命令到用户执行commit命令之间的一系列操作为一个完整的事务周期。若不执行commit命令，系统则默认事务回滚。 ②自动开启自动提交：如果用户在当前情况下（参数autocommit=1）未执行start transaction命令而对数据库进行了操作，系统则默认用户对数据库的每一个操作为一个孤立的事务，也就是说用户每进行一次操作系都会即时提交或者即时回滚。这种情况下用户的每一个操作都是一个完整的事务周期。

事务隔离级别

隔离级别脏读不可重复读幻读
读未提交可能可能可能
读已提交不可能可能可能
可重复读不可能不可能可能
串行化不可能不可能不可能
-- 查看事务隔离级别
SELECT @@transaction_isolation
-- mysql8 默认REPEATABLE-READ

-- 数据准备
CREATE TABLE `users`
(
    `id`   int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(30) DEFAULT NULL,
    `age`  tinyint(4) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO users(`name`, age)
values ('tom', 15),
       ('jack',20),
       ('zed',30);

读未提交

场景

shellA>
-- 设置事务隔离级别为读未提交
set session transaction isolation level read uncommitted ;

start transaction ;

select * from users where name = 'tom';
-- (id=1,name='tom',age = 15)

shellB>
-- 设置事务隔离级别为读未提交
set session transaction isolation level read uncommitted ;

start transaction ;

update users set age=22 where name = 'tom';

select * from users where name ='tom';
-- (id=1,name='tom',age = 22)

shellA>
-- 脏读
select * from users where name = 'tom';
-- (id=1,name='tom',age = 22)

shellB>
rollback ;

select * from users where name ='tom';
-- (id=1,name='tom',age = 15)

shellA>
-- 不可重复读
select * from users where name = 'tom';
-- (id=1,name='tom',age = 15)
commit ;

select * from users where name = 'tom';
-- (id=1,name='tom',age = 15)

影响

会产生脏读、不可重复读、幻读

脏读

事务A读取到事务B未提交数据，但事务B可能回滚事务，导致事务A读到了脏数据

实现方式

mysql采用读不加锁，写加写锁，由于读不加锁，写锁排他性不生效

读已提交

场景

shellA>
-- 设置事务隔离级别为读已提交
set session transaction isolation level read committed ;

start transaction ;

select * from users where name = 'tom';
-- (id=1,name='tom',age = 15)

shellB>
-- 设置事务隔离级别为读已提交
set session transaction isolation level read committed ;

start transaction ;

update users set age=22 where name = 'tom';

select * from users where name ='tom';
-- (id=1,name=tom,age = 22)

shellA>
--  不会产生脏读
select * from users where name = 'tom';
-- (id=1,name='tom',age = 15)

shellB>
commit ;

select * from users where name ='tom';
-- (id=1,name=tom,age = 22)

shellA>
-- 提交后可读产生不可重复读
-- 同一个事务中两次相同查询获取了不同的数据,产生不可重复读问题
select * from users where name = 'tom';
-- (id=1,name=tom,age = 22)
commit ;

select * from users where name = 'tom';
-- (id=1,name=tom,age = 22)

影响

会产生不可重复读、幻读问题

不可重复读

事务A中两次相同查询之间事务B修改并提交了数据导致两次相同的查询在一次事务中获得了不同的结果

主要针对update和delete

实现方式

mysql采用MVCC机制，每个select语句有一份自己的快照，而不是一个事务一份快照。

可重复读

场景

shellA>
-- 设置事务隔离级别为可重复读
set session transaction isolation level repeatable read ;

start transaction ;

select * from users where name = 'tom';
-- (id=1,name='tom',age = 22)

shellB>
-- 设置事务隔离级别为可重复读
set session transaction isolation level repeatable read ;

start transaction ;

update users set age=33 where name = 'tom';

select * from users where name ='tom';
-- (id=1,name='tom',age = 33)

insert into users(id, name, age) value (4,'tim',40);

select * from users where id =4;
-- (id=4,name='tim',age = 40)

shellA>
--  不会产生脏读，不可重复读
select * from users where name = 'tom';
-- (id=1,name='tom',age = 22)
select * from users where id =4;
-- 不存在id=4的记录

shellB>
commit ;

shellA>
-- B提交后仍不可读，解决了不可重复读
select * from users where name = 'tom';
-- (id=1,name='tom',age = 22)
select * from users where id =4;
-- 不存在id=4的记录(mysql的不可重复读实现不能直接复现幻读)
-- 执行插入id=4的数据，产生幻读(以为不存在实际存在，间接证明了存在幻读)
insert into users(id, name, age) value (4,'tim',40);
-- ERROR 1062 (23000): Duplicate entry '4' for key 'users.PRIMARY'
commit ;
-- B提交后才可读
select * from users where name = 'tom';
-- (id=1,name='tom',age = 33)
select * from users where id =4;
-- (id=4,name='tim',age = 40)

mysql在可重复读级别下当前读模式已解决幻读问题：

通过next-key锁阻止其它事务创建满足查询条件的新纪录的方式解决了当前读的幻读问题，但是快照读依然存在幻读问题

例如：

sessionA>
start transaction;
select * from users where id > 2;
sessionB>
start transaction;
insert into users(id,name,age) value(5,'cat',55);
commit;
sessionA>
update users set age=50 where id >2;
select * from users where id > 2;
-- 会查出id=5的记录
commit;

解决方式：

将快照读转换为当前读

sessionA>
start transaction;
select * from users where id > 2 for update;
sessionB>
start transaction;
insert into users(id,name,age) value(5,'cat',55);
-- 此时该语句会阻塞直到sessionA事务提交
commit;
sessionA>
update users set age=50 where id >2;
select * from users where id > 2;
-- 不会查出id=5的记录
commit;

幻读

同一个事务中第一次查没有数据，第二次查返回了数据，主要针对insert

针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象

实现方式：

mysql采用一个事务一份快照保证了可重复读，并采用next-key锁解决了当前读的幻读问题(与标准有所不同)

读已提交每次查询创建一个新的快照，可重复读是是一个事务复用一个快照，在首次查询时创建快照

串行化

将所有事务操作变为顺序执行，后一个事务的执行必须等待前一个事务结束

实现方式：

读的时候加共享锁，也就是其他事务可以并发读，但是不能写。写的时候加排它锁，其他事务不能并发写也不能并发读

事务隔离级别实现原理

读未提交

读不加锁，写加X锁，但是由于读不加锁导致写锁排他性无效

读已提交

通过sql级别一致性视图和record锁

可重复读

通过事务级别一致性视图和next-key锁

串行化

读加S锁，写加X锁

参考资料：

对线面试官：MySQL 事务、锁和MVCC

MySQL事务隔离级别和实现原理

MySQL-当前读、快照读、MVCC

高性能MySQL实战

InnoDB Locking

MySQL 实战 45 讲

对线面试官：通过MVCC数据库事务的一致性

MySQL笔记之一致性视图与MVCC实现