Optimistic locking and full table lock

Question

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.

Answer 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.

Answer 2

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.