SQL BEGIN, COMMIT, ROLLBACK Transactions

You understand why transactions matter. Now it is time to master the actual commands that control them. BEGIN, COMMIT, and ROLLBACK are the three SQL statements that give you explicit control over when changes become permanent and when they should be discarded. Combined with savepoints for partial rollbacks and proper error handling, these commands let you build bulletproof data operations that never leave your database in a broken state.

This guide covers the complete transaction syntax across major databases, how auto-commit behavior works and when it gets in the way, how savepoints let you undo part of a transaction without abandoning all of it, and how to handle errors gracefully inside transactions. Every concept includes practical examples with clear before-and-after states so you can see exactly what happens at each step.

The Sample Data

We will use these tables throughout the guide:

accounts table:

id	owner	account_type	balance
1	Alice	checking	5000.00
2	Alice	savings	12000.00
3	Bob	checking	3200.00
4	Bob	savings	8500.00
5	Charlie	checking	750.00

orders table:

id	customer_id	product	quantity	total	status
1	101	Widget Pro	2	159.98	pending
2	102	Gadget Max	1	49.99	pending

inventory table:

product_id	product_name	stock
501	Widget Pro	10
502	Gadget Max	3
503	Desk Lamp	0

audit_log table:

id	action	details	created_at

Transaction Syntax

BEGIN

BEGIN (or START TRANSACTION) marks the start of a transaction. From this point forward, all SQL statements are part of the same logical unit of work. Nothing becomes permanent until you explicitly COMMIT.

-- PostgreSQL / SQLite
BEGIN;

-- MySQL
START TRANSACTION;

-- SQL Server
BEGIN TRANSACTION;

-- Oracle (no explicit BEGIN; transactions start implicitly)
-- Just start writing statements

COMMIT

COMMIT makes all changes since BEGIN permanent. Once committed, the changes are durable (they survive crashes) and visible to other transactions.

BEGIN;

UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;

COMMIT;  -- Both updates are now permanent

Before COMMIT:

id	owner	balance	State
1	Alice	4800.00	Changed but not permanent
2	Alice	12200.00	Changed but not permanent

After COMMIT:

id	owner	balance	State
1	Alice	4800.00	Permanent
2	Alice	12200.00	Permanent

ROLLBACK

ROLLBACK undoes all changes since BEGIN. The database returns to exactly the state it was in before the transaction started.

BEGIN;

UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;

ROLLBACK;  -- Both updates are undone

After ROLLBACK:

id	owner	balance	State
1	Alice	5000.00	Original value restored
2	Alice	12000.00	Original value restored

It is as if the two UPDATE statements never happened.

Complete Syntax Reference

Database	Begin	Commit	Rollback
PostgreSQL	BEGIN; or START TRANSACTION;	COMMIT;	ROLLBACK;
MySQL	START TRANSACTION; or BEGIN;	COMMIT;	ROLLBACK;
SQL Server	BEGIN TRANSACTION; or BEGIN TRAN;	COMMIT TRANSACTION; or COMMIT;	ROLLBACK TRANSACTION; or ROLLBACK;
Oracle	Implicit (no command needed)	COMMIT;	ROLLBACK;
SQLite	BEGIN TRANSACTION; or BEGIN;	COMMIT;	ROLLBACK;

Naming Transactions (SQL Server)

SQL Server allows you to name transactions, which can help with debugging and logging:

BEGIN TRANSACTION TransferFunds;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT TRANSACTION TransferFunds;

The name appears in logs and error messages, making it easier to identify which transaction caused an issue.

Auto-Commit Behavior

What Is Auto-Commit?

In auto-commit mode, every individual SQL statement is automatically wrapped in its own transaction and committed immediately after execution. You do not need to write BEGIN or COMMIT. Each statement is independent.

-- In auto-commit mode (default for most databases):

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- ← Automatically committed. PERMANENT. Cannot be undone.

UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- ← Automatically committed. PERMANENT. Cannot be undone.

If the second statement fails, the first one is already committed. You cannot roll it back. This is perfectly fine for single-statement operations but dangerous for multi-statement operations that must succeed or fail together.

Auto-Commit Defaults by Database

Database	Auto-Commit Default
PostgreSQL	ON
MySQL	ON
SQL Server	ON
Oracle	OFF (every statement is part of a transaction until you COMMIT)
SQLite	ON

Disabling Auto-Commit

Method 1: Use Explicit Transactions (Recommended)

The most portable and clear approach is to wrap multi-statement operations in explicit BEGIN/COMMIT:

-- Auto-commit is still ON globally, but this block is a single transaction
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

Inside the BEGIN/COMMIT block, auto-commit is effectively suspended. Nothing is committed until you say COMMIT.

Method 2: Disable Auto-Commit at the Session Level

-- PostgreSQL (not common; explicit transactions are preferred)
-- Auto-commit is handled by the client driver, not the server

-- MySQL
SET autocommit = 0;
-- Now every statement requires an explicit COMMIT
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;
SET autocommit = 1;  -- Re-enable

-- SQL Server
SET IMPLICIT_TRANSACTIONS ON;
-- Now every statement starts a transaction automatically
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;
SET IMPLICIT_TRANSACTIONS OFF;

caution

Disabling auto-commit at the session level means every statement requires an explicit COMMIT, even simple SELECT queries (in some databases). If you forget to commit, your transaction stays open indefinitely, holding locks and potentially blocking other users. The explicit BEGIN/COMMIT approach is safer because the transaction scope is always clearly defined.

The Auto-Commit Trap

One of the most common transaction mistakes is assuming that two consecutive statements in auto-commit mode are atomic:

-- WRONG: These are TWO separate transactions in auto-commit mode
UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- Transaction 1: committed
-- ← Power failure here means $500 is gone but never credited
UPDATE accounts SET balance = balance + 500 WHERE id = 2;  -- Transaction 2: never executes

-- RIGHT: One atomic transaction
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;  -- Both committed together, or neither

DDL Statements and Auto-Commit

Some databases automatically commit any open transaction when a DDL statement (CREATE TABLE, ALTER TABLE, DROP TABLE, etc.) is executed. This behavior varies:

Database	DDL Causes Implicit Commit?
PostgreSQL	No (DDL is transactional)
MySQL	Yes (DDL forces a commit before and after)
SQL Server	No (DDL is transactional)
Oracle	Yes (DDL forces a commit)
SQLite	Some DDL is transactional

-- MySQL: DDL breaks your transaction!
START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;

CREATE TABLE temp_log (id INT);  -- IMPLICIT COMMIT happens here!
-- The UPDATE above is now permanently committed

UPDATE accounts SET balance = balance + 500 WHERE id = 2;
ROLLBACK;  -- Only rolls back the credit, not the debit!

caution

In MySQL and Oracle, never mix DDL and DML inside a transaction if you need atomicity. The DDL statement will silently commit your pending changes. PostgreSQL and SQL Server do not have this problem because their DDL statements are transactional.

Savepoints

A savepoint creates a named checkpoint within a transaction. You can roll back to a savepoint without rolling back the entire transaction. This gives you fine-grained control over which parts of a transaction to keep and which to discard.

Why Savepoints Exist

Without savepoints, your only options are COMMIT (keep everything) or ROLLBACK (discard everything). But sometimes you want to:

• Try an operation and undo just that operation if it fails, while keeping earlier work
• Process a batch of items where some may fail individually without killing the entire batch
• Implement nested "try/catch" logic within a single transaction

Syntax

-- Create a savepoint
SAVEPOINT savepoint_name;

-- Roll back to a savepoint (undo everything AFTER the savepoint)
ROLLBACK TO SAVEPOINT savepoint_name;
-- or simply:
ROLLBACK TO savepoint_name;

-- Release a savepoint (discard it without rolling back)
RELEASE SAVEPOINT savepoint_name;

Database	Create	Rollback To	Release
PostgreSQL	SAVEPOINT name;	ROLLBACK TO name;	RELEASE SAVEPOINT name;
MySQL	SAVEPOINT name;	ROLLBACK TO name;	RELEASE SAVEPOINT name;
SQL Server	SAVE TRANSACTION name;	ROLLBACK TRANSACTION name;	Not supported
Oracle	SAVEPOINT name;	ROLLBACK TO name;	Not needed
SQLite	SAVEPOINT name;	ROLLBACK TO name;	RELEASE name;

Basic Savepoint Example

BEGIN;

-- Step 1: Debit the source account
UPDATE accounts SET balance = balance - 500 WHERE id = 1;

-- Create a checkpoint after the debit
SAVEPOINT after_debit;

-- Step 2: Try to credit the destination account
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- Oops, we credited the wrong account! Roll back to the savepoint.
ROLLBACK TO after_debit;
-- The credit to account 2 is undone, but the debit from account 1 is preserved

-- Step 3: Credit the correct account
UPDATE accounts SET balance = balance + 500 WHERE id = 4;

COMMIT;

Step-by-step state:

Step	Account 1	Account 2	Account 4
Before BEGIN	5000	12000	8500
After debit	4500	12000	8500
After wrong credit	4500	12500	8500
After ROLLBACK TO	4500	12000 (restored)	8500
After correct credit	4500	12000	9000
After COMMIT	4500	12000	9000

The debit survived the rollback because it happened before the savepoint. Only changes made after the savepoint were undone.

Multiple Savepoints

You can create as many savepoints as you need within a single transaction:

BEGIN;

INSERT INTO audit_log (action, details) VALUES ('start', 'Processing order batch');
SAVEPOINT batch_start;

-- Process order 1
UPDATE inventory SET stock = stock - 2 WHERE product_id = 501;
INSERT INTO audit_log (action, details) VALUES ('order_1', 'Processed successfully');
SAVEPOINT after_order_1;

-- Process order 2
UPDATE inventory SET stock = stock - 1 WHERE product_id = 502;
INSERT INTO audit_log (action, details) VALUES ('order_2', 'Processed successfully');
SAVEPOINT after_order_2;

-- Process order 3 (this one fails)
UPDATE inventory SET stock = stock - 5 WHERE product_id = 503;
-- stock is 0, subtracting 5 gives -5, CHECK constraint violation!

-- Roll back only order 3
ROLLBACK TO after_order_2;

-- Orders 1 and 2 are still intact
INSERT INTO audit_log (action, details) VALUES ('order_3', 'Failed: insufficient stock');

COMMIT;

Final state:

• Order 1: processed (inventory deducted)
• Order 2: processed (inventory deducted)
• Order 3: not processed (rolled back to savepoint)
• Audit log: Contains entries for all three, including the failure note

Nested Savepoints

Savepoints can be nested. Rolling back to an outer savepoint also discards all inner savepoints and their changes:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT sp_outer;

    UPDATE accounts SET balance = balance - 200 WHERE id = 1;
    SAVEPOINT sp_inner;

        UPDATE accounts SET balance = balance - 300 WHERE id = 1;
        -- Balance is now: 5000 - 100 - 200 - 300 = 4400

    ROLLBACK TO sp_outer;
    -- Rolls back BOTH the -300 AND the -200
    -- Balance is now: 5000 - 100 = 4900

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- Balance is now: 4900 - 50 = 4850

COMMIT;

Final balance for Account 1: 4850

The -200 and -300 operations were both undone because rolling back to sp_outer discards everything that happened after that savepoint, including sp_inner and its changes.

Releasing Savepoints

RELEASE SAVEPOINT destroys the savepoint without rolling back. This is useful for cleanup and also confirms that the work after the savepoint is "accepted" (though not yet committed):

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
SAVEPOINT after_debit;

UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- Everything looks good, release the savepoint (we won't need to roll back to it)
RELEASE SAVEPOINT after_debit;

-- Can no longer ROLLBACK TO after_debit (it's been released)

COMMIT;

info

In PostgreSQL, releasing a savepoint also releases all savepoints that were created after it. In practice, RELEASE is not commonly used unless you are managing many savepoints and want to clean up.

Error Handling in Transactions

Proper error handling is what separates reliable transaction code from code that silently corrupts data. Different databases handle errors within transactions differently, and understanding these differences is crucial.

PostgreSQL: Transactions Abort on Error

In PostgreSQL, if any statement within a transaction fails, the entire transaction enters an aborted state. All subsequent statements are rejected until you issue a ROLLBACK.

BEGIN;

INSERT INTO accounts (id, owner, account_type, balance)
VALUES (1, 'Duplicate', 'checking', 1000);
-- ERROR: duplicate key value violates unique constraint "accounts_pkey"

-- Transaction is now in an aborted state
SELECT * FROM accounts;
-- ERROR: current transaction is aborted, commands ignored until
--        end of transaction block

-- Must ROLLBACK to clear the error
ROLLBACK;

This behavior is actually a safety feature: it prevents you from accidentally committing a partially completed transaction where one step failed silently.

The Savepoint Workaround in PostgreSQL

To handle expected errors without aborting the entire transaction, wrap the risky operation in a savepoint:

BEGIN;

-- Step 1: Safe operation
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Step 2: Risky operation (might fail)
SAVEPOINT risky_op;

INSERT INTO accounts (id, owner, account_type, balance)
VALUES (1, 'Duplicate', 'checking', 1000);
-- ERROR: duplicate key value

-- Roll back only the failed operation
ROLLBACK TO risky_op;

-- Transaction is NOT aborted! We can continue.
-- Step 3: Alternative operation
INSERT INTO accounts (id, owner, account_type, balance)
VALUES (6, 'Diana', 'checking', 2000);

COMMIT;
-- Step 1 and Step 3 are committed. Step 2 is discarded.

MySQL: Statements Fail Independently

MySQL handles transaction errors differently from PostgreSQL. A failed statement does not abort the entire transaction. The failed statement is rolled back automatically, but the transaction continues:

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- ✅ Succeeds

INSERT INTO accounts (id, owner, account_type, balance)
VALUES (1, 'Duplicate', 'checking', 1000);
-- ❌ Error: Duplicate entry. This INSERT is automatically rolled back.
-- BUT the transaction continues!

UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- ✅ Succeeds

COMMIT;
-- The UPDATE on account 1 and the UPDATE on account 2 are committed.
-- The failed INSERT is not.

caution

MySQL's behavior can be dangerous if you are not careful. The transaction partially succeeded (two updates committed, one insert failed). If the insert was a critical step (like creating an order before deducting inventory), you now have an inconsistent state. Always check for errors after each statement in MySQL and explicitly ROLLBACK if a critical step fails.

SQL Server: TRY/CATCH Blocks

SQL Server provides structured error handling within transactions using TRY/CATCH:

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE accounts SET balance = balance - 500 WHERE id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE id = 2;

    INSERT INTO audit_log (action, details, created_at)
    VALUES ('transfer', 'Account 1 to Account 2: $500', GETDATE());

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- An error occurred: roll back everything
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- Log or re-throw the error
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;

@@TRANCOUNT checks whether a transaction is currently open. This prevents errors from trying to ROLLBACK when no transaction exists.

PostgreSQL: PL/pgSQL Error Handling

In PostgreSQL procedural code, you can use EXCEPTION blocks:

DO $$
DECLARE
    v_balance NUMERIC;
BEGIN
    -- Start the transaction (implicit in DO blocks)

    -- Check balance first
    SELECT balance INTO v_balance FROM accounts WHERE id = 5;

    IF v_balance < 500 THEN
        RAISE EXCEPTION 'Insufficient funds: balance is %', v_balance;
    END IF;

    UPDATE accounts SET balance = balance - 500 WHERE id = 5;
    UPDATE accounts SET balance = balance + 500 WHERE id = 4;

    INSERT INTO audit_log (action, details, created_at)
    VALUES ('transfer', 'Account 5 to Account 4: $500', NOW());

EXCEPTION
    WHEN OTHERS THEN
        -- The EXCEPTION block automatically rolls back to an implicit savepoint
        RAISE NOTICE 'Transfer failed: %', SQLERRM;
        -- Re-raise if you want the error to propagate
        RAISE;
END;
$$;

PostgreSQL EXCEPTION Block Behavior

In PostgreSQL, an EXCEPTION block internally creates an implicit savepoint before the block starts. If an error occurs, PostgreSQL rolls back to that savepoint (undoing the block's changes) and then executes the exception handler. The surrounding transaction can continue if the handler does not re-raise the error.

Application-Level Error Handling Patterns

Regardless of your database, the most reliable error handling pattern is in your application code:

# Python with psycopg2 (PostgreSQL)
import psycopg2

conn = psycopg2.connect("dbname=mydb")
try:
    with conn:  # Automatically handles BEGIN/COMMIT/ROLLBACK
        with conn.cursor() as cur:
            cur.execute("UPDATE accounts SET balance = balance - 500 WHERE id = 1")
            cur.execute("UPDATE accounts SET balance = balance + 500 WHERE id = 2")
            cur.execute(
                "INSERT INTO audit_log (action, details, created_at) "
                "VALUES ('transfer', 'Account 1 to 2: $500', NOW())"
            )
    # If we get here, COMMIT happened automatically
except psycopg2.Error as e:
    # ROLLBACK happened automatically due to the exception
    print(f"Transaction failed: {e}")
finally:
    conn.close()

// Node.js with pg (PostgreSQL)
const client = await pool.connect();
try {
    await client.query('BEGIN');
    await client.query('UPDATE accounts SET balance = balance - 500 WHERE id = 1');
    await client.query('UPDATE accounts SET balance = balance + 500 WHERE id = 2');
    await client.query(
        "INSERT INTO audit_log (action, details, created_at) VALUES ('transfer', 'Account 1 to 2: $500', NOW())"
    );
    await client.query('COMMIT');
} catch (e) {
    await client.query('ROLLBACK');
    console.error('Transaction failed:', e.message);
    throw e;
} finally {
    client.release();
}

The pattern is always the same:

1. BEGIN the transaction
2. Execute all statements
3. COMMIT if everything succeeded
4. ROLLBACK if any error occurred
5. Release the connection regardless

Real-World Patterns

Pattern 1: E-Commerce Order Processing

BEGIN;

-- Step 1: Validate and reserve inventory
SAVEPOINT inventory_check;

UPDATE inventory SET stock = stock - 2
WHERE product_id = 501 AND stock >= 2;
-- Check if the update affected any rows
-- (Application code checks: if rows_affected = 0, product is out of stock)

-- Step 2: Create the order
INSERT INTO orders (customer_id, product, quantity, total, status)
VALUES (103, 'Widget Pro', 2, 159.98, 'confirmed');

-- Step 3: Charge the customer
SAVEPOINT before_charge;

UPDATE accounts SET balance = balance - 159.98
WHERE id = 5 AND balance >= 159.98;
-- If balance insufficient, roll back to before_charge

-- Step 4: Log everything
INSERT INTO audit_log (action, details, created_at)
VALUES ('order_placed', 'Customer 103: 2x Widget Pro, $159.98', NOW());

COMMIT;

Pattern 2: Batch Processing with Per-Item Rollback

Process a batch of operations where individual failures should not kill the entire batch:

BEGIN;

-- Process item 1
SAVEPOINT item_1;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 501;
INSERT INTO audit_log (action, details) VALUES ('ship', 'Product 501 shipped');
RELEASE SAVEPOINT item_1;

-- Process item 2
SAVEPOINT item_2;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 502;
INSERT INTO audit_log (action, details) VALUES ('ship', 'Product 502 shipped');
RELEASE SAVEPOINT item_2;

-- Process item 3 (fails: out of stock)
SAVEPOINT item_3;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 503;
-- stock is 0, CHECK constraint violation
ROLLBACK TO item_3;
INSERT INTO audit_log (action, details) VALUES ('ship_failed', 'Product 503 out of stock');

-- Items 1 and 2 succeeded, item 3 failed gracefully
COMMIT;

Final state:

product_id	product_name	stock	Shipped?
501	Widget Pro	9	Yes
502	Gadget Max	2	Yes
503	Desk Lamp	0	No (out of stock)

The audit log contains entries for all three items, including the failure.

Pattern 3: Conditional Commit or Rollback

BEGIN;

-- Perform all updates
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 3;

-- Verify the total balance hasn't changed (business rule: conservation of money)
-- Application code:
SELECT SUM(balance) FROM accounts;
-- If total equals the expected amount → COMMIT
-- If total does NOT equal the expected amount → ROLLBACK (something went wrong)

COMMIT;  -- or ROLLBACK based on the check

Pattern 4: Idempotent Operations with Savepoints

When retrying operations that might partially succeed:

BEGIN;

-- Try to insert, handle duplicate gracefully
SAVEPOINT try_insert;

INSERT INTO accounts (id, owner, account_type, balance)
VALUES (6, 'Diana', 'checking', 2000);

-- If this was a duplicate:
-- ROLLBACK TO try_insert;
-- UPDATE accounts SET balance = 2000 WHERE id = 6;
-- (upsert pattern)

COMMIT;

PostgreSQL also supports INSERT ... ON CONFLICT for this pattern, which is simpler:

INSERT INTO accounts (id, owner, account_type, balance)
VALUES (6, 'Diana', 'checking', 2000)
ON CONFLICT (id) DO UPDATE SET balance = EXCLUDED.balance;

Transaction Scope and Visibility

Understanding what is visible to whom during a transaction is critical for building correct multi-user applications.

Changes Are Visible Inside the Transaction

Statements within the same transaction can see changes made by earlier statements in that transaction:

BEGIN;

INSERT INTO accounts (id, owner, account_type, balance)
VALUES (7, 'Eve', 'checking', 3000);

-- This SELECT sees the newly inserted row (within the same transaction)
SELECT * FROM accounts WHERE id = 7;
-- Returns: (7, 'Eve', 'checking', 3000)

COMMIT;

Changes Are NOT Visible to Other Transactions (Until COMMIT)

Transaction A:                         Transaction B:

BEGIN;
INSERT INTO accounts (id, ...)
VALUES (7, 'Eve', 'checking', 3000);
                                       BEGIN;
                                       SELECT * FROM accounts WHERE id = 7;
                                       -- Returns: NOTHING (not yet committed)
COMMIT;
                                       SELECT * FROM accounts WHERE id = 7;
                                       -- Returns: (7, 'Eve', 'checking', 3000)
                                       --          (now visible after A committed)
                                       COMMIT;

This is the isolation property in action. Transaction B does not see Transaction A's changes until A commits. The exact behavior depends on the isolation level (READ COMMITTED vs REPEATABLE READ), as covered in the ACID properties guide.

Common Mistakes to Avoid

Mistake 1: Forgetting BEGIN for Multi-Statement Operations

-- WRONG: No transaction. Each statement auto-commits independently.
UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- Committed!
-- ← If crash happens here, $500 is gone but never credited
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- RIGHT: Explicit transaction
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

Mistake 2: Leaving Transactions Open

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Developer gets distracted, never commits or rolls back
-- Transaction stays open for HOURS

-- Consequences:
-- • Locks are held, blocking other transactions
-- • Database cannot reclaim dead row space (PostgreSQL VACUUM blocked)
-- • Connection pool exhaustion if this happens repeatedly
-- • Other users experience timeouts and slowness

Fix: Always ensure every BEGIN has a matching COMMIT or ROLLBACK. Set statement timeouts as a safety net:

-- PostgreSQL: Kill transactions that run longer than 5 minutes
SET statement_timeout = '5min';

-- MySQL: Kill transactions that run longer than 30 seconds
SET innodb_lock_wait_timeout = 30;

Mistake 3: Committing After an Error in MySQL

-- MySQL: Error does NOT abort the transaction
START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- ✅ Succeeds (balance is now 4500)

INSERT INTO accounts (id) VALUES (1);
-- ❌ Fails (duplicate key), but transaction continues!

COMMIT;
-- ⚠️ The debit is committed even though the next step failed!

Fix: Always check for errors after each statement and explicitly rollback:

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;

INSERT INTO accounts (id) VALUES (1);
-- Error detected by application code

ROLLBACK;  -- Explicitly undo everything

Mistake 4: Nesting BEGIN Statements

Most databases do not support true nested transactions. A second BEGIN inside an existing transaction either causes an error or is silently ignored:

-- PostgreSQL: Second BEGIN triggers a WARNING
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

BEGIN;  -- WARNING: there is already a transaction in progress
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;  -- Commits EVERYTHING (both updates)
-- There is no second "layer" to commit separately

Fix: Use savepoints for nested rollback points instead of nested transactions:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

SAVEPOINT nested_operation;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- If this needs to be undone independently:
-- ROLLBACK TO nested_operation;

COMMIT;

Mistake 5: Mixing DDL and DML in MySQL/Oracle

-- MySQL: DDL silently commits your pending DML!
START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Not yet committed...

ALTER TABLE audit_log ADD COLUMN severity VARCHAR(20);
-- IMPLICIT COMMIT! The UPDATE above is now permanent!

UPDATE accounts SET balance = balance + 500 WHERE id = 2;

ROLLBACK;
-- Only rolls back the second UPDATE. The first one is already committed!

Fix: Separate DDL and DML operations. Run DDL outside of your data manipulation transactions.

Mistake 6: Rolling Back to a Released or Non-Existent Savepoint

BEGIN;

SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
RELEASE SAVEPOINT sp1;

ROLLBACK TO sp1;
-- ERROR: savepoint "sp1" does not exist (it was released!)

Fix: Do not release savepoints you might need to roll back to. Or, create a new savepoint if you need a rollback point after releasing:

BEGIN;

SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- If everything is OK, proceed (don't release unless you're sure)

-- Later, if needed:
ROLLBACK TO sp1;  -- This works because sp1 was not released
COMMIT;

Transaction Best Practices Summary

• Keep transactions short

  • Do calculations and validation before BEGIN
  • Only keep the transaction open for actual writes

• Always pair BEGIN with COMMIT or ROLLBACK

  • Use try/catch in application code
  • Set statement timeouts as a safety net

• Use SAVEPOINT for partial rollbacks

  • Useful for batch processing with per-item error handling
  • Essential in PostgreSQL for handling expected errors

• Check for errors after *every statement- (especially in MySQL)

  • MySQL does not automatically abort transactions on error
  • Explicitly ROLLBACK when a critical step fails

• Do not mix DDL and DML in MySQL/Oracle transactions

  • DDL can cause implicit commits

• Use the smallest transaction scope possible

  • Reduces lock contention
  • Improves concurrency
  • Minimizes rollback impact

• Test your error paths

  • Intentionally trigger failures
  • Verify rollback behavior
  • Ensure no partial state remains

Summary

SQL COMMIT ROLLBACK commands give you explicit control over when database changes become permanent:

• BEGIN starts a transaction. All subsequent statements are part of one atomic unit until you COMMIT or ROLLBACK.
• COMMIT makes all changes since BEGIN permanent and visible to other transactions. Once committed, changes survive crashes (durability).
• ROLLBACK undoes all changes since BEGIN, restoring the database to its pre-transaction state. Nothing was ever changed from the perspective of other transactions.
• Auto-commit mode wraps each individual statement in its own transaction. It is convenient for single statements but dangerous for multi-statement operations. Use explicit BEGIN/COMMIT for any operation that must be atomic.
• Savepoints create named checkpoints within a transaction. ROLLBACK TO savepoint_name undoes changes after the savepoint while preserving earlier work. Essential for batch processing, expected error handling, and PostgreSQL's strict error behavior.
• Error handling varies by database. PostgreSQL aborts the entire transaction on any error (use savepoints to work around this). MySQL allows the transaction to continue after an error (always check for errors explicitly). SQL Server provides TRY/CATCH blocks.
• Keep transactions short, always match BEGIN with COMMIT or ROLLBACK, and test your error handling paths to ensure no partial state leaks through.

tip

Think of BEGIN as opening a safety net and COMMIT as removing it because the acrobat landed safely. ROLLBACK is the safety net catching the acrobat after a fall. You would never perform dangerous acrobatics without a safety net, and you should never perform multi-statement database operations without a transaction.