|
eXclusive locks for SELECT statements
20/08/2007 12:10:22 PM
(Total replies: 1)
|
| 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 ... |
|
|
|
Re: eXclusive locks for SELECT statements
23/08/2007 5:29:58 PM
(Total replies: 0)
|
| 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 ... |
|