468,101 Members | 1,325 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,101 developers. It's quick & easy.

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

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?

Jul 13 '06 #1
3 4747
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
Jul 13 '06 #2

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.

Jul 17 '06 #3
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
Jul 17 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Jason | last post: by
3 posts views Thread by Michael Teja via SQLMonster.com | last post: by
4 posts views Thread by interflex | last post: by
11 posts views Thread by Andrew Poelstra | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.