-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 
Author Message
 Post subject: Locking with JPA and PESSIMISTIC_WRITE on Sybase ASE 15.7
PostPosted: Sun Mar 20, 2016 12:00 am 
Newbie

Joined: Sat Mar 19, 2016 11:46 pm
Posts: 3
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.


Top
 Profile  
 
 Post subject: Re: Locking with JPA and PESSIMISTIC_WRITE on Sybase ASE 15.7
PostPosted: Mon Mar 21, 2016 2:05 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
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.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Locking with JPA and PESSIMISTIC_WRITE on Sybase ASE 15.7
PostPosted: Wed Mar 23, 2016 1:13 pm 
Newbie

Joined: Sat Mar 19, 2016 11:46 pm
Posts: 3
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:
Quote:
A lock with LockModeType.PESSIMISTIC_WRITE can be obtained on an entity instance
to force serialization among transactions attempting to update the entity data. A lock with
LockModeType.PESSIMISTIC_READ can be used to query data using repeatable-read
semantics without the need to reread the data at the end of the transaction to obtain a lock, and
without blocking other transactions reading the data. A lock with LockModeType.PESSIMISTIC_WRITE
can be used when querying data and there is a high likelihood
of deadlock or update failure among concurrent updating transactions.


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.


Top
 Profile  
 
 Post subject: Re: Locking with JPA and PESSIMISTIC_WRITE on Sybase ASE 15.7
PostPosted: Wed Mar 23, 2016 2:23 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
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?

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Locking with JPA and PESSIMISTIC_WRITE on Sybase ASE 15.7
PostPosted: Tue Mar 29, 2016 11:19 am 
Newbie

Joined: Sat Mar 19, 2016 11:46 pm
Posts: 3
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:
Code:
fid   spid   loid   locktype    table_id     page    row   dbname          class                            context
---   ----   ----   ---------   ----------   -----   ---   -------------   ------------------------------   -------
     0    555        1110 Ex_intent   1314100691        0      0 mydb   Non Cursor Lock
     0    555        1110 Ex_row      1314100691   124352     15 mydb   Non Cursor Lock
     0    555        1110 Ex_row      1314100691   171184      0 mydb   Non Cursor Lock


It looks different when inspecting what Hibernate is doing (externally, using a sleep before transaction committed):
Code:
fid   spid   loid   locktype    table_id     page    row   dbname          class                            context
---   ----   ----   ---------   ----------   -----   ---   -------------   ------------------------------   -------
0     176    352    Sh_intent   1314100691   0       0     mydb   Non Cursor Lock                            
0     176    352    Sh_table    1314100691   0       0     mydb   Non Cursor Lock                            
0     176    352    Sh_row      1314100691   66294   0     mydb   Non Cursor Lock                            
0     176    352    Sh_row      1314100691   67984   8     mydb   Non Cursor Lock                  Range     


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


Top
 Profile  
 
 Post subject: Re: Locking with JPA and PESSIMISTIC_WRITE on Sybase ASE 15.7
PostPosted: Tue Mar 29, 2016 12:49 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
That's what I said instead:

Quote:
As far as I know, Sybase has only a FOR UPDATE directive.


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

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.