幻读和串行化

最新推荐文章于 2024-08-12 18:40:02 发布

原创最新推荐文章于 2024-08-12 18:40:02 发布 · 739 阅读

0 ·

CC 4.0 BY-SA版权

SQL 专栏收录该内容

29 篇文章

订阅专栏

本文深入探讨了数据库事务的隔离级别，特别是REPEATABLEREAD（可重复读）和SERIALIZABLE（串行化）级别的特性。通过实例演示了不同事务在这些隔离级别下如何处理数据读写冲突，解释了幻读现象及其解决方案。

幻读

将隔离级别设置为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结束它所处的事务，或出现等待超时。