Transactions — COMMIT and ROLLBACK

A single INSERT or UPDATE is a tiny, self-contained change. But many real-world operations involve several statements that have to land together — placing an order inserts into orders and order_items, transferring funds debits one account and credits another, signing up a user creates a users row and a user_profiles row. If half of those statements succeed and half fail, the database is left in a broken intermediate state. Transactions are the safety net: a way to group several operations into one atomic unit that either all commits or all gets undone.

What a transaction is, in one sentence

A transaction is a block of SQL statements that succeed or fail together — never partway. You start it with BEGIN (or BEGIN TRANSACTION), execute the statements, and end with either COMMIT (save everything permanently) or ROLLBACK (undo everything as if it never happened).

BEGIN TRANSACTION;
 
INSERT INTO orders (user_id, total, status)
VALUES (1, 150.00, 'pending');
 
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (LAST_INSERT_ID(), 5, 2, 75.00);
 
COMMIT;

Either both INSERTs land or neither does. There's no scenario where the order is created but the line items are missing, or vice versa.

COMMIT vs ROLLBACK in a picture

The transaction is invisible to other database users until you COMMIT. A user querying orders halfway through your transaction won't see the new row — they see the database as it was when you started.

ROLLBACK in action

ROLLBACK is the undo button. The classic use is "I just realised my UPDATE was wrong":

BEGIN TRANSACTION;
 
DELETE FROM order_items WHERE order_id = 1;
DELETE FROM orders WHERE id = 1;
 
-- Wait, that was the wrong order!
ROLLBACK;

The two DELETEs are reverted in one command. As long as you haven't yet COMMITTED, you have an escape hatch.

A "preview-then-decide" workflow

A pattern testers love: wrap a risky change in a transaction, run a verifying SELECT inside it, decide based on what you see, and either COMMIT or ROLLBACK.

BEGIN TRANSACTION;
 
UPDATE products SET price = price * 0.50 WHERE category_id = 1;
 
-- Inside the transaction, see what the world would look like
SELECT name, price FROM products WHERE category_id = 1;

If the prices look right:

COMMIT;   -- prices stay halved

If not:

ROLLBACK; -- prices revert to whatever they were

That two-stage pattern — change, inspect, decide — turns risky UPDATEs and DELETEs into much safer operations.

ACID, in 30 seconds

Transactions exist because of four properties databases give you, abbreviated as ACID:

• Atomicity — all the operations in a transaction succeed together, or none do.
• Consistency — the database moves from one valid state to another. Constraints are enforced.
• Isolation — concurrent transactions don't see each other's uncommitted changes.
• Durability — once COMMITTED, the data survives a database crash.

You'll meet these terms throughout your career. For day-to-day test work, atomicity is the property that matters most — all-or-nothing is what makes transactions useful for cleanup and complex setup.

SAVEPOINT — checkpoints inside a transaction

If you want partial rollback, SAVEPOINT creates a checkpoint you can return to without scrapping the whole transaction:

BEGIN TRANSACTION;
 
INSERT INTO users (name, email, role)
VALUES ('Test User', 'tu@test.com', 'tester');
 
SAVEPOINT after_user;
 
INSERT INTO orders (user_id, total, status)
VALUES (LAST_INSERT_ID(), 100.00, 'pending');
 
-- Hmm, the order was wrong. Roll back just the order, keep the user.
ROLLBACK TO SAVEPOINT after_user;
 
COMMIT;  -- user is saved, order is not

You won't reach for SAVEPOINT often — most teams structure their transactions so that whole-transaction COMMIT/ROLLBACK is enough. But it's a good tool to know exists.

Autocommit — why your single statements work without BEGIN

Most databases have autocommit turned on by default. That means every statement is implicitly its own one-statement transaction — it's started, executed, and committed as one unit. You don't see BEGIN/COMMIT because they're happening invisibly around each statement.

When you write BEGIN TRANSACTION explicitly, you're saying "stop autocommit until I say COMMIT." Now every subsequent statement is part of the same transaction, and ROLLBACK can undo all of them.

A small dialect note for SQLite: the autocommit/transaction terminology varies slightly. BEGIN, BEGIN TRANSACTION, and BEGIN DEFERRED TRANSACTION are all accepted; the simplest form is just BEGIN.

QA use cases for transactions

Three patterns testers use constantly:

-- 1. Atomic test-data setup — if any INSERT fails, none of them land
BEGIN TRANSACTION;
 
INSERT INTO users (name, email, role) VALUES ('Test', 'test@x.local', 'tester');
INSERT INTO products (name, price, category_id, in_stock) VALUES ('Test Product', 9.99, 1, TRUE);
INSERT INTO orders (user_id, total, status) VALUES (LAST_INSERT_ID(), 9.99, 'pending');
 
COMMIT;
 
-- 2. Sandboxed verification — change, inspect, undo
BEGIN TRANSACTION;
 
UPDATE orders SET status = 'shipped' WHERE id = 4;
-- Run any SELECT to check the change behaves as expected
SELECT id, status FROM orders WHERE id = 4;
 
ROLLBACK;  -- database returns to original state
 
-- 3. Test database integrity by killing the process mid-flow
-- (Run the BEGIN + INSERTs in a script that crashes before COMMIT;
--  re-open the database and confirm none of the inserts persisted.)
-- This is how you prove the application's transaction handling is correct.

The second pattern is a tester's superpower: you can try out a risky UPDATE or DELETE, look at the result, and decide whether to keep it — all without putting the database into a state you have to manually clean up.

Testing the application's own transactions

A good integration test in this space: kill the application process between two related INSERTs and confirm the database has neither row, not just the first one. If the application isn't using a transaction internally, you'll find half a row sitting in the database — a real correctness bug. "What happens halfway through?" is one of the most underrated questions a tester can ask.

⚠️ Common Mistakes

• Forgetting to COMMIT or ROLLBACK. An open transaction holds locks. Other sessions waiting on those rows pile up. Eventually you (or the DBA) get a panicked Slack message. Always close every BEGIN with a COMMIT or ROLLBACK.
• Trying to ROLLBACK after COMMIT. Once COMMITTED, the data is durable. ROLLBACK doesn't help — the only recovery is point-in-time restore from a backup. The lesson: COMMIT only when you're sure.
• Assuming every statement is in a transaction by default. It is — autocommit makes each statement its own one-line transaction. But that means an UPDATE with a missing WHERE clause is immediately permanent. The "I'll roll it back if anything goes wrong" plan only works if you started a multi-statement transaction first with BEGIN.

🎯 Practice Task

25 minutes. Use the e-commerce database.

1. Run BEGIN TRANSACTION; INSERT INTO users (name, email, role) VALUES ('Tx Test', 'tx@local', 'tester'); SELECT * FROM users WHERE email = 'tx@local'; ROLLBACK;. Then run the SELECT again — outside the transaction. Confirm the row isn't there.
2. Repeat the same flow but COMMIT instead. Confirm the row is now persistently in the database. Then DELETE it for cleanup.
3. Wrap a "set up cart, verify, cancel" flow in a transaction: INSERT an order, INSERT three order_items, run a SELECT to see them, then ROLLBACK. Confirm your seed-data row counts are unchanged.
4. Try the dangerous version: outside any explicit transaction, run UPDATE products SET price = 0.01 WHERE in_stock = TRUE. Notice the change is immediately permanent. Restore by running the seed-data UPDATEs again, or re-seed the table.
5. Now try the safe version: BEGIN; UPDATE products SET price = 0.01 WHERE in_stock = TRUE; ROLLBACK;. Confirm the prices are unchanged.
6. Stretch: investigate your team's application code. Find a flow that does multiple writes (a sign-up, a checkout, a cascade-delete). Is the code wrapping those writes in a transaction? If you can't tell from a quick read, ask the developer — and add a test that crashes the process mid-flow to confirm.

That wraps up Chapter 4. You can now safely change data, with INSERT, UPDATE, DELETE, and the transaction safety net behind them. Chapter 5 turns the lens around — instead of changing the database, you write the queries a real QA workflow needs: verifying API writes, finding duplicates and orphans, and producing the test reports a PM will actually read.