Posted by Erland Sommarskog on 07/13/06 22:06

wreed (whreed@gmail.com) writes:
> Our SQL Server 200 box is getting perflib errors when we get a decent
> amount of people using an application that I wrote, call queue system,
> web based. To accomplish a queue type system on a button push I wrote
> a query like this...
>
> BEGIN TRANSACTION;
> Select top 1...fields here...
> FROM table with (xlock,readpast)
> (2 joins)
> WHERE numerous where clauses
> ORDER BY 2 order bys.
>
> Now our sql server starts timing out..then in the app log this shows
> up....

SQL Server times out? If you get a message "Timeout expired" that is a
client-side timeout. Or have you enabled lock timeouts.

> Error: 1203, Severity: 20, State: 1
>
> Process ID 62 attempting to unlock unowned resource PAG: 6:1:126407.
>...
> So to avoid this error which I assume is due to my xlock should I
> rethink my query?

1203 is definitely to regard as an internal server error, so you are
hitting a bug of some sort.

It would be interesting to know what happens first, the timeout or the
1203. And what sort of timeout you get.

Since by default most client libraries has a default timeout of 30
seconds, I assume that you have a client-side timeout. When you get
such a timeout, you should always issue a IF @@trancount > 0 ROLLBACK
TRANSACTION, because there is no automatic rollback on timeouts.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]