Search code examples
javadatabasejooq

Will 'forUpdate().fetchAny(condition)' lock only one row or multiple rows?

Recently i have switched to Java stack on new position and i got some troubles with Jooq which is used to access db

My question is: will ForUpdate in the code below block only one row that satisfies another_condition and will be selected or multiple rows that satisfy some_condition

dslContext().select()
.from(my_table)
.where(some_condition)
.forUpdate()
.skipLocked()
.fetchAny(another_condition)
Solution

  • Client side ResultSet reading

    The ResultQuery.fetchAny() method only defines client side behaviour between jOOQ and JDBC. It proceeds to fetch at most one record from the underlying JDBC ResultSet. In short:

    • ResultQuery.fetchAny() fetches the first record (if available) from the ResultSet
    • ResultQuery.fetchOne() fetches the first record (if available) from the ResultSet and attempts to fetch another one, in case of which it throws TooManyRowsException
    • ResultQuery.fetchSingle() fetches the first record from the ResultSet, throws NoDataFoundException if no such record was found, and attempts to fetch another one, in case of which it throws TooManyRowsException

    These methods do not communicate to the server anything about the intended result set usage, so the server doesn't know what you're planning to do.

    Server side locking

    This is unrelated to how you use SQL pessimistic locking clauses, such as FOR UPDATE SKIP LOCKED, which is an operation that happens purely on the server side, and ideally on the entire data set satisfying your WHERE, LIMIT, and other clauses. Even if this weren't the case, it's always better to be very explicit about everything you know about your SQL query directly in SQL.

    So, since you're going to fetch only one record, why not tell the server, using LIMIT 1?

    dslContext()
    .select()
    .from(my_table)
    .where(some_condition)
    .limit(1)
    .forUpdate()
    .skipLocked()
    .fetchOne();

    API side note

    Note, there's no such thing as being able to pass "another_condition" to fetchAny(). You probably meant to stream the result and the use Stream.filter() on it, or something like that? But why would you lock N records and discard most of them?