June 1, 2006 at 12:18 pm
Hi there,
I hope you're having a better day than me - I'm investigating a timeout and deadlock issue in a production database. At least it's not my fault!
I understand a deadlock well enough to create a contrived example here, in a test database.
I create two connections (SPIDs), and issue two update statements in session one (within an explicit transaction), and then reverse the order of the update statements in session two. I get error message:
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
The production issue is a mystery to me for several reasons.
One process is issuing insert/updates (within an explicit transaction), the other process is issuing selects (I don't whether it's within an implicit or explicit transaction). I can understand how a timeout can occur in this scenario, given the right row and timing. But I can't understand how a deadlock can occur in a update/select scenario.
Can an Update from one session, and a select from another session cause a deadlock?
I got the SQL Profiler trace file from the production database. I see several timeouts, before the deadlock.
Can multiple timeouts contribute to a deadlock?
I've noticed that the table involved in the timeouts and deadlocks has 357013 rows. I can't remember how I came to this information, and I'm about to confirm it, but I believe it has no indexes.
The updates apply a where clause, but because the table isn't indexed, the updates are taking considerable time.
Do you have any ideas?
Richard
June 1, 2006 at 12:23 pm
You've answered your own question: "because the table isn't indexed"
That's your culprit.
Minimally, you can run the select with no locks, assuming you don't care about a "dirty" read.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
for the proc
or
WITH (NOLOCK) on the select
But truly, you need proper indexing.
June 1, 2006 at 12:29 pm
First, add some indexes.
Second, a SELCT will create a SHARED lock on data that it has read until the SELECT is complete. This will block writers so it is possible to deadlock, especially in unindexed tables because table scans are required and all data must be looked at.
June 1, 2006 at 1:13 pm
The indexes will reduce the probability of a deadlock happening (by a huge factor), because they will speed up transactions so there is less chance of a temporal overlap, but also possibly because more fine-grained locks can be taken out. But a timeout never contributes to a deadlock - indeed it may help to avoid one (not in a very helpful way, admittedly). The deadlock occurs only because the order of taking out locks on a pair of objects is reversed between the two processes. As long as this is the case, there is always a possibility (which may be tiny for fast, sparse transactions) that each process will hold something the other needs to continue. You can only remove this possiblity altogether by changing the way at least one of the processes takes out locks.
In practice, I'm sure adding indexes to speed up your transactions and reduce locking will indeed solve the problem.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply