summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/transaction-iso.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:19:15 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:19:15 +0000
commit6eb9c5a5657d1fe77b55cc261450f3538d35a94d (patch)
tree657d8194422a5daccecfd42d654b8a245ef7b4c8 /doc/src/sgml/html/transaction-iso.html
parentInitial commit. (diff)
downloadpostgresql-13-upstream.tar.xz
postgresql-13-upstream.zip
Adding upstream version 13.4.upstream/13.4upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
-rw-r--r--doc/src/sgml/html/transaction-iso.html508
1 files changed, 508 insertions, 0 deletions
diff --git a/doc/src/sgml/html/transaction-iso.html b/doc/src/sgml/html/transaction-iso.html
new file mode 100644
index 0000000..5934229
--- /dev/null
+++ b/doc/src/sgml/html/transaction-iso.html
@@ -0,0 +1,508 @@
+<?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.2. Transaction Isolation</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 V1.79.1" /><link rel="prev" href="mvcc-intro.html" title="13.1. Introduction" /><link rel="next" href="explicit-locking.html" title="13.3. Explicit Locking" /></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.2. Transaction Isolation</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="mvcc-intro.html" title="13.1. Introduction">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 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="explicit-locking.html" title="13.3. Explicit Locking">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="TRANSACTION-ISO"><div class="titlepage"><div><div><h2 class="title" style="clear: both">13.2. Transaction Isolation</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="transaction-iso.html#XACT-READ-COMMITTED">13.2.1. Read Committed Isolation Level</a></span></dt><dt><span class="sect2"><a href="transaction-iso.html#XACT-REPEATABLE-READ">13.2.2. Repeatable Read Isolation Level</a></span></dt><dt><span class="sect2"><a href="transaction-iso.html#XACT-SERIALIZABLE">13.2.3. Serializable Isolation Level</a></span></dt></dl></div><a id="id-1.5.12.5.2" class="indexterm"></a><p>
+ The <acronym class="acronym">SQL</acronym> standard defines four levels of
+ transaction isolation. The most strict is Serializable,
+ which is defined by the standard in a paragraph which says that any
+ concurrent execution of a set of Serializable transactions is guaranteed
+ to produce the same effect as running them one at a time in some order.
+ The other three levels are defined in terms of phenomena, resulting from
+ interaction between concurrent transactions, which must not occur at
+ each level. The standard notes that due to the definition of
+ Serializable, none of these phenomena are possible at that level. (This
+ is hardly surprising -- if the effect of the transactions must be
+ consistent with having been run one at a time, how could you see any
+ phenomena caused by interactions?)
+ </p><p>
+ The phenomena which are prohibited at various levels are:
+
+ </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">
+ dirty read
+ <a id="id-1.5.12.5.4.1.1.1.1" class="indexterm"></a>
+ </span></dt><dd><p>
+ A transaction reads data written by a concurrent uncommitted transaction.
+ </p></dd><dt><span class="term">
+ nonrepeatable read
+ <a id="id-1.5.12.5.4.1.2.1.1" class="indexterm"></a>
+ </span></dt><dd><p>
+ A transaction re-reads data it has previously read and finds that data
+ has been modified by another transaction (that committed since the
+ initial read).
+ </p></dd><dt><span class="term">
+ phantom read
+ <a id="id-1.5.12.5.4.1.3.1.1" class="indexterm"></a>
+ </span></dt><dd><p>
+ A transaction re-executes a query returning a set of rows that satisfy a
+ search condition and finds that the set of rows satisfying the condition
+ has changed due to another recently-committed transaction.
+ </p></dd><dt><span class="term">
+ serialization anomaly
+ <a id="id-1.5.12.5.4.1.4.1.1" class="indexterm"></a>
+ </span></dt><dd><p>
+ The result of successfully committing a group of transactions
+ is inconsistent with all possible orderings of running those
+ transactions one at a time.
+ </p></dd></dl></div><p>
+ </p><p>
+ <a id="id-1.5.12.5.5.1" class="indexterm"></a>
+ The SQL standard and PostgreSQL-implemented transaction isolation levels
+ are described in <a class="xref" href="transaction-iso.html#MVCC-ISOLEVEL-TABLE" title="Table 13.1. Transaction Isolation Levels">Table 13.1</a>.
+ </p><div class="table" id="MVCC-ISOLEVEL-TABLE"><p class="title"><strong>Table 13.1. Transaction Isolation Levels</strong></p><div class="table-contents"><table class="table" summary="Transaction Isolation Levels" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>
+ Isolation Level
+ </th><th>
+ Dirty Read
+ </th><th>
+ Nonrepeatable Read
+ </th><th>
+ Phantom Read
+ </th><th>
+ Serialization Anomaly
+ </th></tr></thead><tbody><tr><td>
+ Read uncommitted
+ </td><td>
+ Allowed, but not in PG
+ </td><td>
+ Possible
+ </td><td>
+ Possible
+ </td><td>
+ Possible
+ </td></tr><tr><td>
+ Read committed
+ </td><td>
+ Not possible
+ </td><td>
+ Possible
+ </td><td>
+ Possible
+ </td><td>
+ Possible
+ </td></tr><tr><td>
+ Repeatable read
+ </td><td>
+ Not possible
+ </td><td>
+ Not possible
+ </td><td>
+ Allowed, but not in PG
+ </td><td>
+ Possible
+ </td></tr><tr><td>
+ Serializable
+ </td><td>
+ Not possible
+ </td><td>
+ Not possible
+ </td><td>
+ Not possible
+ </td><td>
+ Not possible
+ </td></tr></tbody></table></div></div><br class="table-break" /><p>
+ In <span class="productname">PostgreSQL</span>, you can request any of
+ the four standard transaction isolation levels, but internally only
+ three distinct isolation levels are implemented, i.e., PostgreSQL's
+ Read Uncommitted mode behaves like Read Committed. This is because
+ it is the only sensible way to map the standard isolation levels to
+ PostgreSQL's multiversion concurrency control architecture.
+ </p><p>
+ The table also shows that PostgreSQL's Repeatable Read implementation
+ does not allow phantom reads. Stricter behavior is permitted by the
+ SQL standard: the four isolation levels only define which phenomena
+ must not happen, not which phenomena <span class="emphasis"><em>must</em></span> happen.
+ The behavior of the available isolation levels is detailed in the
+ following subsections.
+ </p><p>
+ To set the transaction isolation level of a transaction, use the
+ command <a class="xref" href="sql-set-transaction.html" title="SET TRANSACTION"><span class="refentrytitle">SET TRANSACTION</span></a>.
+ </p><div class="important"><h3 class="title">Important</h3><p>
+ Some <span class="productname">PostgreSQL</span> data types and functions have
+ special rules regarding transactional behavior. In particular, changes
+ made to a sequence (and therefore the counter of a
+ column declared using <code class="type">serial</code>) are immediately visible
+ to all other transactions and are not rolled back if the transaction
+ that made the changes aborts. See <a class="xref" href="functions-sequence.html" title="9.17. Sequence Manipulation Functions">Section 9.17</a>
+ and <a class="xref" href="datatype-numeric.html#DATATYPE-SERIAL" title="8.1.4. Serial Types">Section 8.1.4</a>.
+ </p></div><div class="sect2" id="XACT-READ-COMMITTED"><div class="titlepage"><div><div><h3 class="title">13.2.1. Read Committed Isolation Level</h3></div></div></div><a id="id-1.5.12.5.11.2" class="indexterm"></a><a id="id-1.5.12.5.11.3" class="indexterm"></a><p>
+ <em class="firstterm">Read Committed</em> is the default isolation
+ level in <span class="productname">PostgreSQL</span>. When a transaction
+ uses this isolation level, a <code class="command">SELECT</code> query
+ (without a <code class="literal">FOR UPDATE/SHARE</code> clause) sees only data
+ committed before the query began; it never sees either uncommitted
+ data or changes committed during query execution by concurrent
+ transactions. In effect, a <code class="command">SELECT</code> query sees
+ a snapshot of the database as of the instant the query begins to
+ run. However, <code class="command">SELECT</code> does see the effects
+ of previous updates executed within its own transaction, even
+ though they are not yet committed. Also note that two successive
+ <code class="command">SELECT</code> commands can see different data, even
+ though they are within a single transaction, if other transactions
+ commit changes after the first <code class="command">SELECT</code> starts and
+ before the second <code class="command">SELECT</code> starts.
+ </p><p>
+ <code class="command">UPDATE</code>, <code class="command">DELETE</code>, <code class="command">SELECT
+ FOR UPDATE</code>, and <code class="command">SELECT FOR SHARE</code> commands
+ behave the same as <code class="command">SELECT</code>
+ in terms of searching for target rows: they will only find target rows
+ that were committed as of the command start time. However, such a target
+ row might have already been updated (or deleted or locked) by
+ another concurrent transaction by the time it is found. In this case, the
+ would-be updater will wait for the first updating transaction to commit or
+ roll back (if it is still in progress). If the first updater rolls back,
+ then its effects are negated and the second updater can proceed with
+ updating the originally found row. If the first updater commits, the
+ second updater will ignore the row if the first updater deleted it,
+ otherwise it will attempt to apply its operation to the updated version of
+ the row. The search condition of the command (the <code class="literal">WHERE</code> clause) is
+ re-evaluated to see if the updated version of the row still matches the
+ search condition. If so, the second updater proceeds with its operation
+ using the updated version of the row. In the case of
+ <code class="command">SELECT FOR UPDATE</code> and <code class="command">SELECT FOR
+ SHARE</code>, this means it is the updated version of the row that is
+ locked and returned to the client.
+ </p><p>
+ <code class="command">INSERT</code> with an <code class="literal">ON CONFLICT DO UPDATE</code> clause
+ behaves similarly. In Read Committed mode, each row proposed for insertion
+ will either insert or update. Unless there are unrelated errors, one of
+ those two outcomes is guaranteed. If a conflict originates in another
+ transaction whose effects are not yet visible to the <code class="command">INSERT
+ </code>, the <code class="command">UPDATE</code> clause will affect that row,
+ even though possibly <span class="emphasis"><em>no</em></span> version of that row is
+ conventionally visible to the command.
+ </p><p>
+ <code class="command">INSERT</code> with an <code class="literal">ON CONFLICT DO
+ NOTHING</code> clause may have insertion not proceed for a row due to
+ the outcome of another transaction whose effects are not visible
+ to the <code class="command">INSERT</code> snapshot. Again, this is only
+ the case in Read Committed mode.
+ </p><p>
+ Because of the above rules, it is possible for an updating command to see
+ an inconsistent snapshot: it can see the effects of concurrent updating
+ commands on the same rows it is trying to update, but it
+ does not see effects of those commands on other rows in the database.
+ This behavior makes Read Committed mode unsuitable for commands that
+ involve complex search conditions; however, it is just right for simpler
+ cases. For example, consider updating bank balances with transactions
+ like:
+
+</p><pre class="screen">
+BEGIN;
+UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
+UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
+COMMIT;
+</pre><p>
+
+ If two such transactions concurrently try to change the balance of account
+ 12345, we clearly want the second transaction to start with the updated
+ version of the account's row. Because each command is affecting only a
+ predetermined row, letting it see the updated version of the row does
+ not create any troublesome inconsistency.
+ </p><p>
+ More complex usage can produce undesirable results in Read Committed
+ mode. For example, consider a <code class="command">DELETE</code> command
+ operating on data that is being both added and removed from its
+ restriction criteria by another command, e.g., assume
+ <code class="literal">website</code> is a two-row table with
+ <code class="literal">website.hits</code> equaling <code class="literal">9</code> and
+ <code class="literal">10</code>:
+
+</p><pre class="screen">
+BEGIN;
+UPDATE website SET hits = hits + 1;
+-- run from another session: DELETE FROM website WHERE hits = 10;
+COMMIT;
+</pre><p>
+
+ The <code class="command">DELETE</code> will have no effect even though
+ there is a <code class="literal">website.hits = 10</code> row before and
+ after the <code class="command">UPDATE</code>. This occurs because the
+ pre-update row value <code class="literal">9</code> is skipped, and when the
+ <code class="command">UPDATE</code> completes and <code class="command">DELETE</code>
+ obtains a lock, the new row value is no longer <code class="literal">10</code> but
+ <code class="literal">11</code>, which no longer matches the criteria.
+ </p><p>
+ Because Read Committed mode starts each command with a new snapshot
+ that includes all transactions committed up to that instant,
+ subsequent commands in the same transaction will see the effects
+ of the committed concurrent transaction in any case. The point
+ at issue above is whether or not a <span class="emphasis"><em>single</em></span> command
+ sees an absolutely consistent view of the database.
+ </p><p>
+ The partial transaction isolation provided by Read Committed mode
+ is adequate for many applications, and this mode is fast and simple
+ to use; however, it is not sufficient for all cases. Applications
+ that do complex queries and updates might require a more rigorously
+ consistent view of the database than Read Committed mode provides.
+ </p></div><div class="sect2" id="XACT-REPEATABLE-READ"><div class="titlepage"><div><div><h3 class="title">13.2.2. Repeatable Read Isolation Level</h3></div></div></div><a id="id-1.5.12.5.12.2" class="indexterm"></a><a id="id-1.5.12.5.12.3" class="indexterm"></a><p>
+ The <em class="firstterm">Repeatable Read</em> isolation level only sees
+ data committed before the transaction began; it never sees either
+ uncommitted data or changes committed during transaction execution
+ by concurrent transactions. (However, the query does see the
+ effects of previous updates executed within its own transaction,
+ even though they are not yet committed.) This is a stronger
+ guarantee than is required by the <acronym class="acronym">SQL</acronym> standard
+ for this isolation level, and prevents all of the phenomena described
+ in <a class="xref" href="transaction-iso.html#MVCC-ISOLEVEL-TABLE" title="Table 13.1. Transaction Isolation Levels">Table 13.1</a> except for serialization
+ anomalies. As mentioned above, this is
+ specifically allowed by the standard, which only describes the
+ <span class="emphasis"><em>minimum</em></span> protections each isolation level must
+ provide.
+ </p><p>
+ This level is different from Read Committed in that a query in a
+ repeatable read transaction sees a snapshot as of the start of the
+ first non-transaction-control statement in the
+ <span class="emphasis"><em>transaction</em></span>, not as of the start
+ of the current statement within the transaction. Thus, successive
+ <code class="command">SELECT</code> commands within a <span class="emphasis"><em>single</em></span>
+ transaction see the same data, i.e., they do not see changes made by
+ other transactions that committed after their own transaction started.
+ </p><p>
+ Applications using this level must be prepared to retry transactions
+ due to serialization failures.
+ </p><p>
+ <code class="command">UPDATE</code>, <code class="command">DELETE</code>, <code class="command">SELECT
+ FOR UPDATE</code>, and <code class="command">SELECT FOR SHARE</code> commands
+ behave the same as <code class="command">SELECT</code>
+ in terms of searching for target rows: they will only find target rows
+ that were committed as of the transaction start time. However, such a
+ target row might have already been updated (or deleted or locked) by
+ another concurrent transaction by the time it is found. In this case, the
+ repeatable read transaction will wait for the first updating transaction to commit or
+ roll back (if it is still in progress). If the first updater rolls back,
+ then its effects are negated and the repeatable read transaction can proceed
+ with updating the originally found row. But if the first updater commits
+ (and actually updated or deleted the row, not just locked it)
+ then the repeatable read transaction will be rolled back with the message
+
+</p><pre class="screen">
+ERROR: could not serialize access due to concurrent update
+</pre><p>
+
+ because a repeatable read transaction cannot modify or lock rows changed by
+ other transactions after the repeatable read transaction began.
+ </p><p>
+ When an application receives this error message, it should abort
+ the current transaction and retry the whole transaction from
+ the beginning. The second time through, the transaction will see the
+ previously-committed change as part of its initial view of the database,
+ so there is no logical conflict in using the new version of the row
+ as the starting point for the new transaction's update.
+ </p><p>
+ Note that only updating transactions might need to be retried; read-only
+ transactions will never have serialization conflicts.
+ </p><p>
+ The Repeatable Read mode provides a rigorous guarantee that each
+ transaction sees a completely stable view of the database. However,
+ this view will not necessarily always be consistent with some serial
+ (one at a time) execution of concurrent transactions of the same level.
+ For example, even a read only transaction at this level may see a
+ control record updated to show that a batch has been completed but
+ <span class="emphasis"><em>not</em></span> see one of the detail records which is logically
+ part of the batch because it read an earlier revision of the control
+ record. Attempts to enforce business rules by transactions running at
+ this isolation level are not likely to work correctly without careful use
+ of explicit locks to block conflicting transactions.
+ </p><p>
+ The Repeatable Read isolation level is implemented using a technique
+ known in academic database literature and in some other database products
+ as <em class="firstterm">Snapshot Isolation</em>. Differences in behavior
+ and performance may be observed when compared with systems that use a
+ traditional locking technique that reduces concurrency. Some other
+ systems may even offer Repeatable Read and Snapshot Isolation as distinct
+ isolation levels with different behavior. The permitted phenomena that
+ distinguish the two techniques were not formalized by database researchers
+ until after the SQL standard was developed, and are outside the scope of
+ this manual. For a full treatment, please see
+ <a class="xref" href="biblio.html#BERENSON95">[berenson95]</a>.
+ </p><div class="note"><h3 class="title">Note</h3><p>
+ Prior to <span class="productname">PostgreSQL</span> version 9.1, a request
+ for the Serializable transaction isolation level provided exactly the
+ same behavior described here. To retain the legacy Serializable
+ behavior, Repeatable Read should now be requested.
+ </p></div></div><div class="sect2" id="XACT-SERIALIZABLE"><div class="titlepage"><div><div><h3 class="title">13.2.3. Serializable Isolation Level</h3></div></div></div><a id="id-1.5.12.5.13.2" class="indexterm"></a><a id="id-1.5.12.5.13.3" class="indexterm"></a><a id="id-1.5.12.5.13.4" class="indexterm"></a><a id="id-1.5.12.5.13.5" class="indexterm"></a><p>
+ The <em class="firstterm">Serializable</em> isolation level provides
+ the strictest transaction isolation. This level emulates serial
+ transaction execution for all committed transactions;
+ as if transactions had been executed one after another, serially,
+ rather than concurrently. However, like the Repeatable Read level,
+ applications using this level must
+ be prepared to retry transactions due to serialization failures.
+ In fact, this isolation level works exactly the same as Repeatable
+ Read except that it monitors for conditions which could make
+ execution of a concurrent set of serializable transactions behave
+ in a manner inconsistent with all possible serial (one at a time)
+ executions of those transactions. This monitoring does not
+ introduce any blocking beyond that present in repeatable read, but
+ there is some overhead to the monitoring, and detection of the
+ conditions which could cause a
+ <em class="firstterm">serialization anomaly</em> will trigger a
+ <em class="firstterm">serialization failure</em>.
+ </p><p>
+ As an example,
+ consider a table <code class="structname">mytab</code>, initially containing:
+</p><pre class="screen">
+ class | value
+-------+-------
+ 1 | 10
+ 1 | 20
+ 2 | 100
+ 2 | 200
+</pre><p>
+ Suppose that serializable transaction A computes:
+</p><pre class="screen">
+SELECT SUM(value) FROM mytab WHERE class = 1;
+</pre><p>
+ and then inserts the result (30) as the <code class="structfield">value</code> in a
+ new row with <code class="structfield">class</code><code class="literal"> = 2</code>. Concurrently, serializable
+ transaction B computes:
+</p><pre class="screen">
+SELECT SUM(value) FROM mytab WHERE class = 2;
+</pre><p>
+ and obtains the result 300, which it inserts in a new row with
+ <code class="structfield">class</code><code class="literal"> = 1</code>. Then both transactions try to commit.
+ If either transaction were running at the Repeatable Read isolation level,
+ both would be allowed to commit; but since there is no serial order of execution
+ consistent with the result, using Serializable transactions will allow one
+ transaction to commit and will roll the other back with this message:
+
+</p><pre class="screen">
+ERROR: could not serialize access due to read/write dependencies among transactions
+</pre><p>
+
+ This is because if A had
+ executed before B, B would have computed the sum 330, not 300, and
+ similarly the other order would have resulted in a different sum
+ computed by A.
+ </p><p>
+ When relying on Serializable transactions to prevent anomalies, it is
+ important that any data read from a permanent user table not be
+ considered valid until the transaction which read it has successfully
+ committed. This is true even for read-only transactions, except that
+ data read within a <em class="firstterm">deferrable</em> read-only
+ transaction is known to be valid as soon as it is read, because such a
+ transaction waits until it can acquire a snapshot guaranteed to be free
+ from such problems before starting to read any data. In all other cases
+ applications must not depend on results read during a transaction that
+ later aborted; instead, they should retry the transaction until it
+ succeeds.
+ </p><p>
+ To guarantee true serializability <span class="productname">PostgreSQL</span>
+ uses <em class="firstterm">predicate locking</em>, which means that it keeps locks
+ which allow it to determine when a write would have had an impact on
+ the result of a previous read from a concurrent transaction, had it run
+ first. In <span class="productname">PostgreSQL</span> these locks do not
+ cause any blocking and therefore can <span class="emphasis"><em>not</em></span> play any part in
+ causing a deadlock. They are used to identify and flag dependencies
+ among concurrent Serializable transactions which in certain combinations
+ can lead to serialization anomalies. In contrast, a Read Committed or
+ Repeatable Read transaction which wants to ensure data consistency may
+ need to take out a lock on an entire table, which could block other
+ users attempting to use that table, or it may use <code class="literal">SELECT FOR
+ UPDATE</code> or <code class="literal">SELECT FOR SHARE</code> which not only
+ can block other transactions but cause disk access.
+ </p><p>
+ Predicate locks in <span class="productname">PostgreSQL</span>, like in most
+ other database systems, are based on data actually accessed by a
+ transaction. These will show up in the
+ <a class="link" href="view-pg-locks.html" title="51.73. pg_locks"><code class="structname">pg_locks</code></a>
+ system view with a <code class="literal">mode</code> of <code class="literal">SIReadLock</code>. The
+ particular locks
+ acquired during execution of a query will depend on the plan used by
+ the query, and multiple finer-grained locks (e.g., tuple locks) may be
+ combined into fewer coarser-grained locks (e.g., page locks) during the
+ course of the transaction to prevent exhaustion of the memory used to
+ track the locks. A <code class="literal">READ ONLY</code> transaction may be able to
+ release its SIRead locks before completion, if it detects that no
+ conflicts can still occur which could lead to a serialization anomaly.
+ In fact, <code class="literal">READ ONLY</code> transactions will often be able to
+ establish that fact at startup and avoid taking any predicate locks.
+ If you explicitly request a <code class="literal">SERIALIZABLE READ ONLY DEFERRABLE</code>
+ transaction, it will block until it can establish this fact. (This is
+ the <span class="emphasis"><em>only</em></span> case where Serializable transactions block but
+ Repeatable Read transactions don't.) On the other hand, SIRead locks
+ often need to be kept past transaction commit, until overlapping read
+ write transactions complete.
+ </p><p>
+ Consistent use of Serializable transactions can simplify development.
+ The guarantee that any set of successfully committed concurrent
+ Serializable transactions will have the same effect as if they were run
+ one at a time means that if you can demonstrate that a single transaction,
+ as written, will do the right thing when run by itself, you can have
+ confidence that it will do the right thing in any mix of Serializable
+ transactions, even without any information about what those other
+ transactions might do, or it will not successfully commit. It is
+ important that an environment which uses this technique have a
+ generalized way of handling serialization failures (which always return
+ with a SQLSTATE value of '40001'), because it will be very hard to
+ predict exactly which transactions might contribute to the read/write
+ dependencies and need to be rolled back to prevent serialization
+ anomalies. The monitoring of read/write dependencies has a cost, as does
+ the restart of transactions which are terminated with a serialization
+ failure, but balanced against the cost and blocking involved in use of
+ explicit locks and <code class="literal">SELECT FOR UPDATE</code> or <code class="literal">SELECT FOR
+ SHARE</code>, Serializable transactions are the best performance choice
+ for some environments.
+ </p><p>
+ While <span class="productname">PostgreSQL</span>'s Serializable transaction isolation
+ level only allows concurrent transactions to commit if it can prove there
+ is a serial order of execution that would produce the same effect, it
+ doesn't always prevent errors from being raised that would not occur in
+ true serial execution. In particular, it is possible to see unique
+ constraint violations caused by conflicts with overlapping Serializable
+ transactions even after explicitly checking that the key isn't present
+ before attempting to insert it. This can be avoided by making sure
+ that <span class="emphasis"><em>all</em></span> Serializable transactions that insert potentially
+ conflicting keys explicitly check if they can do so first. For example,
+ imagine an application that asks the user for a new key and then checks
+ that it doesn't exist already by trying to select it first, or generates
+ a new key by selecting the maximum existing key and adding one. If some
+ Serializable transactions insert new keys directly without following this
+ protocol, unique constraints violations might be reported even in cases
+ where they could not occur in a serial execution of the concurrent
+ transactions.
+ </p><p>
+ For optimal performance when relying on Serializable transactions for
+ concurrency control, these issues should be considered:
+
+ </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
+ Declare transactions as <code class="literal">READ ONLY</code> when possible.
+ </p></li><li class="listitem"><p>
+ Control the number of active connections, using a connection pool if
+ needed. This is always an important performance consideration, but
+ it can be particularly important in a busy system using Serializable
+ transactions.
+ </p></li><li class="listitem"><p>
+ Don't put more into a single transaction than needed for integrity
+ purposes.
+ </p></li><li class="listitem"><p>
+ Don't leave connections dangling <span class="quote">“<span class="quote">idle in transaction</span>”</span>
+ longer than necessary. The configuration parameter
+ <a class="xref" href="runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT">idle_in_transaction_session_timeout</a> may be used to
+ automatically disconnect lingering sessions.
+ </p></li><li class="listitem"><p>
+ Eliminate explicit locks, <code class="literal">SELECT FOR UPDATE</code>, and
+ <code class="literal">SELECT FOR SHARE</code> where no longer needed due to the
+ protections automatically provided by Serializable transactions.
+ </p></li><li class="listitem"><p>
+ When the system is forced to combine multiple page-level predicate
+ locks into a single relation-level predicate lock because the predicate
+ lock table is short of memory, an increase in the rate of serialization
+ failures may occur. You can avoid this by increasing
+ <a class="xref" href="runtime-config-locks.html#GUC-MAX-PRED-LOCKS-PER-TRANSACTION">max_pred_locks_per_transaction</a>,
+ <a class="xref" href="runtime-config-locks.html#GUC-MAX-PRED-LOCKS-PER-RELATION">max_pred_locks_per_relation</a>, and/or
+ <a class="xref" href="runtime-config-locks.html#GUC-MAX-PRED-LOCKS-PER-PAGE">max_pred_locks_per_page</a>.
+ </p></li><li class="listitem"><p>
+ A sequential scan will always necessitate a relation-level predicate
+ lock. This can result in an increased rate of serialization failures.
+ It may be helpful to encourage the use of index scans by reducing
+ <a class="xref" href="runtime-config-query.html#GUC-RANDOM-PAGE-COST">random_page_cost</a> and/or increasing
+ <a class="xref" href="runtime-config-query.html#GUC-CPU-TUPLE-COST">cpu_tuple_cost</a>. Be sure to weigh any decrease
+ in transaction rollbacks and restarts against any overall change in
+ query execution time.
+ </p></li></ul></div><p>
+ </p><p>
+ The Serializable isolation level is implemented using a technique known
+ in academic database literature as Serializable Snapshot Isolation, which
+ builds on Snapshot Isolation by adding checks for serialization anomalies.
+ Some differences in behavior and performance may be observed when compared
+ with other systems that use a traditional locking technique. Please see
+ <a class="xref" href="biblio.html#PORTS12">[ports12]</a> for detailed information.
+ </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="mvcc-intro.html" title="13.1. Introduction">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="explicit-locking.html" title="13.3. Explicit Locking">Next</a></td></tr><tr><td width="40%" align="left" valign="top">13.1. Introduction </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 13.3. Explicit Locking</td></tr></table></div></body></html> \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-13 14:50:04 +0000