asked
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?
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.
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.
19 People are following this question.
