一文搞懂MySQL事务（数据库事务隔离级别）

最新推荐文章于 2024-06-26 10:47:50 发布

Free Joe

最新推荐文章于 2024-06-26 10:47:50 发布

阅读量830

点赞数 4

分类专栏：数据库

本文链接：https://blog.csdn.net/wangfeijiu/article/details/113763439

版权

数据库专栏收录该内容

7 篇文章 55 订阅

订阅专栏

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务是什么

事务就是用户定义的一系列数据库操作，这些操作可以视为一个完成的逻辑处理工作单元，要么全部执行，要么全部不执行，是不可分割的工作单元。

事务是如何产生的

数据库中的数据是共享资源，因此数据库系统通常要支持多个用户的或不同应用程序的访问，并且各个访问进程都是独立执行的，这样就有可能出现并发存取数据的现象，这里有点类似Java开发中的多线程安全问题（解决共享变量安全存取问题），如果不采取一定的措施则会出现数据异常的情况。

DBMS系统必须对这种并发操作提供一种相应的处理机制来保证，访问彼此之间不受任何干扰，从而保证数据库的正确性不受到破坏，为了避免数据库的不一致性，这种处理机制称之为“并发控制”，其中事务就是为了保证数据的一致性而产生的一种概念和手段（事务不是唯一手段）

事务具备的特征

一般来说，事务是必须满足4个条件（ACID）：原子性、一致性、隔离性、持久性。

原子性（Atomicity，或称不可分割性）

一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。

假如在更新数据的过程中（未提交），数据库系统突然崩溃，那么数据会回滚到原状态。

一致性（Consistency）

在事务开始之前和事务结束以后，数据库的完整性没有被破坏。也就是说一个事务执行之前和执行之后都必须处于一致性状态。

例如现有完整性约束a+b=10，如果一个事务改变了a，那么必须得改变b，使得事务结束后依然满足a+b=10，否则事务失败。

隔离性（Isolation，又称独立性）

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交、读提交、可重复读和串行化。

现有两个并发的事务T1和T2，T1要么在T2开始前执行，要么在T2结束后执行，如果T1先执行，那T2就在T1结束后在执行。

持久性（Durability）

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

数据写入到数据文件中，存储在磁盘文件系统中，所以数据库系统奔溃了也不会影响数据文件。

脏读、不可重复读、幻读

如果事务不设置隔离，会发生什么事呢？

脏读（Dirty Read）

脏读是指一个事务对数据进行了修改，而这种修改还没有提交到数据库中，另外一个事务也访问这个数据，然后使用了这个未提交到数据库的数据。

张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。
与此同时，事务B正在读取张三的工资，读取到张三的工资为8000。
随后，事务A发生异常，而回滚了事务。张三的工资又回滚为5000。
最后，事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。

不可重复读（Non-repeatable Read）

不可重复读是指对于数据库中的某个数据，一个事务范围内的多次查询却返回了不同的结果，这是由于在查询过程中，数据被另外一个事务修改并提交了。

在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。
与此同时，事务B把张三的工资改为8000，并提交了事务。
随后，在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不致，这就产生了不可重复读。

幻读（Phantom Read）

幻读指的是多个事务一起执行时发生的一种现象。例如事务T1对表中某列的所有行数据进行了集中修改，同时，事务T2向表中插入一行新数据。那么，之后操作事务T1的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。。

目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人。
此时，事务B插入一条工资也为5000的记录。
这时，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读。

【补充】区分三者

脏读读取到的是一个未提交的数据，不可重复读读取到的是前一个事务提交的数据
不可重复读的重点是修改：同样的条件，你读取过的数据，再次读取出来发现值不一样了
幻读的重点在于新增或者删除：同样的条件，第 1 次和第 2 次读出来的记录数不一样

事务隔离级别

事务并发访问会导致脏读、不可重复读、幻读等问题，那该如何解决？

数据库通过锁机制解决并发访问的问题。根据锁定对象不同：分为行级锁和表级锁；根据并发事务锁定的关系上看：分为共享锁定和独占锁定，共享锁定可以防止资源被独占。为了更改数据，数据库必须在进行更改的行上施加行独占锁定，insert、update、delete和select...for update语句都会隐式采用必要的行锁定。

但是直接使用锁机制管理是很复杂的，基于锁机制，数据库给用户提供了不同的事务隔离级别，只要设置了事务隔离级别，数据库就会分析事务中的sql语句然后自动选择合适的锁。

以下是MySQL提供的事务隔离级别：（由上至下隔离级别递增）

隔离级别	脏读	不可重复读	幻读
读未提交（Read uncommitted）	允许	允许	允许
读已提交（Read committed）	不允许	允许	允许
可重复读（Repeatable read）	不允许	不允许	允许
串行化（Serializable）	不允许	不允许	不允许

　在MySQL数据库中，支持上面四种隔离级别，默认的为Repeatable Read (可重复读) ；而在 Oracle数据库中，只支持Serializable (串行化) 级别和 Read committed (读已提交) 这两种级别，其中默认的为 Read committed（读已提交）级别。

事务的隔离级别和数据库并发性是成反比的，隔离级别越高，并发性越低。
Serializable隔离级别最高，操作事务时直接进行锁表。锁表后其他事务无法执行，只能等待表解锁后执行。

查询和设置数据库的隔离级别

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

--read-uncommitted    read-committed    repeatable-read    serializable
set tx_isolation='read-uncommitted';

事务提交方式

在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。那事务都是怎么进行提交的呢？

显示提交

用COMMIT命令直接完成的提交为显式提交。

BEGIN;
INSERT ...
COMMIT;

隐示提交

用SQL命令间接完成的提交为隐式提交，这些命令操作的事务是不可以回滚（ROLLBACK）的。这些命令主要是一些DDL（数据定义语言）和DCL（数据控制语言）：

ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME等

自动提交

MySQL默认开启自动提交，则在执行插入、修改、删除等DML（数据管理语言）语句执行后，系统将自动进行提交。

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

SET AUTOCOMMIT=0; -- 禁止自动提交
SET AUTOCOMMIT=1; -- 开启自动提交

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

【补充】Rollback Segment

Rollback Segment是用来临时的保存当数据库数据发生改变时的先前值，它占据一定的存储空间。

DML操作会被放到 Rollback Segment中，事务提交后才生效。如果有相应的触发器，操作执行的时候触发器将被触发。DDL和DCL操作立即生效，原数据不放到 Rollback Segment中，不能回滚。

Rollback Segment作用：

取消某个数据操作，将数据复原至改变之前的值。这种情况只在事务执行过程中生效，一旦执行了COMMIT命令，那么Rollback Segment里面的值就会标识为失效的，数据改变将永久化。
如果一个SELECT语句开始读取一个表同时一个事务也在修改这个表的值（尚未提交），那么修改前的值就会保存到Rollback Segment里面，SELECT语句也是从ROLLBACK SEGMENT里面读取表的值。

【拓展】SQL语言的分类

1.DQL 数据查询语言

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：

SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

2.DML 数据操纵语言（针对实体）

数据操纵语言DML主要有三种形式：

INSERT  插入
UPDATE	更新
DELETE	删除

3.DDL 数据定义语言

DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上，创建/修改/删除数据库中的各种对象-----表、视图、索引、同义词、聚簇等

CREATE	创建
ALTER	修改
DROP	删除
TRUNCATE

4.DCL 数据控制语言

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。

GRANT 授权
ROLLBACK 回滚
COMMIT 提交

事务控制语句

BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

mysql> create table tx_test(
    -> id int(5)
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tx_test values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tx_test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from tx_test;
Empty set (0.00 sec)

保留点 SAVEPOINT

savepoint 是在数据库事务处理中实现“子事务”（subtransaction），也称为嵌套事务的方法。
事务可以回滚到savepoint 而不影响 savepoint 创建前的变化, 不需要放弃整个事务。

使用 SAVEPOINT

SAVEPOINT savepoint_name;    // 声明一个 savepoint

ROLLBACK TO savepoint_name;  // 回滚到savepoint

删除 SAVEPOINT
保留点会在事务处理完成（执行一条 ROLLBACK 或 COMMIT）后自动释放。也可以手动释放：

RELEASE SAVEPOINT savepoint_name;  // 删除指定保留点

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tx_test values(1);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint my_point;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tx_test values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tx_test;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> rollback to my_point;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tx_test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

=============================================================================================

至此！如有不足，还望各位读者指出。如果还不错，希望各位评论点赞关注支持。

=============================================================================================

参考文档：
MySQL 事务
 什么是事务？为什么会有事务？
数据库事务隔离级别（脏读、幻读、不可重复读）
数据库四种隔离级别

Free Joe

关注

4
点赞
踩
21

收藏

觉得还不错? 一键收藏
0
评论
一文搞懂MySQL事务（数据库事务隔离级别）

事务就是用户定义的一系列数据库操作，这些操作可以视为一个完成的逻辑处理工作单元，要么全部执行，要么全部不执行，是不可分割的工作单元。一般来说，事务是必须满足4个条件（ACID）：原子性、一致性、隔离性、持久性。- 脏读读取到的是一个未提交的数据，不可重复读读取到的是前一个事务提交的数据- 不可重复读的重点是修改：同样的条件，你读取过的数据，再次读取出来发现值不一样了- 幻读的重点在于新增或者删除：同样的条件，第 1 次和第 2 次读出来的记录数不一样事务提交方式：显示提交、隐示提交、自动提交
复制链接

扫一扫

专栏目录