Rollback behavior on a statement error in transaction block

Summary: In this article, we will learn the rollback behavior on a statement error in transaction block, and the difference between PostgreSQL and Oracle.

Table of Contents

PostgreSQL - ERROR: current transaction is aborted

Normally, any error you make will throw an exception and cause your current transaction to be marked as aborted. This is sane and expected behavior, but it can be very, very annoying if it happens when you are in the middle of a large transaction and mistype something! At that point, the only thing you can do is rollback the transaction and lose all of your work.

When you encounter an error in PostgreSQL, the transaction cannot continue. For example:

DROP TABLE IF EXISTS demo;

START TRANSACTION;
CREATE TABLE demo(n, t) AS SELECT 1 n, current_timestamp t;
ALTER TABLE demo ADD UNIQUE(n);

SELECT * FROM demo;
 n |               t
---+-------------------------------
 1 | 2021-06-26 18:15:07.207671+08
(1 row)

INSERT INTO demo VALUES (2, current_timestamp);
SELECT * FROM demo;
 n |               t
---+-------------------------------
 1 | 2021-06-26 18:15:07.207671+08
 2 | 2021-06-26 18:15:07.207671+08
(2 rows)

INSERT INTO demo VALUES (1, current_timestamp);
ERROR:  duplicate key value violates unique constraint "demo_n_key"
DETAIL:  Key (n)=(1) already exists.

SELECT * FROM demo;
ERROR:  current transaction is aborted, commands ignored until end of transaction block

ROLLBACK;

SELECT * FROM demo;
ERROR:  relation "demo" does not exist
LINE 1: SELECT * FROM demo;

Here I rolled back. But I can also commit to terminate the transaction, but it will rollback anyway:

COMMIT;

SELECT * FROM demo;
ERROR:  relation "demo" does not exist
LINE 1: SELECT * FROM demo;

Oracle statement-level rollback

If at any time during execution a SQL statement causes an error, all effects of the statement are rolled back. The effect of the rollback is as if that statement were never executed. This is a statement-level rollback.

Errors discovered during SQL statement execution cause statement-level rollbacks. (An example of such an error is attempting to insert a duplicate value in a primary key.) Errors discovered during SQL statement parsing (such as a syntax error) have not yet been executed, so do not cause a statement-level rollback. Single SQL statements involved in a deadlock (competition for the same data) may also cause a statement-level rollback.

A SQL statement that fails causes the loss only of any work it would have performed itself; it does not cause the loss of any work that preceded it in the current transaction. If the statement is a DDL statement, the implicit commit that immediately preceded it is not undone.

In Oracle, when a statement fails in a user call, the modifications done by this user call are rolled back, but not the previous modifications. The transaction can continue (like re-try, or do an alternative change), for example:

CREATE TABLE DEMO AS SELECT 1 n, current_timestamp t FROM dual;
ALTER TABLE DEMO ADD UNIQUE(n);

SELECT * FROM DEMO;
         N T
---------- -----------------------------------
         1 02-AUG-21 11.04.16.507292 PM +00:00

INSERT INTO DEMO VALUES (2, current_timestamp);

SELECT * FROM DEMO;
         N T
---------- -----------------------------------
         1 02-AUG-21 11.04.16.507292 PM +00:00
         2 02-AUG-21 11.04.16.601105 PM +00:00

INSERT INTO DEMO VALUES (1, current_timestamp);
ERROR at line 1:
ORA-00001: unique constraint (DEMO.SYS_C007847) violated

SELECT * FROM DEMO;
         N T
---------- -----------------------------------
         1 02-AUG-21 11.04.16.507292 PM +00:00
         2 02-AUG-21 11.04.16.601105 PM +00:00