summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/applevel-consistency.html
diff options
context:
space:
mode:
Diffstat (limited to 'doc/src/sgml/html/applevel-consistency.html')
-rw-r--r--doc/src/sgml/html/applevel-consistency.html114
1 files changed, 114 insertions, 0 deletions
diff --git a/doc/src/sgml/html/applevel-consistency.html b/doc/src/sgml/html/applevel-consistency.html
new file mode 100644
index 0000000..c885444
--- /dev/null
+++ b/doc/src/sgml/html/applevel-consistency.html
@@ -0,0 +1,114 @@
+<?xml version="1.0" encoding="UTF-8" standalone="no"?>
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>13.4. Data Consistency Checks at the Application Level</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="explicit-locking.html" title="13.3. Explicit Locking" /><link rel="next" href="mvcc-caveats.html" title="13.5. Caveats" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">13.4. Data Consistency Checks at the Application Level</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="explicit-locking.html" title="13.3. Explicit Locking">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="mvcc.html" title="Chapter 13. Concurrency Control">Up</a></td><th width="60%" align="center">Chapter 13. Concurrency Control</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="mvcc-caveats.html" title="13.5. Caveats">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="APPLEVEL-CONSISTENCY"><div class="titlepage"><div><div><h2 class="title" style="clear: both">13.4. Data Consistency Checks at the Application Level</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="applevel-consistency.html#SERIALIZABLE-CONSISTENCY">13.4.1. Enforcing Consistency with Serializable Transactions</a></span></dt><dt><span class="sect2"><a href="applevel-consistency.html#NON-SERIALIZABLE-CONSISTENCY">13.4.2. Enforcing Consistency with Explicit Blocking Locks</a></span></dt></dl></div><p>
+ It is very difficult to enforce business rules regarding data integrity
+ using Read Committed transactions because the view of the data is
+ shifting with each statement, and even a single statement may not
+ restrict itself to the statement's snapshot if a write conflict occurs.
+ </p><p>
+ While a Repeatable Read transaction has a stable view of the data
+ throughout its execution, there is a subtle issue with using
+ <acronym class="acronym">MVCC</acronym> snapshots for data consistency checks, involving
+ something known as <em class="firstterm">read/write conflicts</em>.
+ If one transaction writes data and a concurrent transaction attempts
+ to read the same data (whether before or after the write), it cannot
+ see the work of the other transaction. The reader then appears to have
+ executed first regardless of which started first or which committed
+ first. If that is as far as it goes, there is no problem, but
+ if the reader also writes data which is read by a concurrent transaction
+ there is now a transaction which appears to have run before either of
+ the previously mentioned transactions. If the transaction which appears
+ to have executed last actually commits first, it is very easy for a
+ cycle to appear in a graph of the order of execution of the transactions.
+ When such a cycle appears, integrity checks will not work correctly
+ without some help.
+ </p><p>
+ As mentioned in <a class="xref" href="transaction-iso.html#XACT-SERIALIZABLE" title="13.2.3. Serializable Isolation Level">Section 13.2.3</a>, Serializable
+ transactions are just Repeatable Read transactions which add
+ nonblocking monitoring for dangerous patterns of read/write conflicts.
+ When a pattern is detected which could cause a cycle in the apparent
+ order of execution, one of the transactions involved is rolled back to
+ break the cycle.
+ </p><div class="sect2" id="SERIALIZABLE-CONSISTENCY"><div class="titlepage"><div><div><h3 class="title">13.4.1. Enforcing Consistency with Serializable Transactions</h3></div></div></div><p>
+ If the Serializable transaction isolation level is used for all writes
+ and for all reads which need a consistent view of the data, no other
+ effort is required to ensure consistency. Software from other
+ environments which is written to use serializable transactions to
+ ensure consistency should <span class="quote">“<span class="quote">just work</span>”</span> in this regard in
+ <span class="productname">PostgreSQL</span>.
+ </p><p>
+ When using this technique, it will avoid creating an unnecessary burden
+ for application programmers if the application software goes through a
+ framework which automatically retries transactions which are rolled
+ back with a serialization failure. It may be a good idea to set
+ <code class="literal">default_transaction_isolation</code> to <code class="literal">serializable</code>.
+ It would also be wise to take some action to ensure that no other
+ transaction isolation level is used, either inadvertently or to
+ subvert integrity checks, through checks of the transaction isolation
+ level in triggers.
+ </p><p>
+ See <a class="xref" href="transaction-iso.html#XACT-SERIALIZABLE" title="13.2.3. Serializable Isolation Level">Section 13.2.3</a> for performance suggestions.
+ </p><div class="warning"><h3 class="title">Warning</h3><p>
+ This level of integrity protection using Serializable transactions
+ does not yet extend to hot standby mode (<a class="xref" href="hot-standby.html" title="27.4. Hot Standby">Section 27.4</a>).
+ Because of that, those using hot standby may want to use Repeatable
+ Read and explicit locking on the primary.
+ </p></div></div><div class="sect2" id="NON-SERIALIZABLE-CONSISTENCY"><div class="titlepage"><div><div><h3 class="title">13.4.2. Enforcing Consistency with Explicit Blocking Locks</h3></div></div></div><p>
+ When non-serializable writes are possible,
+ 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>
+ and <code class="command">SELECT FOR SHARE</code> lock 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.
+ </p><p>
+ Also of note to those converting from other environments is the fact
+ that <code class="command">SELECT FOR UPDATE</code> does not ensure that a
+ concurrent transaction will not update or delete a selected row.
+ To do that in <span class="productname">PostgreSQL</span> you must actually
+ update the row, even if no values need to be changed.
+ <code class="command">SELECT FOR UPDATE</code> <span class="emphasis"><em>temporarily blocks</em></span>
+ other transactions from acquiring the same lock or executing an
+ <code class="command">UPDATE</code> or <code class="command">DELETE</code> which would
+ affect the locked row, but once the transaction holding this lock
+ commits or rolls back, a blocked transaction will proceed with the
+ conflicting operation unless an actual <code class="command">UPDATE</code> of
+ the row was performed while the lock was held.
+ </p><p>
+ Global validity checks require extra thought under
+ non-serializable <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 in
+ 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 repeatable read transaction will give an accurate picture of only the
+ effects of transactions that committed before the repeatable read transaction
+ started — but one might legitimately wonder whether the answer is still
+ relevant by the time it is delivered. If the repeatable read 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 either use Read Committed mode, or in Repeatable Read
+ mode be careful to obtain
+ locks before performing queries. A lock obtained by a
+ repeatable read 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 might predate some now-committed
+ changes in the table. A repeatable read 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 is possible to obtain locks explicitly before the snapshot is
+ frozen.
+ </p></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="explicit-locking.html" title="13.3. Explicit Locking">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="mvcc.html" title="Chapter 13. Concurrency Control">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="mvcc-caveats.html" title="13.5. Caveats">Next</a></td></tr><tr><td width="40%" align="left" valign="top">13.3. Explicit Locking </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 13.5. Caveats</td></tr></table></div></body></html> \ No newline at end of file