Usage With A

If the FOR UPDATE sub-clause precedes the WITH LOCK sub-clause, buffered fetches are suppressed.Thus, the lock will be applied to each row, one by one, at the moment it is fetched.It becomes possible, then, that a lock which appeared to succeed when requested will nevertheless fail subsequently, when an attempt is made to fetch a row which has become locked by another transaction in the meantime.This can be avoided by also using SKIP LOCKED.

FOR UPDATE [OF]

When an UPDATE statement tries to access a record that is locked by another transaction, it either raises an update conflict exception or waits for the locking transaction to finish, depending on TPB mode.Engine behaviour here is the same as if this record had already been modified by the locking transaction.

No special error codes are returned from conflicts involving pessimistic locks.

The engine guarantees that all records returned by an explicit lock statement are locked and do meet the search conditions specified in WHERE clause, as long as the search conditions do not depend on any other tables, via joins, subqueries, etc.It also guarantees that rows not meeting the search conditions will not be locked by the statement.It can not guarantee that there are no rows which, though meeting the search conditions, are not locked.

The engine locks rows at fetch time.This has important consequences if you lock several rows at once.Many access methods for Firebird databases default to fetching output in packets of a few hundred rows (“buffered fetches”).Most data access components cannot bring you the rows contained in the last-fetched packet, when an error occurred.