• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Locking problems using JDBC and MS SQL Server 2008.

 
Anders Lunden
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

The intention is that concurrent updates are allowed as except when the updates are for the same row

Would it not be easier to implement an optimistic locking pattern in code? That would be the normal way of addressing this issue.

When you run in MS management studio, is your user sa, or someone with equivalent privileges?

If not, you could try implementing snapshot isolation. This way your get transaction behaviour much more like you would in Oracle, though at the expense of resources on SQL Server (temp db will grow). So no shared select locks that can block.
 
Anders Lunden
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul and thanks for your attention,

Yes, I am running the transaction in Management Studio as a privileged user. However I just tried the same thing with the same user as the java application and that worked just fine as well. No unnecessary locking going on there.

Yes, using optimistic might the way to go and I have been reading up on that. I am however a bit worried of how to implement it since the transaction deletes, inserts and updates multiple rows in multiple tables. Getting a proper update-count on that might be a bit tricky.

I will probably end up implementing an optimistic locking pattern instead, but it just baffles me that the query demands a lock on a row which is not in the search criteria when the query is made from JDBC and not when it's made from Management Studio.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My guess is because user's like sa need to use the MS tool set to diagnose blocks and the like SQL Server lets them run with some subtle differences in row locking. I've certainly seen sa being able to work in a different way from other users when it comes to shared read locks.

 
Anders Lunden
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I've certainly seen sa being able to work in a different way from other users when it comes to shared read locks.


Oh, I didn't know that. I'll change this transaction then. But it's still weird that locks are held for keys which is not part of the transaction.
Anyway, thanks for your help Paul.

/Anders
 
Anders Lunden
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I did some read up on optimistic locking and it appears to me that the pattern is suitable for scenarios where a user reads data, then performs some work on it and then finally performs an update of it. That is there is an considerable amout of time between the read and the update so they can not be within the same transaction. This is not my scenario at all. The read and update (the "update" may be multiple deletes, inserts and updates) in my data is all within the same transaction which in my test environment completes in about 300 ms. The problem is that I might have a queue of thousands of these transactions to perform, each one on different entities hence I want concurrency to process the queue as quick as possible. I believe the optimistic locking pattern wouldn't do me anything since the update-part of my transaction is the most time consuming part.

I did try to change my transaction so that the first query in the transaction is an UPDATE instead of an SELECT. This did not help me at all. The second thread is now waiting for this UPDATE to complete before the second thread gets its' go in the database. Even though the two threads are trying to update different rows:

UPDATE CURRENT_FLIGHTS WITH (ROWLOCK) SET EVENT_TSTMP = :event_tstmp, OTHERCOLUMNS=ther_columns_new_value WHERE ID = '1111' and DATOP = '2010-01-01' and EVENT_TSTMP < :event_tstmp

The EVENT_TSTMP is of datatype datetime.

When I have an open transaction which has run the update query above, and another thread tries to run the same transaction on a different row (ID='2222') it waits for a lock held by the first thread until a lock timeout occurs or the first thread commits. At the end of the day, the problem is the same as I had with the SELECT: For some reason the second thread wants a lock held by the first thread even though the two threads are updating different rows. The lock held and wanted is of KEY type which according to MS documentation is described as "Lock within an index that protects a range of keys in serializable transactions". The "range" part led me to believe that the two rows are protected by the same key lock so I added a thousand or so rows and tried the same thing with two rows I would assume be considered be "far" from each other. No luck there either, still the same problem.

I'm starting to believe that I've missed something really fundamental in SQL Server. For instance, do different versions of SQL Server support cuncurrency differently (eg: Enterprise Edition vs Standard Edition)? I'm using standard edition.

Cheers
/Anders
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

That is there is an considerable amout of time between the read and the update so they can not be within the same transaction

This is not accurate. If there were a considerable amount of time it increases the chances of stale data so is no longer that "optimistic". The normal pattern would be contained in one transaction.


I'm starting to believe that I've missed something really fundamental in SQL Server. For instance, do different versions of SQL Server support cuncurrency differently (eg: Enterprise Edition vs Standard Edition)? I'm using standard edition.

It would be frustrating if they did. Did you try snapshot isolation? Getting SQL Server's read-committed transaction isolation to behave like Oracle's read-committed transaction isolation has solved a lot of problems for me in the past.
 
Anders Lunden
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Sturrock wrote:

Did you try snapshot isolation?


No, I have not. It wasn't obvious how to do it since I use Spring JDBC to query the database and don't work with a Connection instance directly. I'll look in to it.

/Anders
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Enabling snapshot isolation is a database option, you should not need to change your connection properties. Once you've enabled it read-committed isolation (which is the out the box isolation level I think) changes in its implementation, not its meaning. So you should be able to avoid shared select locks because read-committed via snapshot isolation doesn't need them.
 
Anders Lunden
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Sturrock wrote:Enabling snapshot isolation is a database option, you should not need to change your connection properties.


Well, how about that. You're right. It was possible to change to SNAPSHOT isolation in the database without changing any properties on the connection itself. However it didn't matter, still the same problem and I am using read committed isolation level now. Before I used uncommited read. I have verified in the SQL Server Profiler that the connections are using read committed and that READ_COMMITED_SNAPSHOT property is on in the database.

gnn
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have you tried it without locking hints? If you are using read committed you presumably don't need them.
 
Anders Lunden
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Sturrock wrote:Have you tried it without locking hints? If you are using read committed you presumably don't need them.

Wow, that seems to have done the trick \o/
I'm am actually hearing violins and trumpets playing a fanfare in my head right now.

You've been of great help Paul. I couldn't have done this without you!
A simple performance test in my development environment where I'm running four threads I get almost 4 times the throughput compared to earilier. I'm anxious to get these changes in the test environment to see if I get the performance boost I'm expecting on more production like data.

Again, thank you for your help Paul!

Cheers
/Anders
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You are welcome.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic