MySQL documentation尚不清楚InnoDB引擎是实现真正的可序列化的隔离1还是snapshot isolation,这通常也被混淆地称为“可序列化”。哪一个?

如果MySQL InnoDB没有,那么有没有完全免费的,生产质量的RDBMS呢?

在图1中,“真正的可序列化隔离”表示不仅不存在按照SQL标准的读取异常,而且不存在写入偏斜异常,进一步详细解释here

最佳答案

更新:

看到注释,这在MySQL 5.5中似乎是固定的,在这些示例中,我们仍然具有表锁,并且不能欺骗索引next-key锁,即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具有回滚,良好的可序列化性。

这将在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);


现在重做测试。成功,可序列化性仍然得到加强。好消息。

但是有了索引,我认为在索引上进行了next-key锁定和行锁定。这意味着如果不影响并行事务,我们应该能够执行插入操作……这是一个大问题。

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 | <-- test
|     2 |    30 | <-- from trans1
|     1 |   300 | <-- from trans2 ERROR!
+-------+-------+


结果:通过在插入影响并行事务查询的行之前插入新的不相关行,我们欺骗了下一键锁定机制。或者至少这是我从测试中了解到的。所以我想说,不要相信引擎具有真正的可序列化性。当您在事务中具有聚合功能时,最好的办法是手动锁定表,在真正的“只有一个人”的情况下转换您的可序列化性问题,毫不奇怪!示例中的其他可序列化问题是约束验证(在操作后检查数量是否仍为正),您是否也锁定了这些情况。

10-06 01:36