1<!-- 2doc/src/sgml/ref/prepare_transaction.sgml 3PostgreSQL documentation 4--> 5 6<refentry id="sql-prepare-transaction"> 7 <indexterm zone="sql-prepare-transaction"> 8 <primary>PREPARE TRANSACTION</primary> 9 </indexterm> 10 11 <refmeta> 12 <refentrytitle>PREPARE TRANSACTION</refentrytitle> 13 <manvolnum>7</manvolnum> 14 <refmiscinfo>SQL - Language Statements</refmiscinfo> 15 </refmeta> 16 17 <refnamediv> 18 <refname>PREPARE TRANSACTION</refname> 19 <refpurpose>prepare the current transaction for two-phase commit</refpurpose> 20 </refnamediv> 21 22 <refsynopsisdiv> 23<synopsis> 24PREPARE TRANSACTION <replaceable class="parameter">transaction_id</replaceable> 25</synopsis> 26 </refsynopsisdiv> 27 28 <refsect1> 29 <title>Description</title> 30 31 <para> 32 <command>PREPARE TRANSACTION</command> prepares the current transaction 33 for two-phase commit. After this command, the transaction is no longer 34 associated with the current session; instead, its state is fully stored on 35 disk, and there is a very high probability that it can be committed 36 successfully, even if a database crash occurs before the commit is 37 requested. 38 </para> 39 40 <para> 41 Once prepared, a transaction can later be committed or rolled back 42 with <xref linkend="sql-commit-prepared"/> 43 or <xref linkend="sql-rollback-prepared"/>, 44 respectively. Those commands can be issued from any session, not 45 only the one that executed the original transaction. 46 </para> 47 48 <para> 49 From the point of view of the issuing session, <command>PREPARE 50 TRANSACTION</command> is not unlike a <command>ROLLBACK</command> command: 51 after executing it, there is no active current transaction, and the 52 effects of the prepared transaction are no longer visible. (The effects 53 will become visible again if the transaction is committed.) 54 </para> 55 56 <para> 57 If the <command>PREPARE TRANSACTION</command> command fails for any 58 reason, it becomes a <command>ROLLBACK</command>: the current transaction 59 is canceled. 60 </para> 61 </refsect1> 62 63 <refsect1> 64 <title>Parameters</title> 65 66 <variablelist> 67 <varlistentry> 68 <term><replaceable class="parameter">transaction_id</replaceable></term> 69 <listitem> 70 <para> 71 An arbitrary identifier that later identifies this transaction for 72 <command>COMMIT PREPARED</command> or <command>ROLLBACK PREPARED</command>. 73 The identifier must be written as a string literal, and must be 74 less than 200 bytes long. It must not be the same as the identifier 75 used for any currently prepared transaction. 76 </para> 77 </listitem> 78 </varlistentry> 79 </variablelist> 80 </refsect1> 81 82 <refsect1> 83 <title>Notes</title> 84 85 <para> 86 <command>PREPARE TRANSACTION</command> is not intended for use in applications 87 or interactive sessions. Its purpose is to allow an external 88 transaction manager to perform atomic global transactions across multiple 89 databases or other transactional resources. Unless you're writing a 90 transaction manager, you probably shouldn't be using <command>PREPARE 91 TRANSACTION</command>. 92 </para> 93 94 <para> 95 This command must be used inside a transaction block. Use <xref 96 linkend="sql-begin"/> to start one. 97 </para> 98 99 <para> 100 It is not currently allowed to <command>PREPARE</command> a transaction that 101 has executed any operations involving temporary tables or the session's 102 temporary namespace, created any cursors <literal>WITH HOLD</literal>, or 103 executed <command>LISTEN</command>, <command>UNLISTEN</command>, or 104 <command>NOTIFY</command>. 105 Those features are too tightly 106 tied to the current session to be useful in a transaction to be prepared. 107 </para> 108 109 <para> 110 If the transaction modified any run-time parameters with <command>SET</command> 111 (without the <literal>LOCAL</literal> option), 112 those effects persist after <command>PREPARE TRANSACTION</command>, and will not 113 be affected by any later <command>COMMIT PREPARED</command> or 114 <command>ROLLBACK PREPARED</command>. Thus, in this one respect 115 <command>PREPARE TRANSACTION</command> acts more like <command>COMMIT</command> than 116 <command>ROLLBACK</command>. 117 </para> 118 119 <para> 120 All currently available prepared transactions are listed in the 121 <link linkend="view-pg-prepared-xacts"><structname>pg_prepared_xacts</structname></link> 122 system view. 123 </para> 124 125 <caution> 126 <para> 127 It is unwise to leave transactions in the prepared state for a long time. 128 This will interfere with the ability of <command>VACUUM</command> to reclaim 129 storage, and in extreme cases could cause the database to shut down 130 to prevent transaction ID wraparound (see <xref 131 linkend="vacuum-for-wraparound"/>). Keep in mind also that the transaction 132 continues to hold whatever locks it held. The intended usage of the 133 feature is that a prepared transaction will normally be committed or 134 rolled back as soon as an external transaction manager has verified that 135 other databases are also prepared to commit. 136 </para> 137 138 <para> 139 If you have not set up an external transaction manager to track prepared 140 transactions and ensure they get closed out promptly, it is best to keep 141 the prepared-transaction feature disabled by setting 142 <xref linkend="guc-max-prepared-transactions"/> to zero. This will 143 prevent accidental creation of prepared transactions that might then 144 be forgotten and eventually cause problems. 145 </para> 146 </caution> 147 </refsect1> 148 149 <refsect1 id="sql-prepare-transaction-examples"> 150 <title>Examples</title> 151 <para> 152 Prepare the current transaction for two-phase commit, using 153 <literal>foobar</literal> as the transaction identifier: 154 155<programlisting> 156PREPARE TRANSACTION 'foobar'; 157</programlisting></para> 158 </refsect1> 159 160 <refsect1> 161 <title>Compatibility</title> 162 163 <para> 164 <command>PREPARE TRANSACTION</command> is a 165 <productname>PostgreSQL</productname> extension. It is intended for use by 166 external transaction management systems, some of which are covered by 167 standards (such as X/Open XA), but the SQL side of those systems is not 168 standardized. 169 </para> 170 </refsect1> 171 172 <refsect1> 173 <title>See Also</title> 174 175 <simplelist type="inline"> 176 <member><xref linkend="sql-commit-prepared"/></member> 177 <member><xref linkend="sql-rollback-prepared"/></member> 178 </simplelist> 179 </refsect1> 180 181</refentry> 182