SQL 2000 Error: 1203, Severity: 20, State: 1 Error

[wreed]

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....

Error: 1203, Severity: 20, State: 1

Process ID 62 attempting to unlock unowned resource PAG: 6:1:126407.

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.

The reason I am doing xlock is to make a record not viewable to 2
people if they click the button on the web form that runs the above
query within the same minute, they would get different records....

So to avoid this error which I assume is due to my xlock should I
rethink my query?

[Erland Sommarskog]

wreed (wh****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[wreed]

Erland Sommarskog wrote:

>
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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Can it be that I am running a TOP 1 and the query is actually pulling a
whole query back then only taking the top row? Or am I totally wrong
on how a TOP 1 works.

[Erland Sommarskog]

wreed (wh****@gmail.com) writes:

Erland Sommarskog wrote:

>>
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.

Can it be that I am running a TOP 1 and the query is actually pulling a
whole query back then only taking the top row? Or am I totally wrong
on how a TOP 1 works.

Exactly how a TOP 1 is implemented is up to the optimizer. And what
ORDER BY clause you specified.

Yes, TOP 1, could be part of the reason you get this error, although
the locking hints are even more suspects in this case. But as I said,
this is an internal server error. It should simply not occur, and when
it does, it's not your fault.

....unless you first get the query timeout, and you then fail to rollback
the transaction, and the error happens because this creates a mess.

So when this scenario, do you first get a timeout (which happens in the
client), or do you first get the 1203 error?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx