2

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.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
ConanTheGerbil
  • 1,155
  • 4
  • 26
  • 43

2 Answers2

5

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
         );

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.)

Related:

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
3

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.