可序列化事务死锁 [英] Serializable transaction deadlock

问题描述

文档说，serializable 事务一个接一个执行.

Documentation says, serializable transactions execute one after one.

但在实践中似乎并非如此.这是两个几乎相等的事务，不同的是延迟仅 15 秒.

But in practic it seems not to be truth. Here's two almost equal transactions, the difference is delay for 15 seconds only.

#1:

set transaction isolation level serializable
go
begin transaction
if not exists (select * from articles where title like 'qwe')
begin
waitfor delay '00:00:15'
insert into articles (title) values ('qwe')
end
commit transaction go

#2:

set transaction isolation level serializable
go
begin transaction
if not exists (select * from articles where title like 'qwe')
begin
insert into articles (title) values ('asd')
end
commit transaction go

自第一个事务开始以来，第二个事务已在几秒钟后运行.

The second transaction has been run after couple of seconds since the start of first one.

结果是死锁.第一个交易以

The result is deadlock. The first transaction dies with

Transaction (Process ID 58) was deadlocked on 
lock resources with another process and has been chosen as the deadlock victim. 
Rerun the transaction.

原因.

结论，可序列化的事务不是串行的?

The conclusion, serializable transactions are not serial?

推荐答案

可串行化的事务不一定串行执行.

serializable transactions don't necessarily execute serially.

承诺只是事务只有在结果就像它们是连续执行(以任何顺序)时才能提交.

The promise is just that transactions can only commit if the result would be as if they had executed serially (in any order).

满足此保证的锁定要求经常会导致死锁，其中一个事务需要回滚.您需要编写自己的重试逻辑来重新提交失败的查询.

The locking requirements to meet this guarantee can frequently lead to deadlock where one of the transactions needs to be rolled back. You would need to code your own retry logic to resubmit the failed query.

请参阅可序列化隔离级别了解更多关于逻辑描述和实现之间的差异.

See The Serializable Isolation Level for more about the differences between the logical description and implementation.

这篇关于可序列化事务死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！