Writing a query that will quit if there's a lock on a target row

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.

Method 1

SKIP LOCKED might serve you best.

SET    unimportant_column = 'foo'
WHERE  tbl_id = (
         SELECT tbl_id
         FROM   tbl
         WHERE  tbl_id = 3  -- filtering a single row

The manual:

To prevent the operation from waiting for other transactions to commit, use either the NOWAIT or SKIP LOCKED option. 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.)


Method 2

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply