Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Serializable transaction deadlock

Documentation says, serializable transactions execute one after one.

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.

reason.

The conclusion, serializable transactions are not serial?

like image 826
WildDev Avatar asked Dec 07 '14 20:12

WildDev


People also ask

How do you resolve deadlock during transaction processing?

Deadlock frequency can sometimes be reduced by ensuring that all applications access their common data in the same order - meaning, for example, that they access (and therefore lock) rows in Table A, followed by Table B, followed by Table C, and so on.

What does it mean for a transaction to be SERIALIZABLE?

Transactions are said to be serializable if the results of running transactions simultaneously are the same as the results of running them serially - that is, one after the other.

How does SERIALIZABLE isolation level work?

Serializable isolation preserves the illusion that a transaction running against a table is the only transaction that is running against that table. For example, two concurrently running transactions, T1 and T2, must produce the same results as at least one of the following: T1 and T2 run serially in that order.


1 Answers

What happens here: Because transactions 1 runs in serializable isolation level, it keeps a share lock it obtains on table articles while it wait. This way, it is guaranteed that the non exists condition remains true until the transaction terminates. Transaction 2 gets a share lock as well that allows it to do the exist check condition. Then, with the insert statement, Transaction 2 requires to convert the share lock to a exclusive lock but has to wait as Transaction 1 holds a shared lock. When Transaction 1 finishes to wait, it also requests a conversion to exclusive mode => deadlock situation, 1 of the transaction has to be terminated.

like image 72
user6249398 Avatar answered Oct 16 '22 17:10

user6249398