Hello,

I've been working with JPA and Hibernate 4.2.21 and attempted to use the pessimistic write lock mode when reading an entity, i.e. specifying the mode upon invocation EntityManager.find(). However, it seems that on Sybase ASE 15.7 (not sure if other databases) the lock that the server obtains is a shared lock on the row when doing the find() call; in fact, I could not tell whether it was doing anything different with the explicit lock mode, as the emitted SQL seemed that same.

The reason I ask about the locking is because I was trying to address an issue where multiple calls (even though superfluous) would lead to deadlocks as they were loading and attempting to update the same row. I though that by using a pessimistic write mode it will get an outright exclusive lock to avoid issues.

In contrast, when I used the server's SELECT FOR UPDATE type of SELECT statement, an exclusive lock would be acquired as part of the transaction and launching multiple parallel updates would not yield in a deadlock.

So, I'm hoping someone can give me some guidance as to whether it is something that is not implemented by Hibernate on Sybase, or whether I'm misunderstanding the pessimistic write lock mode, or whether something else could be amiss.

Thanks.

By default, all Drivers share the same lock syntax for both read (shared) or write (exclusive) locking. Some drivers define a different syntax because the RDBMS support such a feature.
As far as I know, Sybase has only a FOR UPDATE directive. If it had a "FOR SHARE" locking directive, the Sybase Dialect could be updated to take advantage of it.

Pessimistic locking is not going to free you from deadlocks. In fact, it increases the chances of deadlocks.
There are two ways to avoid anomalies: prevention or detection.
Pessimistic locking tries to prevent anomalies but it also increases the chance of deadlocks.

If you want to avoid deadlocks, you should use an optimistic locking mechanism which can prevent lost updates even across multiple web requests. Optimistic locking reduces the likelihood of deadlocks, scales better, and it also works for multi-requests logical transactions.

I'm unclear: does the fact that it has no "for share" precludes the use of "for update"?
If anything, by default it is "for share" as the locking in effect will depend on the isolation level.

Also, wouldn't it be proper to actually get an exclusive lock upon a pessimistic write lock request?
Looking at the JPA 2.0 specs in §3.4.4.2 seems to imply that it would be necessary:


I understand what you mean by having exclusive locks can lead to deadlocks, but that depends on what one is trying to do and how they go about it.

Most DBs support "for update", but only a few support "for share". That's why "for update" is by default in the base Dialect.

I don't see how it can use "for share" since the SybaseDialect only supports "for update" only. Am I missing something?

I think we are talking at cross purposes: I'm saying that Sybase has SELECT FOR UPDATE which will lock the row, however that is not used by Hibernate even when specifying that a pessimistic write lock mode should be used.

Unless I'm misunderstanding you, you're saying FOR UPDATE is not used because there is no FOR SHARE, right?

I think I mentioned that when I run some manual SQL with FOR UPDATE and sp_lock within same transaction to inspect, I can see the following:


It looks different when inspecting what Hibernate is doing (externally, using a sleep before transaction committed):


So it definitely does not do a SELECT FOR UPDATE, and that's the locking aspect that affects my use case.

That's what I said instead:



If you can provide a replicating test case using our JPA templates or Hibernate-native templates, please fill in a JIRA issue then.