MySQL事务

鹦鹉哥
42 阅读23分钟

1、事务概述

事务是一个数据库管理系统（DBMS）执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

2、事务及其ACID属性

2.1 原子性

原子性是指对数据库的一系列操作，在同一事务内，要么全部成功，要么全部失败。主要针对操作层面，比如一个事务内的所有操作要么成功要么全部失败。 实现原理：基于undo log。 undo log 属于一个逻辑日志，它用来记录的是sql执行相关的信息。对于一个insert语句在回滚时候会执行delete，相反也是如此。

2.2 一致性

一个事务必须使数据库从一个一致性状态变换到另一个一致性状态。主要针对数据层面，所谓的一致性，是指数据处于一种有意义的状态，这种状态是语义上的而不是语法上的。最常见的例子是A转给B一笔钱，如果账户A上的钱减少，B却没有增加，那么认为此时数据处于一种数据不一致的状态。从实际业务逻辑老说，最终结果是对的、跟程序员的所期望的结果完全符合的。

注意：

  1. 一致性是基础，也是最终的目的，原子性、隔离性和持久性都是为了保证数据一致性；这也是实现原理，要保证一致性，需要实现原子性、隔离性和持久性；
  2. 原子性并不能保证一致性，比如有两个事务A和B，A事务先读取张三的数据，然后对数据进行修改-加上100元，B事务对张三的数据也进行修改，同样加上100元，那么最后的结果应该是张三增加了200元。但是事实上，事务A完成后，账号A只增加了100元，因为事务B对事务A进行了覆盖。可以看出，事务A和B都满足原子性，但是数据的一致性并没有保证。

2.3 隔离性

隔离性是指一个事务的执行不被另一个事务干扰。 实现原理：MVCC+锁机制

2.4持久性

一个事务一旦提交，对数据库中的数据的改变就是永久性的。当事务提交后，数据会持久化到磁盘，对数据的修改是永久性的。 实现原理：基于redo log；

redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。

2.5 MVCC多版本并发控制机制

可重复读隔离级别下，同样的mysql查询语句在一个事务中进行多次查询的结果相同，就算其他事务对数据有修改也不会影响当前事务下的sql语句的查询结果。

这个隔离性就是通过MVCC（Multi-Versoin Concurrency Control）机制来保证的，对一行数据的读和写两个操作默认是不会通过加互斥锁来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别中，为了保证较高的隔离性是通过将所有操作加互斥锁来实现的。

MySQL在读已提交和可重复读隔离级别下都实现了MVCC机制，可重复读的环境下，没有使用锁即实现了“读写分离”的方案，节省了频繁加锁带来的开销。

2.5.1 undo日志版本链与read-view机制

undo日志版本链是指一行数据被多个事务依次修改后，在每个事务修改完后，MySQL会保留修改前的数据undo回滚日志，并且用两个隐藏字段row trx_id和roll_pinter把这些undo日志串联起来形成一个历史记录版本链：

Pasted image 20241124154128.png

实际上，图中的三个虚线箭头，就是undo log；而V1、V2、V3并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。比如，需要V2的时候，就是通过V4依次执行U3、U2算出来。

在可重复读隔离级别，当前事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前都不会变化。

如果是读已提交隔离级别在每次执行查询sql时都会重新生成

这个视图由启动了但是还未提交的所有事务的id组成的数组实现。数组里最小的事务id(min_id)作为低水位和已创建的最大事务id(max_id)加1作为高水位组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做对比从而得到最终的快照结果。

Pasted image 20241124144518.png

版本链比对规则：

  1. 如果row的trx_id落在绿色部分(trx_id<min_id)，表示这个版本是已提交的事务生成的，这个数据是可见的；
  2. 如果row的trx_id落在红色部分(trx_id>max_id)，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
  3. 如果row的trx_id落在黄色部分(min_id<=trx_id<=max_id)，那就包括两种情况:
    1. 若row的trx_id在视图数组中，表示这个版本是由还没提交的事务生成的，不可见，若row的trx_id就是当前自己的事务是可见的；
    2. 若row的trx_id不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。

对于删除的情况可以认为是update的待殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的trx_id，同时在该条记录的头信息(decord header)里的(delete_flag)标记位写上true，来表示当前记录已经被制除，在查询时按照上面的规则查到对应的记录，如果标记位为true，意味着记录已被删除，则不返回数据。

注意：begin/start transacion命令并不是一个事务的起点，在执行到它们之后的第一个修改操作innoDB表的语句，事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。start transaction with consistent snapshot这条命令在可重复读隔离级别下名利执行后立即开始事务。由于在读提交隔离级别下，每个sql执行前都会重新计算出新的试图，所以该命令也就相当于失去了作用，变成和begin/start transacion一样的效果。

总结： mvcc机制的实现就是通过read-view和undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。正是因为这个特性，InnoDB实现了“秒级创建快照”的能力，而不是需要耗费大量资源复制整个库的数据作为快照。

2.5.2 更新逻辑

Pasted image 20241124160907.png

按照图上所示，如果事务B在更新之前先进行一次查询，那么查询出来的返回值肯定时1。但是，当它要去更新数据的时候，就不能再在历史版本上更新了，否则事务C的更新就丢失了。因此，事务B此时的set k=k+1是在（1,2）的基础上进行的操作。所以，这里就用到了这样一条规则：更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”​​。

除了update语句外，select查询中加锁，lock in shared 或者 for updates 也是当前读。

当前读，是必须要读最新版本，而且必须要加锁的，如果事务C变成了C^，由于C^拿到了锁，并且事务没有提交，所以事务B需要等待事务C^将锁释放才能进行当前读，从而更新数据。

Pasted image 20241124161919.png

Pasted image 20241124161509.png

到这里，实际已经把一致性读、当前读和行锁串联起来了。

2.5.3 MVCC举例

举例：

2024-01-02-10-50-16.png

可以根据上图例子思考在可重复读和读提交两种隔离级别下的结果是什么。

2.6 InnoDB中更新一条数据的实现流程

undo log ：回滚日志； redo log：重做日志；innodb独有的日志； redo的作用： 记录尚未完成的操作，数据库崩溃则用其重做； redo的组成：

  1. redo log buffer 保存在内存中的重做日志的缓存，是易失的
  2. redo log file 保存在磁盘上的重做日志文件，是持久的。

undo的作用 ： 记录数据更改前的旧值，在操作出错时，可以用于回滚，只将数据库逻辑恢复到原来的样子。 undo的类型：

  • insert undo log：指insert操作中产生的undo log，因为insert操作的记录，只对事务本身可见，对其他事务不可见。故该undo log可以在事务提交后直接删除，不需要purge操作。
  • update undo log：update undo log是在delete和update操作产生的undo log，该undo log可能需要提供mvcc机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。

补充： purge线程主要作用是：清理undo页和page中带有delete_bit标识的数据行。 innoDB事务中的delete操作实际上并不是真正的删除掉数据行，而是一种delete mark操作，在记录上标识delete_bit，而不是删除记录。是一种“假删除”，只是做了个标记，真正的删除工作需要后台purge线程去完成。

工作流程： innoDB的更新操作采用的是Write Ahead Log(预先日志持久化)策略，即先写日志，再写入磁盘。

当要把id=1的数据name=zhuge 修改为name=zhuge666时，流程大致如下：

  1. 加载缓存数据，加载id=1的记录所在的整页数据，mysql中查询的数据都是以页为单位的。
  2. undo日志中写入更新数据的旧值，便于回滚；
  3. 更新内存数据；
  4. 生成一条重做日志并写入redo log buffer，记录数据被修改后的值；
  5. 准备对事务commit时，将redo log buffer中的内存刷新到redo log file，对redo log file采用追加写的方式；
  6. 准备对事务commit时，将binlog(属于server)日志写入磁盘；
  7. 写入commit标记到redo日志文件里，提交事务完成，该标记为了保证事务提交后redo与binlog数据一致。

2024-01-02-10-51-51.png

2024-01-02-10-51-57.png

binlog是用来恢复磁盘的，而因为InnoDB中有buffer pool 所以需要redo用来恢复buffer pool。

2.6.1 为什么MySQL不是直接更新磁盘上的数据？

快！！！ redo 的写入是顺序的 直接写入磁盘无序的 顺序IO比无序IO的效率高很多！！！

  1. 因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差。因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。
  2. MySQL这套机制看起来复杂，但它可以保证每个更新请求都是更新内存buffer pool，然后顺序写日志文件，同时还能保证各种异常请况下的数据一致。
  3. 更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。

2.6.2 redo是如何保证事务的持久性的？

redo log采用的是WAL(Write-ahead logging，预写式日志)，所有修改先写入日志，再更新到磁盘，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。

2.6.3 脏页何时刷入磁盘呢？

  1. 系统内存不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；
  2. MySQL 认为空闲的时间，这种情况没有性能问题；
  3. MySQL 正常关闭之前，会把所有的脏页刷入到磁盘，这种情况也没有性能问题。
  4. redo日志存满的时候；

2.6.4 Undo Log为什么没有Undo Log Buffer？

假设有Buffer存在于InnoDB，将事务开始前的数据状态写入了Undo Log Buffer中，然后开始更新数据。突然，MySQL由于意外进程退出了，如果上一次的事务没有成功需要回滚，这时发现Undo Log buffer中的内容没有写入undo log中，内存中的数据也随着进程退出一起没了，此时就没有办法通过Undo Log去做回滚。

2.6.5 如何保证redo log 和 binlog在事务提交时的数据一致性？

保证Redo Log和Binlog在事务提交时的数据一致性，要么都存在，要么都不存在。MySQL是通过 2PC(two-phase commit protocol)来实现的。 2PC它是一种保证分布式事务数据一致性的协议，它中文名叫两阶段提交，它将分布式事务的提交拆分成了2个阶段，分别是Prepare和Commit/Rollback。 2PC白话原理就像两个拳击手开始比赛之前，裁判会在中间确认两个选手的状态，类似于问你准备好了吗？得到确认之后，裁判才会说Fight。 裁判询问选手的状态，对应的是第一阶段Prepare；得到了肯定的回答之后，裁判宣布比赛正式开始，对应的是第二阶段Commit，但是如果有一方选手没有准备好，裁判会宣布比赛暂停，此时对应的是第一阶段失败的情况，第二阶段的状态会变为Rollback。裁判就对应2PC中的协调者Coordinator，选手就对应参与者Participant。

3、事务的隔离级别

  • 读未提交 read uncommited
  • 读已提交 read commited
  • 可重复读 repeatable read（MySQL的默认隔离级别，oracle/sql server是读已提交）
  • 串行化 serializable

4、 隔离级别与锁的关系

4.1 读未提交

读数据时不加锁，写数据时加上行级共享锁，直到事务结束才释放；

4.2 读已提交

读数据时加上行级共享锁，读完该行，立即释放该行级共享锁； 写数据时加上行级排他锁，直到事务结束才释放；

4.3 可重复读

读取数据加行级共享锁，直到事务结束才释放； 写数据时加行级排他锁，直到事务结束才释放。 为了不加锁解决读写冲突的问题，mysql引入了MVCC机制； 读数据时使用MVCC，读取快照数据，写数据时加上行级排他锁和间隙锁锁住表，直到事务结束才释放； 快照读：读取历史版本；当前读：读取最新版本；

4.4 串行化

读数据时加上表级共享锁，直到事务结束才释放 写数据时加上表级排他锁，直到事务结束才释放；

5、并发事务处理带来的问题

脏写

刚才更新了一条数据，但是转眼就没了。 事务A和B同时更新一条数据，但是A的事务没有提交，而是回滚了，导致B看不到刚更新的值。

脏读

读出来的值和实际值不一样，是个垃圾。 事务A读取一条数据，此时事务B对这条数据进行了修改，读取出来时100，然后B进行了回滚，实际还是50。

不可重复读

每次读取出来的数据都是不一样的。 事务A一只在读一条数据，事务B同时一直在修改这条数据，导致事务A读取的数据每次都不一样。

幻读

出现了感觉不存在但实际存在的数据。 事务A查询数据库中的数据，事务B往数据库中插入数据，因为存在隔离性，A读取不了B插入的数据，但是在A中可以更新这条数据，是可以感知到的，就像幻觉一样。

6、表锁

6.1 锁分类

  • 乐观锁
  • 悲观锁
  • 读锁 写锁（都属于悲观锁）也称为共享锁 排他锁
  • 表锁 行锁
  1. 表锁是指对一整张表加锁，一般是 DDL 处理时使用；而行锁则是锁定某一行或者某几行，或者行与行之间的间隙。
  2. 表锁由 MySQL Server 实现，行锁则是存储引擎实现，不同的引擎实现的不同。在 MySQL 的常用引擎中 InnoDB 支持行锁，而 MyISAM 则只能使用 MySQL Server 提供的表锁。

6.2 特点

每次锁住一张表，开销小，加锁快，不会出现死锁，锁粒度很大，并发度最小；

lock table 表名称1 read(write) , 表名称2 read(write)
show open tables where in use = 1 -- 1代表加了锁 0代表未加锁
unlock tables
6.2.1 对表加读锁
-- 会话0加读锁
lock table  tablelock read ;
-- 操作
select * from tablelock; --读（查），可以
delete from tablelock where id =1 ; --写（增删改），不可以

select * from emp ; --读，不可以(emp为非锁表)
delete from emp where eid = 1; --写，不可以

-- 会话1(非加锁会话)
select * from tablelock;   --读（查），可以
delete from tablelock where id =1 ; --写，会“等待”会话0将锁释放

select * from emp ;  --读（查），可以 (emp为非锁表)
delete from emp where eno = 1; --写，可以

结论：

  1. 当前会话：
    1. 一个会话对A表加了锁，则只能对A表进行读操作；
  2. 其他会话
    1. 可以对其他表（A表之外）进行读、写操作；
    2. 对A表可以读，写需要等待锁释放；
6.2.2 对表加写锁
-- 会话0加写锁
lock table tablelock write ;
-- 其他大体与读锁相似，这里直接讲结论

结论：

  1. 当前会话
    1. 一个会话对A表加了写锁，可以对A表进行增删改查；
    2. 不可以对A表之外的表进行增删改查；
  2. 其他会话
    1. 对A表进行增删改查需要等待锁释放；
    2. 不能操作其他表；

7、行锁

每次锁住一行数据，开销大，加锁慢，会出现死锁，锁粒度最小，并发度最高； innodb支持事务 支持行级锁 这是与myisam最大的区别； begin ; 开启事务 myisam select时 自动加表锁，update delete等自动加写锁； innodb select 时 不会加锁，update delete 等会加锁；

7.1 行锁的加锁原理

update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';

2024-01-02-10-58-17.png

第一条 SQL 使用主键索引来查询，则只需要在 id = 49 这个主键索引上加上写锁；第二条 SQL 则使用二级索引来查询，则首先在 name = Tom 这个索引上加写锁，然后由于使用 InnoDB 二级索引还需再次根据主键索引查询，所以还需要在 id = 49 这个主键索引上加写锁，如上图所示。

也就是说使用主键索引需要加一把锁，使用二级索引需要在二级索引和主键索引上各加一把锁。 如果更新操作涉及多个行呢，比如下面 SQL 的执行场景。

update user set age = 10 where id > 49;

2024-01-02-10-58-33.png

MySQL Server 会根据 WHERE 条件读取第一条满足条件的记录，然后 InnoDB 引擎会将第一条记录返回并加锁，接着 MySQL Server 发起更新改行记录的 UPDATE 请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有匹配的记录为止。

7.2 行锁的模式

7.2.1 读写锁

读锁，又称共享锁(Share locks，简称 S 锁），加了读锁的记录，所有的事务都可以读取，但是不能修改，并且可同时有多个事务对记录加读锁。 写锁，又称排他锁(Exclusive locks，简称 X 锁)，或独占锁，对记录加了排他锁之后，只有拥有该锁的事务可以读取和修改，其他事务都不可以读取和修改，并且同一时间只能有一个事务加写锁。

7.2.2 读写意向锁

由于表锁和行锁虽然锁定范围不同，但是会相互冲突。所以当要加表锁时，势必要先遍历该表的所有记录，判断是否加有排他锁。这种遍历检查的方式显然是一种低效的方式，MySQL 引入了意向锁，来检测表锁和行锁的冲突。 意向锁也是表级锁，也可分为读意向锁（IS 锁）和写意向锁（IX 锁）。当事务要在记录上加上读锁或写锁时，要首先在表上加上意向锁。这样判断表中是否有记录加锁就很简单了，只要看下表上是否有意向锁就行了。 意向锁之间是不会产生冲突的，也不和 AUTO_INC 表锁冲突，它只会阻塞表级读锁或表级写锁，另外，意向锁也不会和行锁冲突，行锁只会和行锁冲突。

7.2.3 自增锁

AUTOINC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTOINC 表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的。AUTOINC 锁具有如下特点：

  • AUTO_INC 锁互不兼容，也就是说同一张表同时只允许有一个自增锁；
  • 自增值一旦分配了就会 +1，如果事务回滚，自增值也不会减回去，所以自增值可能会出现中断的情况。

显然，AUTOINC 表锁会导致并发插入的效率降低，为了提高插入的并发性，MySQL 从 5.1.22 版本开始，引入了一种可选的轻量级锁（mutex）机制来代替 AUTOINC 锁，可以通过参数 innodb.autoinclock.mode 来灵活控制分配自增值时的并发策略。

7.3 行锁的类型

根据锁的粒度可以把锁细分为表锁和行锁，行锁根据场景的不同又可以进一步细分，依次为

  • Next-Key Lock 临键锁
  • Gap Lock 间隙锁
  • Record Lock 记录锁
  • 插入意向 GAP 锁。

不同的锁锁定的位置是不同的，比如说记录锁只锁住对应的记录，而间隙锁锁住记录和记录之间的间隔，Next-Key Lock 则所属记录和记录之前的间隙。不同类型锁的锁定范围大致如下图所示。

2024-01-02-10-59-39.png

记录锁

记录锁是最简单的行锁。上边描述 InnoDB 加锁原理中的锁就是记录锁，只锁住 id = 49 或者 name = 'Tom' 这一条记录。

当 SQL 语句无法使用索引时，会进行全表扫描，这个时候 MySQL 会给整张表的所有数据行加记录锁，再由 MySQL Server 层进行过滤。但是，在 MySQL Server 层进行过滤的时候，如果发现不满足 WHERE 条件，会释放对应记录的锁。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。

所以更新操作必须要根据索引进行操作，没有索引时，不仅会消耗大量的锁资源，增加数据库的开销，还会极大的降低了数据库的并发性能。

间隙锁

还是最开始更新用户年龄的例子，如果 id = 49 这条记录不存在，这个 SQL 语句还会加锁吗？答案是可能有，这取决于数据库的隔离级别。 这种情况下，在RC（读已提交）隔离级别不会加任何锁，在RR（可重复读） 隔离级别会在 id = 49 前后两个索引之间加上间隙锁。

间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。这个间隙可以跨一个索引记录，多个索引记录，甚至是空的。使用间隙锁可以防止其他事务在这个范围内插入或修改记录，保证两次读取这个范围内的记录不会变，从而不会出现幻读现象。

值得注意的是，间隙锁和间隙锁之间是互不冲突的，间隙锁唯一的作用就是为了防止其他事务的插入，所以加间隙 S 锁和加间隙 X 锁没有任何区别。

Next-Key 锁

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。假设一个索引包含 15、18、20 ，30，49，50 这几个值，可能的 Next-key 锁如下：

(-∞, 15]，(15, 18]，(18, 20]，(20, 30]，(30, 49]，(49, 50]，(50, +∞)

通常都用这种左开右闭区间来表示 Next-key 锁，其中，圆括号表示不包含该记录，方括号表示包含该记录。前面四个都是 Next-key 锁，最后一个为间隙锁。 和间隙锁一样，在 RC 隔离级别下没有 Next-key 锁，只有 RR 隔离级别才有。还是之前的例子，如果 id 不是主键，而是二级索引，且不是唯一索引，那么这个 SQL 在 RR 隔离级别下就会加如下的 Next-key 锁 (30, 49](49, 50]，如果id时主键或唯一索引，Next-key则为(49,50]。 此时如果插入一条 id = 31 的记录将会阻塞住。之所以要把 id = 49 前后的间隙都锁住，仍然是为了解决幻读问题，因为 id 是非唯一索引，所以 id = 49 可能会有多条记录，为了防止再插入一条 id = 49 的记录。

注意：无索引行锁会升级为表锁 锁主要是加在索引上的，如果对非索引字段进行更新，则行锁会升级为表锁； InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，如果索引失效，行锁也会变为表锁；

插入意向锁

插入意向锁是一种特殊的间隙锁（简写成 II GAP）表示插入的意向，只有在 INSERT 的时候才会有这个锁。

注意，这个锁虽然也叫意向锁，但是和上面介绍的表级意向锁是两个完全不同的概念，不要搞混了。

插入意向锁和插入意向锁之间互不冲突，所以可以在同一个间隙中有多个事务同时插入不同索引的记录。譬如在上面的例子中，id = 30 和 id = 49 之间如果有两个事务要同时分别插入 id = 32 和 id = 33 是没问题的，虽然两个事务都会在 id = 30 和 id = 50 之间加上插入意向锁，但是不会冲突。 插入意向锁只会和间隙锁或 Next-key 锁冲突，正如上面所说，间隙锁唯一的作用就是防止其他事务插入记录造成幻读，正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。

avatar
文章
阅读
粉丝