Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
FOR UPDATE clause
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
For inter-process synchronisation and other reasons, you may choose to use the SELECT .. FOR UPDATE clause to indicate to the database, that a set of cells or records should be locked by a given transaction for subsequent updates. With jOOQ, this can be achieved as such:
SELECT * FROM BOOK WHERE ID = 3 FOR UPDATE
create.select()
.from(BOOK)
.where(BOOK.ID.eq(3))
.forUpdate()
.fetch();
The above example will produce a record-lock, locking the whole record for updates. Some databases also support cell-locks using FOR UPDATE OF ..
SELECT * FROM BOOK WHERE ID = 3 FOR UPDATE OF TITLE
create.select()
.from(BOOK)
.where(BOOK.ID.eq(3))
.forUpdate().of(BOOK.TITLE)
.fetch();
Oracle goes a bit further and also allows to specify the actual locking behaviour. It features these additional clauses, which are all supported by jOOQ:
-
FOR UPDATE NOWAIT: This is the default behaviour. If the lock cannot be acquired, the query fails immediately -
FOR UPDATE WAIT n: Try to wait for [n] seconds for the lock acquisition. The query will fail only afterwards -
FOR UPDATE SKIP LOCKED: This peculiar syntax will skip all locked records. This is particularly useful when implementing queue tables with multiple consumers
With jOOQ, you can use those Oracle extensions as such:
create.select().from(BOOK).where(BOOK.ID.eq(3)).forUpdate().nowait().fetch(); create.select().from(BOOK).where(BOOK.ID.eq(3)).forUpdate().wait(5).fetch(); create.select().from(BOOK).where(BOOK.ID.eq(3)).forUpdate().skipLocked().fetch();
FOR UPDATE in CUBRID and SQL Server
The SQL standard specifies a FOR UPDATE clause to be applicable for cursors. Most databases interpret this as being applicable for all SELECT statements. An exception to this rule are the CUBRID and SQL Server databases, that do not allow for any FOR UPDATE clause in a regular SQL SELECT statement. jOOQ emulates the FOR UPDATE behaviour, by locking record by record with JDBC. JDBC allows for specifying the flags TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE for any statement, and then using ResultSet.updateXXX() methods to produce a cell-lock / row-lock. Here's a simplified example in JDBC:
try (
PreparedStatement stmt = connection.prepareStatement(
"SELECT * FROM author WHERE id IN (3, 4, 5)",
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery()
) {
while (rs.next()) {
// UPDATE the primary key for row-locks, or any other columns for cell-locks
rs.updateObject(1, rs.getObject(1));
rs.updateRow();
// Do more stuff with this record
}
}
The main drawback of this approach is the fact that the database has to maintain a scrollable cursor, whose records are locked one by one. This can cause a major risk of deadlocks or race conditions if the JDBC driver can recover from the unsuccessful locking, if two Java threads execute the following statements:
-- thread 1 SELECT * FROM author ORDER BY id ASC; -- thread 2 SELECT * FROM author ORDER BY id DESC;
So use this technique with care, possibly only ever locking single rows!
Pessimistic (shared) locking with the FOR SHARE clause
Some databases (MySQL, Postgres) also allow to issue a non-exclusive lock explicitly using a FOR SHARE clause. This is also supported by jOOQ
Optimistic locking in jOOQ
Note, that jOOQ also supports optimistic locking, if you're doing simple CRUD. This is documented in the section's manual about optimistic locking.
Feedback
Do you have any feedback about this page? We'd love to hear it!