executeBatch (JDBC 2.0) and odd deadlocking w/ sql server

From: william milbratz (milbratz_at_hotmail.com)
Date: 04/20/04 

Date: 20 Apr 2004 08:19:13 -0700

Hi,

I'm seeing an odd deadlocking problem when using jdbc 2.0
'executeBatch' calls w/ Microsoft Sql server 2000. The same code
executes fine on Oracle. It's difficult to find documentation
describing the low-level locking w/ jdbc executeBatch(); hence the
newsgroup post.

tech stuff:
-we're using Sql server 2000 SP3
-we're using the MS sql server jdbc drivers dated from 2002-04-19
(i.e. class dates on files in msbase.jar).

The scenario:
-one transaction deletes "a bunch" of records and inserts "another
bunch"
-two separate callable statements, executed via 'addBatch' and
'executeBatch' perform the deletes and inserts respectively
-sql server produces a deadlock error when two different processes are
updating two entirely different sets of records. A trace shows that
the "deadlocking" procedure calls have different input
parameters---i.e. similar procedures (and thus tables) but very
different record sets.

It thus appears that sql server is using 'page level' or 'table level'
locking in this case.

Additionally, this is definitely not a 'classic deadlock' (i.e. as in
'deadly embrace' as:
a) these are different record sets and
b) the procedures are all updating the records in the same sequence
order.

A co-worker wrote:

"I read that there is a separate exclusive lock for bulk updates - not
sure if this means 'bulk loader only' or whether it could also be for
batch executes."

My question:
What causes this deadlock? How can I prevent it?

thanks and best regards,

bill milbratz

Relevant Pages

  • Strange error code for deadlock
    ... I am currently writing an deadlock handler for our application (a COM ... Having searched both ADO and SQL Server references, ... "NativeError" field. ... error message to find "deadlock victim" for example); ...
    (microsoft.public.vb.database.ado)
  • Re: Select statement Causing Table Locks
    ... Ok, it is technically a blocking chain, but this query does not continue. ... a deadlock is when two or more processes all block each ... SQL Server will detect deadlock situations, ...
    (microsoft.public.sqlserver.programming)
  • Deadlocks and Parallel Query Processing
    ... SQL Server 2000 SP3A ... Last week one of our processes starting issuing or suffering deadlock ... detected errors every 15 minutes or so. ... best I can tell from trying to query the actual data each update hits ...
    (comp.databases.ms-sqlserver)
  • Re: Deadlock problem when using SQL server
    ... >>I have a deadlock problem when using SQL server. ... >> It seems that the deadlock occurs when several threads udpate and then ... I suppose SQL Server uses a BTree as Default. ...
    (microsoft.public.sqlserver.server)
  • Re: Strange error code for deadlock
    ... Since you are working through OLEDB provider, ... > I am currently writing an deadlock handler for our application (a COM ... > Having searched both ADO and SQL Server references, ... > error message to find "deadlock victim" for example); ...
    (microsoft.public.vb.database.ado)