Per our discussion on HipChat, +1. On 03/23/2017 06:53 AM, Vlad Mihalcea wrote: > --works > select TOP(?) abstractsk0_.id as id1_0_, abstractsk0_.processed as > processe2_0_ from BatchJob abstractsk0_ with (updlock, rowlock, readpast) > > --fails > select TOP(?) abstractsk0_.id as id1_0_, abstractsk0_.processed as > processe2_0_ from BatchJob abstractsk0_ with (holdlock, rowlock, readpast) > > Hi, > > While working on this issue which adds support for SKIP_LOCKED for SQL > server: > > https://hibernate.atlassian.net/browse/HHH-10654 > > I came to question the way we use the lock hints based on the JPA or > Hibernate LockMode(Type). > > Currently, we do like this: > > - PESSIMISTIC_WRITE -> UPDLOCK > - PESSIMISTIC_READ -> HOLDLOCK > > That's surprising since the HOLDLOCK is actually more restrictive than > UPDLOCK. > > According to the officiala documentation ( > https://msdn.microsoft.com/en-us/library/ms187373.aspx ) : > > UPDLOCK: > > " > Specifies that update locks are to be taken and held until the transaction > completes. > UPDLOCK takes update locks for read operations only at the row-level or > page-level. > If UPDLOCK is combined with TABLOCK, > or a table-level lock is taken for some other reason, an exclusive (X) lock > will be taken instead. > " > > HOLDLOCK: > > " > Is equivalent to SERIALIZABLE. For more information, see SERIALIZABLE later > in this topic. > HOLDLOCK applies only to the table or view for which it is specified > and only for the duration of the transaction defined by the statement that > it is used in. > " > > Now, the difference between these two is that UPDLOCK takes shared > row-level locks while > HOLDLOCK goes byond that and takes range locks as well. > > This assumption is backed by these StackOverflow answers: > > http://stackoverflow.com/questions/7843733/confused-about-updlock-holdlock > > http://stackoverflow.com/questions/42580238/why-does-sql-server-explicit-predicate-locking-disallow-insert-statements-outsid > > For SKIP_LOCKED, which is READPAST in SQL Server, we can't use HOLDLOCK at > all so we need to use UPDLOCK instead. > > Now, I think that both PESSIMISTIC_READ and PESSIMISTIC_WRITE should use > HOLDLOCK, > and only if we specify SKIP_LOCKED, we then switch to UPDLOCK instead. > > Let me know what you think? > > Vlad > _______________________________________________ > hibernate-dev mailing list > hibernate-dev@lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev
_______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
