1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
5 >Data Consistency Checks at the Application Level</TITLE
8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
12 TITLE="PostgreSQL 7.4.1 Documentation"
13 HREF="index.html"><LINK
15 TITLE="Concurrency Control"
16 HREF="mvcc.html"><LINK
18 TITLE="Explicit Locking"
19 HREF="explicit-locking.html"><LINK
21 TITLE="Locking and Indexes"
22 HREF="locking-indexes.html"><LINK
25 HREF="stylesheet.css"><META
27 CONTENT="2003-12-22T03:48:47"></HEAD
33 SUMMARY="Header navigation table"
43 >PostgreSQL 7.4.1 Documentation</TH
51 HREF="explicit-locking.html"
67 >Chapter 12. Concurrency Control</TD
81 HREF="locking-indexes.html"
95 NAME="APPLEVEL-CONSISTENCY"
96 >12.4. Data Consistency Checks at the Application Level</A
99 > Because readers in <SPAN
103 do not lock data, regardless of
104 transaction isolation level, data read by one transaction can be
105 overwritten by another concurrent transaction. In other words,
106 if a row is returned by <TT
109 > it doesn't mean that
110 the row is still current at the instant it is returned (i.e., sometime
111 after the current query began). The row might have been modified or
112 deleted by an already-committed transaction that committed after this one
114 Even if the row is still valid <SPAN
117 >, it could be changed or
119 before the current transaction does a commit or rollback.
122 > Another way to think about it is that each
123 transaction sees a snapshot of the database contents, and concurrently
124 executing transactions may very well see different snapshots. So the
125 whole concept of <SPAN
128 > is somewhat suspect anyway.
130 a big problem if the client applications are isolated from each other,
131 but if the clients can communicate via channels outside the database
132 then serious confusion may ensue.
135 > To ensure the current validity of a row and protect it against
136 concurrent updates one must use <TT
140 > or an appropriate <TT
146 >SELECT FOR UPDATE</TT
148 returned rows against concurrent updates, while <TT
152 > locks the whole table.) This should be taken into
153 account when porting applications to
157 > from other environments.
158 (Before version 6.5 <SPAN
162 read locks, and so this above consideration is also relevant when
170 > Global validity checks require extra thought under <ACRONYM
174 example, a banking application might wish to check that the sum of
175 all credits in one table equals the sum of debits in another table,
176 when both tables are being actively updated. Comparing the results of two
180 > commands will not work reliably under
181 Read Committed mode, since the second query will likely include the results
182 of transactions not counted by the first. Doing the two sums in a
183 single serializable transaction will give an accurate picture of the
184 effects of transactions that committed before the serializable transaction
185 started --- but one might legitimately wonder whether the answer is still
186 relevant by the time it is delivered. If the serializable transaction
187 itself applied some changes before trying to make the consistency check,
188 the usefulness of the check becomes even more debatable, since now it
189 includes some but not all post-transaction-start changes. In such cases
190 a careful person might wish to lock all tables needed for the check,
191 in order to get an indisputable picture of current reality. A
195 > mode (or higher) lock guarantees that there are no
196 uncommitted changes in the locked table, other than those of the current
200 > Note also that if one is
201 relying on explicit locks to prevent concurrent changes, one should use
202 Read Committed mode, or in Serializable mode be careful to obtain the
203 lock(s) before performing queries. An explicit lock obtained in a
204 serializable transaction guarantees that no other transactions modifying
205 the table are still running, but if the snapshot seen by the
206 transaction predates obtaining the lock, it may predate some now-committed
207 changes in the table. A serializable transaction's snapshot is actually
208 frozen at the start of its first query or data-modification command
223 it's possible to obtain explicit locks before the snapshot is
232 SUMMARY="Footer navigation table"
243 HREF="explicit-locking.html"
261 HREF="locking-indexes.html"
271 >Explicit Locking</TD
285 >Locking and Indexes</TD