我的书签
添加书签
移除书签6.1.12. WITH LOCK
Available in
DSQL, PSQL
Used for
Limited pessimistic locking
Description:
WITH LOCK provides a limited explicit pessimistic locking capability for cautious use in conditions where the affected row set is:
extremely small (ideally, a singleton), and
precisely controlled by the application code.
This is for experts only! The need for a pessimistic lock in Firebird is very rare indeed and should be well understood before use of this extension is considered. It is essential to understand the effects of transaction isolation and other transaction attributes before attempting to implement explicit locking in your application. |
Syntax
SELECT ... FROM single_table[WHERE ...][FOR UPDATE [OF ...]]WITH LOCK
If the WITH LOCK clause succeeds, it will secure a lock on the selected rows and prevent any other transaction from obtaining write access to any of those rows, or their dependants, until your transaction ends.
WITH LOCK can only be used with a top-level, single-table SELECT statement. It is not available:
in a subquery specification
for joined sets
with the
DISTINCToperator, aGROUP BYclause or any other aggregating operationwith a view
with the output of a selectable stored procedure
with an external table
with a
UNIONquery
As the engine considers, in turn, each record falling under an explicit lock statement, it returns either the record version that is the most currently committed, regardless of database state when the statement was submitted, or an exception.
Wait behaviour and conflict reporting depend on the transaction parameters specified in the TPB block:
| TPB mode | Behaviour |
|---|---|
isc_tpb_consistency | Explicit locks are overridden by implicit or explicit table-level locks and are ignored. |
isc_tpb_concurrency + isc_tpb_nowait | If a record is modified by any transaction that was committed since the transaction attempting to get explicit lock started, or an active transaction has performed a modification of this record, an update conflict exception is raised immediately. |
isc_tpb_concurrency + isc_tpb_wait | If the record is modified by any transaction that has committed since the transaction attempting to get explicit lock started, an update conflict exception is raised immediately. If an active transaction is holding ownership on this record (via explicit locking or by a normal optimistic write-lock) the transaction attempting the explicit lock waits for the outcome of the blocking transaction and, when it finishes, attempts to get the lock on the record again. This means that, if the blocking transaction committed a modified version of this record, an update conflict exception will be raised. |
isc_tpb_read_committed + isc_tpb_nowait | If there is an active transaction holding ownership on this record (via explicit locking or normal update), an update conflict exception is raised immediately. |
isc_tpb_read_committed + isc_tpb_wait | If there is an active transaction holding ownership on this record (via explicit locking or by a normal optimistic write-lock), the transaction attempting the explicit lock waits for the outcome of blocking transaction and when it finishes, attempts to get the lock on the record again. Update conflict exceptions can never be raised by an explicit lock statement in this TPB mode. |
Usage with a FOR UPDATE Clause
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.
As an alternative, it may be possible in your access components to set the size of the fetch buffer to 1. This would enable you to process the currently-locked row before the next is fetched and locked, or to handle errors without rolling back your transaction. |
OF <column-names>This optional sub-clause does nothing at all. |
See also
How the engine deals with WITH LOCK
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 gdscodes are returned from conflicts involving pessimistic locks.
The engine guarantees that all records returned by an explicit lock statement are actually 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.
This situation can arise if other, parallel transactions commit their changes during the course of the locking statement’s execution. |
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, where an error occurred.
Caveats using WITH LOCK
Rolling back of an implicit or explicit savepoint releases record locks that were taken under that savepoint, but it doesn’t notify waiting transactions. Applications should not depend on this behaviour as it may get changed in the future.
While explicit locks can be used to prevent and/or handle unusual update conflict errors, the volume of deadlock errors will grow unless you design your locking strategy carefully and control it rigorously.
Most applications do not need explicit locks at all. The main purposes of explicit locks are:
to prevent expensive handling of update conflict errors in heavily loaded applications, and
to maintain integrity of objects mapped to a relational database in a clustered environment.
If your use of explicit locking doesn’t fall in one of these two categories, then it’s the wrong way to do the task in Firebird.
Explicit locking is an advanced feature; do not misuse it! While solutions for these kinds of problems may be very important for web sites handling thousands of concurrent writers, or for ERP/CRM systems operating in large corporations, most application programs do not need to work in such conditions.
Examples using explicit locking
Simple:
SELECT * FROM DOCUMENT WHERE ID=? WITH LOCK;
Multiple rows, one-by-one processing with DSQL cursor:
SELECT * FROM DOCUMENT WHERE PARENT_ID=?FOR UPDATE WITH LOCK;
