| 1 |
<!-- doc/src/sgml/xact.sgml --> |
| 2 |
|
| 3 |
<chapter id="transactions"> |
| 4 |
|
| 5 |
<title>Transaction Processing</title> |
| 6 |
|
| 7 |
<para> |
| 8 |
This chapter provides an overview of the internals of |
| 9 |
<productname>PostgreSQL</productname>'s transaction management system. |
| 10 |
The word transaction is often abbreviated as <firstterm>xact</firstterm>. |
| 11 |
</para> |
| 12 |
|
| 13 |
<sect1 id="transaction-id"> |
| 14 |
|
| 15 |
<title>Transactions and Identifiers</title> |
| 16 |
|
| 17 |
<para> |
| 18 |
Transactions can be created explicitly using <command>BEGIN</command> |
| 19 |
or <command>START TRANSACTION</command> and ended using |
| 20 |
<command>COMMIT</command> or <command>ROLLBACK</command>. SQL |
| 21 |
statements outside of explicit transactions automatically use |
| 22 |
single-statement transactions. |
| 23 |
</para> |
| 24 |
|
| 25 |
<para> |
| 26 |
Every transaction is identified by a unique |
| 27 |
<literal>VirtualTransactionId</literal> (also called |
| 28 |
<literal>virtualXID</literal> or <literal>vxid</literal>), which |
| 29 |
is comprised of a backend's process number (or <literal>procNumber</literal>) |
| 30 |
and a sequentially-assigned number local to each backend, known as |
| 31 |
<literal>localXID</literal>. For example, the virtual transaction |
| 32 |
ID <literal>4/12532</literal> has a <literal>procNumber</literal> |
| 33 |
of <literal>4</literal> and a <literal>localXID</literal> of |
| 34 |
<literal>12532</literal>. |
| 35 |
</para> |
| 36 |
|
| 37 |
<para> |
| 38 |
Non-virtual <literal>TransactionId</literal>s (or <type>xid</type>), |
| 39 |
e.g., <literal>278394</literal>, are assigned sequentially to |
| 40 |
transactions from a global counter used by all databases within |
| 41 |
the <productname>PostgreSQL</productname> cluster. This assignment |
| 42 |
happens when a transaction first writes to the database. This means |
| 43 |
lower-numbered xids started writing before higher-numbered xids. |
| 44 |
Note that the order in which transactions perform their first database |
| 45 |
write might be different from the order in which the transactions |
| 46 |
started, particularly if the transaction started with statements that |
| 47 |
only performed database reads. |
| 48 |
</para> |
| 49 |
|
| 50 |
<para> |
| 51 |
The internal transaction ID type <type>xid</type> is 32 bits wide |
| 52 |
and <link linkend="vacuum-for-wraparound">wraps around</link> every |
| 53 |
4 billion transactions. A 32-bit epoch is incremented during each |
| 54 |
wraparound. There is also a 64-bit type <type>xid8</type> which |
| 55 |
includes this epoch and therefore does not wrap around during the |
| 56 |
life of an installation; it can be converted to xid by casting. |
| 57 |
The functions in <xref linkend="functions-pg-snapshot"/> |
| 58 |
return <type>xid8</type> values. Xids are used as the |
| 59 |
basis for <productname>PostgreSQL</productname>'s <link |
| 60 |
linkend="mvcc">MVCC</link> concurrency mechanism and streaming |
| 61 |
replication. |
| 62 |
</para> |
| 63 |
|
| 64 |
<para> |
| 65 |
When a top-level transaction with a (non-virtual) xid commits, |
| 66 |
it is marked as committed in the <filename>pg_xact</filename> |
| 67 |
directory. Additional information is recorded in the |
| 68 |
<filename>pg_commit_ts</filename> directory if <xref |
| 69 |
linkend="guc-track-commit-timestamp"/> is enabled. |
| 70 |
</para> |
| 71 |
|
| 72 |
<para> |
| 73 |
In addition to <literal>vxid</literal> and <literal>xid</literal>, |
| 74 |
prepared transactions are also assigned Global Transaction |
| 75 |
Identifiers (<acronym>GID</acronym>). GIDs are string literals up |
| 76 |
to 200 bytes long, which must be unique amongst other currently |
| 77 |
prepared transactions. The mapping of GID to xid is shown in <link |
| 78 |
linkend="view-pg-prepared-xacts"><structname>pg_prepared_xacts</structname></link>. |
| 79 |
</para> |
| 80 |
</sect1> |
| 81 |
|
| 82 |
<sect1 id="xact-locking"> |
| 83 |
|
| 84 |
<title>Transactions and Locking</title> |
| 85 |
|
| 86 |
<para> |
| 87 |
The transaction IDs of currently executing transactions are shown in |
| 88 |
<link linkend="view-pg-locks"><structname>pg_locks</structname></link> |
| 89 |
in columns <structfield>virtualxid</structfield> and |
| 90 |
<structfield>transactionid</structfield>. Read-only transactions |
| 91 |
will have <structfield>virtualxid</structfield>s but NULL |
| 92 |
<structfield>transactionid</structfield>s, while both columns will be |
| 93 |
set in read-write transactions. |
| 94 |
</para> |
| 95 |
|
| 96 |
<para> |
| 97 |
Some lock types wait on <structfield>virtualxid</structfield>, |
| 98 |
while other types wait on <structfield>transactionid</structfield>. |
| 99 |
Row-level read and write locks are recorded directly in the locked |
| 100 |
rows and can be inspected using the <xref linkend="pgrowlocks"/> |
| 101 |
extension. Row-level read locks might also require the assignment |
| 102 |
of multixact IDs (<literal>mxid</literal>; see <xref |
| 103 |
linkend="vacuum-for-multixact-wraparound"/>). |
| 104 |
</para> |
| 105 |
</sect1> |
| 106 |
|
| 107 |
<sect1 id="subxacts"> |
| 108 |
|
| 109 |
<title>Subtransactions</title> |
| 110 |
|
| 111 |
<para> |
| 112 |
Subtransactions are started inside transactions, allowing large |
| 113 |
transactions to be broken into smaller units. Subtransactions can |
| 114 |
commit or abort without affecting their parent transactions, allowing |
| 115 |
parent transactions to continue. This allows errors to be handled |
| 116 |
more easily, which is a common application development pattern. |
| 117 |
The word subtransaction is often abbreviated as |
| 118 |
<firstterm>subxact</firstterm>. |
| 119 |
</para> |
| 120 |
|
| 121 |
<para> |
| 122 |
Subtransactions can be started explicitly using the |
| 123 |
<command>SAVEPOINT</command> command, but can also be started in |
| 124 |
other ways, such as PL/pgSQL's <literal>EXCEPTION</literal> clause. |
| 125 |
PL/Python and PL/Tcl also support explicit subtransactions. |
| 126 |
Subtransactions can also be started from other subtransactions. |
| 127 |
The top-level transaction and its child subtransactions form a |
| 128 |
hierarchy or tree, which is why we refer to the main transaction as |
| 129 |
the top-level transaction. |
| 130 |
</para> |
| 131 |
|
| 132 |
<para> |
| 133 |
If a subtransaction is assigned a non-virtual transaction ID, |
| 134 |
its transaction ID is referred to as a <quote>subxid</quote>. |
| 135 |
Read-only subtransactions are not assigned subxids, but once they |
| 136 |
attempt to write, they will be assigned one. This also causes all of |
| 137 |
a subxid's parents, up to and including the top-level transaction, |
| 138 |
to be assigned non-virtual transaction ids. We ensure that a parent |
| 139 |
xid is always lower than any of its child subxids. |
| 140 |
</para> |
| 141 |
|
| 142 |
<para> |
| 143 |
The immediate parent xid of each subxid is recorded in the |
| 144 |
<filename>pg_subtrans</filename> directory. No entry is made for |
| 145 |
top-level xids since they do not have a parent, nor is an entry made |
| 146 |
for read-only subtransactions. |
| 147 |
</para> |
| 148 |
|
| 149 |
<para> |
| 150 |
When a subtransaction commits, all of its committed child |
| 151 |
subtransactions with subxids will also be considered subcommitted |
| 152 |
in that transaction. When a subtransaction aborts, all of its child |
| 153 |
subtransactions will also be considered aborted. |
| 154 |
</para> |
| 155 |
|
| 156 |
<para> |
| 157 |
When a top-level transaction with an xid commits, all of its |
| 158 |
subcommitted child subtransactions are also persistently recorded |
| 159 |
as committed in the <filename>pg_xact</filename> subdirectory. If the |
| 160 |
top-level transaction aborts, all its subtransactions are also aborted, |
| 161 |
even if they were subcommitted. |
| 162 |
</para> |
| 163 |
|
| 164 |
<para> |
| 165 |
The more subtransactions each transaction keeps open (not |
| 166 |
rolled back or released), the greater the transaction management |
| 167 |
overhead. Up to 64 open subxids are cached in shared memory for |
| 168 |
each backend; after that point, the storage I/O overhead increases |
| 169 |
significantly due to additional lookups of subxid entries in |
| 170 |
<filename>pg_subtrans</filename>. |
| 171 |
</para> |
| 172 |
</sect1> |
| 173 |
|
| 174 |
<sect1 id="two-phase"> |
| 175 |
|
| 176 |
<title>Two-Phase Transactions</title> |
| 177 |
|
| 178 |
<para> |
| 179 |
<productname>PostgreSQL</productname> supports a two-phase commit (2PC) |
| 180 |
protocol that allows multiple distributed systems to work together |
| 181 |
in a transactional manner. The commands are <command>PREPARE |
| 182 |
TRANSACTION</command>, <command>COMMIT PREPARED</command> and |
| 183 |
<command>ROLLBACK PREPARED</command>. Two-phase transactions |
| 184 |
are intended for use by external transaction management systems. |
| 185 |
<productname>PostgreSQL</productname> follows the features and model |
| 186 |
proposed by the X/Open XA standard, but does not implement some less |
| 187 |
often used aspects. |
| 188 |
</para> |
| 189 |
|
| 190 |
<para> |
| 191 |
When the user executes <command>PREPARE TRANSACTION</command>, the |
| 192 |
only possible next commands are <command>COMMIT PREPARED</command> |
| 193 |
or <command>ROLLBACK PREPARED</command>. In general, this prepared |
| 194 |
state is intended to be of very short duration, but external |
| 195 |
availability issues might mean transactions stay in this state |
| 196 |
for an extended interval. Short-lived prepared |
| 197 |
transactions are stored only in shared memory and WAL. |
| 198 |
Transactions that span checkpoints are recorded in the |
| 199 |
<filename>pg_twophase</filename> directory. Transactions |
| 200 |
that are currently prepared can be inspected using <link |
| 201 |
linkend="view-pg-prepared-xacts"><structname>pg_prepared_xacts</structname></link>. |
| 202 |
</para> |
| 203 |
</sect1> |
| 204 |
|
| 205 |
</chapter> |