【MySQL】事务

最新推荐文章于 2024-10-10 21:13:48 发布

すあ

最新推荐文章于 2024-10-10 21:13:48 发布

阅读量411

点赞数

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_44296614/article/details/128851201

版权

数据库专栏收录该内容

13 篇文章 0 订阅

订阅专栏

文章详细介绍了MySQL事务的ACID特性，包括原子性、一致性、隔离性和持久性，并讨论了事务的四种隔离级别及其在并发控制中可能出现的问题，如脏读、不可重复读和幻读。此外，还提到了MySQL如何通过binlog和redolog实现事务的持久性以及事务状态的管理。

摘要由CSDN通过智能技术生成

MySQL

MySQL 事务
 MySQL 日志–redo log

本文参考尚硅谷康师傅的 MySQL课程

1、事务相关概念

1.1、事务的ACID 特性

原子性：事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
一致性：事务执行前后，数据从一个 合法性状态 变换到另外一个 合法性状态 。这种状态是 语义上 的而不是语法上的，跟具体的业务有关例
隔离性：多个并发事务之间相互不影响
持久性：事务一旦被提交，它对数据库的改变就是永久性的

原子性：通过undo log来实现。
持久性：通过bin log、redo log来实现。
隔离性：通过(读写锁+MVCC)来实现。
一致性：MySQL通过原子性，持久性，隔离性最终实现（或者说定义）数据一致性。

总结：
ACID是事务的四大特性，在这四个特性中，原子性是基础，隔离性是手段一致性是约束条件，而持久性是我们的目的
数据库事务，把需要保证原子性、隔离性、一致性和持久性的一个或多个数据库操作称为一个事务

1.2、事务的状态

活动的
事务对应的数据库操作正在执行过程中时，我们就说该事务处在 活动的 状态
部分提交的
当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并 没有刷新到磁盘时，我们就说该事务处在 部分提交的 状态
失败的
当事务处在 活动的 或者 部分提交的 状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在 失败的 状态
中止的
当 回滚 操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了 中止的 状态
提交的
当一个处在 部分提交的 状态的事务将修改过的数据都 同步到磁盘 上之后，我们就可以说该事务处在了 提交的 状态

状态转换

2、事务的使用

2.1、显式事务

mysql> BEGIN;
#或者
mysql> START TRANSACTION;

2.2、隐式事务

MySQL中有一个系统变量 autocommit ：

SHOW VARIABLES LIKE 'autocommit';

autocommit
想关闭这种自动提交的功能，可以使用下边两种方法之一：

显式的的使用 START TRANSACTION 或者 BEGIN 语句开启一个事务。这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能
把系统变量 autocommit 的值设置为 OFF

set autocommit = 0;

set autocommit = OFF;

3、事务隔离级别

3.1、数据并发问题

脏写
如果事务Session A 修改了另一个未提交事务Session B修改过的数据，那就意味着发生了脏写
脏读
事务 A在读取数据的时候读取到事务 B 未提交的数据
不可重复读
事务 A 中,第一次读取值为 1,此时事务 A 未结束,事务 B 将数值修改为 2并提交,事务 A再次读取时得到的数据是 2,在同一个事务 A 中两次读取的数据值不一样
幻读
只针对insert操作
事务 A 按条件读取到 5 条记录,此时事务 A 未结束,事务 B 向表中插入两条符合条件的数据并提交,事务 A 再次读取时得到 7 条记录,在同一个事务中两次读取的数据条数不一样,后一次比前一次多

3.2、SQL 中的四种隔离级别

READ UNCOMMITTED：读未提交
READ COMMITTED：读已提交
REPEATABLE READ ：可重复读
SERIALIZABLE ：可串行化

SQL标准中规定，针对不同的隔离级别，并发事务可以发生不同严重程度的问题，具体情况如下：

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Not Possible	Possible	Possible
REPEATABLE READ	Not Possible	Not Possible	Possible
SERIALIZABLE	Not Possible	Not Possible	Not Possible

3.3、MySQL 默认的隔离级别

# 查看隔离级别
SHOW VARIABLES LIKE '%_isolation';

mysql隔离级别

3.4、设置事务的隔离级别

## global 影响全局 ，session 影响当前 session
set global transcation isolation level READ UNCOMMITTED;
set global transcation isolation level READ COMMITTED;
set session transcation isolation level REPEATABLE READ;
set session transcation isolation level SERIALIZABLE;

set global transcation isolation = `READ UNCOMMITTED`;
set global transcation isolation = `READ COMMITTED`;
set session transcation isolation = `REPEATABLE READ`;
set session transcation isolation = `SERIALIZABLE`;

3.5、不同隔离级别举例

创建表

CREATE TABLE account ( 
	id int, 
	balance int, 
	PRIMARY KEY (id) 
) Engine=InnoDB CHARSET=utf8;

创建表

演示：读未提交产生脏读问题

执行流程:

执行流程
实操：

结论：

可以看到右边事务 B读取到了左边事务 A未提交的数据，也可以看到在事务 A 未提交或回滚之前，事务 B 的 update 操作阻塞

演示：读已提交产生不可重复读问题

执行流程：

实操：

结论：
在 read committed 时，由①、②、③可以看出不会发生脏读，由③、④看出会发生不可重复读

演示：可重复读级别解决不可重复读问题

执行流程：

执行流程

实操：

结论：
repeatable read 解决了不可重复读问题

演示：可重复读产生幻读问题

执行流程：

实操：

结论：
可以看到左边事务插入 id=4 失败，相当于读取到了右边插入的数据，发生了幻读

拓展：在可重复读级别解决幻读问题

RR也是可以避免幻读的，通过对 select 操作手动加行X锁(独占锁) (SELECT … FOR UPDATE）。同时，即便当前记录不存在，比如id = 3是不存在的，当前事务也会获得一把记录锁（因为InnoDB的行锁锁定的是索引，故记录实体存在与否没关系，存在就加行X锁，不存在就加间隙锁），其他事务则无法插入此索引的记录，故杜绝了幻读