CodeToLive

SQL Transactions

Transactions allow you to execute multiple SQL operations as a single atomic unit of work. They ensure data integrity by following the ACID principles (Atomicity, Consistency, Isolation, Durability).

Sample Tables for Examples

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_name VARCHAR(100) NOT NULL,
    balance DECIMAL(15,2) NOT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    from_account INT,
    to_account INT,
    amount DECIMAL(15,2),
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'PENDING'
);

INSERT INTO accounts VALUES 
(1001, 'Checking Account', 5000.00, CURRENT_TIMESTAMP),
(1002, 'Savings Account', 15000.00, CURRENT_TIMESTAMP),
(1003, 'Business Account', 25000.00, CURRENT_TIMESTAMP);
        

Basic Transaction Syntax

-- MySQL/PostgreSQL/SQLite
START TRANSACTION;
  -- SQL statements
COMMIT;
-- or ROLLBACK;

-- SQL Server
BEGIN TRANSACTION;
  -- SQL statements
COMMIT;
-- or ROLLBACK;

-- Oracle
SET TRANSACTION;
  -- SQL statements
COMMIT;
-- or ROLLBACK;
        

Transaction Example: Money Transfer

START TRANSACTION;

-- Deduct from source account
UPDATE accounts 
SET balance = balance - 1000
WHERE account_id = 1001;

-- Add to destination account
UPDATE accounts 
SET balance = balance + 1000
WHERE account_id = 1002;

-- Record the transaction
INSERT INTO transactions (transaction_id, from_account, to_account, amount)
VALUES (1, 1001, 1002, 1000);

-- Only commit if all operations succeed
COMMIT;

-- If any error occurs:
-- ROLLBACK;
        

Savepoints

Create markers within a transaction to roll back to:

START TRANSACTION;

-- Initial operations
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1001;
SAVEPOINT after_withdrawal;

-- More operations
UPDATE accounts SET balance = balance + 500 WHERE account_id = 1003;
SAVEPOINT after_deposit;

-- Conditionally rollback to savepoint
-- ROLLBACK TO after_withdrawal;

COMMIT;
        

Transaction Isolation Levels

Control how transactions interact with each other:

Setting Isolation Level

-- MySQL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- SQL Server
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- PostgreSQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        

Common Transaction Patterns

Retry Logic

-- Pseudocode for transaction with retry
DECLARE retry_count INT DEFAULT 0;
DECLARE success BOOLEAN DEFAULT FALSE;

WHILE retry_count < 3 AND NOT success DO
  BEGIN
    START TRANSACTION;
      -- Transaction logic
    COMMIT;
    SET success = TRUE;
  EXCEPTION
    WHEN deadlock_detected THEN
      ROLLBACK;
      SET retry_count = retry_count + 1;
      -- Optional: add delay
  END;
END WHILE;
        

Nested Transactions

-- Using savepoints to simulate nested transactions
START TRANSACTION;

  -- Outer transaction
  UPDATE accounts SET balance = balance - 200 WHERE account_id = 1001;
  
  SAVEPOINT nested_transaction;
    -- Inner transaction
    UPDATE accounts SET balance = balance + 200 WHERE account_id = 1002;
    
    -- Conditionally commit/rollback inner
    -- RELEASE SAVEPOINT nested_transaction; -- "commit"
    -- or ROLLBACK TO nested_transaction;   -- "rollback"

COMMIT; -- Commits the outer transaction
        

Distributed Transactions

Transactions spanning multiple databases:

-- MySQL (XA Transactions)
XA START 'transfer_transaction';
  UPDATE db1.accounts SET balance = balance - 500 WHERE account_id = 1001;
  UPDATE db2.accounts SET balance = balance + 500 WHERE account_id = 2001;
XA END 'transfer_transaction';
XA PREPARE 'transfer_transaction';
XA COMMIT 'transfer_transaction';

-- On error:
-- XA ROLLBACK 'transfer_transaction';
        

Transaction Best Practices

• Keep transactions as short as possible
• Handle exceptions properly with rollback
• Choose the right isolation level for your needs
• Avoid user interaction within transactions
• Consider using optimistic/pessimistic locking as needed
• Monitor for long-running transactions

Common Transaction Issues