SQL COMMIT, ROLLBACK, and SAVEPOINT Transactions

COMMIT and ROLLBACK are SQL commands used to control transactions. COMMIT permanently stores the work performed in the current transaction, while ROLLBACK removes changes that have not yet been committed and restores the database to its earlier state. Used together, these commands help maintain data integrity by allowing a complete set of changes to be saved or safely undone when an operation fails.

This tutorial explains the essential syntax for MySQL, PostgreSQL, Oracle, and SQL Server. It also covers savepoints, error-handling approaches, transaction behavior in stored procedures, retry and batch-processing patterns, and queries for diagnosing transaction problems in production environments. The examples can be used with standard installations of the respective database engines.

If you need to investigate a particular transaction problem, the section on diagnosing transaction issues in production contains engine-specific queries. If you are creating transaction logic for an application, the real-world transaction patterns section includes retry and batch-processing examples that can be adapted.

Key Takeaways

• COMMIT permanently stores the current transaction, whereas ROLLBACK removes it. After a successful COMMIT, ordinary transaction-control commands cannot reverse the changes. Recovery then requires a backup or database-specific point-in-time recovery functionality.
• DDL statements cause implicit commits in MySQL and Oracle. As a result, uncommitted DML executed before commands such as ALTER TABLE or DROP TABLE becomes permanent and cannot be rolled back. DDL and DML should therefore be handled as separate transaction units on these systems.
• Default autocommit behavior varies between database engines. MySQL and SQL Server normally use autocommit, Oracle SQL*Plus normally does not, and PostgreSQL provides autocommit at the client level rather than as a server-wide setting.
• SAVEPOINT makes it possible to undo only part of an active transaction. Because duplicate savepoint names behave differently between database systems, each checkpoint should use a unique name.
• Error handling depends on the database platform. SQL Server commonly uses TRY/CATCH together with IF @@TRANCOUNT > 0 and XACT_STATE(). PostgreSQL uses BEGIN ... EXCEPTION subtransactions that are automatically rolled back after an exception. Oracle uses PL/SQL patterns such as EXCEPTION WHEN OTHERS THEN ROLLBACK.
• Identity and sequence values are not reversed when a transaction rolls back on the major database engines. Gaps in AUTO_INCREMENT, IDENTITY, and PostgreSQL sequence values are therefore expected after rollbacks.
• Transaction troubleshooting in production usually begins by identifying long-running or idle in transaction sessions with sources such as pg_stat_activity, sys.dm_tran_active_transactions, information_schema.innodb_trx, or v$transaction.

Prerequisites

• Access to at least one installation of MySQL 8.0+, PostgreSQL 13+, Oracle 19c+, or SQL Server 2019+.
• Basic knowledge of SQL commands including SELECT, INSERT, UPDATE, and DELETE.
• A database client such as mysql, psql, sqlplus, or sqlcmd.
• If you are still selecting a database platform, compare SQLite, MySQL, and PostgreSQL first.

What Is a SQL Transaction?

A SQL transaction is a group of work that the database processes atomically. Either all statements within the transaction succeed and are committed as one unit, or none of their changes remain. A transaction therefore acts as a recovery boundary. If an operation fails before completion, the database engine can restore the data to the state that existed before the transaction without requiring manual cleanup.

The transaction lifecycle can be represented through three primary control points:

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

Transactions should follow ACID principles so that data remains reliable during failures, retries, and concurrent operations.

ACID Properties and Why They Matter

ACID describes the properties transaction engines use to maintain correct data during successful operations as well as failure scenarios.

Autocommit Mode vs. Explicit Transaction Control

With autocommit, every individual statement is executed as a separate transaction. Explicit transaction control instead combines several statements inside a single transaction boundary.

What Is COMMIT in SQL?

COMMIT marks the point where the work performed by a transaction becomes permanent database state. Before the command is issued, changes in the active transaction remain reversible. Once COMMIT succeeds, those modifications persist, become visible according to the database’s concurrency rules, and cannot be reversed using a normal rollback command.

COMMIT Syntax Across Database Platforms

The COMMIT keyword itself is consistent across the major database engines. The main differences concern how transactions are opened and how statements behave when they are not explicitly inside a transaction. The following table compares transaction-start and commit syntax.

After a transaction has been opened, a basic commit follows the same general structure:

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

In MySQL, BEGIN can be used as an alias for START TRANSACTION. For SQL Server, use BEGIN TRANSACTION; instead of BEGIN;. In Oracle, omit the BEGIN statement because the first DML operation starts the transaction implicitly.

One detail to consider is what happens when COMMIT is executed without an active transaction. MySQL and Oracle complete the command without an error. PostgreSQL produces the warning WARNING: there is no transaction in progress. SQL Server reports an error if no corresponding BEGIN TRANSACTION was issued. Applications that execute an unconditional COMMIT should therefore test that behavior on each database engine they support.

When a COMMIT Is Triggered Automatically

When autocommit is enabled, every successful SQL statement is handled as an individual transaction and committed automatically. An explicit COMMIT command is not required. This is a common default for database clients.

MySQL normally enables autocommit. MySQL and Oracle also perform implicit commits around DDL statements such as CREATE, ALTER, and DROP.

If DDL is executed in the middle of a transaction on MySQL or Oracle, previously uncommitted DML is committed automatically. Those earlier changes can no longer be reversed through standard transaction control.

Before changing transaction behavior for a database session, refer to the autocommit and explicit transaction comparison to match the database and client defaults with the correct commands.

What Is ROLLBACK in SQL?

ROLLBACK provides the recovery path when a transaction cannot be completed. A validation error, constraint violation, deadlock, or another failure can cause the work to be abandoned. Executing ROLLBACK restores the database changes to the state that existed before the transaction started. The session can then retry the operation, record the failure, or continue with other work.

ROLLBACK Syntax Across Database Platforms

ROLLBACK is also broadly consistent between database engines. SQL Server additionally supports the form ROLLBACK TRANSACTION, with ROLLBACK as the shorter alternative. Transaction-start rules remain the same as those described in the preceding section.

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

A complete ROLLBACK ends the transaction on every covered database engine and returns the session to its normal autocommit or implicit-transaction behavior. PostgreSQL has an additional rule: if an unhandled error occurs inside a transaction, the transaction enters an aborted state. Until ROLLBACK is issued, further statements are rejected with ERROR: current transaction is aborted, commands ignored until end of transaction block. This commonly becomes visible when a developer tries to execute a SELECT after a failed statement but before rolling the transaction back.

Full Transaction Rollback vs. Partial Rollback

A normal ROLLBACK reverses every modification made since the transaction started. ROLLBACK TO SAVEPOINT instead reverses only the work performed after a specified savepoint. The two commands provide different recovery scopes within the transaction model.

Using ROLLBACK without additional arguments performs a full rollback and closes the transaction. Using ROLLBACK TO SAVEPOINT sp_name performs a partial rollback while leaving the transaction open, allowing more SQL statements to run before a later COMMIT or another ROLLBACK.

The distinction affects more than syntax. A full rollback removes all changes since BEGIN, releases transaction locks, and ends the unit of work. A rollback to a savepoint removes only changes made after the selected checkpoint. Work performed before that checkpoint remains part of the active transaction, and the transaction can continue.

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 is committed, customer 1 has the state New York, customer 3 has Karnataka, and customer 2 remains unchanged. Full rollback and partial rollback therefore work together rather than representing mutually exclusive approaches.

The SAVEPOINT section explains the syntax and behavior of transaction checkpoints in more detail and demonstrates how partial rollback works in transactions with several steps.

What Is a SAVEPOINT in SQL?

A SAVEPOINT creates a checkpoint within an open transaction. The transaction can later return to that checkpoint without ending the complete unit of work. This is particularly useful when a transaction contains several stages and earlier valid operations should remain available even if a later stage must be reversed. Without savepoints, transaction recovery is generally an all-or-nothing choice. Savepoints provide more precise control over which work is retained.

The Savepoint Lifecycle

A savepoint is first created, may later become the target of a rollback, and is eventually released explicitly or removed automatically when the transaction finishes. A complete example 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;

Several details in this sequence are important because they frequently cause confusion when savepoints are used.

RELEASE SAVEPOINT does not commit any database changes. The command only removes the checkpoint itself. Modifications associated with the transaction remain uncommitted and can still be affected by a later COMMIT or ROLLBACK. The word “release” therefore refers to the savepoint marker rather than making its associated work permanent.

Duplicate savepoint names are handled differently between database engines. PostgreSQL moves the marker to the newer position so that the previous marker can no longer be reached. MySQL silently replaces the earlier checkpoint. Oracle makes the earlier checkpoint with the same name inaccessible. SQL Server permits duplicate names and rolls back to the most recently created matching savepoint. A practical cross-platform rule is therefore to avoid reusing savepoint names within the same transaction.

Savepoints are also removed automatically in certain situations. A COMMIT clears every savepoint belonging to the transaction. A ROLLBACK TO SAVEPOINT name removes savepoints created after the selected checkpoint but keeps the selected savepoint itself available. For example, if SAVEPOINT sp_b had been declared after SAVEPOINT after_customer_3_update, rolling back to after_customer_3_update would remove sp_b.

Savepoints also consume resources because the database must retain enough undo information to return to them. In batch processes that iterate over thousands of records, creating a savepoint for every iteration without releasing it can cause undo information to accumulate. Each checkpoint should be released after the protected section completes successfully.

SQL COMMIT and ROLLBACK Examples

The following examples use a single customer table containing four initial records. This makes it possible to compare the database contents before and after COMMIT, ROLLBACK, and rollback to a savepoint.

The examples use BEGIN; as the transaction-start command. This is standard PostgreSQL syntax and is also accepted by MySQL. MySQL can alternatively use START TRANSACTION;. For SQL Server, replace BEGIN; with BEGIN TRANSACTION; and COMMIT; with COMMIT TRANSACTION;.

Basic COMMIT Example

Begin by creating the table.

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

Insert four records.

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 of the records and commit the transaction.

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

Query the table after the 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 examining rollback behavior.

TRUNCATE TABLE customer;

Insert the four records again.

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 a row and then roll the transaction back.

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

Check the table contents after the 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

A savepoint can retain earlier validated modifications while reversing only later work performed 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 modification for customer_id = 1 remains because it occurred before sp1 was created. The later modification for customer_id = 4 is reversed by ROLLBACK TO SAVEPOINT sp1. The final COMMIT permanently stores the changes that remain in the transaction.

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

TRY/CATCH can provide a guaranteed rollback path when a statement inside a SQL Server transaction 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 attempt to insert a duplicate primary key for customer_id = 4 causes execution to move into the CATCH block. In sqlcmd, the output resembles the following:

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 changed state = 'WA' is reversed together with the unsuccessful INSERT. The table therefore returns to the state it had before BEGIN TRANSACTION. The IF @@TRANCOUNT > 0 condition before ROLLBACK TRANSACTION is important because certain error types, including connection-related failures and some severity 17+ errors, can cause the transaction to be rolled back before the CATCH block executes. Issuing another rollback when no transaction exists would generate a separate error and could hide the original failure.

SQL Server also provides SET XACT_ABORT ON, which can force rollback for many runtime errors that might otherwise leave a transaction active.

ROLLBACK on Error Using Exception Handling in PostgreSQL and Oracle

PostgreSQL and Oracle use different exception mechanisms, but both provide ways to handle failures while controlling transaction 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 unsuccessful INSERT run within the same BEGIN ... EXCEPTION subtransaction. When the exception occurs, PL/pgSQL automatically reverses that subtransaction, leaving customer 4 with its previous 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 in the exception handler reverses both the earlier UPDATE and the unsuccessful INSERT. This operation rolls back the entire active transaction for the session, including work completed before the anonymous block. The pattern should therefore be used only when the procedure or block is intended to control the complete transaction boundary.

Real-World Transaction Patterns

Simple examples usually show transactions with a fixed sequence of statements. Production applications often combine transactions with retries, batch processing, and audit logging. Those combinations require additional patterns.

Idempotent retry with deadlock handling. Transactions that operate under contention should be designed so they can safely execute again. A retry wrapper can catch the database-specific deadlock error and repeat the transaction after a delay.

# 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

Both statements inside the transaction need to tolerate a retry. The UPDATE checks qty >= 1, preventing an additional decrement when that condition is no longer true. The INSERT could otherwise create a duplicate order, so the table should use a unique request identifier or an idempotent insert technique such as INSERT ... ON CONFLICT DO NOTHING in PostgreSQL or INSERT ... ON DUPLICATE KEY UPDATE in MySQL.

Batch processing with a savepoint for each record. If a collection of records is processed and a single invalid item should not cancel the complete batch, a savepoint can be created during each 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 pattern is commonly used for ETL imports in which some records are expected to fail. Valid records are committed together, while invalid records are individually rolled back and logged for later review. The savepoint should be released after each record so that retained undo information does not increase with the size of the batch.

Audit logging that survives rollback. Audit information often has to remain available even if the parent transaction is reversed. The supported technique differs by engine:

• Oracle: mark the audit-writing procedure with PRAGMA AUTONOMOUS_TRANSACTION. The autonomous transaction can commit independently.
• PostgreSQL: send the audit entry through dblink to another connection with its own transaction context.
• SQL Server: write the audit record through a stored procedure invoked through a service broker, or use another connection from the application.
• MySQL: perform the audit operation through a separate connection. Standard MySQL SQL does not provide an autonomous-transaction equivalent.

Although the implementations differ, the central requirement is identical: the audit write has to execute outside the transaction boundary of the parent operation.

COMMIT and ROLLBACK in Stored Procedures

Transaction handling inside stored procedures varies between database systems. Transaction statements can be permitted, restricted, or affected by the surrounding execution context. Procedure behavior should therefore be tested with the same client and runtime configuration used in the target environment.

Behavior in SQL Server Stored Procedures

SQL Server procedures participate in the caller’s transaction context unless they begin transaction work themselves. The following defensive structure can be used whether or not the caller already has an active transaction.

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 techniques in this example are especially common in production T-SQL stored procedures.

@@TRANCOUNT is a counter maintained for the database session. Each BEGIN TRANSACTION increases it, while each COMMIT decreases it. The IF @@TRANCOUNT > 0 condition before ROLLBACK TRANSACTION protects against cases in which a runtime or connection-level error has already rolled the transaction back before execution reaches the CATCH block. Attempting another rollback when no transaction exists would raise another error and could hide the original problem.

THROW sends the original error back to the caller after the transaction has been handled. The original error number, severity, state, and message are preserved. For re-raising an existing error, THROW is preferable to RAISERROR, which changes error metadata and can make troubleshooting more difficult.

SET NOCOUNT ON prevents SQL Server from sending a row-count message after every statement. It is not required for transaction control itself, but it is common in stored procedures because these additional messages can interfere with applications that process database result streams.

If a procedure must be safe when called from code that already owns an open transaction, BEGIN TRANSACTION can be replaced with SAVE TRANSACTION sp_name, and the rollback can target ROLLBACK TRANSACTION sp_name. This lets the procedure reverse only its own work without closing the transaction owned by its caller.

Behavior in Oracle PL/SQL

Oracle PL/SQL procedures are able to execute COMMIT and ROLLBACK directly. These statements affect the current transaction context of the session.

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;
/

A procedure-level COMMIT or ROLLBACK in Oracle changes the transaction state for the current session. This type of procedure should therefore be called only when that transaction boundary is intentional.

Behavior in PostgreSQL Functions and Procedures

PostgreSQL procedures support COMMIT and ROLLBACK beginning with PostgreSQL 11. However, a block containing an EXCEPTION clause cannot end the surrounding transaction. PostgreSQL functions cannot execute transaction-control statements. Which pattern to use therefore depends on whether explicit transaction control or exception handling is needed.

Pattern one uses 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 provides exception handling without explicit transaction-control commands. When the exception is caught, PL/pgSQL automatically reverses the implicit subtransaction, so an explicit ROLLBACK is unnecessary.

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;
$$;

A PL/pgSQL block containing an EXCEPTION clause cannot execute COMMIT or ROLLBACK. Attempting to do so produces 2D000: invalid_transaction_termination at runtime. A block must therefore use either explicit transaction control or exception handling according to the required behavior.

Diagnosing Transaction Issues in Production

Transaction problems in production generally appear in one of three ways: a query remains blocked, changes expected to be committed cannot be found, or changes expected to be rolled back are still present. Each symptom requires a different diagnostic approach.

Finding Long-Running or Blocked Transactions

A query that appears to hang is often waiting for a lock held by another transaction on a required row or table. The first task is to identify the blocking transaction. After that, you can decide whether to wait for it, terminate it, or correct the application behavior that left it active.

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 that remain idle in transaction for more than a short period are frequent causes of blocking. The transaction is still open and may hold locks even though no query is running. This generally indicates that an application opened a transaction and then continued with unrelated work. Such a session can be terminated 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;

Terminate the session 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;

Terminate it 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;

Terminate the session with ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;. Without IMMEDIATE, Oracle marks the session for termination and waits until it becomes responsive, which can require several minutes for an idle session.

Recognizing a Doomed or Aborted Transaction

A transaction becomes doomed when an error has occurred and the transaction can no longer be committed. Database engines expose this situation in different ways.

PostgreSQL rejects every command other than ROLLBACK and reports ERROR: current transaction is aborted, commands ignored until end of transaction block. The transaction must be rolled back. If the application needs to recover from a risky operation and continue without removing earlier valid work, create a savepoint before executing that operation so that ROLLBACK TO SAVEPOINT can be used.

SQL Server reports transaction viability through XACT_STATE(). A result of -1 indicates that the transaction still exists but can no longer be committed. A production CATCH block can inspect this state:

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

MySQL and Oracle do not provide an equivalent transaction-state flag. In MySQL, a stored procedure can define DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; to catch errors and perform a rollback automatically. In Oracle, the PL/SQL block can use EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE;. Both approaches follow the stored-procedure patterns described earlier.

Confirming Whether Autocommit Silently Committed Your Work

If a ROLLBACK seems to have no effect and the modifications remain stored, autocommit is a likely explanation. Check the active setting for the database environment.

If autocommit is active and a standalone UPDATE is followed by ROLLBACK, the update was already committed when it completed successfully. The later rollback therefore has no pending changes to remove. To prevent this, either place the work inside an explicit transaction or disable autocommit for the session.

The same diagnostic queries can also be used with managed database services. Provider dashboards commonly expose engine-level metrics for long-running queries and transaction counts, while PostgreSQL and MySQL slow-query logging can be configured through standard database parameters. For PostgreSQL applications that require transaction-aware connection pooling, session-mode pooling preserves transaction state while a connection is pooled. Transaction-mode pooling does not preserve the same session state, so transaction patterns involving advisory locks, prepared statements, or SET LOCAL may behave differently.

Platform-Specific Differences to Know

Although transaction control follows common SQL principles, database engines differ in autocommit defaults, DDL transaction behavior, and nesting. These differences matter when designing safe transaction boundaries for applications that support several database systems.

MySQL and MariaDB

MySQL and MariaDB normally enable autocommit. A standalone UPDATE or INSERT entered in a client is therefore committed as soon as it succeeds. Multi-statement transactions require either disabling autocommit for the session with SET autocommit = 0 or explicitly opening the transaction with START TRANSACTION.

DDL operations including CREATE TABLE, ALTER TABLE, DROP TABLE, and TRUNCATE TABLE perform implicit commits. DML that has not yet been committed becomes permanent when such a DDL operation is executed. Migration scripts that combine schema and data changes can therefore produce unexpected results. DDL and DML should be separated into different transaction units.

Transaction support also depends on the table storage engine. InnoDB supports transactions, while MyISAM does not. A START TRANSACTION involving a MyISAM table does not necessarily produce an error, but ROLLBACK does not restore changes to MyISAM rows. Check the table definition with SHOW CREATE TABLE customer\G and confirm that it uses ENGINE=InnoDB before relying on transaction behavior.

AUTO_INCREMENT values are not rolled back. If an inserted record consumes a value and the surrounding transaction is later reversed, that number remains consumed. Gaps in AUTO_INCREMENT values are therefore normal.

PostgreSQL

PostgreSQL does not provide a server-wide autocommit option. Autocommit is controlled by clients and database drivers. The psql client and many drivers normally use autocommit, causing each statement to execute as its own transaction unless BEGIN opens an explicit one. In psql, autocommit can be disabled using \set AUTOCOMMIT off.

PostgreSQL supports transactional DDL. Commands such as CREATE TABLE, ALTER TABLE, DROP TABLE, and many other schema operations can be executed inside BEGIN ... COMMIT and reversed together with DML. Operations that work with global state or outside the normal MVCC system are exceptions, including CREATE DATABASE, DROP DATABASE, CREATE INDEX CONCURRENTLY, VACUUM, and REINDEX CONCURRENTLY.

When an error occurs inside a PostgreSQL transaction, the transaction enters an aborted state. Until it is closed, the session accepts only ROLLBACK, or ROLLBACK TO SAVEPOINT when a suitable savepoint exists before the failing statement. This prevents subsequent statements from reading an invalid transaction state. The PostgreSQL transaction-management documentation describes this recovery model in more detail. Risky statements can be placed after savepoints when the transaction needs to recover and continue.

PostgreSQL sequence values are not transactional. Executing SELECT nextval('customer_id_seq') consumes the value even if the surrounding transaction is subsequently rolled back. Sequence-generated identifiers should therefore not be expected to remain gap-free.

Oracle

Oracle SQL*Plus normally operates with autocommit disabled. A session is transaction-oriented, and the first DML statement begins the transaction implicitly. Standard Oracle SQL does not require BEGIN or START TRANSACTION to start a normal transaction. SET TRANSACTION is used to specify properties such as isolation level or read-only behavior rather than to open a transaction.

DDL causes an implicit commit both before and after the DDL command itself. The commit before the operation is particularly important. If uncommitted DML exists and the session executes ALTER TABLE, Oracle first commits the existing DML, executes the schema change, and then commits the DDL operation. The earlier data modifications are permanent at that point. Migration and test scripts should therefore avoid mixing DML and DDL within one intended transaction unit.

Oracle supports autonomous transactions through the PL/SQL directive PRAGMA AUTONOMOUS_TRANSACTION. An autonomous transaction can commit or roll back separately from the calling transaction. This can be used for audit data that must remain stored even if the parent operation fails, although autonomous transactions bypass the isolation boundary of the parent and should be used carefully.

Oracle sequences are also non-transactional. Calling customer_id_seq.NEXTVAL consumes the sequence number whether the surrounding transaction commits or rolls back. Together with the normal CACHE 20 behavior, this means sequence gaps are expected.

SQL Server (T-SQL)

SQL Server normally uses autocommit, making every standalone statement an individual transaction unless it is placed inside BEGIN TRANSACTION ... COMMIT TRANSACTION. Implicit-transaction mode can be enabled with SET IMPLICIT_TRANSACTIONS ON. In that mode, DML opens a transaction that remains active until an explicit commit or rollback is performed.

DDL support is partly transactional. Many DDL operations, including CREATE TABLE, ALTER TABLE, and DROP TABLE, can be rolled back within an explicit transaction. Operations that interact with file-system or system-level metadata in ways that cannot be reversed are exceptions. Examples include CREATE DATABASE, BACKUP, RESTORE, and certain ALTER DATABASE operations.

SQL Server does not provide independent nested transactions. Executing BEGIN TRANSACTION inside an existing transaction increases @@TRANCOUNT but does not create a separate inner transaction. Only the outermost COMMIT makes the work permanent. A ROLLBACK reverses the entire outer transaction regardless of the current nesting count. For this reason, defensive T-SQL commonly checks IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION before rolling back, since calling code may already have ended the transaction.

Values generated by IDENTITY columns are not restored by rollback. A failed insert can therefore leave gaps in the identity sequence. DBCC CHECKIDENT can inspect or reseed identity values, although gaps by themselves are expected behavior.

XACT_ABORT and XACT_STATE() are important tools for defensive transaction handling in T-SQL. SET XACT_ABORT ON automatically rolls the transaction back for runtime errors at severity 16 or above, preventing certain failures from leaving an unusable transaction open. XACT_STATE() returns -1 when a transaction is still present but can no longer be committed, so stored procedures should inspect that state before attempting to commit from a CATCH block.

Common Mistakes and How to Avoid Them

• Leaving autocommit enabled unintentionally can make each statement permanent before later validation is complete. For multi-statement work, explicitly begin the transaction with the command required by the engine, such as BEGIN, START TRANSACTION, or BEGIN TRANSACTION. Do not depend on session defaults that may vary between environments.
• Expecting DDL to roll back on MySQL or Oracle can cause unexpected permanent data changes because DDL implicitly commits earlier DML. Keep schema and data modifications in separate migration files or transaction units, and do not place ALTER TABLE between uncommitted DML statements on these systems.
• Combining implicit and explicit transaction styles in the same script can produce inconsistent rollback boundaries. Select one approach for each script: disable autocommit and execute the DML accordingly, or keep autocommit enabled and wrap multi-statement operations in explicit BEGIN ... COMMIT transactions.
• Keeping transactions open for too long extends lock duration, blocks other sessions, and increases timeout risk. Session-level timeouts can be configured with SET statement_timeout = '30s' in PostgreSQL, SET SESSION MAX_EXECUTION_TIME = 30000 in MySQL, or SET LOCK_TIMEOUT 30000 in SQL Server. Long-running sessions can be identified using SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction' in PostgreSQL or SELECT * FROM sys.dm_tran_active_transactions in SQL Server.
• Failing to retry deadlocks can make concurrent batch workloads fail intermittently. Wrap the transaction in retry logic that recognizes the relevant deadlock error, including 1213 for MySQL, 40P01 for PostgreSQL, 1205 for SQL Server, and ORA-00060 for Oracle, then retry using exponential backoff. The complete transaction must be designed to be idempotent.
• Rollback paths that are never tested can hide error-handling defects until production. Tests can deliberately introduce a failing statement, such as a primary-key violation, inside the transaction and verify that the database state after rollback is identical to its state before the transaction.
• Reusing the same savepoint name produces database-specific behavior that can be difficult to diagnose. Generate distinct names for checkpoints, such as sp_iteration_1 and sp_iteration_2, or use a counter.
• Executing COMMIT when no transaction exists can succeed silently, generate a warning, or produce an error depending on the database engine. Applications supporting several engines should inspect transaction state before committing. PostgreSQL provides pg_current_xact_id_if_assigned(), SQL Server provides XACT_STATE(), and MySQL provides @@in_transaction.

Frequently Asked Questions

What Is the Difference Between COMMIT and ROLLBACK in SQL?

COMMIT permanently stores the modifications made in the current transaction. ROLLBACK removes changes that have not been committed and restores the data to its last committed state.

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

The result depends on the database engine and session configuration. With autocommit enabled, individual statements are automatically committed. Explicit transactions are generally rolled back if the session ends unexpectedly before they are completed.

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

A SAVEPOINT identifies an intermediate checkpoint inside an active transaction. It is useful when later stages of a multi-step operation might need to be reversed without removing earlier work that has already been validated.

Can You Roll Back a COMMIT in SQL?

No. Standard transaction-control statements cannot reverse a transaction after it has been committed. Recovery after a commit requires backups or database-specific recovery mechanisms outside normal COMMIT and ROLLBACK processing.

How Does SQL Server Handle Automatic ROLLBACK on Error?

SQL Server does not automatically roll back every type of error with its default settings. Risky statements should be placed inside TRY/CATCH logic, with ROLLBACK TRANSACTION executed from the CATCH block and protected by IF @@TRANCOUNT > 0. For stricter handling, SET XACT_ABORT ON triggers automatic rollback for most runtime errors at severity 16 or higher. XACT_STATE() can identify a doomed transaction when it returns -1 before an attempted commit in a CATCH block.

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

The fundamental COMMIT and ROLLBACK syntax is similar on both systems. Their default operating behavior differs because MySQL normally enables autocommit, whereas Oracle SQL*Plus commonly operates with autocommit disabled.

Can You Use COMMIT and ROLLBACK Inside a Stored Procedure?

Yes, although support and behavior differ between platforms. SQL Server procedures participate in the caller’s transaction context and commonly use TRY/CATCH with IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION. Oracle PL/SQL procedures can execute COMMIT and ROLLBACK directly, but doing so affects the entire transaction for the current session. PostgreSQL procedures, but not functions, support COMMIT and ROLLBACK from PostgreSQL 11 onward. A PostgreSQL block containing an EXCEPTION clause cannot execute transaction-control statements, and PostgreSQL functions cannot perform transaction control at all.

What Is the Difference Between ROLLBACK and ROLLBACK TO SAVEPOINT?

ROLLBACK without arguments reverses every change in the active transaction and closes that transaction. ROLLBACK TO SAVEPOINT reverses only work performed after the named checkpoint and leaves the transaction active.

Conclusion

This tutorial explained SQL transactions and ACID principles, the behavior and syntax of COMMIT, ROLLBACK, and SAVEPOINT, TRY/CATCH and exception-based error handling, transaction management inside stored procedures for four major database engines, retry-safe and batch-safe transaction patterns, and diagnostic queries for long-running or unusable transactions in production.

You can use these techniques to create transaction-safe SQL that commits only validated changes, recover from failures through TRY/CATCH or exception blocks, use savepoints for partial rollback, construct idempotent retry loops for workloads affected by deadlocks, isolate individual failures during batch processing, and investigate transaction problems with pg_stat_activity, sys.dm_tran_active_transactions, information_schema.innodb_trx, or v$transaction.