Posted inDatabase

Managing Database Transactions in SQL: Ensuring Data Consistency and Integrity

Database tutorial - IT technology blog
Database tutorial - IT technology blog

The Day My Bank Transfer Disappeared

A few years back, I was building a payment module for a small e-commerce platform. The logic seemed straightforward: deduct balance from the buyer’s account, then add it to the seller’s. Two UPDATE statements, done. Except one afternoon, the server hiccupped right between those two queries. The buyer’s balance dropped. The seller never received a cent. The money just… vanished.

That bug taught me more about database transactions than any textbook ever could. If you’re writing SQL that touches more than one row — especially across financial, inventory, or user data — transactions aren’t optional. They’re a hard requirement.

Root Cause: Why Data Gets Corrupted Without Transactions

The problem isn’t bad SQL. Databases execute statements one at a time, and the real world doesn’t wait politely between them.

A lot can go wrong between two related statements:

  • The application server crashes mid-operation
  • A network timeout interrupts the connection
  • Another concurrent user reads partial data
  • A constraint violation on statement 2 leaves statement 1 already committed

Without transactions, each SQL statement auto-commits immediately. Once it’s done, it’s permanent. There’s no “undo” if the next statement fails.

I’ve worked with MySQL, PostgreSQL, and MongoDB across different projects. Each has real strengths. But for transactional guarantees specifically, PostgreSQL’s strict ACID compliance has saved me more times than I can count. MySQL (InnoDB) is solid too — you just need to be deliberate about transaction boundaries. MongoDB added multi-document transactions later, and they work, but they feel like a retrofit rather than a core design principle.

What ACID Actually Means (Without the Textbook Fluff)

ACID defines what “reliable” actually means for a transaction. Skip the theory — here’s each property mapped to a real failure you’ve probably already hit:

Atomicity — All or Nothing

Either every statement in the transaction succeeds, or none of them do. If step 3 of 5 fails, steps 1 and 2 are automatically rolled back. My payment bug was an atomicity failure — the two UPDATEs weren’t wrapped in a single atomic unit.

Consistency — Rules Always Hold

The database moves from one valid state to another. Constraints, foreign keys, and business rules are enforced across the entire transaction — not just per statement.

Isolation — Transactions Don’t See Each Other’s Work

Concurrent transactions behave as if they run sequentially. One transaction can’t read another’s uncommitted changes. (The exact guarantee depends on isolation level — more on that shortly.)

Durability — Committed Data Survives Crashes

Once you COMMIT, it’s on disk. Even if the server dies one millisecond later, the data is there when it restarts.

Basic Transaction Syntax

Core commands are the same across MySQL and PostgreSQL:

-- Start a transaction
BEGIN;

-- Your SQL statements
UPDATE accounts SET balance = balance - 500 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE user_id = 2;

-- If everything looks good
COMMIT;

-- Or if something went wrong
ROLLBACK;

In application code, you manage this programmatically. Here’s a Python example using psycopg2 with PostgreSQL:

import psycopg2

conn = psycopg2.connect("dbname=mydb user=postgres")
conn.autocommit = False  # Disable autocommit — transactions are manual

try:
    cur = conn.cursor()

    # Deduct from sender
    cur.execute(
        "UPDATE accounts SET balance = balance - %s WHERE user_id = %s AND balance >= %s",
        (500, 1, 500)
    )
    if cur.rowcount == 0:
        raise ValueError("Insufficient funds or user not found")

    # Add to recipient
    cur.execute(
        "UPDATE accounts SET balance = balance + %s WHERE user_id = %s",
        (500, 2)
    )
    if cur.rowcount == 0:
        raise ValueError("Recipient not found")

    conn.commit()
    print("Transfer successful")

except Exception as e:
    conn.rollback()
    print(f"Transfer failed, rolled back: {e}")

finally:
    conn.close()

Pay attention to conn.autocommit = False — this is critical. Without it, psycopg2 wraps each statement in its own implicit transaction, which defeats the whole point.

SAVEPOINT — Partial Rollbacks When You Need Them

Sometimes you want to roll back part of a transaction without losing everything. SAVEPOINTs let you set named checkpoints within a transaction:

BEGIN;

INSERT INTO orders (user_id, total) VALUES (1, 1500);

SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id, qty) VALUES (101, 5, 2);

-- Oops, product 5 is out of stock
ROLLBACK TO SAVEPOINT after_order;

-- Try a substitute product
INSERT INTO order_items (order_id, product_id, qty) VALUES (101, 7, 2);

COMMIT;

SAVEPOINTs are most useful in batch processing. When I’m inserting 500 rows and hit a bad record at row 347, I want to skip that row — not abort the entire batch. Roll back to the savepoint set before the bad row, log it, move on.

Isolation Levels: Choosing Your Trade-off

Isolation levels are where most developers get surprised. Four standard levels exist, each trading off data accuracy against concurrency throughput:

READ UNCOMMITTED

Transactions can read uncommitted changes from other sessions — known as “dirty reads.” Here’s why that’s dangerous: Thread A updates an account balance from $500 to $0 but hasn’t committed. Thread B reads $0 and blocks a withdrawal. Thread A then rolls back. Thread B just blocked a valid withdrawal based on data that never actually existed. Fastest level, but I’ve never used it outside benchmarking.

READ COMMITTED (PostgreSQL default)

Only reads committed data. Dirty reads are prevented. But you can still hit “non-repeatable reads” — reading the same row twice in one transaction might return different results if another transaction committed in between your two reads.

REPEATABLE READ (MySQL InnoDB default)

Rows you’ve read are locked for the duration of the transaction. Non-repeatable reads are prevented. For most financial operations, this is the right default.

SERIALIZABLE

Full isolation — transactions behave as if they ran one at a time. Prevents all anomalies including phantom reads. Use it for critical financial reconciliation or audit operations. Throughput drops noticeably under high concurrency, so don’t apply it globally.

-- Set isolation level for the current transaction (PostgreSQL)
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Or set for the session (MySQL)
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Common Transaction Pitfalls

Long-Running Transactions

An open transaction holds locks. On a busy PostgreSQL instance handling 500 requests/minute, a transaction left open for just 30 seconds can create a lock queue 50+ connections deep. Your write-ahead log keeps growing the whole time. Other queries pile up waiting.

Keep transactions short — open late, commit early. Move slow computation outside the transaction boundaries entirely:

# Bad: API call inside transaction
conn.autocommit = False
cur.execute("UPDATE orders SET status = 'processing' WHERE id = %s", (order_id,))
response = requests.post(payment_gateway_url, data=payload)  # Could take 3+ seconds!
cur.execute("UPDATE orders SET status = 'paid' WHERE id = %s", (order_id,))
conn.commit()

# Good: Keep the transaction tight around DB work only
response = requests.post(payment_gateway_url, data=payload)  # Outside transaction

conn.autocommit = False
if response.status_code == 200:
    cur.execute("UPDATE orders SET status = 'paid' WHERE id = %s", (order_id,))
    conn.commit()
else:
    cur.execute("UPDATE orders SET status = 'failed' WHERE id = %s", (order_id,))
    conn.commit()

Forgetting Error Handling in Application Code

If your code throws an exception and you never call ROLLBACK, the transaction stays open until the connection closes. In a connection pool, that connection gets recycled to the next request — in a broken state. Always wrap transaction logic in try/except/finally, or use a context manager:

# Python context manager approach (psycopg2)
with psycopg2.connect(dsn) as conn:
    with conn.cursor() as cur:
        cur.execute("UPDATE ...")
        cur.execute("INSERT ...")
    # conn.__exit__ calls commit() on success, rollback() on exception

Transaction Design Principles That Actually Hold Up in Production

After shipping several production systems with transactional requirements, my rules boil down to five:

  1. One logical operation = one transaction. A “transfer funds” action is one transaction, not two separate commits.
  2. Short and focused. Batch 50 inserts together — but don’t mix unrelated operations in the same transaction just for convenience.
  3. Match isolation level to risk. Most CRUD is fine with READ COMMITTED. Aggregations or financial totals need REPEATABLE READ or higher.
  4. Always handle rollback explicitly in code. Don’t rely on connection cleanup to do it for you.
  5. Test failure paths. Simulate crashes mid-transaction in development. Kill the process after statement 2 of 3. If you’ve never verified what happens, you don’t actually know your transaction is correct.

The payment bug I hit early in my career cost a few hours of manual data correction and one very stressed customer service email. It could have been much worse. Transactions are the one SQL feature you shouldn’t ship without — not an optimization, but a correctness guarantee.

Share:
Tags: