Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kenchee
Starting Member
49 Posts |
 Posted - 2008-04-01 : 02:34:46
|
| Hi, I have two process that is blocked. One is blocked on it's own (a select query status:runnable, open transaction 0, waiting type misc) and the other is blocked by the first one (delete query status: sleeping, open transaction 2, waiting type misc). I've tried killing the process but both of them will reappear after that. What can I do?thanks in advanceKen |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-04-01 : 03:02:03
|
| Put a trace on the database to see what they are doing.Run dbcc inputbuffer. |
 |
|
|
kenchee
Starting Member
49 Posts |
Posted - 2008-04-01 : 03:08:50
|
| I've done that. dbcc inputbuffer shows event type: language event, parameters: 0, eventinfo: select * from tablename |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-04-01 : 03:25:44
|
| Do you know where this is coming from and what user the process is running under?Is the tablename one of your user tables or is it a system table?If it is a select *, why is it holding locks? have you checked sp_lock? |
|
|
|
kenchee
Starting Member
49 Posts |
Posted - 2008-04-01 : 03:37:38
|
| the user is the owner of the table.this is why it's puzzling me, it's a select * but it's holding a lock.I've checked sp_lock but i don't really understand what it's outputinghere's an example of the outputspid = 53dbid = 5ojbid = 765961805indid = 3type = keyresource (06011c79ffa8)Mode = SStatus = GRANT |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-04-01 : 04:23:04
|
| Thats a shared lock, look for anything with an X in the mode column. |
|
|
|
kenchee
Starting Member
49 Posts |
Posted - 2008-04-01 : 17:41:26
|
| I've checked the mode column, there's now X but only S and IS |
|
|
|
kenchee
Starting Member
49 Posts |
Posted - 2008-04-01 : 19:59:34
|
| found out the reason whybelow is an extract from microsoft which is the exact issue that I was facing. Is there anything I can do on the sql end to resolve this? (besides killing the application)Client/Server Distributed Deadlock with a Thread per ConnectionEven if a separate thread exists for each connection on the client, a variation of this distributed deadlock may still occur as shown by the following.SPID1------blocked on lock-------->SPID2 /\ (waiting on net write) Server side | | | | | INSERT |SELECT | ================================|================================== | <-- thread per dbproc --> | Client side | \/ dbproc1 <-----data row------- dbproc2 (waiting on (blocked on dbproc1, waiting for it insert) to read the row from its buffer) This case is similar to Example A, except dbproc2 and SPID2 are running a SELECT statement with the intention of performing row-at-a-time processing and handing each row through a buffer to dbproc1 for an INSERT, UPDATE, or DELETE statement on the same table. Eventually, SPID1 (performing the INSERT, UPDATE, or DELETE) becomes blocked on a lock held by SPID2 (performing the SELECT). SPID2 writes a result row to the client dbproc2. Dbproc2 then tries to pass the row in a buffer to dbproc1, but finds dbproc1 is busy (it is blocked waiting on SPID1 to finish the current INSERT, which is blocked on SPID2). At this point, dbproc2 is blocked at the application layer by dbproc1 whose SPID (SPID1) is blocked at the database level by SPID2. Again, this results in a deadlock that SQL Server cannot detect or resolve because only one of the resources involved is a SQL Server resource. |
|
|
|
|
|
|
|
|
How to remove blocking