5

I have a table: OrderDetails.

For 5 years, only the odd Deadlock has occured. If it happened, it happened on indexes which was on OrderDetails table Now all of a sudden, deadlocks are everywhere - happens very minute. We have not made any changes to the application or database - the indexes rebuild every week.

The problem is that the table OrderDetails gets locked on occasion now and all hell breaks loose

Could someone please help and see if they spot anything from the Logs: How can I get rid of the objectLock on the whole OrderDetails Table? Why would the table lock -in the past it was only the Indexes that caused deadlocks. Does this mean that the Indexes failed to find the appropriate records and now the whole table is locked?

It is an Update and a Select that are deadlocking here, and the rows that are locking are not related - they don't have the same Reference number or OrderID

 spid18s,Unknown,waiter id=process91bb58 mode=X requestType=convert
 spid18s,Unknown,waiter id=process9a9018 mode=S requestType=convert
 spid18s,Unknown,waiter-list
 spid18s,Unknown,owner id=process9a9018 mode=IS
 spid18s,Unknown,owner id=process91bb58 mode=IX
 spid18s,Unknown,owner-list
 spid18s,Unknown,objectlock lockPartition=0 objid=1194487334 subresource=FULL dbid=5 objectname=CompZ.dbo.OrderDetails id=lock32163640 mode=IX associatedObjectId=1194487334
 spid18s,Unknown,resource-list
 spid18s,Unknown,Proc [Database Id = 5 Object Id = 711009614]
 spid18s,Unknown,inputbuf
 spid18s,Unknown,OrderNoRef = @OrderNoRef and ClientID = @ClientID and  OrderNo <> @OrderNo
 spid18s,Unknown,where
 spid18s,Unknown,select  Distinct OrderNoRef from OrderDetails
 spid18s,Unknown,frame procname=CompZ.dbo.SP_Duplicate_Ref line=12 stmtstart=530 sqlhandle=0x030005004e25612a5f3d2a0194a100000100000000000000
 spid18s,Unknown,executionStack
 spid18s,Unknown,process id=process9a9018 taskpriority=0 logused=0 waitresource=OBJECT: 5:1194487334:0  waittime=8640 ownerId=17099323 transactionname=SELECT lasttranstarted=2013-06-11T14:34:34.230 XDES=0x2567fb58 lockMode=S schedulerid=2 kpid=15548 status=suspended spid=76 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2013-06-11T14:34:26.570 lastbatchcompleted=2013-06-11T14:34:26.570 clientapp=.Net SqlClient Data Provider hostname=WEBSVR hostpid=16664 loginname=*** isolationlevel=read committed (2) xactid=17099323 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
 spid18s,Unknown,Proc [Database Id = 5 Object Id = 562101043]
 spid18s,Unknown,inputbuf
 spid18s,Unknown,Update OrderDetails set OrderState = 15 where OrderNo = @OrderNo And OrderState in (1,2,3)
 spid18s,Unknown,frame procname=CompZ.dbo.SP_Insert_OrderDet line=59 stmtstart=1726 stmtend=1890 sqlhandle=0x0300050033fb8021a4e1360011a100000100000000000000
 spid18s,Unknown,executionStack
 spid18s,Unknown,process id=process91bb58 taskpriority=0 logused=0 waitresource=OBJECT: 5:1194487334:0  waittime=8609 ownerId=17099322 transactionname=UPDATE lasttranstarted=2013-06-11T14:34:34.230 XDES=0x3a2844e0 lockMode=X schedulerid=1 kpid=23896 status=suspended spid=63 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2013-06-11T14:34:25.347 lastbatchcompleted=2013-06-11T14:34:25.327 clientapp=.Net SqlClient Data Provider hostname=WEBSVR hostpid=16664 loginname=*** isolationlevel=read committed (2) xactid=17099322 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
 spid18s,Unknown,process-list
 spid18s,Unknown,deadlock victim=process9a9018
 spid18s,Unknown,deadlock-list

One of the Indexes had the page and row locks removed (was not documented so I did not know of this - I apologize for stating that nothing changed in the title)- is it correct to say that if both the row and page locks for a Index is disabled that it would then lock the table which uses that index? (Yes, if row and page locks are turned off, then only the table lock is available.)

Improve this question
13
  • 1
    There's a few things you can check - I would start by running profiler to capture the deadlock XML. It could be a hint being used in whatever is connecting to the DB. If a TABLOCK is on a query, that would cause it. It could also be row/page locks not allowed on the clustered index or another index on the table.
    – JNK
    Jun 11, 2013 at 14:53
  • 1
    What's the schema of the table? Is that the only table that's having this problem? Are any traceflags active on the server?
    – Thomas Rushton
    Jun 11, 2013 at 16:06
  • 1
    sounds like lock escalation..... (blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/…)
    – StrayCatDBA
    Jun 11, 2013 at 16:30
  • 1
    You should capture the deadlock graph and post the information here this will help us trouble shoot the problem for you. msdn.microsoft.com/en-us/library/ms188246.aspx
    – Zane
    Jun 11, 2013 at 16:42
  • 3
    Please post the .xdl file.
    – Aaron Bertrand
    Jun 11, 2013 at 17:00

1 Answer 1

Reset to default
1

Use SQL Profiler to trace your deadlocks and find a few example queries that are the root cause.

Then copy that SQL to SSMS and view the execution plan for it.

Look to see what kind of locks are being held when the query runs.

It's almost certain that you are experiencing table locks - actually, as you state, someone removed row-level locks and page-level locks.

You have options:

  1. Modify your SELECT statement to use READ UNCOMMITTED or (nolock). This means this query can read dirty data. Only you will know whether this will be acceptable with your data set - very often, it is fine to have dirty reads and doesn't impact the functionality of the system.

  2. Use row-level locks. You can force these using a query hint.

  3. Use row-versioning - this option means the updates operate on a different version of the rows to the ones that the SELECT statement is reading from avoiding deadlocks altogether.

You can find out more details about row versioning and other deadlock reducing techniques here.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.