diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-16 19:46:48 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-16 19:46:48 +0000 |
| commit | 311bcfc6b3acdd6fd152798c7f287ddf74fa2a98 (patch) | |
| tree | 0ec307299b1dada3701e42f4ca6eda57d708261e /doc/src/sgml/html/applevel-consistency.html | |
| parent | Initial commit. (diff) | |
| download | postgresql-15-311bcfc6b3acdd6fd152798c7f287ddf74fa2a98.tar.xz postgresql-15-311bcfc6b3acdd6fd152798c7f287ddf74fa2a98.zip | |
Adding upstream version 15.4.upstream/15.4upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/applevel-consistency.html')
| -rw-r--r-- | doc/src/sgml/html/applevel-consistency.html | 114 |
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..1e43f83 --- /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-serialization-failure-handling.html" title="13.5. Serialization Failure Handling" /></head><body id="docContent" class="container-fluid col-10"><div 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 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="mvcc-serialization-failure-handling.html" title="13.5. Serialization Failure Handling">Next</a></td></tr></table><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>, <code class="literal">DELETE</code>, or + <code class="literal">MERGE</code>), so it is possible to obtain locks explicitly + before the snapshot is frozen. + </p></div></div><div class="navfooter"><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-serialization-failure-handling.html" title="13.5. Serialization Failure Handling">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 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 13.5. Serialization Failure Handling</td></tr></table></div></body></html>
\ No newline at end of file |