您现在的位置是:首页 >学无止境 >mysql 事务的 ACID 特征与使用网站首页学无止境

mysql 事务的 ACID 特征与使用

曹海峰1988 2023-06-01 00:00:03
简介mysql 事务的 ACID 特征与使用

事务的四大特征:

  • A 原子性:事务是最小的单位,不可以再分割;
  • C 一致性:要求同一事务中的 SQL 语句,必须保证同时成功或者失败;
  • I 隔离性:事务1 和 事务2 之间是具有隔离性的;
  • D 持久性:事务一旦结束 ( COMMIT ) ,就不可以再返回了 ( ROLLBACK ) 。

事务的隔离性

事务的隔离性可分为四种 ( 性能从低到高 ) :

  1. READ UNCOMMITTED ( 读取未提交 )

    如果有多个事务,那么任意事务都可以看见其他事务的未提交数据

  2. READ COMMITTED ( 读取已提交 )

    只能读取到其他事务已经提交的数据

  3. REPEATABLE READ ( 可被重复读 )

    如果有多个连接都开启了事务,那么事务之间不能共享数据记录,否则只能共享已提交的记录。

  4. SERIALIZABLE ( 串行化 )

    所有的事务都会按照固定顺序执行,执行完一个事务后再继续执行下一个事务的写入操作

查看当前数据库的默认隔离级别:

-- MySQL 8.x, GLOBAL 表示系统级别,不加表示会话级别。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
SELECT @@TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                | -- MySQL的默认隔离级别,可以重复读。
+--------------------------------+

-- MySQL 5.x
SELECT @@GLOBAL.TX_ISOLATION;
SELECT @@TX_ISOLATION;

修改隔离级别:

-- 设置系统隔离级别,LEVEL 后面表示要设置的隔离级别 (READ UNCOMMITTED)。
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 查询系统隔离级别,发现已经被修改。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+

脏读

测试 READ UNCOMMITTED ( 读取未提交 ) 的隔离性:

INSERT INTO user VALUES (3, '小明', 1000);
INSERT INTO user VALUES (4, '淘宝店', 1000);

SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

-- 开启一个事务操作数据
-- 假设小明在淘宝店买了一双800块钱的鞋子:
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘宝店';

-- 然后淘宝店在另一方查询结果,发现钱已到账。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+

由于小明的转账是在新开启的事务上进行操作的,而该操作的结果是可以被其他事务(另一方的淘宝店)看见的,因此淘宝店的查询结果是正确的,淘宝店确认到账。但就在这时,如果小明在它所处的事务上又执行了 ROLLBACK 命令,会发生什么?

-- 小明所处的事务
ROLLBACK;

-- 此时无论对方是谁,如果再去查询结果就会发现:
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

这就是所谓的脏读,一个事务读取到另外一个事务还未提交的数据。这在实际开发中是不允许出现的。

读取已提交

把隔离级别设置为 READ COMMITTED :

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-COMMITTED                 |
+--------------------------------+

这样,再有新的事务连接进来时,它们就只能查询到已经提交过的事务数据了。但是对于当前事务来说,它们看到的还是未提交的数据,例如:

-- 正在操作数据事务(当前事务)
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘宝店';

-- 虽然隔离级别被设置为了 READ COMMITTED,但在当前事务中,
-- 它看到的仍然是数据表中临时改变数据,而不是真正提交过的数据。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+


-- 假设此时在远程开启了一个新事务,连接到数据库。
$ mysql -u root -p12345612

-- 此时远程连接查询到的数据只能是已经提交过的
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

但是这样还有问题,那就是假设一个事务在操作数据时,其他事务干扰了这个事务的数据。例如:

-- 小张在查询数据的时候发现:
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+

-- 在小张求表的 money 平均值之前,小王做了一个操作:
START TRANSACTION;
INSERT INTO user VALUES (5, 'c', 100);
COMMIT;

-- 此时表的真实数据是:
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+

-- 这时小张再求平均值的时候,就会出现计算不相符合的情况:
SELECT AVG(money) FROM user;
+------------+
| AVG(money) |
+------------+
|  820.0000  |
+------------+

虽然 READ COMMITTED 让我们只能读取到其他事务已经提交的数据,但还是会出现问题,就是在读取同一个表的数据时,可能会发生前后不一致的情况。这被称为不可重复读现象 ( READ COMMITTED ) 。

幻读

将隔离级别设置为 REPEATABLE READ ( 可被重复读取 ) :

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+

测试 REPEATABLE READ ,假设在两个不同的连接上分别执行 START TRANSACTION :

-- 小张 - 成都
START TRANSACTION;
INSERT INTO user VALUES (6, 'd', 1000);

-- 小王 - 北京
START TRANSACTION;

-- 小张 - 成都
COMMIT;

当前事务开启后,没提交之前,查询不到,提交后可以被查询到。但是,在提交之前其他事务被开启了,那么在这条事务线上,就不会查询到当前有操作事务的连接。相当于开辟出一条单独的线程。

无论小张是否执行过 COMMIT ,在小王这边,都不会查询到小张的事务记录,而是只会查询到自己所处事务的记录:

SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+

这是因为小王在此之前开启了一个新的事务 ( START TRANSACTION ) ,那么在他的这条新事务的线上,跟其他事务是没有联系的,也就是说,此时如果其他事务正在操作数据,它是不知道的。

然而事实是,在真实的数据表中,小张已经插入了一条数据。但是小王此时并不知道,也插入了同一条数据,会发生什么呢?

INSERT INTO user VALUES (6, 'd', 1000);
-- ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'

报错了,操作被告知已存在主键为 6 的字段。这种现象也被称为幻读,一个事务提交的数据,不能被其他事务读取到

串行化

顾名思义,就是所有事务的写入操作全都是串行化的。什么意思?把隔离级别修改成 SERIALIZABLE :

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+

还是拿小张和小王来举例:

-- 小张 - 成都
START TRANSACTION;

-- 小王 - 北京
START TRANSACTION;

-- 开启事务之前先查询表,准备操作数据。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
|  6 | d         |  1000 |
+----+-----------+-------+

-- 发现没有 7 号王小花,于是插入一条数据:
INSERT INTO user VALUES (7, '王小花', 1000);

此时会发生什么呢?由于现在的隔离级别是 SERIALIZABLE ( 串行化 ) ,串行化的意思就是:假设把所有的事务都放在一个串行的队列中,那么所有的事务都会按照固定顺序执行,执行完一个事务后再继续执行下一个事务的写入操作 ( 这意味着队列中同时只能执行一个事务的写入操作 ) 。

根据这个解释,小王在插入数据时,会出现等待状态,直到小张执行 COMMIT 结束它所处的事务,或者出现等待超时。

风语者!平时喜欢研究各种技术,目前在从事后端开发工作,热爱生活、热爱工作。