Debian Codegraph @

doc/sql.extensions/README.explicit_locks

raw
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