How are SQL transactions used in automated testing, and what is the benefit of rolling back after each test?

The pattern:

1. BEGIN TRANSACTION before the test's setup
2. Run setup SQL (INSERTs, UPDATEs)
3. Execute the test action
4. Run assertions (SELECTs)
5. ROLLBACK — all changes vanish, DB returns to pre-test state

BEGIN;

INSERT INTO users (id, email) VALUES (9999, 'test@example.com');

-- ...test action happens here...

-- Assertions
SELECT COUNT(*) FROM orders WHERE customer_id = 9999; -- expect 1

ROLLBACK; -- nothing persisted, no cleanup needed

Advantages:

• Speed — ROLLBACK is instant; DELETE queries on large tables are slow.
• Isolation — parallel test runs on the same DB can each have their own transaction without colliding (if using row-level locking).
• No orphan data — a test crash before teardown? The DB's crash recovery still rolls back uncommitted transactions.

Limitations:

• Doesn't work if the application code commits its own transactions (the test transaction can't roll back what the app already committed).
• Doesn't work across multiple DB connections (each connection has its own transaction scope).
• Some side effects (sending emails, calling external services) happen outside the DB transaction and can't be rolled back.

In practice, many teams use this pattern in unit/integration tests via frameworks like Spring's @Transactional test support or a direct JDBC/pg wrapper.