SQL Server 2008: U and X locks - deadlock on one table without any indexes. How?

Question

I observe really strange behavior of my DB. I have one small table (about 300 rows) where one field is continuously updated.

And I was getting a lot of deadlocks there - update of the table was deadlocking the similar update of the same table (U lock vs X lock).

So I decided to remove the clustered index (so table doesn't have any indexes now) to fix the deadlocks. But it didn't help and now I'm getting the deadlock between the U and X lock modes.

So one table, no indexes and 2 sessions updating one table

Victim

update dbo.MyNumber set
  @nextno = nextno = nextno + 1
where [type] = @type
  and yearid = @yearid

Winning query:

update dbo.MyNumber set
  @nextno = nextno = nextno + 1
where [type] = @TYPE
  and yrclosedyn = 0

Rows are definitely different but the page is the same.

How Is it possible? Maybe it is connected to the lock escalation, or ...?

I really appreciate any suggestions.

Thanks in advance Mike

DEADLOCK XML:

<deadlock-list>
 <deadlock victim="process6c492e8">
  <process-list>
   <process id="processb6a988" taskpriority="0" logused="1848" waitresource="RID: 5:1:127478:16" waittime="3478" ownerId="17153439" transactionname="user_transaction" lasttranstarted="2012-12-18T12:31:40.147" XDES="0xffffffff89482258" lockMode="U" schedulerid="7" kpid="4248" status="suspended" spid="98" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2012-12-18T12:31:49.913" lastbatchcompleted="2012-12-18T12:31:49.913" clientapp="PenAIR" hostname="S16047425" hostpid="9300" loginname="sa" isolationlevel="read committed (2)" xactid="17153439" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="MYDATABASE.dbo.MyStoredProcedure" line="92" stmtstart="9062" stmtend="9388" sqlhandle="0x030005002d15a05e58b5710016a100000100000000000000">
UPDATE dbo.MyNumber Set
  @NEXTNO = NEXTNO = NEXTNO + 1
WHERE  (TYPE = @TYPE) AND (YRCLOSEDYN = 0)     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 5 Object Id = 1587549485]    </inputbuf>
   </process>
   <process id="process6c492e8" taskpriority="0" logused="192" waitresource="RID: 5:1:127478:20" waittime="8252" ownerId="17153562" transactionname="user_transaction" lasttranstarted="2012-12-18T12:31:45.140" XDES="0x6583b1e0" lockMode="U" schedulerid="13" kpid="19824" status="suspended" spid="143" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2012-12-18T12:31:45.140" lastbatchcompleted="2012-12-18T12:31:45.140" clientapp="PenAIR" hostname="S16047425" hostpid="4760" loginname="sa" isolationlevel="read committed (2)" xactid="17153562" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="MYDATABASE.dbo.MyStoredProcedure" line="92" stmtstart="9062" stmtend="9388" sqlhandle="0x030005002d15a05e58b5710016a100000100000000000000">
UPDATE dbo.MyNumber Set
  @NEXTNO = NEXTNO = NEXTNO + 1
WHERE  ([TYPE] = @TYPE) AND (YRCLOSEDYN = 0)     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 5 Object Id = 1587549485]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <ridlock fileid="1" pageid="127478" dbid="5" objectname="MYDATABASE.dbo.MyNumber" id="lock464f2640" mode="X" associatedObjectId="72057594131120128">
    <owner-list>
     <owner id="processb6a988" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process6c492e8" mode="U" requestType="wait"/>
    </waiter-list>
   </ridlock>
   <ridlock fileid="1" pageid="127478" dbid="5" objectname="MYDATABASE.dbo.MyNumber" id="lockfffffffff1974980" mode="X" associatedObjectId="72057594131120128">
    <owner-list>
     <owner id="process6c492e8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="processb6a988" mode="U" requestType="wait"/>
    </waiter-list>
   </ridlock>
  </resource-list>
 </deadlock>
</deadlock-list>

Why on earth did you think removing the clustered index would solve your deadlocks?? The locks happen on the data rows - whether or not you have a clustered index makes **no difference** whatsoever .... — marc_s, Dec 18 '12 at 21:38
I removed clustered index because I hoped that Database Engine will use another way to obtain the locks on the table. When there was the clustered index the deadlock was occurring on HoBT lock level. Now it looks like it appears on page level. — mike, Dec 18 '12 at 21:56
What indexes are on the table? Do you have different NCIs covering the two different `WHERE` clauses? Also are the two different queries potentially updating the same row or an overlapping set of rows? Also do you have the XML deadlock graph? — Martin Smith, Dec 18 '12 at 21:59
There was only clustered index and I removed it so now there is no any indexes. Yes, I have the deadlock XML graph but I can't publish it ... — mike, Dec 18 '12 at 22:02
Don't know why you would be getting a deadlock then. Would have thought it would take `IU` locks on the table and pages within it, `U` locks on the rows and convert the `IU` locks to `IX` and the row `U` lock to a row `X` lock when the matching row is found. Any other statements in the same transactions? — Martin Smith, Dec 18 '12 at 22:17
there other statements but they are not connected to this table. Value is updated and written to the var @number then this variable is used in the inserts to other tables. — mike, Dec 18 '12 at 22:19
Is this sequence generation code only called once in every transaction? I can see how deadlock might easily occur if T1 generates the sequence for TypeX then TypeY and T2 generates the sequence for TypeY then TypeX. — Martin Smith, Dec 18 '12 at 23:04
@user1390785 We can only guess about the reasons for this deadlock without the deadlock graph. Please obfuscate any sensitive information you have and post the xml to help us help you. — Roji P Thomas, Dec 19 '12 at 01:34
Added Deadlock XML to the post. Could you please take a look? In the XML I replaced table name, stored procedure name and database name — mike, Dec 20 '12 at 07:32
Deadlock graph is a bit different from example given in the post earlier (queries are a bit different) bot the idea is the same: One table, One page, different rows. — mike, Dec 20 '12 at 07:49
Also I tried to reproduce it in the SSMS but it didn't appear. — mike, Dec 20 '12 at 07:50

score 3 · Answer 1 · edited May 23 '17 at 12:27

Shredding your deadlock graph into tabular form shows the following.

+----------+-------------------------+-----------+-----------+------------+----------+--------------------+--------------------+---------+
| LockMode |      LockedObject       | TranCount | LockEvent | LockedMode | WaitMode |    WaitResource    |   IsolationLevel   | LogUsed |
+----------+-------------------------+-----------+-----------+------------+----------+--------------------+--------------------+---------+
| U        | MYDATABASE.dbo.MyNumber | NULL      | rid       | X          | U        | RID: 5:1:127478:20 | read committed (2) |     192 |
| U        | MYDATABASE.dbo.MyNumber | NULL      | rid       | X          | U        | RID: 5:1:127478:16 | read committed (2) |    1848 |
+----------+-------------------------+-----------+-----------+------------+----------+--------------------+--------------------+---------+

You still haven't answered my question in the comments as to whether the sequence generation code is only called once in every transaction.

It is easy to generate a deadlock graph similar to the one in your post if not.

Setup

CREATE TABLE dbo.MyNumber
  (
     [TYPE]     CHAR(1),
     YRCLOSEDYN INT,
     NEXTNO     INT
  )

INSERT INTO dbo.MyNumber
VALUES      ('X', 0, 1),
            ('Y', 0, 1)

GO

CREATE PROC MyStoredProcedure @TYPE   CHAR(1),
                              @NEXTNO INT OUTPUT
AS
    UPDATE dbo.MyNumber
    SET    @NEXTNO = NEXTNO = NEXTNO + 1
    WHERE  ( [TYPE] = @TYPE )
           AND ( YRCLOSEDYN = 0 )

Connection 1

BEGIN TRAN

DECLARE @NEXTNO INT

EXEC MyStoredProcedure 'Y', @NEXTNO OUTPUT

WAITFOR DELAY '00:00:05'

EXEC MyStoredProcedure 'X', @NEXTNO OUTPUT

ROLLBACK

Connection 2

(Run immediately after executing the code in connection 1)

BEGIN TRAN
DECLARE @NEXTNO INT

EXEC MyStoredProcedure 'X', @NEXTNO OUTPUT

EXEC MyStoredProcedure 'Y', @NEXTNO OUTPUT

ROLLBACK

The deadlock graph output from that is very similar to the one above

+----------+-------------------------+-----------+-----------+------------+----------+-----------------+--------------------+---------+
| LockMode |      LockedObject       | TranCount | LockEvent | LockedMode | WaitMode |  WaitResource   |   IsolationLevel   | LogUsed |
+----------+-------------------------+-----------+-----------+------------+----------+-----------------+--------------------+---------+
| U        | MYDATABASE.dbo.MyNumber |         2 | rid       | X          | U        | RID: 11:1:144:1 | read committed (2) |     248 |
| U        | MYDATABASE.dbo.MyNumber |         2 | rid       | X          | U        | RID: 11:1:144:0 | read committed (2) |     248 |
+----------+-------------------------+-----------+-----------+------------+----------+-----------------+--------------------+---------+

If this is the explanation for your issue you will need to ensure that you update the Sequences in the same order in all transactions (I assume there must be some good reason why you can't just use an IDENTITY column based solution)

Thank you for explanation, It is very helpful for me in the further investigation. I can't update sequences in the same order because it is done by users' actions. Also I can't use Identity due to software architecture (I definitely agree with you that IDENTITY is the best way for generating sequences). I can't do any updates to the application, I can only modify DB objects without modifying interfaces (in - out SP params etc). Thank you a lot for showing how to reproduce it. — mike, Dec 20 '12 at 14:45
@user1390785 - Well if you can't update sequences in the same order the only way of guaranteeing deadlock avoidance is to serialise access to `MyStoredProcedure` (e.g. with `sp_getapplock` but that may cause an unacceptable amount of blocking) — Martin Smith, Dec 20 '12 at 14:54
It will cause, I tried it. I now think, maybe it will be reasonable to do inserts instead of updates: insert 1 to the NEXTNO column and then take sum of this field. What do you think about it? — mike, Dec 20 '12 at 15:04
It won't solve the problem except if you allow your transactions to do dirty reads. I assume that you must have a requirement for no gaps? If that is the case then the `insert` solution and dirty reads won't work for you as it could read rows that later get rolled back. If it is not the case that you have a requirement for no gaps then you might as well use the `IDENTITY` solution I linked in my answer. — Martin Smith, Dec 20 '12 at 15:07
I can't use IDENTITY because if global transaction is rolled back identity value will be increased any way, and I will have to reseed it any time when doing insert to the table. — mike, Dec 20 '12 at 15:10

SQL Server 2008: U and X locks - deadlock on one table without any indexes. How?

1 Answers1

Setup

Connection 1

Connection 2