deadlock SQL server 7
Ananta Karanam
I am also getting the same exception when I am trying to delete
different rows from same table using different threads.
How to check what locks are presented in SQL Server.
Any suggestions are greatly appreciated. I really need this to fix very soon.
-Ananta
"will" <taina...@hotmail.com> wrote:
>
>Hello,
>
> I am using the Jdriver and and the connection pool pogram from Wrox
>Java Server
>Programming. My program does updates to a particular row in a table and
>does not
>work with any other tables. But I get a deadlock error even though each
>thread
>updates its own row in the table. Each thread obtains its own connection
>from
>the connection pool. I am using the Wrox connection pool because we want
>this
>to be a standalone app from the Weblogic server.
>
>When I checked the locks in SQL server 7 it says that the lock is of
>Type 'DB'
>and not type 'ROWID'. So it looks like each connection has a database
>lock rather
>than a row level lock .Is there any way to ensure that the update is
>a row level
>lock ? Why is this deadlocking?
>
>
>The update i am attempting is:
>
> String SQL ="update customer set "
> + "notified = '" + notified
> + "' where customer_id = " + data.getCustomerId() ;
> DebugUtility.info("update SQL:"+ SQL );
> st= con.createStatement();
> st.executeUpdate(SQL);
>
>And the error I get is:
>
>Fri 07/13/2001 07:56:11 EDT [EROR] [THREAD-17] CustomerEvaluator java.sql.SQLException:
>Your transaction (process ID #69) was deadlocked with another process
>and has
>been chosen as the deadlock victim. Rerun your transaction. Severity
>13, State
>50, Procedure 'APOK null', Line 1
> at weblogic.jdbcbase.mssqlserver4.TdsStatement.processWarning(TdsStatement.java:1188)
> at weblogic.jdbcbase.mssqlserver4.TdsStatement.parseMsWarning(TdsStatement.java:1110)
> at weblogic.jdbcbase.mssqlserver4.TdsStatement.getMoreResults(TdsStatement.java:793)
> at weblogic.jdbcbase.mssqlserver4.TdsStatement.execute(TdsStatement.java:211)
> at weblogic.jdbcbase.mssqlserver4.TdsStatement.executeUpdate(TdsStatement.java:94)
> at com.company.dao.CustomerDao.updateStatus(CustomerDao.java:58)
> at com.company.net.connections.CustomerEvaluator.run(CustomerEvaluator.java:79)
> at java.lang.Thread.run(Thread.java:484)
Joseph Weinstein
> I am also getting the same exception when I am trying to delete
> different rows from same table using different threads.
> How to check what locks are presented in SQL Server.
> Any suggestions are greatly appreciated. I really need this to fix very soon.
Hi. I wish I had a quick fix for you. The fact is that this is 99% a DBMS issue
only, having nothing to do with the client (weblogic, the JDBC driver, or your code).
I am not an expert in SQL 7, but I believe that this DBMS does not provide
full, automatic row level locking for all operations, and therefore may well
lock logically unrelated rows, so some other concurrent transaction may be
blocked unintentionally. Also, depending on the query used, and the indexes on the
tables, and the accuracy/currency of the internal DBMS table statistics, the
DBMS may well believe it has to/should lock a whole table for a given search.
If you are setting your transaciton isolation level inappropriately high, then lowering
it might help, especially if you are blocking on query data as opposed to
update data.
However, the simple update you show below, in itself, could not cause a deadlock.
The connection must be being used elsewhere as well, and have done some other
update or query. Can you verify that for me? You may have an easy fix if you have
control over the order of your updates. Obviously a deadlock occurs, when one
TX has locked row A and wants row B, while another TX has locked row B
and wants row A. If you can alter all your TX code to always get row A before
row B, then you will avoid deadlocks.
If you are getting this deadlock message for a single simple delete/update then it
is an internal DBMS bug.
Joe Weinstein at B.E.A.
Slava Imeshev
I don't think there is a silver bullet for avoiding deadlocks.
Though the proper design would allow to avoid them.
For example, it's better to avoid spreading in time transactions
issuing statements that lock database pages. Your code may
look like
[*] select some rows
... do some processing
[*] update some rows
... do some processing
[*] insert some rows
.... do some processing
commit. Good design would group [*] in one place,
so that these loking operations are not spreaded in time.
The point is that if [*] locked different pages in DB,
there is a higher chance that another TX will lock
the pages down the road and you'll get a deadlock.
[*] could be also splitted into the different transactions.
Another way to avoid deadlocks is using more weak serialization
levels (SERIALIZABLE instead of REPEATABLE READ for instance)
along with otimistic consurrency strategy.
Hope it helps.
Regards,
Slava Imeshev
"Ananta Karanam" <akar...@tibco.com> wrote in message
news:3c925d21$1...@newsgroups.bea.com...