diff options
Diffstat (limited to 'doc/src/sgml/html/sql-set-transaction.html')
| -rw-r--r-- | doc/src/sgml/html/sql-set-transaction.html | 176 |
1 files changed, 176 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-set-transaction.html b/doc/src/sgml/html/sql-set-transaction.html new file mode 100644 index 0000000..186cffe --- /dev/null +++ b/doc/src/sgml/html/sql-set-transaction.html @@ -0,0 +1,176 @@ +<?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>SET TRANSACTION</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="sql-set-session-authorization.html" title="SET SESSION AUTHORIZATION" /><link rel="next" href="sql-show.html" title="SHOW" /></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">SET TRANSACTION</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-set-session-authorization.html" title="SET SESSION AUTHORIZATION">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</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="sql-show.html" title="SHOW">Next</a></td></tr></table><hr></hr></div><div class="refentry" id="SQL-SET-TRANSACTION"><div class="titlepage"></div><a id="id-1.9.3.177.1" class="indexterm"></a><a id="id-1.9.3.177.2" class="indexterm"></a><a id="id-1.9.3.177.3" class="indexterm"></a><a id="id-1.9.3.177.4" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">SET TRANSACTION</span></h2><p>SET TRANSACTION — set the characteristics of the current transaction</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis"> +SET TRANSACTION <em class="replaceable"><code>transaction_mode</code></em> [, ...] +SET TRANSACTION SNAPSHOT <em class="replaceable"><code>snapshot_id</code></em> +SET SESSION CHARACTERISTICS AS TRANSACTION <em class="replaceable"><code>transaction_mode</code></em> [, ...] + +<span class="phrase">where <em class="replaceable"><code>transaction_mode</code></em> is one of:</span> + + ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } + READ WRITE | READ ONLY + [ NOT ] DEFERRABLE +</pre></div><div class="refsect1" id="id-1.9.3.177.8"><h2>Description</h2><p> + The <code class="command">SET TRANSACTION</code> command sets the + characteristics of the current transaction. It has no effect on any + subsequent transactions. <code class="command">SET SESSION + CHARACTERISTICS</code> sets the default transaction + characteristics for subsequent transactions of a session. These + defaults can be overridden by <code class="command">SET TRANSACTION</code> + for an individual transaction. + </p><p> + The available transaction characteristics are the transaction + isolation level, the transaction access mode (read/write or + read-only), and the deferrable mode. + In addition, a snapshot can be selected, though only for the current + transaction, not as a session default. + </p><p> + The isolation level of a transaction determines what data the + transaction can see when other transactions are running concurrently: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">READ COMMITTED</code></span></dt><dd><p> + A statement can only see rows committed before it began. This + is the default. + </p></dd><dt><span class="term"><code class="literal">REPEATABLE READ</code></span></dt><dd><p> + All statements of the current transaction can only see rows committed + before the first query or data-modification statement was executed in + this transaction. + </p></dd><dt><span class="term"><code class="literal">SERIALIZABLE</code></span></dt><dd><p> + All statements of the current transaction can only see rows committed + before the first query or data-modification statement was executed in + this transaction. If a pattern of reads and writes among concurrent + serializable transactions would create a situation which could not + have occurred for any serial (one-at-a-time) execution of those + transactions, one of them will be rolled back with a + <code class="literal">serialization_failure</code> error. + </p></dd></dl></div><p> + + The SQL standard defines one additional level, <code class="literal">READ + UNCOMMITTED</code>. + In <span class="productname">PostgreSQL</span> <code class="literal">READ + UNCOMMITTED</code> is treated as <code class="literal">READ COMMITTED</code>. + </p><p> + The transaction isolation level cannot be changed after the first query or + data-modification statement (<code class="command">SELECT</code>, + <code class="command">INSERT</code>, <code class="command">DELETE</code>, + <code class="command">UPDATE</code>, <code class="command">FETCH</code>, or + <code class="command">COPY</code>) of a transaction has been executed. See + <a class="xref" href="mvcc.html" title="Chapter 13. Concurrency Control">Chapter 13</a> for more information about transaction + isolation and concurrency control. + </p><p> + The transaction access mode determines whether the transaction is + read/write or read-only. Read/write is the default. When a + transaction is read-only, the following SQL commands are + disallowed: <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>, + <code class="literal">DELETE</code>, and <code class="literal">COPY FROM</code> if the + table they would write to is not a temporary table; all + <code class="literal">CREATE</code>, <code class="literal">ALTER</code>, and + <code class="literal">DROP</code> commands; <code class="literal">COMMENT</code>, + <code class="literal">GRANT</code>, <code class="literal">REVOKE</code>, + <code class="literal">TRUNCATE</code>; and <code class="literal">EXPLAIN ANALYZE</code> + and <code class="literal">EXECUTE</code> if the command they would execute is + among those listed. This is a high-level notion of read-only that + does not prevent all writes to disk. + </p><p> + The <code class="literal">DEFERRABLE</code> transaction property has no effect + unless the transaction is also <code class="literal">SERIALIZABLE</code> and + <code class="literal">READ ONLY</code>. When all three of these properties are + selected for a + transaction, the transaction may block when first acquiring its snapshot, + after which it is able to run without the normal overhead of a + <code class="literal">SERIALIZABLE</code> transaction and without any risk of + contributing to or being canceled by a serialization failure. This mode + is well suited for long-running reports or backups. + </p><p> + The <code class="literal">SET TRANSACTION SNAPSHOT</code> command allows a new + transaction to run with the same <em class="firstterm">snapshot</em> as an existing + transaction. The pre-existing transaction must have exported its snapshot + with the <code class="literal">pg_export_snapshot</code> function (see <a class="xref" href="functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION" title="9.27.5. Snapshot Synchronization Functions">Section 9.27.5</a>). That function returns a + snapshot identifier, which must be given to <code class="literal">SET TRANSACTION + SNAPSHOT</code> to specify which snapshot is to be imported. The + identifier must be written as a string literal in this command, for example + <code class="literal">'000003A1-1'</code>. + <code class="literal">SET TRANSACTION SNAPSHOT</code> can only be executed at the + start of a transaction, before the first query or + data-modification statement (<code class="command">SELECT</code>, + <code class="command">INSERT</code>, <code class="command">DELETE</code>, + <code class="command">UPDATE</code>, <code class="command">FETCH</code>, or + <code class="command">COPY</code>) of the transaction. Furthermore, the transaction + must already be set to <code class="literal">SERIALIZABLE</code> or + <code class="literal">REPEATABLE READ</code> isolation level (otherwise, the snapshot + would be discarded immediately, since <code class="literal">READ COMMITTED</code> mode takes + a new snapshot for each command). If the importing transaction uses + <code class="literal">SERIALIZABLE</code> isolation level, then the transaction that + exported the snapshot must also use that isolation level. Also, a + non-read-only serializable transaction cannot import a snapshot from a + read-only transaction. + </p></div><div class="refsect1" id="id-1.9.3.177.9"><h2>Notes</h2><p> + If <code class="command">SET TRANSACTION</code> is executed without a prior + <code class="command">START TRANSACTION</code> or <code class="command">BEGIN</code>, + it emits a warning and otherwise has no effect. + </p><p> + It is possible to dispense with <code class="command">SET TRANSACTION</code> + by instead specifying the desired <em class="replaceable"><code>transaction_modes</code></em> in + <code class="command">BEGIN</code> or <code class="command">START TRANSACTION</code>. + But that option is not available for <code class="command">SET TRANSACTION + SNAPSHOT</code>. + </p><p> + The session default transaction modes can also be set or examined via the + configuration parameters <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TRANSACTION-ISOLATION">default_transaction_isolation</a>, + <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TRANSACTION-READ-ONLY">default_transaction_read_only</a>, and + <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TRANSACTION-DEFERRABLE">default_transaction_deferrable</a>. + (In fact <code class="command">SET SESSION CHARACTERISTICS</code> is just a + verbose equivalent for setting these variables with <code class="command">SET</code>.) + This means the defaults can be set in the configuration file, via + <code class="command">ALTER DATABASE</code>, etc. Consult <a class="xref" href="runtime-config.html" title="Chapter 20. Server Configuration">Chapter 20</a> + for more information. + </p><p> + The current transaction's modes can similarly be set or examined via the + configuration parameters <a class="xref" href="runtime-config-client.html#GUC-TRANSACTION-ISOLATION">transaction_isolation</a>, + <a class="xref" href="runtime-config-client.html#GUC-TRANSACTION-READ-ONLY">transaction_read_only</a>, and + <a class="xref" href="runtime-config-client.html#GUC-TRANSACTION-DEFERRABLE">transaction_deferrable</a>. Setting one of these + parameters acts the same as the corresponding <code class="command">SET + TRANSACTION</code> option, with the same restrictions on when it can + be done. However, these parameters cannot be set in the configuration + file, or from any source other than live SQL. + </p></div><div class="refsect1" id="id-1.9.3.177.10"><h2>Examples</h2><p> + To begin a new transaction with the same snapshot as an already + existing transaction, first export the snapshot from the existing + transaction. That will return the snapshot identifier, for example: + +</p><pre class="programlisting"> +BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SELECT pg_export_snapshot(); + pg_export_snapshot +--------------------- + 00000003-0000001B-1 +(1 row) +</pre><p> + + Then give the snapshot identifier in a <code class="command">SET TRANSACTION + SNAPSHOT</code> command at the beginning of the newly opened + transaction: + +</p><pre class="programlisting"> +BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SET TRANSACTION SNAPSHOT '00000003-0000001B-1'; +</pre></div><div class="refsect1" id="R1-SQL-SET-TRANSACTION-3"><h2>Compatibility</h2><p> + These commands are defined in the <acronym class="acronym">SQL</acronym> standard, + except for the <code class="literal">DEFERRABLE</code> transaction mode + and the <code class="command">SET TRANSACTION SNAPSHOT</code> form, which are + <span class="productname">PostgreSQL</span> extensions. + </p><p> + <code class="literal">SERIALIZABLE</code> is the default transaction + isolation level in the standard. In + <span class="productname">PostgreSQL</span> the default is ordinarily + <code class="literal">READ COMMITTED</code>, but you can change it as + mentioned above. + </p><p> + In the SQL standard, there is one other transaction characteristic + that can be set with these commands: the size of the diagnostics + area. This concept is specific to embedded SQL, and therefore is + not implemented in the <span class="productname">PostgreSQL</span> server. + </p><p> + The SQL standard requires commas between successive <em class="replaceable"><code>transaction_modes</code></em>, but for historical + reasons <span class="productname">PostgreSQL</span> allows the commas to be + omitted. + </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="sql-set-session-authorization.html" title="SET SESSION AUTHORIZATION">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-show.html" title="SHOW">Next</a></td></tr><tr><td width="40%" align="left" valign="top">SET SESSION AUTHORIZATION </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"> SHOW</td></tr></table></div></body></html>
\ No newline at end of file |