On Fri, 2022-05-20 at 15:43 +1200, Tim Uckun wrote:
> I have multiple processes connecting to the same database. In order to
> prevent race conditions the I would like the process to issue a
> SELECT FOR UPDATE to lock the records that need to be processed.
>
> The problem is that the locks are not inside of a single translation.
> The process is multi step. Records are selected and presented to the
> user, the user does some stuff and submits a form, the submitted form
> updates those records and frees the lock.
>
> Ideally I would do a select for update when the form is shown to the
> user but with an expiry time so that if the user decides to move on
> and not submit any changes the locks will expire.
>
> I could do this with a locked_at field that I can update on select but
> I would prefer to let postgres handle the locks if possible.
The customary solution to this is to use optimistic locking:
During the UPDATE, you check if a concurrent transaction modified
the row since you read it, and if yes, you redo the whole operation.
That could look like:
SELECT id, col_to_change, othercol1, othercol2
FROM tab
WHERE id = 42;
[user interaction]
UPDATE tab
SET col_to_change = 'newvalue'
WHERE id = 42
AND othercol1 = 'original value'
AND othercol2 = 'original value';
If the UPDATE modifies no row, something must have changed, and you
redo the whole operation.
The advantage is that you don't have to keep a transaction open.
(Regular) long transactions are a big problem in relational databases.
In PostgreSQL you could use the system columns "ctid" and "xmin"
to check if the row changed, so you don't have to fetch and check
all columns.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com