Transactions and Isolation

Some changes only make sense together. Reducing stock and recording the order line are two statements describing one event. If the first succeeds and the second fails, your stock figures are wrong and nothing says so.

A transaction makes several statements one unit. Either all of them take effect, or none do.

BEGIN TRANSACTION;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1043, 318, 5, 11.49);

UPDATE products
SET units_in_stock = units_in_stock - 5
WHERE product_id = 318;

COMMIT;

• Between BEGIN and COMMIT the changes are visible only to this session. Other sessions see the previous state.
• COMMIT makes them permanent and visible to everyone, together.
• ROLLBACK instead of COMMIT discards both. A failure part-way cannot leave stock reduced for a line that was never inserted.

Two transactions running at once can interfere in three specific ways. Each has a name, and each isolation level is defined by which of them it prevents. Understanding the anomalies first makes the levels obvious.

The pattern is a single trade: stricter isolation means more locking, which means less work happening at the same time. There is no level that is correct for everything, which is why the default is a middle position rather than the strictest one.

Start with the default, READ COMMITTED. Raise the level for a specific transaction where you can state which anomaly you are preventing and why it matters. Raising it globally because it sounds safer buys blocking you did not need.

-- Stock check then reserve: the count must not change underneath us
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

DECLARE @available INT;

SELECT @available = units_in_stock
FROM products
WHERE product_id = 318;

IF @available >= 5
BEGIN
    UPDATE products
    SET units_in_stock = units_in_stock - 5
    WHERE product_id = 318;

    INSERT INTO order_items (order_id, product_id, quantity, unit_price)
    VALUES (1043, 318, 5, 11.49);

    COMMIT;
END
ELSE
BEGIN
    ROLLBACK;
END;

• Reading the stock and then acting on it is a read-then-write sequence. Under READ COMMITTED, another transaction can reduce the stock between those two statements, so both sessions pass the check and stock goes negative.
• REPEATABLE READ holds a lock on the row from the moment it is read, so the second session waits. The cost is exactly that waiting.
• There is a simpler alternative worth preferring here: skip the read, and write UPDATE products SET units_in_stock = units_in_stock - 5 WHERE product_id = 318 AND units_in_stock >= 5, then check @@ROWCOUNT. One atomic statement, no elevated isolation, no lock held across two statements.
• That is the general lesson. Before raising an isolation level, check whether the sequence can be expressed as one statement.

A deadlock is two transactions each waiting for a lock the other holds. A updates products then orders; B updates orders then products. Neither can proceed and neither will give up.

The database detects this and kills one of them, which receives an error. Nothing is corrupted — the victim is rolled back completely.

Two things reduce deadlocks. Touch tables in the same order everywhere, so two transactions cannot hold each other's next lock. And keep transactions short, because a lock held for 50 milliseconds collides with far less than one held for 5 seconds.

Application code that writes to the database should be prepared to retry a deadlock victim. It is not a bug to eliminate entirely; it is a condition to handle.