Transactions

10 min read•Learning SQL

Transactions

A transaction groups multiple SQL statements into a single unit of work. Either all statements succeed, or none of them do. This guarantees data consistency even when operations fail partway through.

Why Transactions Matter

Consider transferring money between bank accounts:

SQL

-- Without transactions: dangerous!
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- What if the server crashes here?
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

If the system fails between these statements, $100 disappears. Transactions prevent this:

SQL

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

Now both updates succeed together, or both are rolled back if anything fails.

ACID Properties

Transactions guarantee four properties:

Atomicity

All operations complete, or none do. Partial results are never visible.

SQL

START TRANSACTION;
INSERT INTO orders (customer_id, total_amount) VALUES (1, 50.00);
-- Even if this insert was successful, it's not "real" yet
INSERT INTO order_items (order_id, book_id, quantity) VALUES (LAST_INSERT_ID(), 1, 2);
-- If this fails, the order insert is also undone
COMMIT;

Consistency

The database moves from one valid state to another. Constraints, triggers, and rules are enforced.

SQL

START TRANSACTION;
UPDATE books SET stock_quantity = stock_quantity - 10 WHERE book_id = 1;
-- If this violates a CHECK constraint (e.g., stock_quantity >= 0),
-- the entire transaction fails
COMMIT;

Isolation

Concurrent transactions don't see each other's uncommitted changes (by default).

SQL

-- Transaction A              -- Transaction B
START TRANSACTION;            START TRANSACTION;
UPDATE books SET price = 20   SELECT price FROM books
WHERE book_id = 1;            WHERE book_id = 1;
                              -- Still sees old price!
COMMIT;
                              SELECT price FROM books
                              WHERE book_id = 1;
                              -- Now sees 20

Durability

Once committed, changes survive system failures. The database writes to permanent storage.

Transaction Control Statements

START TRANSACTION / BEGIN

Explicitly starts a transaction:

SQL

START TRANSACTION;  -- Standard SQL
-- or
BEGIN;              -- Also widely supported
-- or
BEGIN TRANSACTION;  -- SQL Server

COMMIT

Makes all changes permanent:

SQL

START TRANSACTION;
INSERT INTO customers (first_name, last_name, email)
VALUES ('Jane', 'Doe', 'jane@example.com');
UPDATE customer_stats SET total_customers = total_customers + 1;
COMMIT;  -- Both changes are now permanent

ROLLBACK

Undoes all changes since the transaction started:

SQL

START TRANSACTION;
DELETE FROM orders WHERE customer_id = 5;
-- Oops, wrong customer!
ROLLBACK;  -- Phew, nothing was deleted

Note

After COMMIT, you cannot ROLLBACK. After ROLLBACK, the transaction is ended and changes are gone.

Savepoints

Savepoints let you roll back to a specific point without abandoning the entire transaction:

SQL

START TRANSACTION;
 
INSERT INTO orders (customer_id, total_amount) VALUES (1, 100);
SAVEPOINT order_created;
 
INSERT INTO order_items (order_id, book_id, quantity) VALUES (LAST_INSERT_ID(), 999, 1);
-- Error: book_id 999 doesn't exist!
 
ROLLBACK TO SAVEPOINT order_created;
-- Order insert is still there, only order_items rolled back
 
INSERT INTO order_items (order_id, book_id, quantity) VALUES (LAST_INSERT_ID(), 1, 1);
-- Correct book_id this time
 
COMMIT;

Savepoint Syntax

SQL

SAVEPOINT savepoint_name;           -- Create savepoint
ROLLBACK TO SAVEPOINT savepoint_name;  -- Rollback to savepoint
RELEASE SAVEPOINT savepoint_name;   -- Remove savepoint (optional)

Multi-step order processing with savepoints:

SQL

START TRANSACTION;
 
-- Step 1: Create order
INSERT INTO orders (customer_id, order_date, status)
VALUES (1, CURRENT_DATE, 'pending');
SET @order_id = LAST_INSERT_ID();
SAVEPOINT after_order;
 
-- Step 2: Add items
INSERT INTO order_items (order_id, book_id, quantity, unit_price)
SELECT @order_id, book_id, 1, price FROM books WHERE book_id IN (1, 2, 3);
SAVEPOINT after_items;
 
-- Step 3: Update inventory
UPDATE books SET stock_quantity = stock_quantity - 1 WHERE book_id IN (1, 2, 3);
-- If inventory goes negative, we can rollback just this step
IF (SELECT MIN(stock_quantity) FROM books WHERE book_id IN (1, 2, 3)) < 0 THEN
    ROLLBACK TO SAVEPOINT after_items;
    UPDATE orders SET status = 'backordered' WHERE order_id = @order_id;
END IF;
 
COMMIT;

Autocommit Mode

By default, most databases run in autocommit mode: each statement is its own transaction.

SQL

-- In autocommit mode, this is immediately permanent:
INSERT INTO customers (first_name, last_name, email)
VALUES ('John', 'Smith', 'john@example.com');
-- No COMMIT needed, already committed

To execute multiple statements as one transaction, you must explicitly start one:

SQL

-- Check autocommit status
SELECT @@autocommit;  -- MySQL: 1 = on, 0 = off
 
-- Disable autocommit for session
SET autocommit = 0;
-- Now all statements require explicit COMMIT

Warning

Leaving autocommit off means uncommitted changes can block other users. Always COMMIT or ROLLBACK before leaving.

Isolation Levels

Isolation levels control how transactions interact with each other. Higher isolation = more consistency but less concurrency.

READ UNCOMMITTED

Can see other transactions' uncommitted changes (dirty reads).

SQL

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Problems: Dirty reads—you might read data that gets rolled back.

READ COMMITTED

Only sees committed changes. Default in PostgreSQL, Oracle, SQL Server.

SQL

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Problems: Non-repeatable reads—the same query might return different results within one transaction.

REPEATABLE READ

Same query returns same results within a transaction. Default in MySQL.

SQL

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Problems: Phantom reads—new rows might appear that match your criteria.

SERIALIZABLE

Complete isolation. Transactions execute as if they were serial.

SQL

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Problems: Lowest concurrency, potential for deadlocks.

Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	No	Possible	Possible
REPEATABLE READ	No	No	Possible
SERIALIZABLE	No	No	No

Deadlocks

A deadlock occurs when two transactions wait for each other's locks:

SQL

-- Transaction A                    -- Transaction B
START TRANSACTION;                  START TRANSACTION;
UPDATE books SET price = 15         UPDATE authors SET name = 'Bob'
WHERE book_id = 1;                  WHERE author_id = 1;
-- Holds lock on book 1             -- Holds lock on author 1
 
UPDATE authors SET name = 'Alice'   UPDATE books SET price = 20
WHERE author_id = 1;                WHERE book_id = 1;
-- Waits for author 1               -- Waits for book 1
-- DEADLOCK!                        -- DEADLOCK!

Handling Deadlocks

Databases detect deadlocks and kill one transaction:

SQL

-- One transaction gets this error:
-- ERROR 1213 (40001): Deadlock found when trying to get lock
 
-- Your application should retry:
-- 1. Catch the deadlock error
-- 2. Wait briefly (random backoff)
-- 3. Retry the transaction

Preventing Deadlocks

Access tables in consistent order across all transactions
Keep transactions short to minimize lock time
Use appropriate isolation levels (lower when possible)
Index columns used in WHERE clauses for faster locking

Locking Strategies

Optimistic Locking

Assume conflicts are rare. Check at commit time.

SQL

-- Read with version
SELECT book_id, title, price, version FROM books WHERE book_id = 1;
-- version = 5
 
-- Update only if version unchanged
UPDATE books
SET price = 20, version = version + 1
WHERE book_id = 1 AND version = 5;
 
-- If 0 rows affected, someone else changed it - retry!

Pessimistic Locking

Lock rows when reading to prevent changes.

SQL

START TRANSACTION;
 
-- Lock the row for update
SELECT * FROM books WHERE book_id = 1 FOR UPDATE;
 
-- Safe to modify - no one else can change it
UPDATE books SET price = 20 WHERE book_id = 1;
 
COMMIT;

FOR UPDATE variants:

SQL

SELECT * FROM books WHERE book_id = 1 FOR UPDATE;           -- Exclusive lock
SELECT * FROM books WHERE book_id = 1 FOR SHARE;            -- Shared lock
SELECT * FROM books WHERE book_id = 1 FOR UPDATE NOWAIT;    -- Error if locked
SELECT * FROM books WHERE book_id = 1 FOR UPDATE SKIP LOCKED; -- Skip locked rows

Practical Examples

Example 1: Order Placement

SQL

DELIMITER //
CREATE PROCEDURE place_order(
    IN p_customer_id INT,
    IN p_book_ids JSON,
    OUT p_order_id INT
)
BEGIN
    DECLARE v_book_id INT;
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_total DECIMAL(10,2) DEFAULT 0;
    DECLARE v_idx INT DEFAULT 0;
 
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_order_id = NULL;
    END;
 
    START TRANSACTION;
 
    -- Create order
    INSERT INTO orders (customer_id, order_date, status, total_amount)
    VALUES (p_customer_id, CURRENT_DATE, 'pending', 0);
    SET p_order_id = LAST_INSERT_ID();
 
    -- Add items and calculate total
    WHILE v_idx < JSON_LENGTH(p_book_ids) DO
        SET v_book_id = JSON_EXTRACT(p_book_ids, CONCAT('$[', v_idx, ']'));
 
        SELECT price INTO v_price FROM books WHERE book_id = v_book_id FOR UPDATE;
 
        INSERT INTO order_items (order_id, book_id, quantity, unit_price)
        VALUES (p_order_id, v_book_id, 1, v_price);
 
        UPDATE books SET stock_quantity = stock_quantity - 1
        WHERE book_id = v_book_id AND stock_quantity > 0;
 
        IF ROW_COUNT() = 0 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock';
        END IF;
 
        SET v_total = v_total + v_price;
        SET v_idx = v_idx + 1;
    END WHILE;
 
    -- Update order total
    UPDATE orders SET total_amount = v_total WHERE order_id = p_order_id;
 
    COMMIT;
END //
DELIMITER ;

Example 2: Balance Transfer

SQL

DELIMITER //
CREATE PROCEDURE transfer_funds(
    IN p_from_account INT,
    IN p_to_account INT,
    IN p_amount DECIMAL(10,2)
)
BEGIN
    DECLARE v_from_balance DECIMAL(10,2);
 
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Transfer failed';
    END;
 
    START TRANSACTION;
 
    -- Lock accounts in consistent order (lower ID first to prevent deadlock)
    IF p_from_account < p_to_account THEN
        SELECT balance INTO v_from_balance FROM accounts
        WHERE account_id = p_from_account FOR UPDATE;
        SELECT balance FROM accounts WHERE account_id = p_to_account FOR UPDATE;
    ELSE
        SELECT balance FROM accounts WHERE account_id = p_to_account FOR UPDATE;
        SELECT balance INTO v_from_balance FROM accounts
        WHERE account_id = p_from_account FOR UPDATE;
    END IF;
 
    -- Verify sufficient funds
    IF v_from_balance < p_amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;
 
    -- Perform transfer
    UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from_account;
    UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to_account;
 
    -- Log the transfer
    INSERT INTO transfer_log (from_account, to_account, amount, transfer_date)
    VALUES (p_from_account, p_to_account, p_amount, NOW());
 
    COMMIT;
END //
DELIMITER ;

Example 3: Inventory Batch Update

SQL

START TRANSACTION;
 
-- Create temporary tracking table
CREATE TEMPORARY TABLE inventory_changes (
    book_id INT,
    old_quantity INT,
    new_quantity INT
);
 
-- Save current state
INSERT INTO inventory_changes (book_id, old_quantity, new_quantity)
SELECT book_id, stock_quantity, stock_quantity FROM books;
 
SAVEPOINT before_update;
 
-- Apply updates
UPDATE books b
JOIN inventory_import i ON b.book_id = i.book_id
SET b.stock_quantity = i.new_quantity;
 
-- Verify no negative quantities
IF EXISTS (SELECT 1 FROM books WHERE stock_quantity < 0) THEN
    ROLLBACK TO SAVEPOINT before_update;
    SELECT 'Error: Negative inventory detected' AS result;
ELSE
    -- Update our tracking table
    UPDATE inventory_changes ic
    JOIN books b ON ic.book_id = b.book_id
    SET ic.new_quantity = b.stock_quantity;
 
    -- Log changes
    INSERT INTO inventory_audit (book_id, old_qty, new_qty, change_date)
    SELECT book_id, old_quantity, new_quantity, NOW()
    FROM inventory_changes
    WHERE old_quantity != new_quantity;
 
    COMMIT;
    SELECT 'Inventory updated successfully' AS result;
END IF;
 
DROP TEMPORARY TABLE inventory_changes;

What's Next

The final chapter covers indexes and constraints—optimizing query performance and enforcing data integrity at the database level.