Is this a deadlock ?

There is one exclusive lock there (IX), so a deadlock is likely.

Is it giving you any additional info, in particular a for each node with something like:

KEY: 8:1653632984:2 (da00ce043a9e) CleanCnt:1 Mode: U Fl ags: 0x0

?

End deadlock search 518 ... a deadlock was found.
ResType:LockOwner Stype:'OR' Mode: U SPID:167 ECID:0 Ec:(0x72781540) Value:0x2604a7c0 Cost:(0/0)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:167 ECID:0 Ec:(0x72781540) Value:0x2604a7c0 Cost:(0/0)
Requested By:
Input Buf: Language Event: Update (Table, bunch of stuff)
SPID: 158 ECID: 0 Statement Type: UPDATE Line #: 1
Owner:0x61c303e0 Mode: U        Flg:0x0 Ref:0 Life:00000001 SPID:158 ECID:0
Grant List 1::
KEY: 8:677577452:1 (4d01023b23ce) CleanCnt:1 Mode: U Flags: 0x0
Node:2
ResType:LockOwner Stype:'OR' Mode: IX SPID:158 ECID:0 Ec:(0x42E33548) Value:0x261940e0 Cost:(0/0)
Requested By:
Grant List 1::
Input Buf: Language Event: Update (Table, stuff)
SPID: 167 ECID: 0 Statement Type: UPDATE Line #: 1
Owner:0x4cbfd840 Mode: S        Flg:0x0 Ref:0 Life:00000001 SPID:167 ECID:0
Grant List 0::
PAG: 8:1:1755643               CleanCnt:1 Mode: SIU Flags: 0x2
Node:1
Wait-for graph

Deadlock encountered .... Printing deadlock information

Cycle:       ResType:LockOwner Stype:'OR' Mode: IX SPID:158 ECID:0 Ec:(0x42E33548) Value:0x261940e0 Cost:(0/0)
Node:2       ResType:LockOwner Stype:'OR' Mode: U SPID:167 ECID:0 Ec:(0x72781540) Value:0x2604a7c0 Cost:(0/0)
Node:1       ResType:LockOwner Stype:'OR' Mode: IX SPID:158 ECID:0 Ec:(0x42E33548) Value:0x261940e0 Cost:(0/0)
Deadlock cycle was encountered .... verifying cycle
Cycle:       ResType:LockOwner Stype:'OR' Mode: IX SPID:158 ECID:0 Ec:(0x42E33548) Value:0x261940e0
Node:2       ResType:LockOwner Stype:'OR' Mode: U SPID:167 ECID:0 Ec:(0x72781540) Value:0x2604a7c0
Node:1       ResType:LockOwner Stype:'OR' Mode: IX SPID:158 ECID:0 Ec:(0x42E33548) Value:0x261940e0
ResType:LockOwner Stype:'OR' Mode: IX SPID:158 ECID:0 Ec:(0x42E33548) Value:0x261940e0
Target Resource Owner:


whew.  Can you give me any insight to this ?  I have the developers looking at the code generating the sql, but this is somewhat new territory for me.  I'm not sure what's the important data in here.

Thanks,
JK

From within that db (DBID=8) in QA, this should give you the relevant table name:

SELECT OBJECT_NAME(677577452)

If you're not sure of the db name, do this:

SELECT DB_NAME(8)

from QA (from within master of any other db).

Since the index id = 1, that is the clustered index on that table.

The two "Update (Table, [bunch of] stuff)" should be the actual UPDATE statements that were involved in the deadlock.

Sometimes changing the clustered key can help prevent deadlocks.  Please post the results of an:

EXEC sp_help 'tableName'  --<<-- OBJECT_NAME(...) from above

IllustrationID      int      no      4      10         0          no      (n/a)      (n/a)      NULL
PricingModelID      int      no      4      10         0          yes      (n/a)      (n/a)      NULL
PolicyID      int      no      4      10         0          yes      (n/a)      (n/a)      NULL
UserID      int      no      4      10         0          yes      (n/a)      (n/a)      NULL
IllYear      int      no      4      10         0          yes      (n/a)      (n/a)      NULL
IllDB      float      no      8      53         NULL      yes      (n/a)      (n/a)      NULL
IllPrem      float      no      8      53         NULL      yes      (n/a)      (n/a)      NULL
ULReqdPrem      float      no      8      53         NULL      yes      (n/a)      (n/a)      NULL
InputPremium      float      no      8      53         NULL      yes      (n/a)      (n/a)      NULL
IllAV      float      no      8      53         NULL      yes      (n/a)      (n/a)      NULL
IllCSV      float      no      8      53         NULL      yes      (n/a)      (n/a)      NULL
IllDividends      float      no      8      53         NULL      yes      (n/a)      (n/a)      NULL
IllCashLoanInt      float      no      8      53         NULL      yes      (n/a)      (n/a)      NULL
IllusIntRate      float      no      8      53         NULL      yes      (n/a)      (n/a)      NULL
PolicyLoad      float      no      8      53         NULL      yes      (n/a)      (n/a)      NULL
PremiumLoad      float      no      8      53         NULL      yes      (n/a)      (n/a)      NULL
UnitLoad      float      no      8      53         NULL      yes      (n/a)      (n/a)      NULL
ProjIntRate      float      no      8      53         NULL      yes      (n/a)      (n/a)      NULL
IllLoadDate      datetime      no      8                            yes      (n/a)      (n/a)      NULL
PremLoadUpToTarget      float      no      8      53         NULL      yes      (n/a)      (n/a)      NULL

Identity / seed / increment
IllustrationID      1      1      0

Tbl name
IX_tbl_SAVEDIllustrations_3      clustered located on PRIMARY      UserID, PricingModelID, IllYear

DEFAULT on column PremLoadUpToTarget      DF__tbl_Prici__PremL__387A3A7B      (n/a)      (n/a)      (n/a)      (n/a)      (0)


The clustered index was changed recently, but I'm not sure that's the problem - it may be part of it.

Apparently, for some reason, the application accessing this table updates it almost constantly every time it's being accessed, even when it's only being read.   Just scrolling through records can cause deadlocks because it's continuously rewriting the data in the table, with the exclusive lock on it.  Likely the issue ?

Scott,

  I'm waiting for an answer on how often that number is expected to be modified.

  Can you explain why the clustered indexes are a potential cause of deadlocks ?

Thanks,
JK

That's would be pretty involved solely in writing.

But at root, keep in mind that a clustered index physically stores in key order.  So, if I have a page with values 1-50 on it, and I try to insert 45, it *must* go on that same page.  If someone else is also trying to insert, say, 11, it too *must* go on that same page.  Since SQL will often get a key range lock when dealing with clustered indexes, overlapping requests for the same page/range of keys values can cause deadlocks.

Thanks, Scott.

Congratulations on your 2000th question answered !

Thanks!  Honestly I hadn't noticed until you noticed it for me :-)