September 9, 2013 at 9:25 am
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?
September 9, 2013 at 9:35 am
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.
September 9, 2013 at 10:11 am
The index page is showing on the deadlock graph which led to my assumption.
September 10, 2013 at 6:55 am
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
Consultant - Straight Path Solutions
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
September 10, 2013 at 8:42 am
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
September 10, 2013 at 8:48 am
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)
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
September 10, 2013 at 9:13 am
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
Consultant - Straight Path Solutions
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
September 10, 2013 at 9:29 am
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
September 10, 2013 at 9:46 am
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
September 10, 2013 at 9:51 am
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
Consultant - Straight Path Solutions
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
September 10, 2013 at 11:13 am
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