All we need is an easy explanation of the problem, so here it is.
Is it possible to write an
UPDATE query that will simply quit if the record it tries to change is locked by another process (rather than waiting for the lock to be released)?
I have a process that should update records in a table, occasionally these records are locked. Updating these records is desirable, but not essential. If the records are in use I’d rather my process just forgot about the update and moved on to something more important.
My current approach is to set the command timeout to 1 second, but even this is longer than I’d like to wait – a normal update takes a fraction of a millisecond, so waiting a second is a major overhead.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
SKIP LOCKED might serve you best.
UPDATE tbl SET unimportant_column = 'foo' WHERE tbl_id = ( SELECT tbl_id FROM tbl WHERE tbl_id = 3 -- filtering a single row FOR UPDATE SKIP LOCKED );
To prevent the operation from waiting for other transactions to commit, use either the
SKIP LOCKEDoption. With
NOWAIT, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately. With
SKIP LOCKED, any selected rows that cannot be immediately locked are skipped.
In your case, the
SELECT filters exactly one row. If that is locked it is skipped, and nothing happens.
The essential difference to
NOWAIT: no error is raised, the operation is skipped silently. That’s probably best for your case.
You can also update multiple rows this way (with
IN instead of
=). Any locked rows are skipped, the rest is updated. With
NO WAIT, the whole
UPDATE would be prevented by the raised error. (And the whole transaction rolled back unless you catch the exception.)
You could lock the row manually before doing the update using:
select ... from the_table where ... for update nowait
That will throw an error if the lock can’t be obtained.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂