ABORT Statement
The ABORT statement, when used without the optional TO savepoint_name clause, allows the user to terminate an in-progress multi-statement transaction (MST) at any time before the transaction is committed with an explicit END TRANSACTION statement. ABORT causes all database changes effected by the MST to be undone and terminates the MST.
You also have the option of aborting part of a transaction to a pre-declared savepoint. The ABORT statement with the optional TO savepoint_name clause undoes the database effects of all statements in the MST that follow the declaration of the named savepoint. Following an abort to savepoint_name, the MST remains in progress, and new statements can be added to the MST in the normal fashion. Repeated aborts can be executed to the same savepoint.
This statement has the following format:
ABORT [TO savepoint_name]
Note: When executing an abort to a given savepoint, all savepoints declared after the named savepoint are nullified.
ABORT Examples
The following example begins a transaction, executes some SQL statements, and aborts the transaction before committing the database changes:
BEGIN TRANSACTION;
INSERT INTO emp (name, sal, bdate)
VALUES ('Jones,Bill', 100000, 1814);
INSERT INTO emp (name, sal, bdate)
VALUES ('Jones,Bill', 100000, 1714);
ABORT; \g
/* undoes both inserts; table is unchanged */
The following example begins a transaction, establishes savepoints, and does a partial abort of the MST:
BEGIN TRANSACTION;
INSERT INTO emp (name, sal, bdate)
VALUES ('Jones,Bill', 100000, 1945);
SAVEPOINT setone;
INSERT INTO emp (name, sal, bdate)
VALUES ('Smith,Stan', 50000, 1911);
SAVEPOINT 2; \g
/* undoes second insert; deactivates savepoint 2 */
ABORT TO setone; \g
INSERT INTO emp (name, sal, bdate)
VALUES ('Smith,Stan', 50000, 1948);
ABORT TO setone; \g
END TRANSACTION; \g
/* only the first insert is committed */
Last modified date: 08/29/2024