Do subtransactions need to assign their own XID?
Subtransaction, also known as “nested transaction”, is a transaction started by instruction within the scope of an already started transaction. This feature allows users to partially rollback a transaction, which is helpful in many cases: fewer steps need to be repeated to retry the action if some error occurs.
Then, do subtransactions need to assign their own transaction IDs? Is there a risk of running out of transaction IDs for too many subtransactions?
Redrock Postgres implemented subtransactions based on the undo record position, these subtransactions are only temporary marks in a transaction, they do not need to assign their own transaction IDs, and have no risk to the system.
There are following cases using subtransactions:
- The SQL Command
SAVEPOINTdefines a new savepoint within the current transaction.
BEGIN / EXCEPTION WHEN .. / ENDblocks in PL/pgSQL procedure.
Let’s start a transaction block, use
SAVEPOINT to create multiple subtransactions in the transaction, and insert a record into the table
test1 in each subtransaction.
CREATE TABLE test1(i integer); BEGIN; SELECT pg_current_xact_id(); pg_current_xact_id -------------------- (6,15,1) INSERT INTO test1 VALUES (1); SAVEPOINT s1; INSERT INTO test1 VALUES (2); SAVEPOINT s2; SAVEPOINT s3; INSERT INTO test1 VALUES (3); COMMIT;
Let’s query the system columns rowxid, rowtime of the tuples in the table to view the transaction ID used by each subtransaction.
SELECT ctid, rowxid, rowtime, i FROM test1; ctid | rowxid | rowtime | i -------+----------+---------+--- (0,1) | (6,15,1) | 946 | 1 (0,2) | (6,15,1) | 946 | 2 (0,3) | (6,15,1) | 946 | 3
In the above example, we see that the subtransactions use the same transaction ID as the transaction block, and they do not need to be assigned a separate transaction ID.