November 18, 2010 at 2:45 am
HI,
I am running the following Select on production server, using Sql Server Management Studio.
SELECT A.*,
B.*,
C.*
FROM FRAUDASSESSMENT A
JOIN PROFILE B ON B.ID = A.PROFILE
JOIN RESERVATIONDETAILS C ON C.BOOKINGCODE = A.BOOKINGCODE
JOIN PAYMENTATTEMPT D ON D.RESERVATION_ID = C.BOOKINGCODE
My Transaction isolation level is "read committed", the default for database engine.
Why does this select fail with the following error:
"Transaction (Process ID 218) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
Since the level is "read committed" and according to my knowledge, it should never fail. It should only bring back the commited rows.
Panayotis
November 18, 2010 at 3:07 am
Is it part of a transaction? Are any of those objects views?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2010 at 3:25 am
No. It is not part of a transaction. I just log in with Management Studio and I run this SQL command. No. These are not views. They are tables.
Of course, this is a database of a production system. Other users use it at the same time. Other applications (WEB) are using it as the same time.
But, according to my knowledge, this should be irrelevant, since the transaction isolation level is "read committed".
November 18, 2010 at 3:32 am
Isolation level's not that relevant. The locks taken are. A single select alone should not be able to participate in a deadlock, regardless of isolation level, unless there are odd things occurring on the other side of the deadlock.
Does this happen consistently? Is it repeatable? Deadlocks involve at least 2 processes and without knowing what both are doing, it's hard to say what's wrong.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2010 at 3:47 am
Maybe you are right that I have to see the other side too.
However, what is your opinion about a Select failing with transaction deadlock error? What might be the possible roots of error?
I suspect that other processes exclusively lock the data that I am trying to read, not even allowing "read committed" session.
Do you agree?
November 18, 2010 at 3:54 am
matsinopoulos (11/18/2010)
However, what is your opinion about a Select failing with transaction deadlock error? What might be the possible roots of error?
No idea. Would need to see the deadlock graph.
I suspect that other processes exclusively lock the data that I am trying to read, not even allowing "read committed" session.
Do you agree?
No. That would cause blocking, it should not cause deadlocks. For a deadlock you need minimum 2 processes, minimum two sets of locks taken in sequence so that each process acquires one set of locks and not the other. Very hard for a single select to do that. Possible, but far from common.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2010 at 4:07 am
Which tool shall I use to see the deadlock graph?
November 18, 2010 at 4:13 am
Profiler (deadlock graph event) or turn traceflag 1222 on and it will be written to the error log. Neither is historical. Hence why I asked if it was reproducible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2010 at 4:32 am
It is reproducible. I will try your suggestion.
November 18, 2010 at 4:41 am
why should SELECT be immune from deadlocks?
even a SELECT from a single table can get deadlocked due to multiple indexes involved
(if index access is in different order)
two connections issuing "select [field] from
where [otherfield] = blah"
connection 1 - get lock on index A
connection 2 - get lock on index B
connection 1 - waits for lock in index B
connection 2 - deadlocks on lock for index A
November 18, 2010 at 5:07 am
DataDog (11/18/2010)
connection 1 - get lock on index Aconnection 2 - get lock on index B
connection 1 - waits for lock in index B
connection 2 - deadlocks on lock for index A
Wouldn't it be a shared lock as they are only getting read. shared locks can happen between multiple processes.
November 18, 2010 at 5:55 am
DataDog (11/18/2010)
two connections issuing "select [field] from
where [otherfield] = blah"
connection 1 - get lock on index A
connection 2 - get lock on index B
connection 1 - waits for lock in index B
connection 2 - deadlocks on lock for index A
Firstly there's likely only one lock involved in that. Unless you're getting a nonclustered index seek and a key lookup. If that is the case, both would take the lock on the noncluster first and the lock on the cluster second, so no possibility for deadlock.
Second, selects take shared locks. Shared locks do not block other shared locks. Two selects will not block each other unless someone's put an XLock hint into one of them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply