Transactions can be created explicitly using BEGIN
or START TRANSACTION and ended using
COMMIT or ROLLBACK. SQL
statements outside of explicit transactions automatically use
single-statement transactions.
Every transaction is identified by a unique
VirtualTransactionId (also called
virtualXID or vxid), which
is comprised of a backend ID (or backendID)
and a sequentially-assigned number local to each backend, known as
localXID. For example, the virtual transaction
ID 4/12532 has a backendID
of 4 and a localXID of
12532.
Non-virtual TransactionIds (or xid),
e.g., 278394, are assigned sequentially to
transactions from a global counter used by all databases within
the Postgres Pro cluster. This assignment
happens when a transaction first writes to the database. This means
lower-numbered xids started writing before higher-numbered xids.
Note that the order in which transactions perform their first database
write might be different from the order in which the transactions
started, particularly if the transaction started with statements that
only performed database reads.
In Postgres Pro Enterprise, the internal transaction
ID type xid is 64 bits wide to prevent
transaction ID wraparound.
Each tuple header contains two XIDs, so extending them would lead to
high overhead. For that reason, when saved on disk on-tuple XIDs
are 32-bit, but each page special area contains an offset, called
base XID. When a tuple is read into memory,
the base XID is added to its 32-bit XIDs, and both XIDs in the tuple
become full 64 bits wide so the tuple becomes the so-called in-memory tuple.
Full 64-bit XIDs acquired after adding the base XID are used in
comparison and other arithmetic operations.
In some contexts, xid8 is also used.
The functions in Table 9.81
return xid8 values. Xids are used as the
basis for Postgres Pro's MVCC concurrency mechanism and streaming
replication.
When a top-level transaction with a (non-virtual) xid commits,
it is marked as committed in the pg_xact
directory. Additional information is recorded in the
pg_commit_ts directory if track_commit_timestamp is enabled.
In addition to vxid and xid,
prepared transactions are also assigned Global Transaction
Identifiers (GID). GIDs are string literals up
to 200 bytes long, which must be unique amongst other currently
prepared transactions. The mapping of GID to xid is shown in pg_prepared_xacts.