[hibernate-dev] SQL Server lock hints misunderstanding

[Chris Cranford]

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 at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev