 lbao
|
| Joined: 20 Aug 2004 |
| Total Posts: 2 |
| |
|
eXclusive locks for SELECT statements
Posted: 20 Aug 2007 12:10 PM |
Hi,
Is it possible for a select statement to obtain an exclusive lock? if so, why?
The question comes from the investigation of the following 1204 (deadlock) trace log:
Node:1
RID: 2:1:1734:0 CleanCnt:2 Mode: X Flags: 0x2
Owner:0x5155af80 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:64 ECID:0
SPID: 64 ECID: 0 Statement Type: SELECT Line #: 21
Input Buf: RPC Event: LONGTEST_TEST.dbo.ACCOUNT_GETDETAIL_BULK;1
ResType:LockOwner Stype:'OR' Mode: S SPID:136 ECID:0 Ec:(0x46D91558) Value:0x454d6b40 Cost:(0/B0)
Node:2
RID: 2:1:1734:1 CleanCnt:2 Mode: X Flags: 0x2
Owner:0x5e094720 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:136 ECID:0
SPID: 136 ECID: 0 Statement Type: SELECT Line #: 21
Input Buf: RPC Event: LONGTEST_STABLE.dbo.ACCOUNT_GETDETAIL_BULK;1
ResType:LockOwner Stype:'OR' Mode: S SPID:64 ECID:0 Ec:(0xBEFC1550) Value:0x8657a320 Cost:(0/B0)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:64 ECID:0 Ec:(0xBEFC1550) Value:0x8657a320 Cost:(0/B0)
------------------------
-- Background info --
------------------------
- Two databases firing the same stored procedure.
- The stored procedure contains one select statement.
- The select statement joins on a table in tempdb.
- No indexes exist on the tempdb table.
--------------------------
-- My understanding --
--------------------------
- SPID 64 has an exclusive lock on RID 2:1:1734:0 (temp table)
- SPID 136 has an exclusive lock on RID 2:1:1734:1 (temp table)
- Both want a S lock but deadlocked.
This comes back to the initial question, why does a select statement need an exclusive lock?
thanks,
Louie |
|
|
 |
|
urid
|
 |
| Joined: 18 Jun 2007 |
| Total Posts: 34 |
| |
|
Re: eXclusive locks for SELECT statements
Posted: 20 Aug 2007 02:33 PM |
Hi Louie
How much data do you return? What does execution plan say? What is ISOLATION LEVEL?
Where do you fill a temprary table or it is permanent table located in tempdb?
|
Best Regards,
Uri Dimant
SQL Server MVP
http://dimantdatabasesolutions.blogspot.com/
|
|
|
|
lbao
|
| Joined: 20 Aug 2004 |
| Total Posts: 2 |
| |
|
Re: eXclusive locks for SELECT statements
Posted: 23 Aug 2007 05:29 PM |
I have solved the issue.
Apparently, my understadning of the trace log was flawed.
The SELECT statement actually wanted a S lock. It was another statement (INSERT into tempdb table) which was holding the X lock.
The real picture was:
- SPID 64 was doing an insert into the tempdb table.
- SPID 136 was doing another insert into the tempdb table.
- SPID 64 then tried to read from the tempdb table (including rows inserted by SP 136).
- SPID 136 then tried to read from the tempdb table (including rows inserted by SP 64).
- Deadlock!
I am not 100% sure but I think table scans were responsible for the deadlock.
A clustered index on the tempdb table might have fixed the issue but WITH (NOLOCK) hint was the easiest solution in my case.
So I guess a SELECT statement doesn't need an eXclusive lock after all.
Thanks.
Louie |
|
|
|
|