Начало Към постинга

Новина от дневника:

Sql Server Deadlock Example 

declare @i int = 1

while @i<500

begin

   begin tran 
      --SET TRANSACTION ISOLATION LEVEL  repeatable read <--whatever!!!
/*1*/       update accounts set [AccountBalance]=AccountBalance + 0.01 
/*2*/       update invoices set amountdue = amountdue + 0.01
   commit

   set @i = @i + 1

end

 

declare @i int = 1

while @i<500

begin

   begin tran 
      --SET TRANSACTION ISOLATION LEVEL  repeatable read <--whatever!!!
/*2*/   update invoices set amountdue = amountdue + 0.01 
/*1*/   update accounts set [AccountBalance]=AccountBalance + 0.01    

   commit

   set @i = @i + 1

end

 

 these two scripts ran by two different connections cause sql deadlock error like this:

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

the one transaction is executed ok, the other transaction is rolled back and needs to be rerun.

this error is inevitable and in my tests cannot be remedied by chosing the "right" isolation level although the docs clearly says that this error is result from using shared locks. i thought that using isolation level with no shared locks would prevent it. but it still occurs. why?

2017-05-25: somebody else also needed sql server deadlock demonstration, here it is on stackoverflow.

2017-05-26: the best solution is to be reordered the operations and in this manner this classic deadlock would never happen. Still if some other software out of our control works with the tables in the wrong order it would cause deadlock after all. This is why in such case the handling of this deadlock exception is mandatory.

 

Коментари:

Засега няма коментари касаещи тази новина.

Вашият коментар:

Име:
Сайт:
Допустими тагове:

<b> <i> <big> <pre>
[youtube]youtube_video_id[/youtube]
[vbox7]vbox7_video_id[/vbox7]
[img500]imgurl[/img500]