SQL Commit And Rollback

• COMMIT permanently saves the current transaction; ROLLBACK discards it. Once COMMIT returns, standard transaction control cannot undo the changes, and recovery requires a backup or engine-specific point-in-time recovery.
• DDL statements implicitly commit on MySQL and Oracle, so any uncommitted DML before an ALTER TABLE or DROP TABLE becomes permanent and unrollable. Keep DDL and DML in separate transactional units on those engines.
• Autocommit defaults differ across engines. MySQL and SQL Server default to autocommit on; Oracle SQL*Plus defaults off; PostgreSQL has no server-level autocommit, only a client-side one.
• SAVEPOINT enables partial rollback inside an open transaction. Reusing savepoint names is engine-specific and surprising; generate unique names per checkpoint.
• Error handling is platform-specific: SQL Server uses TRY/CATCH guarded by IF @@TRANCOUNT > 0 and XACT_STATE(); PostgreSQL uses BEGIN ... EXCEPTION subtransactions that auto-rollback on exception; Oracle uses PL/SQL EXCEPTION WHEN OTHERS THEN ROLLBACK.
• Identity and sequence values do not roll back on any major engine. Gaps in AUTO_INCREMENT, IDENTITY, or PostgreSQL sequences are normal after rollbacks.
• Production debugging starts with finding long-running or idle in transaction sessions through pg_stat_activity, sys.dm_tran_active_transactions, information_schema.innodb_trx, or v$transaction.

• Working access to at least one of MySQL 8.0+, PostgreSQL 13+, Oracle 19c+, or SQL Server 2019+.
• Basic familiarity with SQL SELECT, INSERT, UPDATE, and DELETE.
• A client tool such as mysql, psql, sqlplus, or sqlcmd.
• If you are choosing a platform, review this comparison first: SQLite vs MySQL vs PostgreSQL.

A SQL transaction is a unit of work the database treats atomically: either every statement inside it succeeds and is committed together, or none of the changes are kept. The transaction is the unit of recovery. If anything fails partway through, the engine can return the data to its pre-transaction state without manual cleanup.

The transaction lifecycle has three points of control:

BEGIN -> EXECUTE STATEMENTS -> COMMIT (persist) | ROLLBACK (discard)

ACID Properties and Why They Matter

ACID properties define how transaction engines preserve correct data under normal execution and failure conditions.

Autocommit Mode vs. Explicit Transaction Control

Autocommit runs each statement as its own transaction, while explicit mode groups multiple statements in one transaction boundary.

A COMMIT is the moment your work becomes part of the database. Until you issue it, every change in the current transaction is private to your session and reversible. After it returns successfully, the changes are persistent, visible to other sessions, and outside the reach of any standard rollback command.

COMMIT Syntax Across Database Platforms

COMMIT itself is a single keyword across every major engine. The differences sit in how you start a transaction and how the engine treats statements outside one. The table below maps the start-of-transaction and commit syntax for the four engines covered in this tutorial.

A canonical commit looks the same on every engine once the transaction is open:

BEGIN;
UPDATE customer SET state = 'TX' WHERE customer_id = 4;
COMMIT;

On MySQL, BEGIN is an alias for START TRANSACTION. On SQL Server, replace BEGIN; with BEGIN TRANSACTION;. On Oracle, drop the BEGIN line entirely and rely on the implicit start.

A common gotcha: running COMMIT when no transaction is active behaves differently across engines. MySQL and Oracle silently succeed. PostgreSQL emits a warning (WARNING: there is no transaction in progress). SQL Server raises an error if BEGIN TRANSACTION was never issued. If your application code issues an unconditional COMMIT at the end of a code path, test that path on each target engine.

When a COMMIT Is Triggered Automatically

In autocommit mode, each individual SQL statement is treated as its own transaction and committed immediately upon successful execution, without requiring an explicit COMMIT. This behavior is common in default client sessions.

MySQL enables autocommit by default. In MySQL and Oracle, DDL statements such as CREATE, ALTER, and DROP cause implicit commit behavior, where the current open transaction is committed before the DDL runs.

Review the Autocommit Mode vs. Explicit Transaction Control section for a quick default and command reference before switching session behavior. That table helps you map client defaults to transaction commands for each platform.

ROLLBACK is the recovery escape hatch. When something goes wrong inside a transaction, whether it is an application-level validation failure, a constraint violation, or a deadlock, ROLLBACK reverts the database to the state it had before the transaction began. The session is then ready to retry, log the failure, or move on.

ROLLBACK Syntax Across Database Platforms

ROLLBACK is also a single keyword across engines. SQL Server uses ROLLBACK TRANSACTION (or its short form ROLLBACK). The transaction-start behavior is identical to the table in the previous section, so the only practical difference is what state the session lands in afterward.

BEGIN;
UPDATE customer SET state = 'TX' WHERE customer_id = 4;
ROLLBACK;

After ROLLBACK, the transaction ends on every engine and the session returns to autocommit (or implicit-transaction) mode. PostgreSQL has one extra wrinkle: if any statement inside the transaction raised an error and you did not catch it, the transaction enters an aborted state and the session refuses any further commands except ROLLBACK until you issue it. The error message is ERROR: current transaction is aborted, commands ignored until end of transaction block. This catches developers who try to issue SELECT to inspect state after a failure and wonder why the database has stopped responding.

Full Transaction Rollback vs. Partial Rollback

A full ROLLBACK undoes every change made since the transaction began, while ROLLBACK TO SAVEPOINT undoes only changes made after a named savepoint was created. These two forms serve different recovery scopes in the same transaction model.

ROLLBACK without arguments performs full rollback by default, and the transaction ends after that operation. ROLLBACK TO SAVEPOINT sp_name performs partial rollback, keeps the transaction open, and allows additional SQL followed by a later COMMIT or another ROLLBACK.

The difference is operational, not just syntactic. A full rollback is a clean slate: the engine drops every change since BEGIN, releases locks the transaction held, and ends the unit of work. A partial rollback is a checkpoint reset: the engine drops only the work after the named savepoint, keeps the locks the transaction held before that savepoint, and the transaction is still open and writable.

BEGIN;
UPDATE customer SET state = 'New York' WHERE customer_id = 1;
SAVEPOINT after_first_update;
UPDATE customer SET state = 'Texas' WHERE customer_id = 2;
-- Decide the second update was wrong
ROLLBACK TO SAVEPOINT after_first_update;
-- The transaction is still open. The first update is still uncommitted.
UPDATE customer SET state = 'Karnataka' WHERE customer_id = 3;
COMMIT;

After this transaction commits, customer 1 is in New York, customer 3 is in Karnataka, and customer 2 is unchanged. The full and partial rollback forms cooperate. They are not alternatives.

See the What Is a SAVEPOINT in SQL section for the syntax and operational behavior of savepoints. The examples there show how partial rollback works in multi-step units of work.

A SAVEPOINT lets you create checkpoints inside a transaction, then selectively roll back to any of them without ending the transaction itself. The classic use case is a multi-step transaction where the early steps must persist if the later steps fail. Without savepoints, the choice is binary: commit everything or roll back everything. With savepoints, you can keep what worked and discard what did not, all inside a single unit of work.

The Savepoint Lifecycle

A savepoint goes through three states: created, optionally rolled-back-to, and finally released or implicitly cleared. The full lifecycle in one transaction looks like this.

BEGIN;
UPDATE customer SET state = 'New York' WHERE customer_id = 3;

SAVEPOINT after_customer_3_update;

UPDATE customer SET state = 'Florida' WHERE customer_id = 2;

-- Decide the second update should not survive
ROLLBACK TO SAVEPOINT after_customer_3_update;
-- Customer 3 is still updated to New York. Customer 2 is unchanged.

-- Free the savepoint name so it does not consume tracking memory
RELEASE SAVEPOINT after_customer_3_update;

UPDATE customer SET state = 'Karnataka' WHERE customer_id = 1;
COMMIT;

Three behaviors here are worth pinning down because they are the source of the most common savepoint confusion.

RELEASE SAVEPOINT does not commit work. It only discards the savepoint marker. The data changes covered by that savepoint remain part of the open transaction and are still subject to a later ROLLBACK or COMMIT. This naming trips up developers who expect “release” to mean “make permanent.”

Duplicate savepoint names behave differently per engine. In PostgreSQL, declaring a savepoint with an existing name moves the marker forward to the new position, and the old marker is no longer reachable. In MySQL, declaring a duplicate name silently replaces the previous one. In Oracle, the previous savepoint with the same name becomes inaccessible. SQL Server allows duplicate names and rolls back to the most recent one when referenced. The practical rule across all four: do not reuse savepoint names within a transaction, because the behavior on duplication is engine-specific and surprising.

Savepoints also disappear silently in two other situations. A COMMIT releases all savepoints in the transaction. A ROLLBACK TO SAVEPOINT name releases every savepoint declared after name, but keeps name itself active. So in the example above, if there were a SAVEPOINT sp_b between the SAVEPOINT after_customer_3_update and the ROLLBACK TO, the rollback would have removed sp_b automatically.

These examples use one customer table with four seed rows so you can compare table state before and after COMMIT, ROLLBACK, and savepoint rollback.

The examples below use BEGIN; to start a transaction, which is the standard syntax in PostgreSQL and is also accepted in MySQL. In MySQL you can use START TRANSACTION; interchangeably. In SQL Server, replace BEGIN; with BEGIN TRANSACTION; and COMMIT; with COMMIT TRANSACTION;.

Basic COMMIT Example

Create the table first.

CREATE TABLE customer (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(100),
  state VARCHAR(100),
  country VARCHAR(100)
);

Insert four rows.

INSERT INTO customer (customer_id, customer_name, state, country) VALUES
(1, 'Akash', 'Delhi', 'India'),
(2, 'Amit', 'Hyderabad', 'India'),
(3, 'Jason', 'California', 'USA'),
(4, 'John', 'Texas', 'USA');

Delete one row, then commit.

BEGIN;
DELETE FROM customer WHERE state = 'Texas';
COMMIT;

Query the table state after commit.

SELECT * FROM customer;

+-------------+---------------+------------+---------+
| CUSTOMER ID | CUSTOMER NAME | STATE      | COUNTRY |
+-------------+---------------+------------+---------+
| 1           | Akash         | Delhi      | India   |
| 2           | Amit          | Hyderabad  | India   |
| 3           | Jason         | California | USA     |
+-------------+---------------+------------+---------+

Basic ROLLBACK Example

Reset the table before testing rollback behavior.

TRUNCATE TABLE customer;

Insert four rows.

INSERT INTO customer (customer_id, customer_name, state, country) VALUES
(1, 'Akash', 'Delhi', 'India'),
(2, 'Amit', 'Hyderabad', 'India'),
(3, 'Jason', 'California', 'USA'),
(4, 'John', 'Texas', 'USA');

Delete one row, then roll back.

BEGIN;
DELETE FROM customer WHERE state = 'Texas';
ROLLBACK;

Query the table state after rollback.

SELECT * FROM customer;

+-------------+---------------+------------+---------+
| CUSTOMER ID | CUSTOMER NAME | STATE      | COUNTRY |
+-------------+---------------+------------+---------+
| 1           | Akash         | Delhi      | India   |
| 2           | Amit          | Hyderabad  | India   |
| 3           | Jason         | California | USA     |
| 4           | John          | Texas      | USA     |
+-------------+---------------+------------+---------+

Using SAVEPOINT With ROLLBACK

Use a savepoint to keep early validated changes while discarding only later changes in the same transaction.

BEGIN;
UPDATE customer SET state = 'Karnataka' WHERE customer_id = 1;
SAVEPOINT sp1;
UPDATE customer SET state = 'Nevada' WHERE customer_id = 4;
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

The first update on customer_id = 1 is preserved because it was made before sp1 was set. The second update on customer_id = 4 is discarded by ROLLBACK TO SAVEPOINT sp1, and the final COMMIT persists all remaining changes.

SELECT * FROM customer;

+-------------+---------------+------------+---------+
| CUSTOMER ID | CUSTOMER NAME | STATE      | COUNTRY |
+-------------+---------------+------------+---------+
| 1           | Akash         | Karnataka  | India   |
| 2           | Amit          | Hyderabad  | India   |
| 3           | Jason         | California | USA     |
| 4           | John          | Texas      | USA     |
+-------------+---------------+------------+---------+

ROLLBACK on Error Using TRY/CATCH in SQL Server

Use TRY/CATCH to guarantee a rollback path when a statement fails.

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE customer
    SET state = 'WA'
    WHERE customer_id = 4;

    INSERT INTO customer (customer_id, customer_name, state, country)
    VALUES (4, 'Duplicate Id', 'NA', 'USA');

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    THROW;
END CATCH;

The duplicate primary key on customer_id = 4 triggers the CATCH block. The output looks like this in sqlcmd:

Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint 'PK__customer'. Cannot insert duplicate key in object 'dbo.customer'. The duplicate key value is (4).

The earlier UPDATE that set state = 'WA' is rolled back along with the failed INSERT, so the table state after the rollback is identical to the state before BEGIN TRANSACTION. The IF @@TRANCOUNT > 0 guard before ROLLBACK TRANSACTION matters because some classes of error (including connection-level failures and severity 17+ errors) automatically roll back the transaction before the CATCH block runs. Calling ROLLBACK TRANSACTION when no transaction is open raises a separate error and masks the original.

ROLLBACK on Error Using Exception Handling in PostgreSQL and Oracle

PostgreSQL functions and Oracle PL/SQL blocks handle exceptions differently, but both patterns allow controlled failure behavior.

DO $$
BEGIN
    UPDATE customer SET state = 'WA' WHERE customer_id = 4;
    INSERT INTO customer (customer_id, customer_name, state, country)
    VALUES (4, 'Duplicate Id', 'NA', 'USA');
EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END;
$$;

PostgreSQL output:

ERROR:  duplicate key value violates unique constraint "customer_pkey"
DETAIL:  Key (customer_id)=(4) already exists.
CONTEXT:  PL/pgSQL function inline_code_block line 4 at SQL statement

The UPDATE and the failed INSERT are inside the same BEGIN ... EXCEPTION subtransaction. PL/pgSQL automatically rolls that subtransaction back when the exception is raised, so customer 4 retains its original state.

BEGIN
    UPDATE customer SET state = 'WA' WHERE customer_id = 4;
    INSERT INTO customer (customer_id, customer_name, state, country)
    VALUES (4, 'Duplicate Id', 'NA', 'USA');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

Oracle output:

ORA-00001: unique constraint (SCHEMA.SYS_C0011234) violated
ORA-06512: at line 4

The ROLLBACK inside the exception handler discards the UPDATE along with the failed INSERT. Note that this rolls back the entire current session transaction, including any work that was performed before this anonymous block. Use this pattern only when the procedure or block owns the entire transaction boundary.

Real-World Transaction Patterns

The textbook examples show one transaction with a fixed shape. Production code usually combines transactions with retry logic, batch processing, or audit logging, and those combinations have their own conventions.

Idempotent retry with deadlock handling. Any transaction running under contention should be written to be safe to re-run. The retry wrapper catches the platform-specific deadlock error and tries again with backoff.

# Pseudocode in any host language. Replace BEGIN/COMMIT with the
# transaction control commands appropriate to your driver.
attempt = 0
while attempt < 3:
    try:
        BEGIN
        UPDATE inventory SET qty = qty - 1 WHERE sku = 'A100' AND qty >= 1
        INSERT INTO orders (sku, customer_id) VALUES ('A100', 42)
        COMMIT
        break
    except DeadlockError:
        ROLLBACK
        attempt += 1
        sleep(2 ** attempt * 0.05)  # 50ms, 100ms, 200ms

The two statements inside the transaction must be safe to re-run. The UPDATE is conditional on qty >= 1, so a successful first attempt followed by a retry will not double-decrement. The INSERT would create a duplicate order row, so the orders table should have a unique constraint on a request ID or use INSERT ... ON CONFLICT DO NOTHING (PostgreSQL) or INSERT ... ON DUPLICATE KEY UPDATE (MySQL) to remain idempotent.

Batch processing with savepoint-per-record. When processing a list of records and a single bad record should not abort the whole batch, declare a savepoint per iteration.

# Pseudocode. The transaction is opened once and closed once;
# savepoints handle per-record failure isolation inside it.
BEGIN

for record in batch:
    SAVEPOINT sp_record
    try:
        INSERT INTO orders (...) VALUES (record values)
    except Exception:
        ROLLBACK TO SAVEPOINT sp_record
        log_failure(record)
    RELEASE SAVEPOINT sp_record

COMMIT

This is the canonical pattern for ETL imports where some rows are expected to fail. The successful rows commit together; the failed rows are rolled back individually and logged for review. Release each savepoint after processing the record so memory does not grow with batch size.

Audit logging across rollback. Audit entries usually need to persist even when the parent transaction rolls back. Each engine handles this differently:

• Oracle: declare the audit-write procedure as PRAGMA AUTONOMOUS_TRANSACTION. The autonomous transaction commits independently.
• PostgreSQL: write the audit entry through dblink to a separate connection that has its own transaction context.
• SQL Server: write the audit entry through a stored procedure called via a service broker or use a separate connection from the calling application.
• MySQL: write through a separate connection. MySQL has no autonomous-transaction equivalent in standard SQL.

In all four cases, the principle is the same: the audit write must escape the transaction boundary of the parent operation.

Stored procedure transaction behavior is platform-dependent, and transaction statements may be allowed, restricted, or context-sensitive. Test procedure behavior with the same client and runtime settings used in production.

Behavior in SQL Server Stored Procedures

SQL Server procedures run in the caller transaction context unless they start their own. The defensive pattern below works whether the caller already has an open transaction or not.

CREATE OR ALTER PROCEDURE dbo.UpdateCustomerState
    @CustomerId INT,
    @State NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
        UPDATE customer SET state = @State WHERE customer_id = @CustomerId;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;

Three idioms in this template are worth understanding because they appear in almost every production T-SQL procedure.

@@TRANCOUNT is a session-level counter that increments on each BEGIN TRANSACTION and decrements on each COMMIT. The IF @@TRANCOUNT > 0 guard before ROLLBACK TRANSACTION exists because some runtime errors (severity 17 and above, plus connection-level failures) automatically roll the transaction back before control reaches the CATCH block. Calling ROLLBACK TRANSACTION when no transaction is open raises a new error and masks the original. The guard prevents that.

THROW re-raises the original error to the caller after the rollback. This preserves the original error number, severity, state, and message. Use THROW rather than RAISERROR for re-raising; RAISERROR rewrites the error metadata, which makes debugging harder.

SET NOCOUNT ON suppresses the row-count messages that SQL Server normally emits after each statement. It is not strictly required for transaction control but is conventional in stored procedures because the row-count messages can confuse application code that parses the result stream.

For procedures that need to be safely callable from a caller that already has a transaction open, replace BEGIN TRANSACTION with SAVE TRANSACTION sp_name and ROLLBACK TRANSACTION with ROLLBACK TRANSACTION sp_name. The procedure then rolls back only its own work without disrupting the caller’s open transaction.

Behavior in Oracle PL/SQL

Oracle PL/SQL procedures can issue COMMIT or ROLLBACK directly, and that action affects the current session transaction context.

CREATE OR REPLACE PROCEDURE update_customer_state (
    p_customer_id IN NUMBER,
    p_state IN VARCHAR2
) AS
BEGIN
    UPDATE customer
    SET state = p_state
    WHERE customer_id = p_customer_id;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

Behavior in PostgreSQL Functions and Procedures

PostgreSQL procedures support COMMIT and ROLLBACK from PostgreSQL 11 onward, but a block that contains an EXCEPTION clause cannot end the surrounding transaction. PostgreSQL functions, by contrast, cannot run transaction control statements at all. Use one of the two patterns below depending on whether you need transaction control or exception handling.

Pattern one, transaction control without an exception block:

CREATE OR REPLACE PROCEDURE update_customer_state(
    p_customer_id INT,
    p_state TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE customer
    SET state = p_state
    WHERE customer_id = p_customer_id;
    COMMIT;
END;
$$;

Pattern two, exception handling without explicit transaction control. PL/pgSQL automatically rolls back the implicit subtransaction when an exception is caught, so no ROLLBACK statement is needed.

CREATE OR REPLACE PROCEDURE update_customer_state(
    p_customer_id INT,
    p_state TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE customer
    SET state = p_state
    WHERE customer_id = p_customer_id;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Update failed: %', SQLERRM;
        RAISE;
END;
$$;

When a transaction misbehaves in production, the symptoms are usually one of three: a query is hanging, changes that should have committed are missing, or changes that should have rolled back are still there. Each has a different diagnostic path.

Finding Long-Running or Blocked Transactions

A hung query usually means another transaction is holding a lock on the row or table you need. Identify the offender first, then decide whether to wait, kill it, or fix the application that left it open.

PostgreSQL:

SELECT pid,
       state,
       xact_start,
       now() - xact_start AS duration,
       query
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
  AND xact_start IS NOT NULL
ORDER BY xact_start;

Sessions in idle in transaction for more than a few seconds are the usual culprit. They have an open transaction holding locks but are not running a query, which means the application opened a transaction and went off to do something else. Kill with SELECT pg_terminate_backend(pid);.

SQL Server:

SELECT s.session_id,
       s.login_name,
       t.transaction_id,
       t.transaction_begin_time,
       DATEDIFF(SECOND, t.transaction_begin_time, GETDATE()) AS duration_seconds,
       r.command,
       r.status
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_session_transactions st ON st.transaction_id = t.transaction_id
JOIN sys.dm_exec_sessions s ON s.session_id = st.session_id
LEFT JOIN sys.dm_exec_requests r ON r.session_id = s.session_id
ORDER BY t.transaction_begin_time;

Kill with KILL <session_id>;.

MySQL:

SELECT trx_id,
       trx_state,
       trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_seconds,
       trx_mysql_thread_id,
       trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

Kill with KILL <trx_mysql_thread_id>;.

Oracle:

SELECT s.sid,
       s.serial#,
       s.username,
       t.start_time,
       t.used_ublk
FROM v$transaction t
JOIN v$session s ON s.saddr = t.ses_addr
ORDER BY t.start_time;

Kill with ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;. Without IMMEDIATE, Oracle marks the session for termination and waits for it to check in, which can take several minutes on an idle session.

Recognizing a Doomed or Aborted Transaction

A doomed transaction has hit an error and can no longer be committed. Different engines surface this differently.

PostgreSQL refuses every command except ROLLBACK and shows ERROR: current transaction is aborted, commands ignored until end of transaction block. The fix is always ROLLBACK. If you need to recover and continue inside the same transaction without discarding all earlier work, wrap the risky statement in a savepoint before it runs, as described in the ROLLBACK Syntax Across Database Platforms section.

SQL Server exposes the doomed state through XACT_STATE(). A return value of -1 means the transaction is alive but cannot be committed. Production CATCH blocks should check this:

BEGIN CATCH
    IF XACT_STATE() = -1
        ROLLBACK TRANSACTION;
    ELSE IF XACT_STATE() = 1
        COMMIT TRANSACTION;
    THROW;
END CATCH;

MySQL and Oracle do not expose a comparable doomed-state flag. In MySQL, declare a condition handler at the top of a stored procedure with DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; to catch errors and roll back automatically. In Oracle, use EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; inside the PL/SQL block. Both patterns are covered in the stored procedure section above.

Confirming Whether Autocommit Silently Committed Your Work

If a ROLLBACK appeared to do nothing and the changes are still there, autocommit is the most likely cause. Check the current setting:

If autocommit is on and you ran a bare UPDATE followed by ROLLBACK, the UPDATE committed the moment it succeeded and the ROLLBACK had nothing to discard. The fix is to wrap the work in an explicit transaction or disable autocommit at the start of the session.

If you are running on DigitalOcean Managed Databases, the same diagnostic queries apply. The metric panels in the control panel surface long-running queries and transaction counts at the engine level, and the slow-query log (PostgreSQL, MySQL) is enabled through the standard engine parameters. For applications that need transaction-aware connection pooling on PostgreSQL, the Managed Databases connection-pool service supports session-mode pooling, which preserves transaction state across a pooled connection; transaction-mode pooling does not, and some transaction patterns (advisory locks, prepared statements, SET LOCAL) will not behave as expected under transaction mode.

Transaction control follows SQL standards at a high level, but autocommit behavior, DDL handling, and nesting support vary by engine. These differences change how you design safe transaction boundaries when targeting more than one platform.

MySQL and MariaDB

MySQL and MariaDB default to autocommit on, so a bare UPDATE or INSERT issued at the prompt commits the moment it succeeds. To run a multi-statement transaction, either disable autocommit for the session (SET autocommit = 0) or wrap the work in an explicit START TRANSACTION block.

DDL statements such as CREATE TABLE, ALTER TABLE, DROP TABLE, and TRUNCATE TABLE cause an implicit commit. Any uncommitted DML in the transaction at that moment becomes permanent and cannot be rolled back. This catches migration scripts that mix schema changes and data changes inside one apparent transaction. The fix is to keep DDL and DML in separate transactional units.

Transaction support also depends on the storage engine. InnoDB supports transactions; MyISAM does not. A START TRANSACTION against a MyISAM table runs without complaint but ROLLBACK is silently a no-op on MyISAM rows. Always confirm the table engine before relying on transactional behavior. Check with SHOW CREATE TABLE customer\G and look for ENGINE=InnoDB.

The AUTO_INCREMENT value does not roll back. If a transaction inserts a row and then rolls back, the consumed identity value is gone. Gaps in AUTO_INCREMENT columns are normal and not a sign of corruption.

PostgreSQL

PostgreSQL has no server-level autocommit setting. Autocommit is a client-side construct. The psql client and most drivers default to autocommit on, which means each statement runs as its own transaction unless BEGIN opens an explicit one. Disable autocommit in psql with \set AUTOCOMMIT off.

DDL is transactional in PostgreSQL. CREATE TABLE, ALTER TABLE, DROP TABLE, and most other schema changes can sit inside a BEGIN ... COMMIT block and be rolled back along with DML. The exceptions are operations that touch global state or run outside the MVCC system, including CREATE DATABASE, DROP DATABASE, CREATE INDEX CONCURRENTLY, VACUUM, and REINDEX CONCURRENTLY. These run outside transaction control.

A transaction that hits an error enters an aborted state. The session refuses every command except ROLLBACK (or ROLLBACK TO SAVEPOINT if a savepoint exists before the failed statement) until the transaction is closed. This is by design: PostgreSQL does not let you read inconsistent state after a failure. The PostgreSQL transaction management documentation covers the recovery model in detail. Wrap risky statements in savepoints if you need to recover and continue inside the same transaction.

Sequence values do not roll back. SELECT nextval('customer_id_seq') consumes the value even if the surrounding transaction rolls back. Treat sequence-generated IDs as advisory, not gap-free.

Oracle

Oracle SQL*Plus does not autocommit by default. Each session begins in transactional mode and the first DML opens the transaction. There is no BEGIN or START TRANSACTION keyword in standard Oracle SQL; the transaction is implicit. SET TRANSACTION is for declaring isolation level or read-only mode, not for opening a transaction.

DDL issues an implicit commit both before and after the DDL itself. The “before” part is the dangerous one. If your session has an open transaction with uncommitted DML and you issue ALTER TABLE, Oracle commits the DML first, then runs the DDL, then commits the DDL. The earlier DML is now permanent regardless of what you intended. Migration scripts and test fixtures should never mix DML and DDL in the same logical unit on Oracle.

Oracle supports autonomous transactions through the PRAGMA AUTONOMOUS_TRANSACTION directive in PL/SQL. An autonomous transaction is a child transaction that commits or rolls back independently of its caller. This is useful for audit logging that must persist even when the parent transaction rolls back, but it bypasses the parent’s isolation guarantees and should be used deliberately.

Sequences are not transactional. customer_id_seq.NEXTVAL consumes the value regardless of whether the surrounding transaction commits. Combined with the default CACHE 20 setting, gaps in sequence values are normal in Oracle.

SQL Server (T-SQL)

SQL Server runs in autocommit mode by default. Each statement is its own transaction unless wrapped in BEGIN TRANSACTION ... COMMIT TRANSACTION. The alternate mode, implicit transactions, is enabled with SET IMPLICIT_TRANSACTIONS ON and turns off the per-statement commit so DML opens a transaction that stays open until you commit or roll back explicitly.

DDL is partially transactional. Most DDL statements can be rolled back inside a BEGIN TRANSACTION block, including CREATE TABLE, ALTER TABLE, and DROP TABLE. The exceptions are operations that touch the file system or system metadata in ways the engine cannot reverse, including CREATE DATABASE, BACKUP, RESTORE, and certain ALTER DATABASE commands.

SQL Server does not support true nested transactions. BEGIN TRANSACTION inside another BEGIN TRANSACTION increments the @@TRANCOUNT counter but does not create an independent inner transaction. Only the outermost COMMIT actually commits, and any ROLLBACK rolls back the entire outermost transaction regardless of how deeply nested the call was. This is why production T-SQL almost always checks IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION before issuing rollback: the calling code may have already rolled back, and a second rollback would error.

Identity columns do not roll back. A failed insert that consumed IDENTITY values leaves gaps. Use DBCC CHECKIDENT to inspect or reseed values, but understand that gaps are normal.

XACT_ABORT and XACT_STATE() are the two keys to defensive T-SQL. SET XACT_ABORT ON forces the transaction to roll back automatically on any runtime error severity 16 or above, which closes a class of bugs where TRY/CATCH would otherwise leave a doomed transaction open. XACT_STATE() returns -1 when the transaction is doomed and can no longer be committed; production stored procedures should check it before attempting COMMIT in a CATCH block.

• Forgetting to disable autocommit makes each statement permanent before validation steps complete. Fix: at the top of any multi-statement script, explicitly start a transaction with BEGIN, START TRANSACTION, or BEGIN TRANSACTION per platform. Do not rely on session defaults that may change between environments.
• Assuming DDL can be rolled back in MySQL or Oracle causes data change surprises, because implicit commit behavior persists prior DML. Fix: split migrations into separate files or transactional units, one for DML and one for DDL, and run them in order. Never interleave ALTER TABLE with uncommitted DML on these engines.
• Mixing implicit and explicit transaction patterns in one script leads to inconsistent rollback scope. Fix: pick one pattern per script. Either turn autocommit off and use bare DML, or leave autocommit on and wrap all multi-statement work in explicit BEGIN ... COMMIT blocks.
• Leaving long-running transactions open increases lock duration, blocks other sessions, and raises timeout risk. Fix: set a session-level statement timeout (SET statement_timeout = '30s' in PostgreSQL, SET SESSION MAX_EXECUTION_TIME = 30000 in MySQL, SET LOCK_TIMEOUT 30000 in SQL Server). Identify long-running offenders with SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction' on PostgreSQL or SELECT * FROM sys.dm_tran_active_transactions on SQL Server.
• Ignoring deadlock retries makes batch jobs fail intermittently under concurrency. Fix: wrap the transaction in retry logic that catches the deadlock-victim error code (1213 on MySQL, 40P01 on PostgreSQL, 1205 on SQL Server, ORA-00060 on Oracle) and retries with exponential backoff. The retried transaction must be idempotent, so design accordingly.
• Not testing rollback paths leaves unverified error behavior in production code. Fix: in your test suite, force a rollback by inserting a deliberately failing statement (such as a primary-key violation) inside the transaction and assert the post-rollback state matches the pre-transaction state.
• Reusing savepoint names inside a transaction produces engine-specific behavior that is hard to debug. Fix: generate unique savepoint names per checkpoint, for example sp_iteration_1, sp_iteration_2, or use a counter variable.
• Issuing COMMIT against a session with no open transaction can be a silent success, a warning, or an error depending on engine. Fix: in application code that may run on multiple engines, check transaction state before committing. PostgreSQL exposes pg_current_xact_id_if_assigned(). SQL Server exposes XACT_STATE(). MySQL exposes @@in_transaction.

What Is the Difference Between COMMIT and ROLLBACK in SQL?

COMMIT permanently saves changes made in the current transaction. ROLLBACK discards uncommitted changes and returns data to the previous committed state.

What Happens if You Do Not Issue a COMMIT or ROLLBACK?

Behavior depends on session settings and engine defaults. In autocommit mode, statements commit automatically, while explicit transactions are usually rolled back when the session ends unexpectedly.

What Is a SAVEPOINT in SQL and When Should You Use It?

A SAVEPOINT marks an intermediate point in a transaction. Use it when one part of multi-step work may need rollback without discarding earlier validated steps.

Can You Roll Back a COMMIT in SQL?

No, standard transaction control cannot roll back a committed transaction. Recovery after commit requires backups or engine-specific recovery features outside normal COMMIT and ROLLBACK statements.

How Does SQL Server Handle Automatic ROLLBACK on Error?

SQL Server does not roll back every error automatically by default. Wrap risky statements in TRY/CATCH and issue ROLLBACK TRANSACTION inside the CATCH block, guarded by IF @@TRANCOUNT > 0. For stricter behavior, SET XACT_ABORT ON forces automatic rollback on most runtime errors at severity 16 and above. Use XACT_STATE() to detect a doomed transaction (XACT_STATE() = -1) before attempting COMMIT in a CATCH block.

Is COMMIT and ROLLBACK Syntax the Same in Oracle and MySQL?

Core COMMIT and ROLLBACK syntax is similar across both systems. Operational behavior differs around defaults, because MySQL enables autocommit by default, while Oracle SQL*Plus commonly runs with autocommit off.

Can You Use COMMIT and ROLLBACK Inside a Stored Procedure?

Yes, but support varies. SQL Server procedures inherit the caller’s transaction context and use TRY/CATCH with IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION. Oracle PL/SQL procedures can issue COMMIT and ROLLBACK directly, but a procedure-level commit affects the whole session transaction. PostgreSQL procedures (not functions) support COMMIT and ROLLBACK from version 11 onward, but a block with an EXCEPTION clause cannot run transaction control statements. Functions in PostgreSQL cannot run transaction control at all.

What Is the Difference Between ROLLBACK and ROLLBACK TO SAVEPOINT?

ROLLBACK without arguments undoes all changes in the active transaction and ends it. ROLLBACK TO SAVEPOINT undoes only the changes after a named savepoint, and keeps the transaction open.

This tutorial covered SQL transactions and ACID behavior, the syntax and semantics of COMMIT, ROLLBACK, and SAVEPOINT, error-handling patterns in TRY/CATCH and exception blocks, transaction control inside stored procedures across the four major engines, real-world patterns for retry-safe and batch-safe transactions, and production-debugging queries for long-running and doomed transactions.

You can now write transaction-safe SQL that commits only validated work, handle failures with TRY/CATCH or exception blocks, apply savepoints for partial undo, design idempotent retry loops for deadlock-prone workloads, isolate failures in batch processing through per-record savepoints, and diagnose transaction issues directly against pg_stat_activity, sys.dm_tran_active_transactions, information_schema.innodb_trx, or v$transaction when something goes wrong in production.

For next steps, review Understanding SQL Constraints, Introduction to Transactions in PostgreSQL, How To Use Triggers in MySQL, SQLite vs MySQL vs PostgreSQL: A Comparison of Relational Database Management Systems, and How To Create and Use Stored Procedures in MySQL.