Re: [hibernate-dev] SQL Server lock hints misunderstanding

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