We have 3 non-transactional getById methods for a single id in PostgresClient:
Add a transactional variant for each of them where the first parameter is "AsyncResult<SQLConnection> conn".
Also add a transactional variant with "FOR UPDATE" for each of them. The name should be getByIdAsStringForUpdate or getByIdForUpdate. It locks the table as described on https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE .
If there are several update requests for the same record "FOR UPDATE" will make the database queue them up so that the transactions happen in sequence. Without "FOR UPDATE" one transaction will succeed and all others will fail (rollback).
This has been requested on https://wiki.folio.org/display/FOLIJET/Working+with+transactions+in+modules+based+on+RMB
It is one possible solution to prevent update conflicts (
One example where "FOR UPDATE" is needed is check-out using an RFID reader that can read a batch of items at once. For each item there is a check that the number of checked-out items doesn't exceed the limit for the patron. Each loan increases the number of checked-out items. The second item should wait unil the first item's transaction has finished before reading the number of checked-out items.