SQL ABORT

What does the SQL ABORT command do?

ABORT immediately rolls back the current transaction, discarding all changes since BEGIN.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL ABORT: Supported: PostgreSQL, Amazon Redshift, Greenplum. Not supported: MySQL, SQL Server, Oracle, SQLite.

SQL ABORT Full Explanation

ABORT is a PostgreSQL-specific transaction control statement that cancels the current transaction and reverts the database to the state it was in before the corresponding BEGIN or START TRANSACTION. It performs the same action as ROLLBACK, but the keyword ABORT is retained for historical and internal consistency with PostgreSQL’s source code. When executed, all data modifications, locks, and sequence changes made within the transaction are undone, and all locks are released. ABORT has no effect when executed outside an open transaction block; PostgreSQL simply replies with a NOTICE and does nothing. Because ABORT discards every change made in the ongoing transaction, it should be issued whenever an unrecoverable error or business rule violation is detected before COMMIT.

SQL ABORT Syntax

ABORT [ WORK | TRANSACTION ];

SQL ABORT Parameters

Example Queries Using SQL ABORT

-- Example 1: abort a transaction after a failed check
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- business rule fails: balance cannot be negative
SELECT ABORT;

-- Example 2: ABORT synonymously with ROLLBACK
BEGIN WORK;
INSERT INTO logs(message) VALUES ('test');
ABORT WORK;

Expected Output Using SQL ABORT

The database reverts all changes made since BEGIN, leaving affected tables unchanged
PostgreSQL returns the command tag ROLLBACK, releases acquired locks, and ends the transaction

Use Cases with SQL ABORT

Cancel a transaction when application-level validations fail
Handle unexpected errors caught in PL/pgSQL exception blocks
Safely revert bulk data loads detected to have bad input
Abort long-running maintenance scripts without partial commits

Common Mistakes with SQL ABORT

Trying to use ABORT in databases that do not support it
Executing ABORT outside a transaction and expecting a rollback
Confusing ABORT with COMMIT; ABORT never saves changes
Forgetting that sequences advanced inside the transaction will also roll back

First Introduced In

PostgreSQL 6.5

Frequently Asked Questions

What happens if I run ABORT after COMMIT?

Nothing. Once a transaction is committed the changes are permanent, and ABORT has no active transaction to cancel.

Is ABORT part of the ANSI SQL standard?

No. The SQL standard defines ROLLBACK. ABORT is a PostgreSQL extension kept for backward compatibility.

Does ABORT release locks?

Yes. All row and table locks acquired in the aborted transaction are released immediately after the command executes.

Can ABORT roll back sequence increments?

Yes. Sequence values advanced inside the same transaction are rolled back, so the next transaction will reuse those values if the sequence is declared as transactional.