Killing a system lock? - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Killing a system lock?

Author

Topic

Ruskin
Starting Member

13 Posts

Posted - 2002-09-06 : 11:34:00

I went and did a silly thing, I wrote a TRAN into a stored proc, but didn't do any error checking and ROLLBACKS. Well my SP had an error so the TRAN never got committed or rolled back, now there's a lock on the SP so I can't ALTER/DROP it. This was a web app calling the SP so I can't kill it like you can kill a user spid.

any ideas? Thanks so much.

chadmat
The Chadinator

1974 Posts

Posted - 2002-09-06 : 13:15:00

Restart SQL. That should clear the lock.

-Chad

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-09-06 : 13:18:06

Why can't you kill the spid? Even if it's a web app that was running the sproc, it still has to login as a SQL User.

Otherwise, won't the app timeout eventually?

Ruskin
Starting Member

13 Posts

Posted - 2002-09-06 : 14:03:43

AjarnMark - When I run the KILL I get

"Only user processes can be killed."

chadmat - not an option

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-09-06 : 14:32:37

No offence but if this got onto your production server (which is why I assume you don't want to stop and start SQL service) then you need to review your development and QA cycle as to how it got into production. How are you identifying the spid to kill ? It sounds like you are trying to kill a system spid which you can't do - why do you think you can't kill the user spid - it sounds more like you are picking the wrong one. Unless the spid has spawned a thread calling an xp or external process outside of SQL's control you can kill it. Killing the spid will automatically rollback the outstanding transaction(s).

HTH
Jasper Smith

Ruskin
Starting Member

13 Posts

Posted - 2002-09-06 : 14:42:58

heh, if it eases your mind a tad, this is a test database on a production server, I am not afforded a FULL testing environment due to versioning, just weird stuff.

OK Back to specifics:
The application is an ASP.NET application calling the stored Proc. The proc runs 2 update commands then commits the tran. The error happened in the first update command due to a typo so when it tried to do "UPDATE <tabelname>" <tabelname> was not a valid object so it errored.

I found the spid that had the lock on the DB and more specifically it had the lock on the SP. It was the only process listed with an open TRAN and when I ran an ALTER command on the SP the spid reported that it was blocking. So, I know I had the right spid. Now, I say "had," because eventually (a few hours later) it let go.

So, prevention... in cases like that (misspelled object name), can I get away with "IF @@error <> 0" at the END of the TRAN or will it error out on the first query and lock me up again?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-09-06 : 16:05:35

The @@ERROR variable only holds the status of the most recently completed command, so you need to check it after every command that you want to know about.

And if you're going to do anything else with the value, like RAISE it, you need to store it in another variable first because the IF command will read and then reset the value of @@ERROR.

Ruskin
Starting Member

13 Posts

Posted - 2002-09-06 : 16:12:16

OH I don't care about losing the @@ERROR value, I can debug pretty easily. What I was just wondering is if I can just put one check in at the end so the TRAN doesn't get locked if an error occurs on the 1st query.

I've read all those threads about SELECT @error = @@ERROR, blah blah after EVERY statement, but I'm not really trapping errors, I would just like to run the TRAN and check at the end if there were any probs and ROLLBACK then, but if Query 1 errors will I ever reach the ROLLBACK?


CREATE PROCEDURE spMyProc
AS
BEGIN TRAN myTran
  UPDATE Tabel -- invalid object name
  SET Col1 = 2
  WHERE ID = 2

  UPDATE Table
  SET Col2 = 4
  WHERE ID = 2

  IF @@ERROR <> 0
    BEGIN
      ROLLBACK TRAN myTran
      RETURN
    END
COMMIT TRAN myTran

izaltsman
A custom title

1139 Posts

Posted - 2002-09-06 : 16:44:08

The problem is that every command you run overwrites the value of @@Error. So the way you have it written, if the first one bombs, but the second succeeds, @@Error will be set to 0 and you will not get to rollback. So do as Mark says, and check @@Error after each command (or store it in a local variable).

Edited by - izaltsman on 09/06/2002 16:44:48

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-09-06 : 16:50:17

@@ERROR will not help you at all in the case of an invalid object error.Unfortunately the handling of objects not found is pretty crap - not to put too fine a point on it. SQL will terminate the batch and not execute the following statements and not rollback the transaction e.g. if you run all the below in Query Analyzer as one batch

begin tran
select @@trancount --1

update khhkjhkj
set sdsd=2 --error terminates batch

print 'after' -- not executed

select @@trancount --now run this - its still 1

rollback tran --now run this to rollback

As you can tell, since no statements can be run after the error, you can't use @@ERROR and issue a rollback tran - it won't get that far
What you can do is trap the error on the client and execute "IF @@TRANCOUNT>0 ROLLBACK TRAN" for this specific error or better yet avoid it in the first place

HTH
Jasper Smith

Edited by - jasper_smith on 09/06/2002 16:58:04

Ruskin
Starting Member

13 Posts

Posted - 2002-09-06 : 17:03:51

bah, Jasper, wish I'd seen your post, I ran a test with an invalid object name and @@error checking and BOOM, nothing, and I'm locked again. Except this time it locked the Table and not the SP and isn't causing any blocking.

And by the time I was done writing this, the lock went away.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources