Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
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();
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!