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



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



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2