Hi
I have this strange error where a transaction is forced to wait on an unnecessary lock when using
JDBC. When using the Microsoft Management Studio development tool to run the application, the error does not occur. I'll try to describe my problem.
I have a table where the primary key consists of two columns, id (varchar) and datop (date).
My transaction first selects a row from the column, and depending on what it finds it updates the row and potentially multiple other rows in other tables. The problem is that if this transaction is run concurrently in two different threads the second transaction is not able to retrieve the row due to a lock which is held in the first transaction. This effectivly singlethreads all updates made by this transaction, causing poor performance.
Here is the scenario when
Thread 2 is made via JDBC:
1. Thread 1: Starts transaction
2. Thread 1: Runs
SELECT * FROM FLIGHTS WITH (ROWLOCK, UPDLOCK) WHERE id = '1111' and datop = '2010-01-01'
3. Thread 2: Starts transaction
4. Thread 2: Runs
SELECT * FROM FLIGHTS WITH (ROWLOCK, UPDLOCK) WHERE id = '2222' and datop = '2010-01-01'
5. Thread 2: Waits until Thread 1 completes or until a LOCK_TIMEOUT threshold is breached.
The intention is that concurrent updates are allowed as except when the updates are for the same row. The example above fetches DIFFERENT rows, hence should be able to be updated simultaneously but they aren't. If I run Thread 2 from Microsoft Management Studio it works as expected, that is Thread 2 does not wait for the transaction in Thread 1 to complete. Thread 2 is able to complete before Thread 1. I use the UPDLOCK hint because later in the transaction it is very likely that the row will be updated and no other session is allowed to update the same row during that time.
When I look at the locks held in the SQL Server database at the time given by mark 5 above I see that Thread 1 has a KEY lock (lock on index) on a resource with an id "X" and Thread 2 has a KEY lock on a resource with id "Y". Thread 2 is also waiting for a KEY lock on resource with id "X". The resource X corresponds to the row given by
id='1111' and
datop='2010-01-01' and the resource Y corresponds to the row given by
id='2222' and
datop='2010-01-01'.
When I run the same scenario but use MS Management Studio to run Thread 2, then Thread 2 DOESN'T try to lock X, it only holds lock Y and is fine with that and is able to complete without Thread 1 commiting. This is the expected behaviour.
For some unknown reason, when the Thread 2 is made from the
java application it demands additional locks.
I have tried both Microsoft JDBC Driver 3.0 and the jTDS driver version 1.2.5 with the same result.
Any help is appreciated.
Cheers
/Anders