Locks from inactive spid????

Question

Post reply

Locks from inactive spid????

spaghettidba

SSC Guru

Points: 105732
More actions
August 6, 2008 at 3:15 am

#154590

This morning my users called me to report that all applications were hung.
I looked at the processes and I found lots of spids blocked by spid 150.
When I run sp_who2 150 it was AWAITING COMMAND and no transaction open.
sp_lock 150 reported lots and lots of objects with locks X and IX.
What could it be? Can a process hold locks when no transaction is open and no command is being performed?
Any help would be greatly appreciated
Regards
Gianluca
-- Gianluca Sartori

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

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

How did you determine there was no open transaction? You can be awaiting command with transactions open.

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

In Activity Monitor there's a column named "Open Transactions", that contained a 0 for my mysterious spid... I don't know where that information is extracted from, but I think I can rely on it.

-- Gianluca Sartori

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

Ok, that is a good check.

Did the spid have a cursor open?

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Well, I don't know if it had cursors open in that moment, all I know is that it opened 2 (small) cursors, but I checked and the deallocate instruction was in the right place in the procedure. The first thing that comes to my mind is to get rid of the cursors, but I still can't get any sense out of this.

I've been working for many years with different versions of SQLServer and I never ran into this kind of problem.

-- Gianluca Sartori

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

Were the cursors related to the locked records? That would be the first place to look. Is it an updatable cursor? If it is and does not need to be, perhaps just using the READONLY keyword will solve your issue (you will not get exclusive locks).

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

In fact the cursor was declared without any option, so I switched it to FAST_FORWARD, just to get a quick fix. I'll try to code it without cursors, also because there's no need for cursors in that context.

Thank you very much for your help, I'll see in the next days what's happening with these modifications.

Regards

Gianluca

-- Gianluca Sartori