Stored Proc blocking forever with transactions / TABLOCKX

[dave rivard]

Hi all,

I am having a problem with a stored procedure blocking indefinitely without
causing a deadlock and also blocking other procs from executing. I have
recreated the problem in its essence in Query Analyzer. After reading the
"Inside SQL Server 7.0" section on deadlocks and locking 3 times, I am still
at a loss to explain why this is happening. The problem is this.

Create Procedure GetFoo
as
BEGIN TRANSACTION
declare @c int
select @c = count(*) from foo(TABLOCKX) -- Table lock exclusive.
COMMIT TRANSACTION

Now open up 2 windows with query analyzer.
Window 1:
-----------
while 1 = 1
begin
exec GetFoo
end

Window 2:
-----------
while 1 = 1
begin
select * from foo
end

Run both windows and you will see that there is no blocking going on. Hit
the STOP/Cancel query button on window 1. Then run it again. It will run a
little bit, then both are blocked forever.

stop and start window 2 and it will just hang. The only way to get a clean
start is to close both windows and open up new ones.

Another nifty wrinkle is if you copy-n-paste the code in GetFoo into the
loop in QueryAnalyzer, it does NOT hang.

I am using Query Analyzer to recreate the problems I am seeing in my product
in the field. Is my lack of understanding in
a) Locks
b) weirdness of how Query Analyzer does things

For locks, it would think that doing a TABLOCKX at the beginning of a
transaction would guarantee that you will not have any deadlocking issues.
If the other process is only doing a select, it should block just until the
select is complete, then grant the TABLOCKX to the other process.

Can someone please help me out by explaining how this can happen? Im very
frustrated.
Thanks in advance,
dave.

[Kalen Delaney]

First of all, you're right that the TABLOCK should prevent a deadlock, and
indeed, you're not getting a deadlock. SQL Server will detect a deadlock and
cancel one of the processes if a true deadlock occurs, and this is not
happening. Your second process is just being blocked by your first. So
forget about deadlocks for now, but you should have read in Inside SQL
Server that they will be automatically detected, and reported.

I didn't get the same behavior. I got blocking as soon as I started the two
windows running. The second one was blocked on the first right from the get
go, and returned no data, ever.

I then ran the same two scripts on SQL 2000 and had no problems whatsoever
at first, but then was able to duplicate your problem. Since you didn't say
what version you were running, but you mentioned the 7.0 book, I assumed you
were running this on SQL 7.

Something is strange here. The second script should be able to get some work
done.
I'll run some more tests and see if I can figure anything out.

--
Kalen Delaney, MVP, MCSE, MCT
www.InsideSQLServer.com

Feed someone for free today!
www.thehungersite.com
------------------------------------------------

"dave rivard" <dri...@wirelessknowledge.com> wrote in message
news:eV1Yr7jkAHA.460@tkmsftngp04...

[Erland Sommarskog]

Kalen Delaney (kalen_delaney@hotmail_please_respond_on_newsgroups.com)
writes:

>First of all, you're right that the TABLOCK should prevent a deadlock, and
>indeed, you're not getting a deadlock. SQL Server will detect a deadlock and
>cancel one of the processes if a true deadlock occurs, and this is not
>happening. Your second process is just being blocked by your first. So
>forget about deadlocks for now, but you should have read in Inside SQL
>Server that they will be automatically detected, and reported.

That's the theory. Those of us who have read fixlists for service
pack have every once in a while seen the expression "Undetected
deadlock".

>I then ran the same two scripts on SQL 2000 and had no problems whatsoever
>at first, but then was able to duplicate your problem. Since you didn't say
>what version you were running, but you mentioned the 7.0 book, I assumed you
>were running this on SQL 7.

I also tried the script on SQL2000, and I'm not really sure whether
I to replicate the problem because the script in window 1 never
outputted anything.

When I spied on the processes with my aba_lockinfo, there didn't seem
to be any deadlock, but one of the processes were waiting on the other.
Then again, you wouldn't expect an undetected deadlock to show up
that easily.

What I did see though, was that cancelling the query with the procedure,
did not roll back the transaction that the procedure had started. Giving
a ROLLBACK TRANSACTION in that window would bring the second window into
life.

--
Erland Sommarskog, Stockholm, som...@algonet.se

[Kalen Delaney]

You can tell that it's not a deadlock, and that the script in Window 1 is
not dead by looking at sysprocesses periodically and seeing the CPU activity
is continuing.

I am researching this problem.

--
Kalen Delaney, MVP, MCSE, MCT
www.InsideSQLServer.com

Feed someone for free today!
www.thehungersite.com
------------------------------------------------

"Erland Sommarskog" <som...@algonet.se> wrote in message
news:Xns9045F4A3...@127.0.0.1...

[dave rivard]

Hi Kalen,

Have you had any luck with this? I replaced TABLOCKX with UPDLOCK on the
tables. This prevents the SQL server hang problem, but isnt quite the
locking behavior im looking for.

Regards,
Dave.

"Kalen Delaney" <kalen_delaney@hotmail_please_respond_on_newsgroups.com>
wrote in message news:OuMRzx#kAHA.2112@tkmsftngp05...