Prev: Pass table name to stored procedure?
Next: Code versus Int
From: Darian Miller on 12 May 2010 16:12
I've been using a combination of sp_getapplock and sp_releaseapplock for some
custom application locking purposes for years now with success and just the
last few days I've started to get some timeout expired errors on a
sp_releaseapplock which is a bit puzzling. When listing the current locks,
there's less than a dozen of these active, and the rest of the dedicated
server is way underutilized at the moment (less than 100 batches/sec with a
mutli-processor, 32GB Ram, higher end machine.)

Is there a specific resource to be monitored that may point me in the right
direction for determing why such a lightweight operation is timing out? This
is called within a stored proc with a timeout of 120 seconds which seems to
be amazingly long for this operation to timeout on.

SQL 2000 SP4 running on Windows 2003 Server.

TSQL used (@pLockUniqueName is VarChar(255))

EXEC @pLockSuccess = sp_getapplock @pLockUniqueName, 'Exclusive', 'Session', 0
EXEC @pUnLockSuccess = sp_releaseapplock @pLockUniqueName, 'Session'

Thanks,

Darian
From: Erland Sommarskog on 12 May 2010 17:55
Darian Miller (DarianMiller(a)discussions.microsoft.com) writes:
> I've been using a combination of sp_getapplock and sp_releaseapplock for
> some custom application locking purposes for years now with success and
> just the last few days I've started to get some timeout expired errors
> on a sp_releaseapplock which is a bit puzzling. When listing the
> current locks, there's less than a dozen of these active, and the rest
> of the dedicated server is way underutilized at the moment (less than
> 100 batches/sec with a mutli-processor, 32GB Ram, higher end machine.)
>
> Is there a specific resource to be monitored that may point me in the
> right direction for determing why such a lightweight operation is timing
> out? This is called within a stored proc with a timeout of 120 seconds
> which seems to be amazingly long for this operation to timeout on.

sp_releaseapplock does not have any timeout parameter as far as I can
see. So where does the timeout come from? Does the client that calls
the procedure have a query timeout of 120 seconds?

How do you determine that the procedure is stalled on sp_releaseapplock?
Or do you call sp_releaseapplock directly from the client?

If sp_releaseapplock is really not returning within 120 seconds, something
is really fishy. I would suspect that there is some internal corruption
in internal locking structures. I would check the SQL Server errorlog for
interesting messages. But most of all, I would try to find an occasion
to restart SQL Server.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Darian Miller on 12 May 2010 18:48
> sp_releaseapplock does not have any timeout parameter as far as I can
> see. So where does the timeout come from? Does the client that calls
> the procedure have a query timeout of 120 seconds?

Yes, there's no timeout on the sp_releaseapplock, it's a specified stored
procedure execution timeout of 120 seconds. (Way overkill for this
particular stored proc which only executes this particular routine.)


> How do you determine that the procedure is stalled on sp_releaseapplock?
> Or do you call sp_releaseapplock directly from the client?

It's definitely sp_releaseapplock being called in the middleware - have a
log entry before and after the stored proc call and it's capturing the
timeout on this call. I was looking in many other places until I logged
everything... this was the last place I would have looked as it's stable, old
shared code that never has big usage requirements or runtime issues and it's
executing on our lowest utilized SQL cluster.

> If sp_releaseapplock is really not returning within 120 seconds, something
> is really fishy.

Definitely...which is why I'm here! :)

>I would suspect that there is some internal corruption
> in internal locking structures. I would check the SQL Server errorlog for
> interesting messages. But most of all, I would try to find an occasion
> to restart SQL Server.

Already checked the error logs - nothing there. Nothing else is obviously
demonstrating a problem on the server. It hasn't been reset in a month, but
it has gone many months before without a reset. We're already scheduling a
reset for the next available window, but I thought I'd try here (and posted
on serverfault.com) to see if anyone has heard of this before. I dislike
the reboot-n-hope-it-goes-away approach but in this case, I imagine that's
the only path..

Thanks,

Darian
From: Erland Sommarskog on 13 May 2010 08:22
Darian Miller (DarianMiller(a)discussions.microsoft.com) writes:
> Yes, there's no timeout on the sp_releaseapplock, it's a specified stored
> procedure execution timeout of 120 seconds. (Way overkill for this
> particular stored proc which only executes this particular routine.)

So what you have is:

CREATE PROCEDURE release_lock @pLockUniqueName nvarchar(32) AS
EXEC @pUnLockSuccess sp_releaseapplock @pLockUniqueName, 'Session'
RETURN

> Already checked the error logs - nothing there. Nothing else is
> obviously demonstrating a problem on the server. It hasn't been reset
> in a month, but it has gone many months before without a reset. We're
> already scheduling a reset for the next available window, but I thought
> I'd try here (and posted on serverfault.com) to see if anyone has heard
> of this before. I dislike the reboot-n-hope-it-goes-away approach but
> in this case, I imagine that's the only path..

I can agree that the suggestion to reboot is an cheap attempt to get out,
but if you have a situation which "should not occur", a reboot is a good
start. If it never comes back, you did not waste time chasing ghosts. But,
of course, if it comes back you need to investigate more.

What I would like to see from this situation is information about locks,
blocking and current statement on the server. One way to capture this
information is aba_lockinfo, which you find on my web site:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html. The tricky part
here is that you need to run it during this stalling situation. But you
could run it from Agent once a minute, and then go back and look at
the output when a timeout occurs.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Pass table name to stored procedure?
Next: Code versus Int