-- The problem: what if we crash between these two statements?
-- Solution: wrap them in a transaction
 
START TRANSACTION;  -- or BEGIN;
 
-- Step 1: deduct from sender
UPDATE accounts
SET balance = balance - 100
WHERE id = 1 AND balance >= 100;  -- check sufficient funds
 
-- Verify the update succeeded (check rows affected)
-- In application code, check affected_rows = 1
 
-- Step 2: add to recipient
UPDATE accounts
SET balance = balance + 100
WHERE id = 2;
 
-- If both succeeded: permanently save
COMMIT;
 
-- If anything failed: undo everything
-- ROLLBACK;
 
-- Verify
SELECT id, balance FROM accounts WHERE id IN (1, 2);

const mysql = require('mysql2/promise');
 
async function transferMoney(senderId, recipientId, amount) {
  const connection = await pool.getConnection();
  
  try {
    await connection.beginTransaction();
    
    // Check and deduct from sender
    const [sender] = await connection.execute(
      'SELECT balance FROM accounts WHERE id = ? FOR UPDATE'FROM accounts WHERE id = ? FOR UPDATE',  // row-level lock
      [senderId]
    );
    
    if (sender[0].balance < amount) {
      throw new Error('Insufficient funds');
    }
    
    await connection.execute(
      'UPDATE accounts SET balance = balance - ? WHERE id = ?'SET balance = balance - ? WHERE id = ?',
      [amount, senderId]
    );
    
    // Add to recipient
    await connection.execute(
      'UPDATE accounts SET balance = balance + ? WHERE id = ?'SET balance = balance + ? WHERE id = ?',
      [amount, recipientId]
    );
    
    // Log the transaction
    await connection.execute(
      'INSERT INTO transfer_log (from_id, to_id, amount, created_at) VALUES (?, ?, ?, NOW())'INTO transfer_log (from_id, to_id, amount, created_at) VALUES (?, ?, ?, NOW())',
      [senderId, recipientId, amount]
    );
    
    await connection.commit();  // ALL or NOTHINGNOTHING
    return { success: true };
    
  } catch (error) {
    await connection.rollback();  // undo everything
    throw error;
  } finally {
    connection.release();
  }
}

-- Savepoints let you rollback to a specific point within a transaction
-- Without rolling back the entire thing
 
START TRANSACTION;
 
INSERT INTO orders (user_id, total) VALUES (1, 150.00);
SAVEPOINT after_order;  -- mark this point
 
INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 5, 2);
SAVEPOINT after_items;
 
-- Try to apply a discount coupon
UPDATE coupons SET used = TRUE WHERE code = 'SAVE20';
 
-- If coupon is invalid, rollback just the coupon step
ROLLBACK TO SAVEPOINT after_items;
-- The order and items are still in the transaction, coupon update is undone
 
-- Continue and commit what's valid
COMMIT;