[hibernate-dev] SQL Server lock hints misunderstanding

[Vlad Mihalcea]

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