Serialized transactional deadlock
The documentation says serializable
transactions are executed one by one.
But in practice this is not true. There are two almost equal transactions, the difference is a delay of only 15 seconds.
# 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 was completed in a couple of seconds from the beginning of the first.
The result is a stalemate. 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.
cause.
Conclusion, serializable transactions are not serialized?
source to share
serializable transactions are not necessarily serialized.
The promise is that transactions can only commit transactions if the result is as if they were executed serially (in any order).
The locking requirements to fulfill this guarantee can often lead to deadlock situations where one of the transactions must be rolled back. You will need to code your own retry logic to resubmit the failed request.
For details on the differences between boolean description and implementation, see Serializable Isolation Level .
source to share
What's going on here: Since transactions 1 are executed in a serialized isolation level, it retains the access locks it acquires on the table entries while it waits. This ensures that the non exists condition remains true until the transaction completes. Transaction 2 also acquires an access lock, which allows it to fulfill an existing validation condition. Then, with an insert statement, Transaction 2 asks to convert the shared lock to an exclusive lock, but it must wait until Transaction 1 holds the shared lock. When transaction 1 finishes to wait, it also requests an exception conversion => deadlock, transaction 1 must complete.
source to share
I ran into a similar problem and found that:
From MSDN :
SERIALIZABLE Specifies the following:
- Applications cannot read data that has changed but has not yet been committed by other transactions.
- No other transactions can modify data that was read by the current transaction before the completion of the current transaction.
- Other transactions cannot insert new rows with key values that fall within the range of keys read by any statements in the current transaction prior to the completion of the current transaction.
The second dot does not indicate that both sessions cannot use the same lock, which would lead to a deadlock. We solved this with a SELECT hint.
select * from articles WITH (UPDLOCK, ROWLOCK) where title like 'qwe'
Haven't tried if it worked in this case, but I think you will have to lock part of the table since the row has not been created yet.
source to share