1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>12.4.Data Consistency Checks at the Application Level</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="mvcc.html" title="Chapter12.Concurrency Control">
<link rel="prev" href="explicit-locking.html" title="12.3.Explicit Locking">
<link rel="next" href="locking-indexes.html" title="12.5.Locking and Indexes">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="applevel-consistency"></a>12.4.Data Consistency Checks at the Application Level</h2></div></div></div>
<p> Because readers in <span class="productname">PostgreSQL</span>
do not lock data, regardless of
transaction isolation level, data read by one transaction can be
overwritten by another concurrent transaction. In other words,
if a row is returned by <code class="command">SELECT</code> it doesn't mean that
the row is still current at the instant it is returned (i.e., sometime
after the current query began). The row might have been modified or
deleted by an already-committed transaction that committed after this one
started.
Even if the row is still valid “<span class="quote">now</span>”, it could be changed or
deleted
before the current transaction does a commit or rollback.
</p>
<p> Another way to think about it is that each
transaction sees a snapshot of the database contents, and concurrently
executing transactions may very well see different snapshots. So the
whole concept of “<span class="quote">now</span>” is somewhat ill-defined anyway.
This is not normally
a big problem if the client applications are isolated from each other,
but if the clients can communicate via channels outside the database
then serious confusion may ensue.
</p>
<p> To ensure the current validity of a row and protect it against
concurrent updates one must use <code class="command">SELECT FOR UPDATE</code>,
<code class="command">SELECT FOR SHARE</code>, or an appropriate <code class="command">LOCK
TABLE</code> statement. (<code class="command">SELECT FOR UPDATE</code>
or <code class="command">SELECT FOR SHARE</code> locks just the
returned rows against concurrent updates, while <code class="command">LOCK
TABLE</code> locks the whole table.) This should be taken into
account when porting applications to
<span class="productname">PostgreSQL</span> from other environments.
(Before version 6.5 <span class="productname">PostgreSQL</span> used
read locks, and so this above consideration is also relevant when
upgrading from <span class="productname">PostgreSQL</span> versions
prior to 6.5.)
</p>
<p> Global validity checks require extra thought under <acronym class="acronym">MVCC</acronym>.
For example, a banking application might wish to check that the sum of
all credits in one table equals the sum of debits in another table,
when both tables are being actively updated. Comparing the results of two
successive <code class="literal">SELECT sum(...)</code> commands will not work reliably under
Read Committed mode, since the second query will likely include the results
of transactions not counted by the first. Doing the two sums in a
single serializable transaction will give an accurate picture of the
effects of transactions that committed before the serializable transaction
started [mdash ] but one might legitimately wonder whether the answer is still
relevant by the time it is delivered. If the serializable transaction
itself applied some changes before trying to make the consistency check,
the usefulness of the check becomes even more debatable, since now it
includes some but not all post-transaction-start changes. In such cases
a careful person might wish to lock all tables needed for the check,
in order to get an indisputable picture of current reality. A
<code class="literal">SHARE</code> mode (or higher) lock guarantees that there are no
uncommitted changes in the locked table, other than those of the current
transaction.
</p>
<p> Note also that if one is
relying on explicit locking to prevent concurrent changes, one should use
Read Committed mode, or in Serializable mode be careful to obtain the
lock(s) before performing queries. A lock obtained by a
serializable transaction guarantees that no other transactions modifying
the table are still running, but if the snapshot seen by the
transaction predates obtaining the lock, it may predate some now-committed
changes in the table. A serializable transaction's snapshot is actually
frozen at the start of its first query or data-modification command
(<code class="literal">SELECT</code>, <code class="literal">INSERT</code>,
<code class="literal">UPDATE</code>, or <code class="literal">DELETE</code>), so
it's possible to obtain locks explicitly before the snapshot is
frozen.
</p>
</div></body>
</html>
|
