What Is ACID? Why Your Database Doesn't Lose Your Data

Why AI Coders Need to Understand ACID

When you're vibe-coding with AI, it writes your database queries for you. Most of the time, that's fine. But there's a category of bug that AI-generated code gets silently wrong — not because the SQL is incorrect, but because the operations aren't wrapped in a transaction.

Here's the scenario that breaks apps: you have a multi-step operation. Create a user, then create their profile, then send a welcome email trigger. AI writes three separate database calls. Then your server crashes — or hits a timeout, or runs out of memory — after step one but before step three. Now you have a half-created user in your database. Your app will behave strangely forever, or until someone manually cleans up that data.

ACID is the solution to this problem. It's the set of properties that lets you say: "Either all of this succeeds, or none of it happens." It's why your bank account doesn't lose money when the server crashes mid-transfer.

As a builder, you need to understand ACID for three reasons:

• Knowing when to use transactions. AI doesn't always add them automatically — you need to recognize when they're needed.
• Debugging mysterious data corruption. Half-written records are almost always a missing transaction.
• Choosing your database. Not all databases are ACID compliant. SQL vs NoSQL is partly a question of how much you care about ACID guarantees.

You don't need to implement ACID yourself — your database does it. But you do need to know how to use it correctly, and how to recognize when AI-generated code is leaving it out.

Real Scenario: The Order That Can't Be Half-Processed

You're building an e-commerce feature. When a user places an order, your app needs to do three things in sequence: deduct inventory, charge the card, and create the order record. If any of these fail, none of them should happen.

This is exactly the kind of prompt where ACID matters. Let's see what AI generates — and more importantly, why the transaction wrapper is the most important part of the code.

What AI Generated

The Safe Version (with Transactions)

// process-order.js
// ACID-safe order processing — all steps succeed or none do

import pg from 'pg';
import Stripe from 'stripe';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const stripe = new Stripe(process.env.STRIPE_SECRET_KEY);

async function processOrder({ userId, productId, paymentMethodId, amount }) {
  const client = await pool.connect();

  try {
    // BEGIN starts the transaction — nothing is committed until COMMIT
    await client.query('BEGIN');

    // Step 1: Deduct inventory (inside the transaction)
    const inventoryResult = await client.query(
      `UPDATE products
       SET inventory = inventory - 1
       WHERE id = $1 AND inventory > 0
       RETURNING inventory`,
      [productId]
    );

    if (inventoryResult.rowCount === 0) {
      throw new Error('Product out of stock');
    }

    // Step 2: Charge the card (outside the transaction — Stripe is external)
    const charge = await stripe.paymentIntents.create({
      amount,
      currency: 'usd',
      payment_method: paymentMethodId,
      confirm: true,
    });

    if (charge.status !== 'succeeded') {
      throw new Error(`Payment failed: ${charge.status}`);
    }

    // Step 3: Create the order record
    const order = await client.query(
      `INSERT INTO orders (user_id, product_id, stripe_payment_id, amount, status)
       VALUES ($1, $2, $3, $4, 'completed')
       RETURNING id`,
      [userId, productId, charge.id, amount]
    );

    // COMMIT makes all database changes permanent
    await client.query('COMMIT');

    return { orderId: order.rows[0].id, chargeId: charge.id };

  } catch (error) {
    // ROLLBACK undoes all database changes if anything failed
    await client.query('ROLLBACK');
    throw error;

  } finally {
    // Always release the connection back to the pool
    client.release();
  }
}

The Dangerous Version (without Transactions)

// ❌ What AI often generates without explicit prompting

async function processOrderUnsafe({ userId, productId, paymentMethodId, amount }) {
  // These three queries have NO transaction wrapper
  // If the server crashes between any two, your data is broken

  await pool.query(
    'UPDATE products SET inventory = inventory - 1 WHERE id = $1',
    [productId]
  );
  // ← If the server dies here, inventory is deducted but no order exists

  const charge = await stripe.paymentIntents.create({ ... });
  // ← If this fails, inventory is already deducted. $0 charged, -1 inventory.

  await pool.query(
    'INSERT INTO orders (user_id, product_id, ...) VALUES ($1, $2, ...)',
    [userId, productId]
  );
}

The unsafe version looks fine and will work 99% of the time during development. The 1% failure — a network timeout, an out-of-memory error, a deployment mid-request — leaves your data in an inconsistent state that's hard to detect and painful to fix.

Understanding Each Part of ACID

A — Atomicity: All or Nothing

Atomicity is the most intuitive property. It means a transaction is treated as a single unit — either every operation inside it succeeds and gets written, or none of them do. There's no middle state.

The word comes from "atom" in the Greek sense: indivisible. Your transaction cannot be partially applied.

In practice, this means:

• You start a transaction with BEGIN
• You run all your queries
• You either COMMIT (make everything permanent) or ROLLBACK (undo everything)
• If your code throws an error or your server crashes before COMMIT, the database automatically rolls back to the state before your BEGIN

-- Atomic money transfer: either both updates happen, or neither does
BEGIN;

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

COMMIT;  -- Both updates are now permanent

-- If anything fails between BEGIN and COMMIT, PostgreSQL
-- automatically rolls back. Account 1's money is not lost.

Without atomicity, a crash between the two UPDATE statements would deduct $500 from one account and never add it to the other. That money simply vanishes. Atomicity prevents that.

C — Consistency: Valid State Always

Consistency means the database always transitions from one valid state to another valid state. It can never be left in an invalid state — one that violates your rules.

"Valid state" is defined by your database constraints: primary keys, foreign keys, NOT NULL columns, CHECK constraints, unique constraints. If a transaction would violate any of these, the database rejects it entirely.

-- Example: a foreign key constraint enforces consistency
CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER NOT NULL REFERENCES users(id),  -- must reference a real user
  amount     NUMERIC NOT NULL CHECK (amount > 0)     -- must be positive
);

-- This will FAIL — user 9999 doesn't exist:
INSERT INTO orders (user_id, amount) VALUES (9999, 49.99);
-- ERROR: insert or update on table "orders" violates foreign key constraint

-- This will also FAIL — negative amounts aren't valid:
INSERT INTO orders (user_id, amount) VALUES (1, -10.00);
-- ERROR: new row for relation "orders" violates check constraint

Consistency is partly the database's job (enforcing constraints) and partly your job as the developer (writing those constraints correctly in the first place). AI will often generate tables without constraints — make sure you add them, because they're what makes consistency work.

Practical tip: Define your constraints at the database level, not just in your application code. If you only validate in your API layer, a bug or a direct database query can bypass it and leave you with corrupted data.

I — Isolation: Transactions Don't See Each Other's Work-in-Progress

Isolation is the most complex ACID property. It answers this question: if two users are making changes to the database at the exact same time, what do they see?

Imagine two people buying the last item in stock simultaneously. Without isolation, both could read "1 item available," both decide to buy, and you end up with -1 inventory. Isolation prevents this.

The database handles concurrent transactions so that each one appears to run as if it's the only one. Changes from one transaction aren't visible to another until the first one commits.

-- Two concurrent transactions trying to buy the last item

-- Transaction A (User 1):
BEGIN;
SELECT inventory FROM products WHERE id = 42;  -- sees: 1
-- (Transaction B starts here and also sees 1)

-- Transaction B (User 2):
BEGIN;
SELECT inventory FROM products WHERE id = 42;  -- also sees: 1

-- Transaction A continues:
UPDATE products SET inventory = inventory - 1 WHERE id = 42 AND inventory > 0;
COMMIT;  -- inventory is now 0

-- Transaction B tries to commit:
UPDATE products SET inventory = inventory - 1 WHERE id = 42 AND inventory > 0;
-- PostgreSQL detects the conflict and either blocks, retries, or rejects
-- depending on isolation level — inventory never goes below 0

Isolation levels control exactly how much transactions see of each other. PostgreSQL offers four levels, from most to least strict:

For most apps, the default READ COMMITTED is fine. If you're doing financial calculations or anything where reading stale data mid-transaction would cause problems, use SERIALIZABLE:

-- Set isolation level for a critical transaction
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT balance FROM accounts WHERE id = $1;
-- Guaranteed: this value won't change under us before we commit

UPDATE accounts SET balance = balance - $2 WHERE id = $1;
COMMIT;

D — Durability: Committed Data Survives Crashes

Durability means once you COMMIT a transaction and the database says "OK, done," that data is permanent. A power outage, a server crash, a kernel panic — none of it will undo a committed transaction.

How does this work? The database writes changes to a write-ahead log (WAL) on disk before acknowledging your COMMIT. Even if the database crashes a millisecond after confirming, when it restarts it reads the WAL, replays any committed-but-not-yet-applied transactions, and recovers to the correct state.

-- After this COMMIT returns successfully:
BEGIN;
INSERT INTO payments (user_id, amount, status) VALUES (1, 99.99, 'completed');
COMMIT;

-- Even if the server loses power immediately after COMMIT returns,
-- this payment record WILL be in the database when the server restarts.
-- That's durability.

Durability is mostly invisible to you as a developer — the database handles it automatically. What matters is that you never assume data is saved until you've received a successful COMMIT response. If your code crashes before committing, that data is gone and that's correct behavior.

A note on connection pooling: When using connection pooling, make sure you release connections correctly after committing or rolling back. A connection returned to the pool mid-transaction causes silent bugs where the next user gets a connection that's already inside an open transaction.

What AI Gets Wrong About ACID

1. Forgetting to Wrap Multi-Step Operations in Transactions

This is the #1 ACID mistake in AI-generated code. When you ask AI to "create a user and their profile," it often writes two separate INSERT statements with no transaction wrapper. If the second one fails, you have a user row with no profile row — a broken state.

// ❌ AI often generates this (two separate operations, no transaction):
async function createUser(userData) {
  const user = await db.query(
    'INSERT INTO users (email, name) VALUES ($1, $2) RETURNING id',
    [userData.email, userData.name]
  );

  // If this fails, the user row exists but has no profile
  await db.query(
    'INSERT INTO profiles (user_id, bio) VALUES ($1, $2)',
    [user.rows[0].id, userData.bio]
  );
}

// ✅ Always ask AI to wrap multi-step writes in a transaction:
async function createUser(userData) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    const user = await client.query(
      'INSERT INTO users (email, name) VALUES ($1, $2) RETURNING id',
      [userData.email, userData.name]
    );

    await client.query(
      'INSERT INTO profiles (user_id, bio) VALUES ($1, $2)',
      [user.rows[0].id, userData.bio]
    );

    await client.query('COMMIT');
    return user.rows[0].id;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

2. Mixing External API Calls Inside Database Transactions

AI sometimes puts Stripe charges, email sends, or HTTP requests inside a BEGIN/COMMIT block. This is wrong for two reasons: external APIs aren't part of your database transaction (they can't be rolled back), and long-running HTTP calls inside a transaction hold a database connection open and lock rows unnecessarily.

// ❌ External API call inside the transaction (wrong):
await client.query('BEGIN');
await client.query('UPDATE inventory ...'); // holds a lock
await stripe.charges.create({ ... });       // slow HTTP call holding DB lock!
await client.query('COMMIT');

// ✅ Do external calls outside the transaction, use DB for compensation:
// 1. Check and reserve inventory in a transaction
// 2. Call Stripe outside any transaction
// 3. Confirm or release the reservation in a new transaction

3. Not Handling Rollback on Error

AI sometimes wraps code in BEGIN/COMMIT but forgets the ROLLBACK in the error handler. A transaction left open (neither committed nor rolled back) holds locks on every row it touched — grinding your entire app to a halt.

// ❌ Missing ROLLBACK — if an error throws, the transaction hangs open:
async function transferMoney(fromId, toId, amount) {
  const client = await pool.connect();
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, fromId]);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, toId]);
  await client.query('COMMIT');
  client.release(); // If an error throws above, this line is never reached
}

// ✅ Always use try/catch/finally:
async function transferMoney(fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, fromId]);
    await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, toId]);
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK'); // ← always roll back on failure
    throw err;
  } finally {
    client.release(); // ← always release the connection
  }
}

4. Assuming NoSQL Databases Are ACID by Default

When AI helps you work with MongoDB, it doesn't always mention that multi-document ACID transactions are not the default behavior. A single document write in MongoDB is atomic, but writing to multiple documents is not — unless you explicitly use a session and transaction. For most apps, this matters less than you'd think, but it's worth knowing if you're building anything financial.

// MongoDB: single-document operations are atomic (fine)
await db.collection('users').updateOne(
  { _id: userId },
  { $set: { email: newEmail } }
);

// MongoDB: multi-document operation requires explicit transaction
const session = client.startSession();
try {
  session.startTransaction();

  await db.collection('accounts').updateOne(
    { _id: fromAccountId },
    { $inc: { balance: -amount } },
    { session }  // ← pass session to include in transaction
  );

  await db.collection('accounts').updateOne(
    { _id: toAccountId },
    { $inc: { balance: amount } },
    { session }
  );

  await session.commitTransaction();
} catch (err) {
  await session.abortTransaction();
  throw err;
} finally {
  await session.endSession();
}

5. Skipping Database-Level Constraints

AI often validates data in application code and doesn't bother adding NOT NULL, CHECK, or foreign key constraints to the table itself. This breaks the "Consistency" guarantee — a bug in your app layer, or a direct SQL query run during debugging, can bypass all your validation and insert garbage data.

-- ❌ No constraints — any value can be inserted:
CREATE TABLE orders (
  id      SERIAL PRIMARY KEY,
  user_id INTEGER,
  amount  NUMERIC,
  status  TEXT
);

-- ✅ Constraints enforce consistency at the database level:
CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  amount     NUMERIC NOT NULL CHECK (amount > 0),
  status     TEXT NOT NULL DEFAULT 'pending'
               CHECK (status IN ('pending', 'completed', 'refunded', 'cancelled')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

How to Debug ACID-Related Issues

Problem: Partial Writes — Some Records Exist, Others Are Missing

Classic symptom: a user was created but has no profile row. An order exists but inventory was never deducted. You can see the broken data in your database.

Steps to diagnose:

• Look for multiple INSERT or UPDATE calls in the same function with no surrounding transaction
• Check if your error handler has a ROLLBACK call
• Verify your connection is released in a finally block, not only on success

Problem: Deadlocks

A deadlock happens when two transactions are each waiting for a lock the other holds. PostgreSQL detects this automatically and kills one of the transactions with an error.

-- ERROR: deadlock detected
-- DETAIL: Process 12345 waits for ShareLock on transaction 67890;
-- blocked by process 67890.
-- Process 67890 waits for ShareLock on transaction 12345;
-- blocked by process 12345.

The fix is almost always the same: access tables and rows in the same order in all transactions. If Transaction A always updates accounts then orders, and Transaction B does the same, they'll never deadlock. Deadlocks only happen when transactions acquire locks in different orders.

Problem: Slow Queries Because of Long-Running Transactions

If a transaction holds a lock and never commits (because of a bug, a stalled request, or a forgotten rollback), every other query that needs that lock will queue up and wait.

-- Find long-running transactions in PostgreSQL:
SELECT pid, now() - xact_start AS duration, query, state
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state != 'idle'
ORDER BY duration DESC;

-- Kill a stuck transaction if needed (use the pid from above):
SELECT pg_terminate_backend(12345);

If you see HTTP 500 or 503 errors combined with database timeouts, a stuck transaction is often the cause. Check pg_stat_activity first before restarting anything.

Problem: "Transaction Already Aborted" Errors

-- ERROR: current transaction is aborted, commands ignored until
-- end of transaction block

This means a previous statement in your transaction already failed, but you didn't check for the error — you kept running more queries. PostgreSQL ignores them all until you call ROLLBACK. The fix is to always check errors and call ROLLBACK immediately when one occurs, rather than continuing.

In most ORM frameworks (Prisma, Drizzle, SQLAlchemy), this is handled automatically when you use their transaction helpers. It's mainly a concern when writing raw SQL.

Checking If Your ORM Handles Transactions

Different ORMs have different ways to create transactions. Always verify that your ORM's transaction helper actually issues a BEGIN:

// Prisma — uses prisma.$transaction():
await prisma.$transaction(async (tx) => {
  await tx.user.create({ data: { email, name } });
  await tx.profile.create({ data: { userId, bio } });
  // If either fails, both are rolled back automatically
});

// Drizzle — uses db.transaction():
await db.transaction(async (tx) => {
  await tx.insert(users).values({ email, name });
  await tx.insert(profiles).values({ userId, bio });
});

// Sequelize — uses sequelize.transaction():
const t = await sequelize.transaction();
try {
  await User.create({ email, name }, { transaction: t });
  await Profile.create({ userId, bio }, { transaction: t });
  await t.commit();
} catch (err) {
  await t.rollback();
  throw err;
}

What to Learn Next

Frequently Asked Questions