Solved

After 5 years of working, SQL Server 2k5 Table now locking without any changes made to DB

Posted on 2013-06-11
16
371 Views
Last Modified: 2016-02-11
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 lock 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

Open in new window

0
Comment
Question by:jxharding
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
  • +1
16 Comments
 
LVL 21

Expert Comment

by:oleggold
ID: 39237955
what could be happening, if no other changes that the db file /log file reached it limit or no free space on disk - easy to check,just look on free space on the server
0
 
LVL 21

Expert Comment

by:oleggold
ID: 39237960
0
 
LVL 21

Expert Comment

by:oleggold
ID: 39237964
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21

Expert Comment

by:oleggold
ID: 39237966
0
 
LVL 21

Expert Comment

by:oleggold
ID: 39237967
0
 
LVL 21

Expert Comment

by:oleggold
ID: 39237970
0
 
LVL 21

Expert Comment

by:oleggold
ID: 39237974
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39238573
Running this should help...

ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39238711
First, verify that "OrderState" is numeric -- if that column is not numeric, change your code to reflect that, viz.:

-- make these changes if and only if OrderState is a [var]char data type
Update OrderDetails set OrderState = '15' where OrderNo = @OrderNo And OrderState in ('1','2','3')


Then, until you can investigate more deeply, create nonclustered covering indexes to help with the statements involved:


CREATE NONCLUSTERED INDEX OrderDetails__IX_OrderNoRef ON dbo.OrderDetails ( OrderNoRef ) INCLUDE ( ClientID, OrderNo ) WITH ( ONLINE = ON, SORT_IN_TEMPDB = ON ) --remove "ONLINE = ON" if not avail., either because not on Enterprise Ed. or bcz table contains xml, etc.

CREATE NONCLUSTERED INDEX OrderDetails__IX_OrderNo ON dbo.OrderDetails ( OrderNo, OrderState ) WITH ( ONLINE = ON, SORT_IN_TEMPDB = ON ) --remove "ONLINE = ON" if not avail., either because not on Enterprise Ed. or bcz table contains xml, etc.
0
 

Author Comment

by:jxharding
ID: 39238738
Thank you, looking at all options
Can verify that the OrderState is numeric - thank you @ScottPletcher
Also looking at the indexes recommended

@Racimo - investigating snapshot isolation thank you - are there big companies using Snapshot Isolation?

@Oleggold - thanks, went through all the articles, more than 100gigs open on disk, not using SSIS at this point

Thanks again!
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39238794
Be aware that activating SI (snapshot isolation) initially could result in a BIG slowdown of response time for the first subsequent update to a row/page and possibly even the first queries.
0
 

Author Comment

by:jxharding
ID: 39243936
Hi, found out what it was,
One of the Indexes had the page and row locks removed (was not documented so I did not know of this - I apologize profusely 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?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39244004
investigating snapshot isolation thank you - are there big companies using Snapshot Isolation?
Yes.  Sometime, people simply don't want to bother with these issues and can accept the overhead on TEMPDB.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39244824
>> 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? <<

I believe so; I think SQL honors those settings, and thus the only locking left is table level.


>> are there big companies using Snapshot Isolation?
Yes.  Sometime, people simply don't want to bother with these issues and can accept the overhead on TEMPDB. <<

The initial concern with SI is not tempdb, it's the source database tables themselves.  Adding SI to them can sometimes cause massive page splits and, therefore, horrible performance on the original tables.  Often people don't realize the change that SI forces on the base table.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39244855
Row versioning overhead makes SI to be used with caution but it does not put major problems on below 50GB db's  as long as the infrastructure can keep up.  In other words, in most applications, it is a non issue when the storage can keep up in terms of throughput and IOPS.
0
 

Author Closing Comment

by:jxharding
ID: 39245865
All great help, I thank everyone for the time and guidance!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question