Transaction Statements

START TRANSACTION, COMMIT, ROLLBACK, and SAVEPOINT

START TRANSACTION (or BEGIN) opens a transaction, COMMIT makes its changes permanent and visible, and ROLLBACK discards them. Modifiers include READ ONLY and WITH CONSISTENT SNAPSHOT (MVCC). Transactions do not nest: a second START TRANSACTION commits the first. A savepoint marks a point you can return to with ROLLBACK TO SAVEPOINT without abandoning the transaction; RELEASE SAVEPOINT forgets one, and COMMIT or ROLLBACK clears them all.

A safe checkout decrements stock first, with the availability test inside the UPDATE. Its row lock makes a second buyer wait. Below, Session A also adds a free mug and then withdraws it with a savepoint; once A commits, B's UPDATE re-reads the committed row, finds 2 copies, and matches nothing:

Two checkouts race for the last five copiesSQL

Session A> START TRANSACTION;
Session A> UPDATE products SET stock = stock - 5 WHERE id = 5 AND stock >= 5;
Session A: Query OK, 1 row affected
Session B> START TRANSACTION;
Session B> UPDATE products SET stock = stock - 5 WHERE id = 5 AND stock >= 5;
Session B: (waiting)
Session A> INSERT INTO orders (customer_id, ordered_at) VALUES (7, NOW());
Session A: Query OK, 1 row affected
Session A> INSERT INTO order_items VALUES (LAST_INSERT_ID(), 5, 5, 34.00);
Session A: Query OK, 1 row affected
Session A> SAVEPOINT gift;
Session A> UPDATE products SET stock = stock - 1 WHERE id = 7;
Session A: Query OK, 1 row affected
Session A> ROLLBACK TO SAVEPOINT gift;
Session A> COMMIT;
Session B: Rows matched: 0  Changed: 0  Warnings: 0
Session B> ROLLBACK;
Session A> SELECT id, stock FROM products WHERE id IN (5, 7);
Session A: +----+-------+
Session A: | id | stock |
Session A: +----+-------+
Session A: |  5 |     2 |
Session A: |  7 |    60 |
Session A: +----+-------+

The mug is back at 60 while the order's decrement stands. An affected-row count of 0 (Transactions) means sold out: roll back and tell the customer. Reading stock with a plain SELECT and writing back a value computed in PHP instead lets two buyers who both read 7 sell ten books from seven. Laravel 2,157 maps nested DB::transaction() calls onto savepoints (Transactions and Locks).

MENU