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

user_15571560 0
Viewed 61

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)
jooq database java
1 Answers

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?

user_5217990
Related