SQL BEGIN, COMMIT, ROLLBACK Transactions

-- PostgreSQL / SQLite
BEGIN;

-- MySQL
START TRANSACTION;

-- SQL Server
BEGIN TRANSACTION;

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

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

BEGIN;

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

ROLLBACK;  -- Both updates are undone

BEGIN TRANSACTION TransferFunds;

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

COMMIT TRANSACTION TransferFunds;

-- 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.

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

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

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

-- 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!

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

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;

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;

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;

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;

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;

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.

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.

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;

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

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())"
            )
except psycopg2.Error as e:
    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();
}

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;

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;

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

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;

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

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;

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;

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

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

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

-- 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!

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

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

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;

-- 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!

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!)

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;