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

The question:

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.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.

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 Comment