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