MySQL对SERIALIZABLE的解释比PostgreSQL少，对吗？

vxqlmq5t 于 2024-01-05 发布在 Mysql

关注(0)|答案(2)|浏览(340)

当使用SERIALIZABLE transactions来实现仅在值不存在时才将值插入数据库的模式时，我观察到MySQL和PostgreSQL在SERIALIZABLE隔离级别的定义上存在显著差异。
请看下表：

CREATE TABLE person (
    person_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR NOT NULL
);

字符串
和下面的插入代码，在两个连接上并发运行：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT person_id FROM person WHERE name = 'Bob Ross';
-- sync point: both transactions should run through here before proceeding to
-- demonstrate the effect
-- 0 results, so we will insert
INSERT INTO person (name) VALUES ('Bob Ross');
SELECT last_insert_id();
COMMIT;

型
在PostgreSQL中（经过适当的SQL翻译），效果正如我所料：只有一个事务可以成功提交。这与我对PostgreSQL描述的SERIALIZABLE的理解一致，以及其他引用ANSI标准的来源：存在会产生相同效果的事务的串行执行。不存在返回0结果的这两个事务的串行执行然后添加条目。
在MySQL 5.7中，两个事务都成功，表中有2个'Bob Ross'条目。MySQL文档定义了SERIALIZABLE，禁止脏读，不可重复读和幻影读;它没有提到串行执行的存在。
SQLite还正确地防止了双重插入，至少在其默认模式下，由于其保守的锁定策略。
我的问题是：**MySQL在这种情况下的行为是正确的吗？还是说它违反了SQL标准，允许这些事务都成功？**我怀疑答案可能取决于“效果”的定义-从第一个SELECT观察到一个空的结果集是否算作两个具有相同效果的串行执行的“效果”？
其他一些评论可以帮助解决这个问题：

我知道我可以在MySQL中实现所需的行为，首先用ON CONFLICT IGNORE执行插入，然后执行选择。我试图理解为什么等效的标准SQL在两个引擎中没有表现出相同的行为。
我知道我也可以通过在name字段上设置一个唯一的约束来解决这个问题，无论如何，这可能是一个更好的数据模型，但核心问题仍然存在：为什么这些事务都成功了？

mysql

来源：https://stackoverflow.com/questions/49414519/mysql-interprets-serializable-less-strenuously-than-postgresql-is-it-correct

2条答案

按热度按时间

sshcrbum1#

SQL标准在第4.35.4节SQL事务的隔离级别（重点是我的）中说：
隔离级别SERIALIZABLE的并发SQL事务的执行保证是可串行化的。**可串行化的执行被定义为并发执行的SQL事务的操作的执行，其产生与这些相同SQL事务的某些串行执行相同的效果。串行执行是每个SQL事务在下一个SQL事务开始之前执行完成的执行。
再往下一点，它继续混淆这个问题：
隔离级别指定在执行并发SQL事务期间可能发生的现象类型。可能出现以下现象：
[跳过 P1（“脏读”）、P2（“不可重复读”）**和 P3（“幻影”）**的定义]
这四个隔离级别保证每个SQL事务将完全执行或根本不执行，并且不会丢失更新。隔离级别根据现象 P1、P2 和 P3 而不同。表8，“SQL事务隔离级别和三种现象”指定了给定隔离级别可能和不可能的现象。

+------------------+--------------+--------------+--------------+ 
| Level            | P1           | P2           | P3           |
+------------------+--------------+--------------+--------------+
| 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 |
+------------------+--------------+--------------+--------------+

字符串
注53 -对于在隔离级别SERIALIZABLE执行的SQL事务，排除这些现象是要求这些事务是可串行化的结果。
这种措辞产生了不幸的后果，许多实现者认为排除脏读、不可重复读和幻影读就足以正确实现SERIALIZABLE隔离级别，尽管注解澄清了这不是定义，而是定义的结果。
所以我认为MySQL是错误的，但它并不孤单：Oracle数据库以同样的方式解释SERIALIZABLE。

展开查看全部

赞(0）回复(0）举报 2024-01-05

gdx19jrr2#

我不能在MySQL 5.7中重现这一点。其他事务总是得到一个错误：
错误1213（40001）：试图获取锁时发现死锁;
原因是SELECT在SELECT部分中没有使用索引列，所以它将s锁设置为它找到的每一行，gap-s锁设置为找到的行之间的每一个间隙，而下一个键锁设置为找到的最后一行之后的正无穷大。因此在这种情况下，并发SELECT是不可能的。
你得到的结果的一个可能原因可能是这样的：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

字符串
它只为下一个事务设置隔离级别。如果您在此之后执行了一个SELECT，隔离级别将更改回正常（REPEATABLE READ）。
最好使用

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

型

赞(0）回复(0）举报 2024-01-05

我来回答

MySQL对SERIALIZABLE的解释比PostgreSQL少，对吗？

2条答案

相关问题

热门标签

最新问答