Databases
StackOverflow2013
Postsdbo
POTransaction isolation level REPEATABLE READ causes deadlocks
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTransaction isolation level REPEATABLE READ causes deadlocks
    primarykey
    data
    text
    <p>A part of my application updates a table as per business logic after opening a connection on transaction isolation level REPEATABLE READ. In a rare scenario, If this operation coincides with another part of the application which opens a different connection and tries to reset the same record to its default value. I get following error</p> <pre><code>Msg 1205, Level 13, State 45, Line 7 Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. </code></pre> <p>I think i am able to re-produce the issue using following example.</p> <p>1.</p> <pre><code>create table Accounts ( id int identity(1,1), Name varchar(50), Amount decimal ) </code></pre> <p>2.</p> <pre><code>insert into Accounts (Name,Amount) values ('ABC',5000) insert into Accounts (Name,Amount) values ('WXY',4000) insert into Accounts (Name,Amount) values ('XYZ',4500) </code></pre> <p>3.</p> <p>Start a long transaction with isolation level as REPEATABLE READ</p> <pre><code>Set transaction isolation level REPEATABLE READ begin tran declare @var int select @var=amount from Accounts where id=1 waitfor delay '0:0:10' if @var &gt; 4000 update accounts set amount = amount -100; Commit </code></pre> <p>4.</p> <p>While Step.3 above is still being executed. Start another transaction on a different connection</p> <pre><code>Begin tran update accounts set Amount = 5000 where id = 1 commit tran </code></pre> <p>Transaction started in Step 3 would eventually complete but the one started in Step 4 would fail with following error message.</p> <pre><code>Msg 1205, Level 13, State 45, Line 7 Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. </code></pre> <p>What are my options to be able to eventually run transaction in step 4. The idea is to be able to reset the record to a default value and anything being performed on other transactions should be overridden in this case. I don't see any issue if both the transactions are not concurrent.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USRon5504
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTransaction isolation level REPEATABLE READ causes deadlocks
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POTransaction isolation level REPEATABLE READ causes deadlocks
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POTransaction isolation level REPEATABLE READ causes deadlocks
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSo `Account` table is a heap without any non-clustered index ?
      singulars
      1. POTransaction isolation level REPEATABLE READ causes deadlocks
      1. USBogdan Sahlean
    2. COFor this re-pro example, its a heap but i have to check the actual table. Would this matter ?
      singulars
      1. POTransaction isolation level REPEATABLE READ causes deadlocks
      1. USRon5504
    3. COOf course. It matters every indexes. Please add the list with all indexes on Account table.
      singulars
      1. POTransaction isolation level REPEATABLE READ causes deadlocks
      1. USBogdan Sahlean
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload