November 10, 2009 at 7:44 am
I have added the dead lock graph. I am not able to get from the following :-
<keylock hobtid="72057594640007168" dbid="6" objectname="CAMS.dbo.RequestStatus" indexname="IX_RequestStatus" id="lock75255200" mode="RangeX-X" associatedObjectId="72057594640007168">
<owner-list>
<owner id="process89bc48" mode="RangeX-X"/>
</owner-list>
<waiter-list>
<waiter id="process929888" mode="RangeI-N" requestType="wait"/>
</waiter-list>
</keylock>
This is what RangeX-X means.
RangeX-X : Exclusive range, exclusive resource lock; used when updating a key in a range.
It means that it will be imposed when data will be updated not on insert? please correct my understanding.
Also what I came to know is :-
Before key-range locking can occur, The transaction-isolation level must be set to SERIALIZABLE. In the code, we are not changing any isolation levels. From the dead lock graph, its appearing that key-range locks are being imposed but I am also not setting any transaction isolation levels to SERIALIZABLE. Then why the key-range locks are being imposed?
Any clue any one can provide me understanding the same correctly.
cheers
November 10, 2009 at 7:52 am
Are you using serialisable isolation level? If so, why?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 10, 2009 at 9:50 am
In the procs, we are not using any isolation level. We are using read committed which is there by default. That's why I am also confused.
cheers
November 10, 2009 at 1:34 pm
One of your processes is indeed using serialisable, at least according to the deadlock graph, and in a user-started transaction. If the proc's not setting it, maybe it's the ADO command?
(unneeded info removed)
<process id="process89bc48" waitresource="KEY: 6:72057595065991168 (5e018bcf9e6e)" ownerId="34919136" transactionname="user_transaction" lasttranstarted="2009-11-03T09:49:25.393" XDES="0x5e8f648" lockMode="RangeS-S" schedulerid="1" kpid="4004" status="suspended" spid="75" transcount="1" lastbatchstarted="2009-11-03T09:49:25.410" lastbatchcompleted="2009-11-03T09:49:25.393" clientapp=".Net SqlClient Data Provider" isolationlevel="serializable (4)" currentdb="6">
<executionStack>
<frame procname="CAMS.dbo.CreateMailDetails" line="232" stmtstart="24790" stmtend="25618" sqlhandle="0x03000600726e6132a94d1601259c00000100000000000000">
SELECT
@newLMName = ISNULL(FirstName,'')+' '+ISNULL(SurName,''),
@newLMEmailID = EmailID,
@newLMEIN = EIN
FROM dbo.ODSFeed WHERE EIN =
(
SELECT ApproverID FROM dbo.RequestStatus WHERE ID =
(
SELECT
MAX(ID)
FROM dbo.RequestStatus
WHERE ID <> ISNULL(@firstLMRequestStatusID,0) AND RequestID = @requestID AND NextStatusID IN (5,7)
)
) </frame>
<frame procname="CAMS.dbo.CreateRequestStatus" line="533" stmtstart="58134" stmtend="58256" sqlhandle="0x03000600016c0e391dc94b01b59c00000100000000000000">
EXEC dbo.CreateMailDetails @requestID,NULL,@remark,NULL; </frame>
<frame procname="CAMS.dbo.CreateRequest" line="84" stmtstart="7424" stmtend="7696" sqlhandle="0x030006001c95a82c6cca2001299b00000100000000000000">
EXEC dbo.CreateRequestStatus @requestID,@createdStatusID,@createdFor,@approverID,@createdEmailID,@approverEmailID,'request created'; </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 6 Object Id = 749245724] </inputbuf>
</process>
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 10, 2009 at 4:48 pm
Is the table concerned used in any indexed views?
The part of the plan that maintains indexed views runs at the SERIALIZABLE isolation level, regardless of the isolation level in effect at the time.
Just a guess - but I've come across it before, so thought it worth mentioning.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 11, 2009 at 3:46 am
There are one view which is base on RequestStatus table. I have check the code and its not being accessed in the procs which are there in dead lock graph. Index views are materialized so that why rangeS-S lock is being imposed? Please correct me if I am wrong. Any suggestions how to over come the same. Could you please also let me know, how to check RangeS-S locks are being imposed?
SELECT MAX(ID) FROM dbo.RequestStatus
WHERE RequestID = @requestID AND (StatusID = 10 OR NextStatusID = 10)
The above sql was using two index to get the results. I have added one more index as so that I can avoid hitting index IX_RequestStatus_RequestID and which worked after the new index was introduced.
CREATE NONCLUSTERED INDEX [IX_RequestStatus_RequestID_ID_StatusID_NextStatusID_ApproverID] ON [dbo].[RequestStatus]
(
[RequestID] ASC,
[ID] ASC,
[StatusID] ASC,
[NextStatusID] ASC,
[ApproverID] ASC
)
The same might help. Data base engine tuning advisor also suggested to add this index.
Also I need to understand more on internal working of data base engine, which is the best book to refer. While trouble shooting the deadlcoks, came across so many things which I am not able to find in MSDN.
Also any link for understanding dead lock graph in and out?
cheers
November 11, 2009 at 5:25 am
I would check first that the procedure named wasn't being run explicitly at the SERIALIZABLE isolation level.
Having looked at the graph (I didn't have time earlier) it certainly seems like a user, not indexed view maintenance.
I don't want to confuse the issue by going into it now, but I'd be happy to chat about why range locks are necessary when an indexed view is updated later on.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 11, 2009 at 5:51 am
siddartha pal (11/11/2009)
SELECT MAX(ID) FROM dbo.RequestStatusWHERE RequestID = @requestID AND (StatusID = 10 OR NextStatusID = 10)
Just to double check, dbo.RequestStatus is a table?
Does this deadlock happen often? Can you intentionally cause it? If so, try running profiler, see what the connection settings are and see if there's anything explicitly setting the isolation level.
Also I need to understand more on internal working of data base engine, which is the best book to refer. While trouble shooting the deadlcoks, came across so many things which I am not able to find in MSDN.
Inside SQL Server 2005: The Storage Engine is good, as is SQL Server 2008 internals. Both written primarily by Kalen Delaney.
Also any link for understanding dead lock graph in and out?
Shameless plug. http://www.sqlservermvpdeepdives.com/ There's a chapter in there on deadlock graphs. Lots of other useful and interesting stuff too. Don't know any online articles offhand. Ask google.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2009 at 7:35 am
Yes, dbo.RequestStatus is a table.
Yes, we can repordue the same. We are not setting any isolation level in code through procs.
Thanks alot for the link and name of the book.
cheers
November 11, 2009 at 7:44 am
Yes, we are not changing any isolation level in code.
Could you please share your experience on range locks and on views, how the same works?
November 11, 2009 at 7:57 am
siddartha pal (11/11/2009)
Yes, we are not changing any isolation level in code.
Well something is setting the isolation level to serialisable. I think you need to find what it is. Check the ADO.net connection, make sure it's not getting set from there. A trace will pick up if there is some proc that shouldn't have an isolation level change but does.
As for the locks, see Conor's blog post (on cascading update/delete and serialisable)
http://blogs.msdn.com/conor_cunningham_msft/archive/2009/03/13/conor-vs-isolation-level-upgrade-on-update-delete-cascading-ri.aspx (He hasn't yet gotten around to actually writing the promised post on indexed views
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2009 at 8:32 am
Hi Gail
Could you please let me know how to check connection setting in profiler?
One more thing, I have checked in dead lock graph, I can see isolationlevel="serializable (4)" in process node. This is amazing. what is causing the same to happen?
cheers
November 11, 2009 at 8:53 am
I have just conducted the test on dead locks and came to know that out of 2 issues, one is sorted.
Also when i submitted the trace file to Data base engine tunning advisor, I get recommendations for creating index. Should I go and create all these recommended indexes?
cheers
November 11, 2009 at 9:04 am
siddartha pal (11/11/2009)
Could you please let me know how to check connection setting in profiler?
The audit login event. The textdata column will give the isolation level (as well as a whole lot of other settings)
One more thing, I have checked in dead lock graph, I can see isolationlevel="serializable (4)" in process node. This is amazing. what is causing the same to happen?
Yes, I know, I said so several posts up. That's why I posted the section of the deadlock graph with the isolationlevel attribute bolded.
Something, somewhere is setting the isolation level. That's why I'm suggesting you investigate what's setting it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2009 at 9:08 am
siddartha pal (11/11/2009)
Should I go and create all these recommended indexes?
Test the suggestions carefully first. If they help, create them, if they don't then don't create them
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply