Table is locked but can't find SPID to kill

Question

Table is locked but can't find SPID to kill

Mike01

SSChampion

Points: 11395
More actions
October 15, 2009 at 12:54 pm

#156322

I have some .Net developers that are opening a transaction to SQL, performing an insert/update and then killing the app in the middle. This is locking tha table. I ran dbcc opentran and it identified an open transaction but with a SPID that doesn't exist. It also shows a SPID with a letter attached to it (s). Below is the out put from the DBCC that I ran. The only way to delete the lock was to have them exit out of Visual Studio, but recently even that doesn't clear out the lock. Does anyone have an idea on how to find the SPID and delete the lock?
Transaction information for database 'Database1'.
Oldest active transaction:
SPID (server process ID): 74s
UID (user ID) : -1
Name : user_transaction
LSN : (800:830:1)
Start time : Oct 15 2009 2:19:34:390PM
SID : 0x60a478329b1a1349a3c8cea3bb229809
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

Just found out by stopping and starting DTC on developer's machine, freed the lock. But I should still be able to delete the lock through SSMS, no?

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 2

Here is an article that I believe explains your issue and details a way to kill the negative spids

http://www.sqlserverclub.com/articles/how-to-deal-with-negative-spids-in-sql-server.aspx

andrew

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Cool article.... thanks, Andrew.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

Oh yes... Distributed transactions ...

If the app is killed (client side), msdtc can get stuck and sqlserver will need a non-standard way to get rid of the pending lock(s).

the referred URL is a very good resource !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

mstjean Hall of Fame Points: 3310 More actions · Answer 5

I thought the OP was trying to resolve a spid = -1; I poked around online and the only references to -1 spid were dealing with SQL 6.5(!)

As far as clearing -2 spids, realize that just because you SEE a -2 in master..syslockinfo does not mean it's a problem. Normally these will have a short lifetime; they SHOULD normally clear within a few seconds (in my environment, anyway). The exact "lifespan" is probably wildly variable depending on the environment.

So to clear -2 spids, first determin how long they normally last in your world. Once you figure that out, run something similar to this (assumes normal lifetime of 5 seconds, so we run once, wait 10 seconds, and run again):

--char 38 is single quote

select 'kill ' + char(39) + cast(req_transactionUOW as varchar(50)) + char(39) as RunThis from master..syslockinfo where req_spid = -2

WAITFOR DELAY '00:10' -- wait some time period (10 sec here) will likely be different based on your environment

select 'kill ' + char(39) + cast(req_transactionUOW as varchar(50)) + char(39) as RunThis from master..syslockinfo where req_spid = -2

--if it's in both selects, kill it! (maybe!)

If you see a UOW in BOTH resultsets, it's possible that it needs to be killed. You could certainly store the results from both SELECTs into a temp table (add a datetime column to differentiate run1 and run2)-- and do a query to only show the matches, but if you catch the problem quickly, there usually aren't that many to deal with.

Also-- don't worry if you get a UOW that contains all 0's and it does not clear-- don't bother trying to kill it.

Cursors are useful if you don't know SQL

jbuttery Ten Centuries Points: 1226 More actions · Answer 6

Wouldn't a best practice be to free or kill the connection prior to app exit?