micronaut-projects / micronaut-data

Ahead of Time Data Repositories
Apache License 2.0
459 stars 196 forks source link

Add "NOWAIT" and "SKIP LOCKED" support to SELECT FOR UPDATE style query finder methods #2942

Open mshannongit opened 1 month ago

mshannongit commented 1 month ago

Feature description

currently to lock rows one can trigger a statement like the following findTop5ByDepartmentEqualsForUpdate

however with concurrent threads wanting to run the same query, the second thread is blocked as it is trying to update potentially the same rows

to avoid this, one can use the SELECT FOR UPDATE SKIP LOCKED variant, which would allow the second thread to get a second set of non-locked rows; We need support in Micronaut Data thus for a query finder method like ... findTop5ByDepartmentEqualsForUpdateSkipLocked

Similarly, we may want to quickly fail if rows are already locked, in which case we can use SELECT FOR UPDATE NOWAIT functionality. findByNameEqualsForUpdateNoWait

dstepanov commented 1 month ago

We might want to support it using the @QueryHint