Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Deadlock involving Identity Column Expand / Collapse
Author
Message
Posted Monday, September 09, 2013 9:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 2:57 PM
Points: 7, Visits: 147
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?
Post #1492835
Posted Monday, September 09, 2013 9:35 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 3,081, Visits: 11,230
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.


Post #1492837
Posted Monday, September 09, 2013 10:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 2:57 PM
Points: 7, Visits: 147
The index page is showing on the deadlock graph which led to my assumption.
Post #1492855
Posted Tuesday, September 10, 2013 6:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:24 AM
Points: 10,907, Visits: 12,540
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.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1493148
Posted Tuesday, September 10, 2013 8:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 2:57 PM
Points: 7, Visits: 147
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 TaskProxy0x1BD48374) Value:0x1fb6a860 Cost0/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 TaskProxy0x0C814374) Value:0x3e016160 Cost0/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 TaskProxy0x1BD48374) Value:0x1fb6a860 Cost0/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
Post #1493220
Posted Tuesday, September 10, 2013 8:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:41 AM
Points: 6,748, Visits: 12,843
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1493225
Posted Tuesday, September 10, 2013 9:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:24 AM
Points: 10,907, Visits: 12,540
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.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1493248
Posted Tuesday, September 10, 2013 9:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 2:57 PM
Points: 7, Visits: 147
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
Post #1493257
Posted Tuesday, September 10, 2013 9:46 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 4:17 PM
Points: 439, Visits: 1,094
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
Post #1493266
Posted Tuesday, September 10, 2013 9:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:24 AM
Points: 10,907, Visits: 12,540
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.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1493269
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse