INSERT adds rows, UPDATE modifies existing rows, and DELETE removes rows. A transaction groups several such statements so they either all succeed (COMMIT) or all undo (ROLLBACK) together — the mechanism that keeps multi-step changes consistent. Testers use these to create and adjust state and to reason about atomicity.
Write operations are powerful and dangerous: the single most important habit is that UPDATE and DELETE without a WHERE clause affect EVERY row in the table. In a shared or production-like database this is catastrophic, so testers always scope writes with a precise WHERE and often preview with a SELECT first. Transactions matter for testers in two ways. First, they enable clean test isolation (wrap setup + test in a transaction, roll back to leave no trace). Second, understanding ACID properties (Atomicity, Consistency, Isolation, Durability) lets a tester design tests for partial-failure behavior — e.g. if step 3 of a 4-step order fails, does the whole transaction roll back, or are you left with half-written data. Isolation levels (read committed, repeatable read, serializable) govern what concurrent transactions can see and are the source of subtle bugs like lost updates and phantom reads that testers may need to reproduce. Knowing SAVEPOINT, COMMIT and ROLLBACK gives fine control during test setup.
A tester needs to verify that a failed payment rolls back an entire order. They exercise the flow so the transaction inserts the order and items but the payment step fails, then query the database to confirm NO order, item, or inventory change persisted — proving the transaction rolled back atomically.
-- ALWAYS scope writes; preview with SELECT before UPDATE/DELETE
SELECT id FROM orders WHERE id = 95001; -- confirm the target first
UPDATE orders SET status = 'cancelled' WHERE id = 95001;
-- DANGER: no WHERE = every row changes
-- UPDATE orders SET status = 'cancelled'; -- never do this
-- Transaction: all-or-nothing multi-step change
BEGIN;
INSERT INTO orders (id, user_id, status, total) VALUES (95002, 90001, 'pending', 20.00);
UPDATE products SET stock = stock - 1 WHERE id = 42;
-- if anything is wrong:
ROLLBACK; -- undoes BOTH statements; COMMIT would persist both
-- Test isolation pattern: seed, test, then discard
-- BEGIN; <seed + run assertions> ROLLBACK;Scenario: A tester is asked to run a quick data fix on a shared test database: 'set all pending orders older than 30 days to cancelled'. They run UPDATE orders SET status = 'cancelled' and every order in the system becomes cancelled, breaking dozens of other tests. What went wrong, and what safe practice prevents this?