How to Use Transactions in SQL

Use transactions when multiple SQL statements must succeed or fail as one unit. This protects data consistency in workflows like payments, inventory updates, account transfers, and multi-step admin actions.

What you’ll build or solve

You’ll learn how to use transactions in SQL with BEGIN, COMMIT, and ROLLBACK. You’ll also know when transactions prevent partial writes.

When this approach works best

This approach is the right choice when several related writes depend on each other.

Common real-world scenarios include:

• Bank transfers
• Order + inventory updates
• User creation + profile insert
• Billing workflows
• Bulk data cleanup

This is a bad idea when a single isolated statement already guarantees atomicity on its own.

Prerequisites

You only need:

• A database engine with transaction support
• Multiple related write operations
• Permission to write data

Step-by-step instructions

Step 1: Wrap related statements in one transaction

Start the transaction first.

BEGIN;

Run all dependent writes.

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

If everything succeeds, finalize it.

COMMIT;

If anything fails, undo everything.

ROLLBACK;

This prevents half-completed workflows.

What to look for:

• BEGIN starts the transaction
• COMMIT saves all changes
• ROLLBACK undoes everything
• Great for multi-step writes
• Protects consistency

Examples you can copy

Inventory-safe order

BEGIN;

INSERT INTO orders (user_id, total)
VALUES (101, 49.99);

UPDATE products
SET stock = stock - 1
WHERE product_id = 5;

COMMIT;

User + profile creation

BEGIN;
INSERT INTO users ...
INSERT INTO profiles ...
COMMIT;

Bulk cleanup

BEGIN;
DELETE FROM logs ...
DELETE FROM sessions ...
ROLLBACK;

Common mistakes and how to fix them

Mistake 1: Forgetting rollback on failure

What the reader might do:

Run 3 updates and stop after one fails.

Why it breaks: partial writes leave inconsistent data.

Corrected approach:

Always rollback on error.

Mistake 2: Leaving transactions open too long

What the reader might do:

Keep the transaction open during long application logic.

Why it breaks: locks increase contention.

Corrected approach:

Keep transactions short and focused.

Mistake 3: Wrapping independent writes unnecessarily

What the reader might do:

Transaction-wrap unrelated analytics inserts.

Why it breaks: this adds locking overhead without integrity benefit.

Corrected approach:

Use transactions only for dependent operations.

Troubleshooting

If rows stay locked, check for uncommitted transactions.

If partial data appears, verify rollback behavior.

If performance drops, shorten transaction scope.

If deadlocks happen, standardize statement order.

Quick recap

• Transactions group related writes atomically
• Use BEGIN, COMMIT, and ROLLBACK
• Great for multi-step workflows
• Keep transactions short
• Roll back on any failure