3

We have a system where we once in a while get an optimistic locking exception. We have worked around this in the code, but now I am looking at JPA 2 and see that it has an annotation (@Version) for dealing with this.
The problem we have is that more than one transaction is working on a table, and with a full table lock this causes the optimistic locking exception even though changes are not made to the same records.

We are using hibernate on a JBoss 4.2 server, and the database could either be MySQL or SQL Server.

If we changed to using @Version instead, would this enforce row lock on both databases or can we still expect to see optimistic locking exception caused by full table lock?

Edit:
What we actually see is not an optimistic locking error, but a deadlock:

SQLServerException: Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

We deal with this in the code, but I was wondering if @Version could be of any help in this case.
At least in one of the cases this deadlock was caused by a table lock, where two clients were working with there own data.

homaxto
  • 5,428
  • 8
  • 37
  • 53
  • I'm afraid this question mixes up the concepts of Optimistic Concurrency Control (aka Optimistic Locking) and Database Locks on tables and rows. – Peter Wippermann May 22 '14 at 08:15

2 Answers2

1

It depends on your SQL statements. An optimistic lock will occur whenever the transaction fails to acquire a lock on the needed resource for whatever reason it might be. It can be due to a table lock or row lock. It doesn't matter.

If you are running SQL queries that require table locks then you won't have any luck with the version field. By default MS SQL Server is using row locks. So probably your have small tables or missing primary keys or there is some other reason for SQL Server to use table locks on your query.

You should investigate for the query that produces table locks and trying to tweak it. You should expect occasional locking to occur and deal with it in your application.

MicSim
  • 26,265
  • 16
  • 90
  • 133
  • Actually optimistic locking as very little to do with "locks" (in this case with Hibernate). Its sort of an ORM hack and generally has very little to do with table/row lock. Usually its because of concurrency issues. There is DB implementation specific OCC but I'm confident that is not what the OP is talking about. – Adam Gent Mar 07 '13 at 15:02
1

Possible duplicate: Optimistic vs. Pessimistic locking

Optimistic locking exceptions are almost always a stateful concurrency issue. For example: two threads load up the exact same entity, change the object in parallel and then save it. Regardless of transactions (table or row lock) you will get an optimistic locking exception when this happen (see referenced question).

I am shocked your getting optimistic locking exceptions w/o @Version in which case maybe you are getting real RDBMS OCC error but I doubt it. Whats most likely happening is that the entire object is be diff'ed to the row (since you didn't specify @Version) in which case any changes to the row will cause an optimistic locking exception. Please add the exception to your question so I don't have to assume.

Community
  • 1
  • 1
Adam Gent
  • 47,843
  • 23
  • 153
  • 203
  • Well, I took a look at our jira server, and found out that it's actually a deadlock problem we have and which we deal with in the code. When I was reading about @Version, I just asked myself if this could help us, or if it's another problem. – homaxto Mar 08 '13 at 08:55
  • 1
    Its sort of unclear what your asking. As I stated in my answer optimistic locking does not cause deadlocks and hibernate will still do it regardless of @Version. So using will not make a difference in that regard. – Adam Gent Mar 08 '13 at 12:30