Practice Questions — Transactions and ACID Properties

START TRANSACTION;
    UPDATE accounts SET balance = balance - 1500 WHERE id = 101;
    UPDATE accounts SET balance = balance + 1500 WHERE id = 102;
COMMIT;

START TRANSACTION;
    SELECT balance INTO @bal FROM accounts WHERE id = 101 FOR UPDATE;
    IF @bal < 700 THEN
        ROLLBACK;
    ELSE
        UPDATE accounts SET balance = balance - 700 WHERE id = 101;
        UPDATE accounts SET balance = balance + 700 WHERE id = 102;
        COMMIT;
    END IF;

START TRANSACTION;
    INSERT INTO orders(customer_id, total) VALUES (7, 1500);
    SET @oid = LAST_INSERT_ID();
    SAVEPOINT after_order;
    -- pretend the next insert fails; handle in app, then:
    ROLLBACK TO SAVEPOINT after_order;
    INSERT INTO order_items(order_id, product_id, qty) VALUES (@oid, 99, 1);
COMMIT;

START TRANSACTION;
    SELECT booked_by INTO @who FROM seats WHERE id = 1 FOR UPDATE;
    IF @who IS NULL THEN
        UPDATE seats SET booked_by = 1001 WHERE id = 1;
    END IF;
COMMIT;

-- Session 1
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 101;  -- 5000
-- <wait>
SELECT balance FROM accounts WHERE id = 101;  -- 9999 (dirty)
COMMIT;

-- Session 2
START TRANSACTION;
UPDATE accounts SET balance = 9999 WHERE id = 101;
-- do NOT commit
ROLLBACK;
-- Session 1's second SELECT had seen 9999, a value that never officially existed.

-- Session 1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 101;  -- 5000 (snapshot taken)
-- <wait for Session 2 to commit>
SELECT balance FROM accounts WHERE id = 101;  -- still 5000
COMMIT;

-- Session 2
START TRANSACTION;
UPDATE accounts SET balance = 6000 WHERE id = 101;
COMMIT;
-- Session 2's new value is invisible to Session 1 until Session 1 commits and starts a fresh transaction.

import time
from pymysql.err import OperationalError  # or the equivalent in your driver

def run_with_retry(conn, body_fn, max_attempts=3):
    for attempt in range(max_attempts):
        try:
            conn.begin()
            body_fn(conn)
            conn.commit()
            return
        except OperationalError as e:
            if e.args and e.args[0] == 1213:
                conn.rollback()
                time.sleep(0.01 * (2 ** attempt))
                continue
            raise
    raise RuntimeError('Gave up after %d deadlock retries' % max_attempts)

-- Always SELECT ... FOR UPDATE both rows in (smaller id, larger id) order,
-- then apply the debit/credit based on which id is the source.
START TRANSACTION;
    SELECT id INTO @lo FROM accounts WHERE id = LEAST(:from_id, :to_id) FOR UPDATE;
    SELECT id INTO @hi FROM accounts WHERE id = GREATEST(:from_id, :to_id) FOR UPDATE;

    UPDATE accounts SET balance = balance - :amt WHERE id = :from_id;
    UPDATE accounts SET balance = balance + :amt WHERE id = :to_id;
COMMIT;