Transaction Control (BEGIN, COMMIT, ROLLBACK)

-- Basic transaction
BEGIN;

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

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

COMMIT;

-- Transaction with rollback on error
BEGIN;

UPDATE inventory 
SET quantity = quantity - 5 
WHERE product_id = 123;

INSERT INTO orders (user_id, product_id, quantity)
VALUES (1, 123, 5);

-- If any error occurs, rollback
ROLLBACK;

-- Transaction with conditional logic
BEGIN;

-- Check if sufficient balance
SELECT balance INTO @current_balance
FROM accounts 
WHERE id = 1;

IF @current_balance >= 100 THEN
  UPDATE accounts 
  SET balance = balance - 100 
  WHERE id = 1;
  
  INSERT INTO transactions (account_id, amount, type)
  VALUES (1, -100, 'withdrawal');
  
  COMMIT;
ELSE
  ROLLBACK;
END IF;

-- Savepoint (partial rollback)
BEGIN;

INSERT INTO orders (user_id, total) 
VALUES (1, 100);

SAVEPOINT order_created;

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (LAST_INSERT_ID(), 123, 2);

-- If this fails, can rollback to savepoint
ROLLBACK TO SAVEPOINT order_created;

-- Or continue and commit everything
COMMIT;

-- Transaction isolation levels
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- queries...
COMMIT;

-- Isolation levels:
-- READ UNCOMMITTED - can see uncommitted changes (dirty reads)
-- READ COMMITTED - only see committed changes (default)
-- REPEATABLE READ - consistent snapshot during transaction
-- SERIALIZABLE - full isolation, as if transactions run serially

// Node.js with pg (PostgreSQL)
const { Client } = require('pg');
const client = new Client();

async function transferMoney(fromId, toId, amount) {
  try {
    await client.query('BEGIN');
    
    // Deduct from sender
    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
      [amount, fromId]
    );
    
    // Add to receiver
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toId]
    );
    
    await client.query('COMMIT');
    console.log('Transfer successful');
  } catch (error) {
    await client.query('ROLLBACK');
    console.error('Transfer failed, rolled back:', error);
    throw error;
  }
}

// With connection pooling
async function safeUpdate(pool) {
  const client = await pool.connect();
  
  try {
    await client.query('BEGIN');
    
    // Multiple queries...
    await client.query('UPDATE ...');
    await client.query('INSERT ...');
    
    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}