SQL ACID Transactions — How to Ensure Data Integrity and Handle Concurrency in Your Database

Your database loses data when two customers try to buy the last product at the same time? Or a mid-payment error leaves the system with an invoice issued but stock not updated? These are the problems that ACID transactions solve — and that we, at Meteora Web, deal with every day in e-commerce and ERP projects.

If you don't manage transactions, your code will eventually produce inconsistent data. It's not a matter of "if", but "when". And when it happens, the cost comes in refunds, complaints, and lost trust.

Why Are ACID Transactions Essential for Your Application?

Imagine an online order:
- Deduct 1 unit from inventory.
- Charge the customer's card.
- Insert the order row.
If the server crashes after the charge but before the order row, you have money taken but no order recorded. Or stock updated but payment failed. ACID transactions guarantee that all these operations are atomic: all or nothing.

We work with businesses processing hundreds of orders daily. Without transactions, every concurrency creates a data gap. With transactions, the database becomes an umpire that does not allow half-baked operations.

How Do the ACID Properties Work — Atomicity, Consistency, Isolation, Durability

The four properties are not options; they are requirements. Here is what each means in practice:

Atomicity: a transaction is a single unit. If any statement fails, the entire block is undone (rollback). No partial changes.

Consistency: before and after the transaction, the database remains in a valid state. Constraints (foreign keys, unique, check) are respected. Break a rule, and the transaction does not commit.

Isolation: concurrent transactions do not interfere. The isolation level (we will cover it next) controls how much they see each other's intermediate data.

Durability: once committed, the change is permanent, even on immediate crash. Database logs guarantee recovery.

What Concurrency Problems Do Transactions Solve?

Three classic problems we have seen in production:

• Dirty read: a transaction reads uncommitted data from another. If that one rolls back, the data read is false.
• Lost update: two transactions read the same value, modify it, write it. The last overwrites the first, losing the update.
• Phantom read: a transaction runs the same query twice and gets different results because another inserted rows in between.

We saw a real case: a fashion e-commerce lost stock updates because two users bought the last pair of shoes. Result: sold to two customers, inventory at -1. ACID transactions, with the right isolation level, solve all this.

How to Implement a Transaction with Rollback in MySQL and PostgreSQL

The base mechanism is identical in both databases: BEGIN TRANSACTION, run the queries, then COMMIT if all ok, ROLLBACK if something fails.

Here is a concrete example for a payment, using PHP (PDO) and MySQL:

try {
    $pdo->beginTransaction();

    // 1. Update inventory
    $stmt = $pdo->prepare("UPDATE prodotti SET quantita = quantita - 1 WHERE id = :id AND quantita > 0");
    $stmt->execute(['id' => $prodottoId]);

    if ($stmt->rowCount() === 0) {
        throw new Exception("Product not available");
    }

    // 2. Insert order
    $stmt = $pdo->prepare("INSERT INTO ordini (cliente_id, totale, stato) VALUES (:cliente, :totale, 'completato')");
    $stmt->execute(['cliente' => $clienteId, 'totale' => $totale]);

    $ordineId = $pdo->lastInsertId();

    // 3. Insert order lines
    $stmt = $pdo->prepare("INSERT INTO righe_ordine (ordine_id, prodotto_id, prezzo) VALUES (:ordine, :prodotto, :prezzo)");
    $stmt->execute(['ordine' => $ordineId, 'prodotto' => $prodottoId, 'prezzo' => $prezzo]);

    // Everything went well, commit
    $pdo->commit();

} catch (Exception $e) {
    // If any step fails, everything is undone
    $pdo->rollBack();
    // Log error, notify operator
    error_log($e->getMessage());
}

In MySQL, BEGIN implicitly starts a transaction, but it is good practice to use START TRANSACTION for clarity. In PostgreSQL, it works the same.

Intermediate Saves with SAVEPOINT

If you want partial rollback without discarding the entire transaction, use SAVEPOINT. Useful for complex processes:

START TRANSACTION;
SAVEPOINT before_payment;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- If payment fails, rollback only to this point
ROLLBACK TO SAVEPOINT before_payment;
-- then retry or handle error
COMMIT;

Which Isolation Level Should You Choose and Why?

The SQL standard defines 4 levels. Higher means more protection but less concurrency. Here is the scale:

• READ UNCOMMITTED: allows dirty reads. We never use it. Zero integrity.
• READ COMMITTED: prevents dirty reads. Default in PostgreSQL and many systems. Good compromise.
• REPEATABLE READ: prevents dirty reads and lost updates. Default in MySQL (InnoDB). Guarantees that a row read twice in the same transaction gives the same value.
• SERIALIZABLE: maximum isolation. Runs transactions as if they were serial. Prevents phantom reads. Expensive in performance.

We recommend READ COMMITTED for most web applications, REPEATABLE READ for financial scenarios where read consistency is critical, and SERIALIZABLE only when concurrency is low or absolute correctness is mandatory (e.g., banking transactions).

In MySQL, set the level globally or per session:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

In PostgreSQL:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

How to Diagnose and Resolve Deadlocks from Concurrent Transactions

Deadlock: two transactions block each other waiting for resources held by the other. The database detects it and rolls back one. We have seen them in booking and accounting systems. To avoid them:
- Access tables always in the same order.
- Keep transactions short.
- Use SELECT ... FOR UPDATE sparingly.

Example of a row lock to prevent concurrent updates:

START TRANSACTION;
SELECT quantity FROM products WHERE id = 123 FOR UPDATE;
-- Now no other transaction can modify that row until we commit or rollback
UPDATE products SET quantity = quantity - 1 WHERE id = 123;
COMMIT;

ACID Transactions and Performance — When to Avoid or Optimize

Every transaction opens a lock and writes logs. If you make transactions too long or on huge tables, the database slows down. Practical rules:
- Do not put slow operations (external APIs, file uploads) inside a transaction.
- Commit frequently for batches, not a single transaction with 10,000 rows.
- Monitor lock waits with SHOW PROCESSLIST in MySQL or pg_stat_activity in PostgreSQL.

We optimized a management system processing 50,000 rows per day: we split the batch into 500-record chunks with one transaction per chunk. Execution time dropped by 70%.

What to Do Now

1. Check your existing code: in critical functions (orders, payments, registrations), are there explicit transactions? If not, add them immediately.
2. Choose the right isolation level: by default, InnoDB uses REPEATABLE READ, PostgreSQL uses READ COMMITTED. Evaluate if your scenario requires changing it.
3. Implement a retry pattern: when the database detects a deadlock and rolls back, retry the transaction (2–3 attempts with backoff).
4. Test concurrency: use tools like mysqlslap or custom scripts with 10 concurrent threads to simulate real loads.
5. Read the official documentation: MySQL Transaction Model and PostgreSQL Transactions.

At Meteora Web, we apply these practices every day to ensure our clients' databases never lose a cent. If you want to dive deeper into relational queries, read our main article on SQL and Relational Databases (Italian, but concepts apply universally).