I have some legacy code using PESSIMISTIC_WRITE in JpaRepository and CriteriaQuery.

I attempt to configure a SKIP LOCKED for the both technologies.

I succeed with the CriteriaQuery using a setHint(HibernateHints.HINT_NATIVE_LOCK_MODE, LockMode.UPGRADE_SKIPLOCKED)

But I failed with JpaRepository because @QueryHint expects a String value but LockMode is an enum.

I found a trick with a timeout of -2, it works, but its awful. I would prefer to use the same LockMode.UPGRADE_SKIPLOCKED on @QueryHint as the CriteriaQuery.

Below a test class reproducing my attempts, three tests passed, the fourth failed, I would to fix it.

I use the last Spring Boot version (3.4.3) with a Postgresql (15) database.

Any help will be appreciated.

import jakarta.persistence.*;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Root;
import .hibernate.LockMode;
import .hibernate.jpa.HibernateHints;
import .hibernate.jpa.SpecHints;
import .junit.jupiter.api.BeforeEach;
import .junit.jupiter.api.Test;
import .springframework.beans.factory.annotation.Autowired;
import .springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase;
import .springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import .springframework.data.domain.Limit;
import .springframework.data.jpa.repository.JpaRepository;
import .springframework.data.jpa.repository.Lock;
import .springframework.data.jpa.repository.Query;
import .springframework.data.jpa.repository.QueryHints;
import .springframework.stereotype.Repository;

import java.io.OutputStream;
import java.io.PrintStream;
import java.util.List;

import static .junit.jupiter.api.Assertions.assertEquals;

@Entity
@Table(schema = "public", name = "tb_value")
class ValueEntity {
    @Id
    @Column(name = "id")
    private String id;
    @Column(name = "text")
    private String text;
}

@Repository
interface ValueJpaRepository extends JpaRepository<ValueEntity, String> {
    String LOCK_MODE_UPGRADE_SKIPLOCKED = "upgrade-skiplocked"; // LockMode.UPGRADE_SKIPLOCKED.toExternalForm()

    @Query(value = "SELECT v FROM ValueEntity v")
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @QueryHints({@QueryHint(name = HibernateHints.HINT_NATIVE_LOCK_MODE, value = LOCK_MODE_UPGRADE_SKIPLOCKED)})
    List<ValueEntity> findValuesWithUpgradeSkipLocked(Limit limit);

    @Query(value = "SELECT v FROM ValueEntity v")
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @QueryHints({@QueryHint(name = SpecHints.HINT_SPEC_LOCK_TIMEOUT, value = "-2")})
    List<ValueEntity> findValuesWithTimeout(Limit limit);
}

class ValueCriteriaRepository {
    public static List<ValueEntity> findValuesWithLockAndHint(EntityManager entityManager, int limit,
                                                              String hintName, Object hintValue) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

        CriteriaQuery<ValueEntity> query = criteriaBuilder.createQuery(ValueEntity.class);
        Root<ValueEntity> from = query.from(ValueEntity.class);
        query.select(from);

        TypedQuery<ValueEntity> typedQuery = entityManager.createQuery(query)
                .setLockMode(LockModeType.PESSIMISTIC_WRITE)
                .setHint(hintName, hintValue)
                .setMaxResults(limit);

        return typedQuery.getResultList();
    }
}

@DataJpaTest(properties = {
        "spring.jpa.hibernate.ddl-auto=create-drop",
        "spring.jpa.show-sql=true",
        "spring.jpa.hibernate.format_sql=true",
        "spring.datasource.driverClassName=.postgresql.Driver",
        "spring.datasource.url=jdbc:postgresql://localhost:5432/testdb",
        "spring.datasource.username=testuser",
        "spring.datasource.password=testpwd"
})
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
public class SkipLockedTest {
    private StringOutputStream stringOutputStream;

    @BeforeEach
    void setUp() {
        stringOutputStream = new StringOutputStream();
        System.setOut(new PrintStream(stringOutputStream));
    }

    @Autowired
    private EntityManager entityManager;

    @Autowired
    private ValueJpaRepository valueJpaRepository;

    private final String EXPECTED = "Hibernate: select ve1_0.id,ve1_0.text from public.tb_value ve1_0 " +
            "fetch first ? rows only " + // LIMIT 1
            "for no key update " + // PESSIMISTIC_WRITE
            "skip locked" + // SKIP_LOCKED
            System.lineSeparator();

    @Test
    void testWithCriteriaBuilderAndUpgradeSkipLocked() { // OK
        ValueCriteriaRepository.findValuesWithLockAndHint(entityManager, 1, HibernateHints.HINT_NATIVE_LOCK_MODE, LockMode.UPGRADE_SKIPLOCKED);
        assertEquals(EXPECTED, stringOutputStream.toString());
    }

    @Test
    void testWithCriteriaBuilderAndLockTimeoutMinus2() { // OK
        ValueCriteriaRepository.findValuesWithLockAndHint(entityManager, 1, SpecHints.HINT_SPEC_LOCK_TIMEOUT, -2);
        assertEquals(EXPECTED, stringOutputStream.toString());
    }

    @Test
    void testWithJpaRepositoryAndUpgradeSkipLocked() { // KO : SKIP LOCKED is missing
        valueJpaRepository.findValuesWithUpgradeSkipLocked(Limit.of(1));
        assertEquals(EXPECTED, stringOutputStream.toString());
    }

    @Test
    void testWithJpaRepositoryAndLockTimeoutMinus2() { // OK
        valueJpaRepository.findValuesWithTimeout(Limit.of(1));
        assertEquals(EXPECTED, stringOutputStream.toString());
    }

    static class StringOutputStream extends OutputStream {
        private final StringBuilder sb = new StringBuilder();

        @Override
        public void write(int b) {
            sb.append((char) b);
        }

        @Override
        public String toString() {
            return sb.toString();
        }
    }
}

NB. In my pom.xml : spring-boot-starter-parent, spring-boot-starter-data-jpa, spring-boot-starter-test, hibernate-core, postgresql