Deadlock involving Identity Column

  • I recently inherited a server where tables incorporate identity columns. These columns also serve as clustered indexes. I have noticed numerous deadlocks on inserts. My thought is to change the clustering indexes as all inserts are going to the end of the table and same data/index page causing contention. Is there any benefit to having identity columns as a clustered index?

  • I doubt that having a clustered index on the identity column is the cause of the deadlocks.

    You need to analyze the deadlock information in detail to determine exactly what resource is being deadlocked.

  • The index page is showing on the deadlock graph which led to my assumption.

  • It is highly unlikely that having a clustered key on an identity column is causing deadlocks. An identity column is typically a very good candidate for a clustered key because it is increasing and, depending on the data type, narrow.

    If you could post the deadlock information it would be easier to be helpful.

  • The deadlock info is below. Both spids are inserting via the same stored procedure. This is an audit type table so there are high inserts. There is another table that has a similar problem to a lesser degree but they are consistent where they are clustering on the identity column.

    Unknown,waiter id=processc19e28 mode=IX requestType=convert

    07/23/2013 10:59:28,spid21s,Unknown,waiter id=process3b9787a8 mode=IX requestType=convert

    07/23/2013 10:59:28,spid21s,Unknown,waiter-list

    07/23/2013 10:59:28,spid21s,Unknown,owner id=process3b9787a8 mode=S

    07/23/2013 10:59:28,spid21s,Unknown,owner id=processc19e28 mode=S

    07/23/2013 10:59:28,spid21s,Unknown,owner-list

    07/23/2013 10:59:28,spid21s,Unknown,pagelock fileid=1 pageid=1662692 dbid=5 objectname=AMAC.dbo.AuditLog id=lock24d2c5c0 mode=S associatedObjectId=72057594504937472

    07/23/2013 10:59:28,spid21s,Unknown,resource-list

    07/23/2013 10:59:28,spid21s,Unknown,Proc [Database Id = 5 Object Id = 292912115] Stored procedure doing insert

    07/23/2013 10:59:28,spid21s,Unknown,inputbuf

    07/23/2013 10:59:28,spid21s,Unknown,( @intAuditLog_ID<c/> @intUserID<c/> @dtDateTime<c/> @chrIsSubscriber<c/> @chrSubscriber_AgencyID<c/> @vtxtChangesMade<c/>@vChrScreenName)

    07/23/2013 10:59:28,spid21s,Unknown,VALUES

    07/23/2013 10:59:28,spid21s,Unknown,AuditLog (AuditLog_ID<c/> User_ID<c/> DateTime<c/> IsSubscriber<c/> SubscriberAgency_ID<c/> ChangesMade<c/>ScreenName)

    07/23/2013 10:59:28,spid21s,Unknown,INSERT INTO

    07/23/2013 10:59:28,spid21s,Unknown,frame procname=AMAC.dbo.ap_AuditLog_i line=44 stmtstart=3020 stmtend=3528 sqlhandle=0x03000500f37b75119b788301fa9300000100000000000000

    07/23/2013 10:59:28,spid21s,Unknown,executionStack

    07/23/2013 10:59:28,spid21s,Unknown,process id=process3b9787a8 taskpriority=0 logused=676 waitresource=PAGE: 5:1:1662692 waittime=4000 ownerId=66749210653 transactionname=user_transaction lasttranstarted=2013-07-23T10:59:24.170 XDES=0x4b47c6d8 lockMode=IX schedulerid=7 kpid=1112 status=suspended spid=118 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2013-07-23T10:59:24.170 lastbatchcompleted=2013-07-23T10:59:24.170 clientapp=Internet Information Services hostname=WEB1 hostpid=5964 loginname=sa isolationlevel=serializable (4) xactid=66749210653 currentdb=5 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128024

    07/23/2013 10:59:28,spid21s,Unknown,Proc [Database Id = 5 Object Id = 292912115]

    07/23/2013 10:59:28,spid21s,Unknown,inputbuf

    07/23/2013 10:59:28,spid21s,Unknown,( @intAuditLog_ID<c/> @intUserID<c/> @dtDateTime<c/> @chrIsSubscriber<c/> @chrSubscriber_AgencyID<c/> @vtxtChangesMade<c/>@vChrScreenName)

    07/23/2013 10:59:28,spid21s,Unknown,VALUES

    07/23/2013 10:59:28,spid21s,Unknown,AuditLog (AuditLog_ID<c/> User_ID<c/> DateTime<c/> IsSubscriber<c/> SubscriberAgency_ID<c/> ChangesMade<c/>ScreenName)

    07/23/2013 10:59:28,spid21s,Unknown,INSERT INTO

    07/23/2013 10:59:28,spid21s,Unknown,frame procname=AMAC.dbo.ap_AuditLog_i line=44 stmtstart=3020 stmtend=3528 sqlhandle=0x03000500f37b75119b788301fa9300000100000000000000

    07/23/2013 10:59:28,spid21s,Unknown,executionStack

    07/23/2013 10:59:28,spid21s,Unknown,process id=processc19e28 taskpriority=0 logused=592 waitresource=PAGE: 5:1:1662692 waittime=3968 ownerId=66749209961 transactionname=user_transaction lasttranstarted=2013-07-23T10:59:24.110 XDES=0x17c78648 lockMode=IX schedulerid=5 kpid=6996 status=suspended spid=254 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2013-07-23T10:59:24.110 lastbatchcompleted=2013-07-23T10:59:24.110 clientapp=Microsoft® Windows® Operating System hostname=LICAPPS04 hostpid=8044 loginname=amacbusiness isolationlevel=serializable (4) xactid=66749209961 currentdb=5 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128536

    07/23/2013 10:59:28,spid21s,Unknown,process-list

    07/23/2013 10:59:28,spid21s,Unknown,deadlock victim=processc19e28

    07/23/2013 10:59:28,spid21s,Unknown,deadlock-list

    07/23/2013 10:59:28,spid4s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x17C78648 Mode: IX SPID:254 BatchID:0 ECID:0 TaskProxy:(0x1BD48374) Value:0x1fb6a860 Cost:(0/592)

    07/23/2013 10:59:28,spid4s,Unknown,Victim Resource Owner:

    07/23/2013 10:59:28,spid4s,Unknown,

    07/23/2013 10:59:28,spid4s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x4B47C6D8 Mode: IX SPID:118 BatchID:0 ECID:0 TaskProxy:(0x0C814374) Value:0x3e016160 Cost:(0/676)

    07/23/2013 10:59:28,spid4s,Unknown,Requested By:

    07/23/2013 10:59:28,spid4s,Unknown,Grant List 3:

    07/23/2013 10:59:28,spid4s,Unknown,Input Buf: RPC Event: Proc [Database Id = 5 Object Id = 292912115]

    07/23/2013 10:59:28,spid4s,Unknown,SPID: 254 ECID: 0 Statement Type: INSERT Line #: 44

    07/23/2013 10:59:28,spid4s,Unknown,Owner:0x2DEE1720 Mode: S Flg:0x0 Ref:0 Life:02000000 SPID:254 ECID:0 XactLockInfo: 0x17C7866C

    07/23/2013 10:59:28,spid4s,Unknown,Grant List 2:

    07/23/2013 10:59:28,spid4s,Unknown,PAGE: 5:1:1662692 CleanCnt:4 Mode:S Flags: 0x2

    07/23/2013 10:59:28,spid4s,Unknown,Node:2

    07/23/2013 10:59:28,spid4s,Unknown,

    07/23/2013 10:59:28,spid4s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x17C78648 Mode: IX SPID:254 BatchID:0 ECID:0 TaskProxy:(0x1BD48374) Value:0x1fb6a860 Cost:(0/592)

    07/23/2013 10:59:28,spid4s,Unknown,Requested By:

    07/23/2013 10:59:28,spid4s,Unknown,Input Buf: RPC Event: Proc [Database Id = 5 Object Id = 292912115]

    07/23/2013 10:59:28,spid4s,Unknown,SPID: 118 ECID: 0 Statement Type: INSERT Line #: 44

    07/23/2013 10:59:28,spid4s,Unknown,Owner:0x25696060 Mode: S Flg:0x0 Ref:0 Life:02000000 SPID:118 ECID:0 XactLockInfo: 0x4B47C6FC

    07/23/2013 10:59:28,spid4s,Unknown,Grant List 3:

    07/23/2013 10:59:28,spid4s,Unknown,Grant List 2:

    07/23/2013 10:59:28,spid4s,Unknown,PAGE: 5:1:1662692 CleanCnt:4 Mode:S Flags: 0x2

    07/23/2013 10:59:28,spid4s,Unknown,Node:1

    07/23/2013 10:59:28,spid4s,Unknown,

    07/23/2013 10:59:28,spid4s,Unknown,Wait-for graph

    07/23/2013 10:59:28,spid4s,Unknown,Deadlock encountered .... Printing deadlock information

  • The code too please James - or at least enough of it to be sure that it contains the insert (AMAC.dbo.ap_AuditLog_i line=44)

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • The first thing I noticed is that the Isolation Level is serializable which will cut down on your concurrency quite a bit. I'd be looking into the reason for using Serializable. Based on the deadlock graph both queries are getting S locks and requesting IX locks. The IX lock is incompatible with the S lock so holding the shared locks is what seems to be causing the deadlocking. Without seeing the query I'm guessing there is a SELECT followed by the INSERT and using Serializable is causing the S locks to be held. Just a guess though.

  • Code below. You are correct on the serializable but am verifying I have the right code as this is almost the entire sp less some declarations. I asked about the need for isolation level but have yet to receive a response. Appreciate the input

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    -- Start the transaction

    BEGIN TRANSACTION

    INSERT INTO

    AuditLog (AuditLog_ID, User_ID, DateTime, IsSubscriber, SubscriberAgency_ID, ChangesMade,ScreenName)

    VALUES

    ( @intAuditLog_ID, @intUserID, @dtDateTime, @chrIsSubscriber, @chrSubscriber_AgencyID, @vtxtChangesMade,@vChrScreenName)

    if (@@ERROR > 0)

    ROLLBACK TRANSACTION

    ELSE

    COMMIT TRANSACTION

    -- Set back the locking to default

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

  • Weird that it's deadlocking on a page. Since you have the page address (5:1:1662692), it would be nice to see what the actual deadlocked resource is.

    Print the page header with the following code, and report back what you see for the following values:

    m_type

    Metadata: IndexID

    Here's the code:

    DBCC TRACEON(3604);

    DBCC PAGE(5, 1, 1662692);

    Eddie Wuerch
    MCM: SQL

  • Eddie Wuerch (9/10/2013)


    Weird that it's deadlocking on a page. Since you have the page address (5:1:1662692), it would be nice to see what the actual deadlocked resource is.

    Print the page header with the following code, and report back what you see for the following values:

    m_type

    Metadata: IndexID

    Here's the code:

    DBCC TRACEON(3604);

    DBCC PAGE(5, 1, 1662692);

    I believe it is doing a page lock because of the isolation level. From BOL on Serializable:

    Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. The range locks are held until the transaction completes. This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

    This doesn't look to me like a situation where SERIALIZABLE would be necessary. I would think that READ COMMITTED would be appropriate for this type of insert.

  • This indicates a text mix page which is new to me. The object id is the table. My issue may be more isolation level than the clustering as I previously thought. Thanks for pointing me in a different direction.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    PAGE: (1:1662692)

    BUFFER:

    BUF @0x03CC8A0C

    bpage = 0x5CC52000 bhash = 0x00000000 bpageno = (1:1662692)

    bdbid = 5 breferences = 3 bUse1 = 24899

    bstat = 0x4c00009 blog = 0x159a2159 bnext = 0x00000000

    PAGE HEADER:

    Page @0x5CC52000

    m_pageId = (1:1662692) m_headerVersion = 1 m_type = 3

    m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000

    m_objId (AllocUnitId.idObj) = 8934 m_indexId (AllocUnitId.idInd) = 256

    Metadata: AllocUnitId = 72057594623426560

    Metadata: PartitionId = 72057594510245888 Metadata: IndexId = 1

    Metadata: ObjectId = 1806785644 m_prevPage = (0:0) m_nextPage = (0:0)

    pminlen = 0 m_slotCnt = 38 m_freeCnt = 542

    m_freeData = 7840 m_reservedCnt = 0 m_lsn = (87842:11032:62)

    m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0

    m_tornBits = 338776153

    Allocation Status

    GAM (1:1533696) = ALLOCATED SGAM (1:1533697) = NOT ALLOCATED

    PFS (1:1658040) = 0x43 ALLOCATED 95_PCT_FULL DIFF (1:1533702) = NOT CHANGED

    ML (1:1533703) = NOT MIN_LOGGED

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply