jOOQ select for update nowait does not throw when row is locked

Question

When using select for update nowait with jooq the statement didn't throw an exception, but instead kept on waiting for the row to get unlocked.

Recently I tried to use select for update nowait in jooq running in spring boot, like so:

 public void findByIdForUpdateNoWait(UUID id) {

        dslContext.select()
                .from(PAYMENT)
                .where(PAYMENT.ID.eq(id))
                .forUpdate()
                .noWait()
                .fetch();
    }

And wrote a test to check the behaviour:

    @Test
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    void findByIdForUpdateNoWait_fail() {

        UUID id = UUID.fromString("3d346f2f-9c44-4e8c-8479-f3eb4b46195f");
        repository.findByIdForUpdateNoWait(id);
        testTransactionProvider.runInSeparateTransaction(() -> {
            assertThrows(DataAccessException.class,()->{
                repository.findByIdForUpdateNoWait(id);
            });
        });
    }

where method runInSeparateTransaction is:

    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void runInSeparateTransaction(Runnable test) {

        test.run();
    }

The test method is stuck. I see in logs that jooq throws an error DataAccessException, but the error doesn't reach the test method. Here is the query that jooq generates (based on logs)

select "payment"."id" where "payment"."id" = '3d346f2f-9c44-4e8c-8479-f3eb4b46195f' for update nowait

When querying the database directly at the moment of test run (when another transaction has locked the rows) using this sql:

select * from payment ipi where ipi.id = '3d346f2f-9c44-4e8c-8479-f3eb4b46195f' for update nowait;

The query throws the lock exception as expected.

How should I create the jOOQ query so it throws when rows are locked just like with the native query instead of waiting for them to get unlocked?

I have read the official docs https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/select-statement/for-update-clause/

I am using jOOQ 3.14.16 with postgres 42.6.0

Answer 1

Actually the problem was that I was using

@Sql(scripts = "/sql/payment-tear-down.sql", config = @SqlConfig(transactionMode = ISOLATED), executionPhase = AFTER_TEST_METHOD)

which actually couldn't run because the transaction was still holding locks on the table, hence it hanged the test and the test never passed.