SQLServer 2000 JDBC /SQL Exception Error Messages - Meaning?

[Steve]

Hi;

I went to the microsoft site to try to find a guide to the error
messages that the jdbc drivers give ( for sqlserver 2000 ).

I had no luck.

Does anyone know if there is such a guide?

I got this mysterious error message below in my logs ( nothing visible
in user land ).

Anyone know what it means?

Thanks in advance

Steve


java.sql.SQLExc eption: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]Transaction (Process ID 151) was deadlocked on lock
resources with another process and has been chosen as the deadlock
victim. Rerun the transaction.

[Michel Laan]

Re: SQLServer 2000 JDBC /SQL Exception Error Messages - Meaning?

To me it sounds like a regular deadlock message. A deadlock being a
situation in which two processes are waiting for each other to release a
lock on a table, page or row before continuing. If this situation occurs SQL
server will choose one of the processes as least important and kill the
statement running. The other process can than continue.
If it is just a deadlock, either re-run the statement or if the statement
always generates this message, check the statement itself to see if it does
not generate the lock itself.

Michel Laan
"Steve" <stevesusenet@y ahoo.com> wrote in message
news:6f8cb8c9.0 401130605.4af3e ec7@posting.goo gle.com...[color=blue]
> Hi;
>
> I went to the microsoft site to try to find a guide to the error
> messages that the jdbc drivers give ( for sqlserver 2000 ).
>
> I had no luck.
>
> Does anyone know if there is such a guide?
>
> I got this mysterious error message below in my logs ( nothing visible
> in user land ).
>
> Anyone know what it means?
>
> Thanks in advance
>
> Steve
>
>
> java.sql.SQLExc eption: [Microsoft][SQLServer 2000 Driver for
> JDBC][SQLServer]Transaction (Process ID 151) was deadlocked on lock
> resources with another process and has been chosen as the deadlock
> victim. Rerun the transaction.[/color]

[Erland Sommarskog]

Re: SQLServer 2000 JDBC /SQL Exception Error Messages - Meaning?

[posted and mailed, please reply in news]

Steve (stevesusenet@y ahoo.com) writes:[color=blue]
> I went to the microsoft site to try to find a guide to the error
> messages that the jdbc drivers give ( for sqlserver 2000 ).
>
> I had no luck.
>
> Does anyone know if there is such a guide?
>
> I got this mysterious error message below in my logs ( nothing visible
> in user land ).
>
> Anyone know what it means?
>
> java.sql.SQLExc eption: [Microsoft][SQLServer 2000 Driver for
> JDBC][SQLServer]Transaction (Process ID 151) was deadlocked on lock
> resources with another process and has been chosen as the deadlock
> victim. Rerun the transaction.[/color]

First, while the JDBC driver is the one that communicates the problem
to you, the error does not come from there. The last bracket says
[SQLServer], so this means that the error comes from SQL Server originally.

Had the error come directly from SQL Server, [SQL Server 2000 Driver
for JDBC] would have been the last bracket.

I don't know about any guides for JDBC driver messages, but the SQL
Server messages are all listed in Books Online, and some of the
errors also have topics which explains more about the error. There
is one problem in this case to find the error, because the far most
easiest way to look up an error is use the error number, and the
JDBC Driver does not seem to include that. (But it may be a property
of the execption - if exceptions can have properties in Java.)

One way to find the number is to issue something like:

SELECT * FROM master..sysmess ages WHERE description LIKE '%deadlocked%'

from Query Analyser. (Replace %deadlocked%) with some other characteristic
part of the error message, but don't include object names, as they are
parameters to the messages and not available in sysmessages.

Now, this particular message has error 1205 and is a common error in
multi-user applications. What happens is that two connections are
both waiting for resources that are blocked by the other connection.
This situation is know as "deadlock". SQL Server detects this situation,
and selects one of the processes as a victim, and aborts its batch
and transaction.

Occasional deadlocks are nothing to lose sleep over, but if they are
frequent enough, this calls for a review of the the SQL code in the
application.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Joe Weinstein]

Re: SQLServer 2000 JDBC /SQL Exception Error Messages - Meaning?

Steve wrote:[color=blue]
> Hi;
> I went to the microsoft site to try to find a guide to the error
> messages that the jdbc drivers give ( for sqlserver 2000 ).
> I had no luck. Does anyone know if there is such a guide?
> I got this mysterious error message below in my logs ( nothing visible
> in user land ). Anyone know what it means? Thanks in advance
> Steve
>
> java.sql.SQLExc eption: [Microsoft][SQLServer 2000 Driver for
> JDBC][SQLServer]Transaction (Process ID 151) was deadlocked on lock
> resources with another process and has been chosen as the deadlock
> victim. Rerun the transaction.[/color]

Hi. The majority of the messages the JDBC driver will deliver, come
from the DBMS, so the documentation for these messages will be in the
DBMS documentation. It is so in this case. The message refers to a
basic DBMS concurrency problem where two DBMS clients have locked each
other from proceeding. One has locked resource A and then tries to lock
resource B. The other user has locked resource B, and is now trying to
lock resource A. The DBMS detects this and must break the log jam by
choosing to kill/rollback one of these transactions. The chosen victim
gets the message you see.

Joe Weinstein at BEA

[Steve]

Re: SQLServer 2000 JDBC /SQL Exception Error Messages - Meaning?

Thanks for this incredibly useful post.

Steve


Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns946FF3 4BA994CYazorman @127.0.0.1>...[color=blue]
> [posted and mailed, please reply in news]
>
> Steve (stevesusenet@y ahoo.com) writes:[color=green]
> > I went to the microsoft site to try to find a guide to the error
> > messages that the jdbc drivers give ( for sqlserver 2000 ).
> >
> > I had no luck.
> >
> > Does anyone know if there is such a guide?
> >
> > I got this mysterious error message below in my logs ( nothing visible
> > in user land ).
> >
> > Anyone know what it means?
> >
> > java.sql.SQLExc eption: [Microsoft][SQLServer 2000 Driver for
> > JDBC][SQLServer]Transaction (Process ID 151) was deadlocked on lock
> > resources with another process and has been chosen as the deadlock
> > victim. Rerun the transaction.[/color]
>
> First, while the JDBC driver is the one that communicates the problem
> to you, the error does not come from there. The last bracket says
> [SQLServer], so this means that the error comes from SQL Server originally.
>
> Had the error come directly from SQL Server, [SQL Server 2000 Driver
> for JDBC] would have been the last bracket.
>
> I don't know about any guides for JDBC driver messages, but the SQL
> Server messages are all listed in Books Online, and some of the
> errors also have topics which explains more about the error. There
> is one problem in this case to find the error, because the far most
> easiest way to look up an error is use the error number, and the
> JDBC Driver does not seem to include that. (But it may be a property
> of the execption - if exceptions can have properties in Java.)
>
> One way to find the number is to issue something like:
>
> SELECT * FROM master..sysmess ages WHERE description LIKE '%deadlocked%'
>
> from Query Analyser. (Replace %deadlocked%) with some other characteristic
> part of the error message, but don't include object names, as they are
> parameters to the messages and not available in sysmessages.
>
> Now, this particular message has error 1205 and is a common error in
> multi-user applications. What happens is that two connections are
> both waiting for resources that are blocked by the other connection.
> This situation is know as "deadlock". SQL Server detects this situation,
> and selects one of the processes as a victim, and aborts its batch
> and transaction.
>
> Occasional deadlocks are nothing to lose sleep over, but if they are
> frequent enough, this calls for a review of the the SQL code in the
> application.[/color]

[darpansw]

Dear Steve,
i guess you have come up with a workaround for this problem.Could you please guide me with the workaround u implemented since i am getting a simmilar exception

regards,
Darpan
darpan79@yahoo. com