mysql怎么实现事务序列化_事务 – MySQL / InnoDB是否实现真正的可序列化隔离？

最新推荐文章于 2023-06-21 09:06:50 发布

re硕君

最新推荐文章于 2023-06-21 09:06:50 发布

阅读量168

点赞数

文章标签： mysql怎么实现事务序列化

本文链接：https://blog.csdn.net/weixin_34792641/article/details/114346750

版权

更新：

看到评论,这似乎是在MySQL 5.5中修复的,这些例子我们还是有一个表锁,而索引下一键锁不能被愚弄,AFAIK.

原版的：

昨天发现你的问题,我也想知道InnoDb的MVCC可编程性模型.

所以我做了一些测试. MySQL 5.1.37.可串行化问题的一个很好的测试是postgrESQL 9.0 MVCC documentation中提供的一个测试,本章可序列化隔离与真正的串行化可以看出,如果没有执行谓词锁定,我们可以看到MVCC模型对可序列化的限制.

所以让我们在MySQL上测试一下：

CREATE TABLE t1 (

class integer,

value integer

) ENGINE=InnoDB;

INSERT INTO t1 (`class`,`value`) VALUES

(1,10),

(1,20),

(2,100),

(2,200);

现在我们将打开两个不同的连接来进行两个并行事务(T1和T2)：

T1：

SET TRANSACTIOn ISOLATION LEVEL SERIALIZABLE;

BEGIN;

SELECT SUM(value) FROM t1 WHERE class = 1;

结果是30.

T2：

SET TRANSACTIOn ISOLATION LEVEL SERIALIZABLE;

BEGIN;

SELECT SUM(value) FROM t1 WHERE class = 2;

结果是300.

现在出现了序列化问题.如果T1插入一行渲染从T2无效的选择(这里T2相同).

T1：

INSERT INTO t1 (`class`,`value`) VALUES (2,30);

==>等待(锁定到位)

T2：

INSERT INTO t1 (`class`,`value`) VALUES (1,300);

==>错误1213(40001)：尝试锁定时发现死锁;尝试重新启动事务

T1现在成功插入,t2有一个ROLLBACK,良好的可串行性.

这将在PostgreSQL 9.0上失败(事情在9.1上发生变化,但这是另一个问题).

实际上只有一个事务可以在表上执行插入.即使我们尝试用class = 3插入.

INSERT INTO t1 (`class`,`value`) VALUES (3,30);

我们会看到一个等待的锁,如果出现问题,就会发生僵局.看来我们在MySQL中有一个谓词锁定

但事实上,它是InnoDB中的next-key locking实现.

Innodb执行行锁,并在索引上锁定一些空白.这里我们在表上没有索引,看起来像MySQL决定锁定表.

所以我们来试试下一个键锁来看看这是否强制可序列化.首先回滚正在运行的事务(T1).然后创建一个索引.

CREATE index t1class ON t1 (class);

现在重做测试.成功,序列化仍然被强制执行.好消息.

但是,索引到位后,我认为下一键锁定和行锁是在索引上进行的.这意味着我们应该能够执行插入,如果它不影响并行事务…这里是一个大问题.

T1：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;

SELECT SUM(value) FROM t1 WHERE class = 1;

结果是30.

T2：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;

SELECT SUM(value) FROM t1 WHERE class = 2;

结果是300.

在这里,我们将在T1上建立一个无关的插入,现在我们有一个索引,这将成功：

T1：

INSERT INTO t1 (`class`,`value`) VALUES (3,30);

两者都可以执行插入(这里我只做了一个),这是正常的.没有应用预测锁定,没有对class = 3进行SELECT查询.看起来如果我们给它很好的索引(插入没有表锁定),下一个键锁定效果会更好.

现在我们尝试插入下一个键锁,在一行匹配选择T2(class = 2)：

T1：

INSERT INTO t1 (`class`,`value`) VALUES (2,30);

哎哟.它成功了！

T2：

INSERT INTO t1 (`class`,`value`) VALUES (1,300);

==>等候.那里还有一个锁.希望.

T1：

COMMIT;

T2：(锁已经走了,插入)

SELECT SUM(value) FROM t1 WHERE class = 2;

COMMIT;

仍然有300在这里.似乎可以串行化了.

select * from t1;

+-------+-------+

| class | value |

+-------+-------+

| 1 | 10 |

| 1 | 20 |

| 2 | 100 |

| 2 | 200 |

| 3 | 30 |

| 2 | 30 |

| 1 | 300 |

+-------+-------+

结果：通过在插入影响并行事务查询的行之前插入一个新的不相关的行,我们欺骗了下一个键锁定机制.或至少这是我从测试中了解的.所以我会说,不要相信引擎的真正可串行性.当您在交易中拥有聚合功能时,最好的办法是手动锁定表,将您的可序列化问题转化为真正的一人情况,没有惊喜！示例中的其他可序列化问题是约束验证(检查您的操作后数量是否为正),您是否也拥有这些案例的锁.