具有 innodb 和可序列化事务的 Mysql 不会（总是）锁定行
我有一个带有 SELECT 和可能的 INSERT 的事务。出于并发原因，我将 FOR UPDATE 添加到 SELECT 中。为了防止出现幻像行，我使用了 SERIALIZABLE 事务隔离级别。当表中有任何行时，这一切都可以正常工作，但如果表为空，则不行。当表为空时，SELECT FOR UPDATE 不会执行任何（独占）锁定，并且并发线程/进程可以发出相同的 SELECT FOR UPDATE 而不会被锁定。
CREATE TABLE t (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
display_order INT
) ENGINE = InnoDB;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT COALESCE(MAX(display_order), 0) + 1 from t FOR UPDATE;
..
这个概念在 SQL Server 上按预期工作，但在 MySQL 上却不然。关于我做错了什么有什么想法吗？
编辑
在 display_order 上添加索引不会改变行为。
I have a transaction with a SELECT and possible INSERT. For concurrency reasons, I added FOR UPDATE to the SELECT. To prevent phantom rows, I'm using the SERIALIZABLE transaction isolation level. This all works fine when there are any rows in the table, but not if the table is empty. When the table is empty, the SELECT FOR UPDATE does not do any (exclusive) locking and a concurrent thread/process can issue the same SELECT FOR UPDATE without being locked.
CREATE TABLE t (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
display_order INT
) ENGINE = InnoDB;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT COALESCE(MAX(display_order), 0) + 1 from t FOR UPDATE;
..
This concept works as expected with SQL Server, but not with MySQL. Any ideas on what I'm doing wrong?
EDIT
Adding an index on display_order does not change the behavior.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这有一些有趣，两个事务都准备好获得真正的锁。一旦其中一个事务尝试执行插入，锁就会在那里。如果两个事务都尝试，其中一个事务将陷入死锁并回滚。如果只有其中一个尝试，则会出现锁定等待超时。
如果检测到锁等待超时，您可以回滚，这将允许下一个事务执行插入。
因此，我认为您可能会很快遇到死锁异常或超时异常，这应该可以挽救局面。但谈到完美的“可序列化”情况，这实际上是空表的不良副作用。引擎不可能在所有情况下都是完美的，至少不能完成双事务插入。
我昨天在 potsgreSQl 文档上发送了一个有趣的真实可串行性与引擎可串行性的案例，请检查这个示例，它很有趣< /em> : http://www.postgresql. org/docs/8.4/static/transaction-iso.html#MVCC-SERIALIZABILITY
更新：
其他有趣的资源： MySQL/InnoDB 是否实现真正的可序列化隔离？< /a>
There's something fun with this, both transaction are ready to get the real lock. As soon as one of the transaction will try to perform an insert the lock will be there. If both transactions try it one will get a deadlock and rollback. If only one of them try it it will get a lock wait timeout.
If you detect the lock wait timeout you can rollback and this will allow the next transaction to perform the insert.
So I think you're likely to get a deadlock exception or a timeout exception quite fast and this should save the situation. But talking about perfect 'serializable' situation this is effectively a bad side effect of empty table. The engine cannot be perfect on all cases, at least No double-transaction-inserts can be done..
I've send yesterday an interesting case of true seriability vs engine seriability, on potsgreSQl documentation, check this example it's funny : http://www.postgresql.org/docs/8.4/static/transaction-iso.html#MVCC-SERIALIZABILITY
Update:
Other interesting resource: Does MySQL/InnoDB implement true serializable isolation?
这可能不是一个错误。
不同数据库实现特定事务隔离级别的方式并非 100% 一致，并且需要考虑许多表现不同的边缘情况。 InnoDB 的目的是模仿 Oracle，但即使如此，我相信在某些情况下它的工作方式有所不同。
如果您的应用程序依赖于特定事务隔离模式中非常微妙的锁定行为，那么它可能会被破坏：
This is probably not a bug.
The way that the different databases implement specific transaction isolation levels is NOT 100% consistent, and there are a lot of edge-cases to consider which behave differently. InnoDB was meant to emulate Oracle, but even there, I believe there are cases where it works differently.
If your application relies on very subtle locking behaviour in specific transaction isolation modes, it is probably broken:
你看过这个文档吗：
http://dev.mysql.com/doc/refman /5.1/en/innodb-locking-reads.html
如果你问我，mysql 不是为了以这种方式使用而构建的......
我的建议是：
如果你可以的话->锁定整个表。
Did you have a look at this document:
http://dev.mysql.com/doc/refman/5.1/en/innodb-locking-reads.html
If you ask me, mysql wasn't built to be used in that way...
My recomendation is:
If you can affort it -> Lock the whole table.