| 1 |
SQL Language Extension: WITH LOCK clause in the SELECT statement |
| 2 |
|
| 3 |
Syntax |
| 4 |
======== |
| 5 |
|
| 6 |
SELECT ... FROM <sometable> [WHERE ...] [FOR UPDATE [OF ...]] WITH LOCK; |
| 7 |
|
| 8 |
The WITH LOCK clause lets you lock the selected rows so that |
| 9 |
other users cannot lock or update the rows until you end your |
| 10 |
transaction. You can specify this clause only in a top-level |
| 11 |
SELECT statement (not in subqueries). This clause can be used |
| 12 |
in DSQL and PSQL. |
| 13 |
|
| 14 |
Restrictions: |
| 15 |
- You cannot specify this clause with the following other constructs: |
| 16 |
the DISTINCT operator, group_by_clause, or aggregate functions. |
| 17 |
- from_clause of SELECT statement must specify single database table |
| 18 |
(views and external tables are not allowed) |
| 19 |
|
| 20 |
Author: |
| 21 |
Nickolay Samofatov <[email protected]> |
| 22 |
|
| 23 |
|
| 24 |
N O T E S |
| 25 |
========= |
| 26 |
|
| 27 |
When engine processes each record falling under explicit lock |
| 28 |
statement it either returns most current committed record version |
| 29 |
or raises exception. |
| 30 |
|
| 31 |
Wait behaviour and conflict reporting depends on the transaction |
| 32 |
parameters specified in the TPB block. |
| 33 |
|
| 34 |
TPB mode Behavior |
| 35 |
------------------------------------------------------------- |
| 36 |
isc_tpb_consistency Explicit locks are overriden by |
| 37 |
implicit or explicit table-level locks |
| 38 |
and are ignored |
| 39 |
|
| 40 |
isc_tpb_concurrency + If record is modified by any committed |
| 41 |
isc_tpb_nowait transaction since transaction attempting |
| 42 |
to get explicit lock started or there is |
| 43 |
active transaction that performed |
| 44 |
modifiction of this record, update |
| 45 |
conflict exception is raised immediately |
| 46 |
|
| 47 |
isc_tpb_concurrency + If record is modified by any committed |
| 48 |
isc_tpb_wait transaction since transaction attempting |
| 49 |
to get explicit lock started, update conflict |
| 50 |
exception is raised immediately. |
| 51 |
If there is active transaction holding ownership |
| 52 |
on this record (via explicit lock or normal update) |
| 53 |
locking transaction waits for outcome of |
| 54 |
blocking transation and when it finishes |
| 55 |
attempts to get lock on record again. |
| 56 |
This means that if blocking transaction |
| 57 |
commits modification of this record, |
| 58 |
update conflict exception will be raised. |
| 59 |
|
| 60 |
isc_tpb_read_committed + If there is active transaction holding ownership |
| 61 |
isc_tpb_nowait on this record (via explicit lock or normal update), |
| 62 |
update conflict exception is raised immediately. |
| 63 |
|
| 64 |
isc_tpb_read_committed + If there is active transaction holding ownership |
| 65 |
isc_tpb_wait on this record (via explicit lock or normal update), |
| 66 |
locking transaction waits for outcome of |
| 67 |
blocking transation and when it finishes |
| 68 |
attempts to get lock on record again. |
| 69 |
Update conflict exceptions can never be raised |
| 70 |
by this kind of explicit lock statement. |
| 71 |
|
| 72 |
When UPDATE statement steps on a record that is locked by another transaction |
| 73 |
it either raises update conflict exception or waits for the end of locking |
| 74 |
transaction depending on TPB mode. Engine behaviour here is the same as if this |
| 75 |
record was already modified by locking transaction. |
| 76 |
|
| 77 |
Engine guaranties that all records returned by explicit lock statement |
| 78 |
are actually locked and DO fall under search condition specified in WHERE clause |
| 79 |
if this search condition depends only on a record to be returned (for example, it |
| 80 |
contains no subqueries reading mutable tables, etc). It also guaranties that |
| 81 |
no rows not falling under search condition are locked by the statement. It doesn't |
| 82 |
guaranty that there are no rows falling under search condition, but not locked |
| 83 |
(this may happen if other parallel transactions commit their changes during |
| 84 |
execution of locking statement). |
| 85 |
|
| 86 |
Engine locks rows at fetch time. This has important consequences if you lock |
| 87 |
several rows at once. By default, access methods for Firebird databases |
| 88 |
fetch results in packs of a few hundred rows. Most access components may not |
| 89 |
give you the rows contained in the last fetched packed where error happened. |
| 90 |
You may use FOR UPDATE clause to prevent usage of buffered fetches (and may |
| 91 |
use positioned updates feature) or set fetch buffer size to 1 in your access |
| 92 |
components in case you need to process locked row before next row is locked |
| 93 |
or if you want actually process all rows that can be locked before first row |
| 94 |
producing error. |
| 95 |
|
| 96 |
Commit and rollback retaining release explicit and implicit locks |
| 97 |
exactly as normal commit and rollback. Be careful when using buffered |
| 98 |
fetches and retaining operations. Records contained in the client-side |
| 99 |
buffer may be unlocked even before returned to the application. |
| 100 |
|
| 101 |
Rolling back of implicit or explicit savepoint releases record locks that |
| 102 |
were taken under this savepoint, but doesn't notify waiting transactions. |
| 103 |
Applications should not depend on this behaviour as it may get changed in |
| 104 |
the future. |
| 105 |
|
| 106 |
While explicit locks can be used to prevent and/or handle update conflict |
| 107 |
errors amount of deadlock errors will grow unless you carefully design your |
| 108 |
locking strategy. Most applications do not need explicit locks at all. The main |
| 109 |
purposes of explicit locks are (1) to prevent expensive handling of update |
| 110 |
conflict errors in heavily loaded applications and (2) to maintain integrity |
| 111 |
of objects mapped to relational database in clustered environment. |
| 112 |
While solutions for this problems may be very important for web sites |
| 113 |
handling thousands of concurrent users or ERP/CRM solutions |
| 114 |
working in large corporations most application programs do not |
| 115 |
need to work in such conditions. Explicit locking is an advanced feature, |
| 116 |
do not misuse it ! If your use of explicit locking doesn't fall in |
| 117 |
one of two categories above think of another way to do the task. |
| 118 |
|
| 119 |
Examples: |
| 120 |
|
| 121 |
A) (simple) |
| 122 |
SELECT * FROM DOCUMENT WHERE ID=? WITH LOCK |
| 123 |
|
| 124 |
B) (multiple rows, one-by-one processing with DSQL cursor) |
| 125 |
SELECT * FROM DOCUMENT WHERE PARENT_ID=? FOR UPDATE WITH LOCK |