Why do we need roll back when the transaction doesnt go through completely

Mission: Vacuuming Postgres Databases
Slide: Transaction Blocks

The below statement says that any exception caused will make sure that the transaction is not successful. In that case, when the statement execution(all the statements in the transaction) is getting failed, why do we need a rollback command(when nothing actually has been performed).

If any of the execute() commands fail, then the full transaction enclosed by BEGIN...COMMIT will not go through. A failure will cause the transaction block to be aborted , and an error message will be thrown.

1 Like

Hi @sreeramkolluru,

Sorry for the late response. Transaction is a single unit of work. Either the entire transaction block succeeds or the entire transaction block fails. Now if it fails entirely, why do we need to use ROLLBACK?

The answer is that, In PostgreSQL, if there is a failure in transaction, the transaction will be aborted. However, we have to explicitly end a transaction. Otherwise, it will not allow us to enter any commands.

For example:

CREATE TABLE transaction (
  column1 INTEGER PRIMARY KEY,
  column2 TEXT
);
BEGIN;
INSERT INTO transaction 
VALUES
  (1, 'abc');
INSERT INTO transaction 
VALUES
  ('cde', 2);
ERROR:  invalid input syntax for integer: "cde"
LINE 3:   ('cde', 2);
           ^
SELECT * FROM transaction;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
SELECT * FROM transaction;
 column1 | column2 
---------+---------
(0 rows)

Now despite the error, if you COMMIT the transaction, it will automatically ROLLBACK. So you don’t need to explicitly call ROLLBACK in this case.

The behaviour varies slightly from database to database. For example, SQLite allows you to enter any commands you like after the transaction fails. And therefore, you can even see the change if you use a SELECT query. However, unless you COMMIT, the changes will ROLLBACK from the database when you quit SQLite shell. Also, In SQLite if you COMMIT a transaction after failure, all the changes that are successful will be applied to the Database.

Since, the behaviour between Databases varies, it is always best to use ROLLBACK to make sure things go as you wished.

Hope this helps :slightly_smiling_face:.

Best,
Sahil

1 Like