This topic describes the FOR UPDATE clause.

Syntax

FOR UPDATE [WAIT n|NOWAIT|SKIP LOCKED]

Description

The FOR UPDATE clause locks the rows that are retrieved by the SELECT statement as though for an update. This prevents a row from being modified or deleted by other transactions until the current transaction ends. A transaction that attempts to run the UPDATE, DELETE, or SELECT FOR UPDATE command on a selected row is blocked until the current transaction ends. If an UPDATE, DELETE, or SELECT FOR UPDATE command from another transaction has locked one or more selected rows, SELECT FOR UPDATE waits for the previous transaction to complete. Then, SELECT FOR UPDATE locks and returns the updated rows. If the rows are deleted, SELECT FOR UPDATE locks and returns no rows.

FOR UPDATE can be used in the context where returned rows can be clearly identified by individual table rows (such as aggregation).

You can use FOR UPDATE options to specify locking preferences.

  • Include the WAIT n keywords to specify the number of seconds (or fractional seconds) that the SELECT statement waits for a row that is locked by another session. Use a decimal form to specify fractional seconds. For example, WAIT 1.5 instructs the server to wait 1.5 seconds. You can specify a maximum of four digits to the right of the decimal point.
  • Include the NOWAIT keyword to immediately report an error if a row cannot be locked by the current session.
  • Include SKIP LOCKED to instruct the server to lock rows if possible, and skip rows that have been locked by another session.