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.
