SQL 2008 deadlock with TABLOCKX and HOLDLOCK

Hi All,

I have procedure running in SQL 2008 with TABLOCKX and HOLDLOCK with READ uncommitted isolation level. While running the proc sometimes transaction fails with deadlock.

And from the deadlock trace, looks both processes are running the same SP with select stmt.

SELECT abcd FROM abcd1234 WITH (tablockx holdlock)

Can any one please explain if lock partition help here?

more ▼

asked Jul 31, 2015 at 12:06 AM in Default

avatar image

1 1 1 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I doubt very much that there's a single SQL command in this stored procedure. It's probably a series of different INSERT/UPDATE/DELETE statements with some SELECT statements mixed in and all of it controlled through some IF/THEN logic, right?

What's going on is that different calls to the procedure are taking different paths and holding different locks on various tables or rows and arriving at the point where they need to take the lock on the table in question. But when more than one process is holding locks on tables where the other process also needs locks on those tables you arrive at a deadlock.

You have to evaluate the code to determine where the locks are being held. Using lots of locking hints as you've been doing with the NOLOCK and TABLOCKX and HOLDLOCK suggests that you've been having issues with this code and you're attempting to fix it by avoiding rewriting and restructuring it. But, it sounds like you need to restructure it.

Even with a NOLOCK hint, some locks are always taken out, especially if you're doing INSERT/UPDATE/DELETE. Those are unavoidable. So you have to ensure that your query has a single path through to do the work that you're attempting. If it has multiple access paths to the data, you're likely to hit deadlocks.

Also, deadlocks are actually, at their core, a performance issue. So you also have to make sure that the queries are tuned to run as fast as possible. That's something else to look at.

You may see deadlocks in some situations where you have parallel execution. In this instance you'll note that the process ID is the same in both the deadlock victim and the successful process. That's primarily about tuning the query in that case.

more ▼

answered Jul 31, 2015 at 10:24 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

Just to add to one of Grant's points, if you have multiple processes working against the same set of tables, it helps to keep the order the same. In other words, if procedure A reads from table X1 and then updates table X2, try to avoid creating procedure B that reads table X2 and then updates table X1. Sometimes, it's unavoidable, but at least try to avoid doing both with stricter isolation levels. In the end, it comes down to Grant's comment on performance. The faster each process is, the less likely it is to cause a deadlock.

Jul 31, 2015 at 02:49 PM Tom Staab ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 31, 2015 at 12:06 AM

Seen: 519 times

Last Updated: Jul 31, 2015 at 02:49 PM

Copyright 2018 Redgate Software. Privacy Policy