Stats
Actions
Tags
From harness-claude
Explains ACID transaction guarantees with worked SQL examples. Useful when debugging data inconsistencies, designing transactional workflows, or evaluating database choices.
How this skill is triggered — by the user, by Claude, or both
Slash command
/harness-claude:db-acid-propertiesThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
> ACID guarantees that database transactions are processed reliably: each transaction is all-or-nothing (Atomic), leaves the database in a valid state (Consistent), operates as if no other transactions are running (Isolated), and once committed, persists even through crashes (Durable).
ACID guarantees that database transactions are processed reliably: each transaction is all-or-nothing (Atomic), leaves the database in a valid state (Consistent), operates as if no other transactions are running (Isolated), and once committed, persists even through crashes (Durable).
A transaction is all-or-nothing. Either every statement succeeds and is committed, or none of them take effect.
Worked Example -- bank transfer:
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE id = 1;
UPDATE accounts SET balance = balance + 100.00
WHERE id = 2;
COMMIT;
If the server crashes between the two UPDATEs, PostgreSQL rolls back the entire transaction on recovery. The money neither disappears nor duplicates.
Partial rollback with SAVEPOINTs:
BEGIN;
INSERT INTO orders (customer_id, total) VALUES (42, 250.00);
SAVEPOINT before_loyalty;
INSERT INTO loyalty_points (customer_id, points)
VALUES (42, 25);
-- If loyalty service is down, rollback just this part
ROLLBACK TO SAVEPOINT before_loyalty;
-- Order still commits successfully
COMMIT;
SAVEPOINTs let you isolate optional operations within a larger atomic unit.
Database constraints enforce invariants. A transaction that would violate any constraint is rejected entirely.
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
owner TEXT NOT NULL,
balance NUMERIC(12,2) NOT NULL CHECK (balance >= 0)
);
-- This transaction fails atomically -- both statements rolled back
BEGIN;
UPDATE accounts SET balance = balance - 500.00 WHERE id = 1; -- balance goes to -100
UPDATE accounts SET balance = balance + 500.00 WHERE id = 2;
COMMIT;
-- ERROR: new row for relation "accounts" violates check constraint "accounts_balance_check"
Important distinction: ACID consistency means the database satisfies all defined constraints after every transaction. This is different from CAP theorem consistency, which means linearizability (every read returns the most recent write across distributed nodes). They share a name but are different concepts.
Concurrent transactions operate as if each runs alone. PostgreSQL's default isolation level is Read Committed -- each statement within a transaction sees only data committed before that statement began.
Concurrent session example:
Session A Session B
───────── ─────────
BEGIN;
SELECT balance FROM accounts
WHERE id = 1; -- returns 400
BEGIN;
UPDATE accounts SET balance = 300
WHERE id = 1;
COMMIT;
SELECT balance FROM accounts
WHERE id = 1; -- returns 300 (sees B's commit)
COMMIT;
Under Read Committed, Session A sees Session B's committed change within the same transaction. For stricter isolation (repeatable reads, serializable), see the db-isolation-levels skill.
Once COMMIT returns success, the data survives crashes. PostgreSQL achieves this through Write-Ahead Logging (WAL): changes are written to the WAL and fsynced to disk before COMMIT returns. See db-acid-in-practice for implementation details.
-- After this returns, the data is on disk regardless of what happens next
INSERT INTO audit_log (event, timestamp) VALUES ('payment_processed', NOW());
-- COMMIT is implicit in autocommit mode
BEGIN;
-- Create user account
INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice')
RETURNING id INTO user_id;
-- Create financial account with opening balance
INSERT INTO accounts (user_id, balance) VALUES (user_id, 0.00);
-- Record the registration event
INSERT INTO audit_log (user_id, event) VALUES (user_id, 'registered');
COMMIT;
Atomicity ensures all three inserts succeed or none do. Consistency ensures the CHECK constraints hold. Isolation ensures concurrent registrations do not interfere. Durability ensures the registration persists.
Auto-commit mode for multi-statement operations. Without an explicit BEGIN, each statement is its own transaction. Two related INSERTs can partially succeed, leaving the database in an inconsistent state that no constraint can prevent.
Assuming ACID means "no bugs." ACID guarantees transaction correctness at the database level. It does not prevent application-level logic errors like transferring money to a wrong account or double-processing an idempotent operation.
Relying on application code for invariants that belong in constraints. If balance >= 0 is a business rule, put it in a CHECK constraint. Application code can have bugs; database constraints cannot be bypassed by any client.
Long-running transactions. Holding a transaction open for minutes (or hours) blocks row cleanup, bloats the WAL, and increases lock contention. Keep transactions short and focused.
db-cap-theorem.| Property | Failure Mode | Symptom |
|---|---|---|
| Atomicity | Partial commit (should never happen in PG) | Half-completed operation in data |
| Consistency | Constraint not defined | Invalid data accepted silently |
| Isolation | Wrong isolation level chosen | Phantom reads, non-repeatable reads |
| Durability | fsync disabled or hardware failure | Committed data lost after crash |
MySQL's storage engine determines ACID compliance:
Always verify SHOW ENGINES; and confirm your tables use InnoDB: SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_db';
An e-commerce platform processed payments without explicit transactions. The flow was: (1) deduct inventory, (2) charge payment, (3) create order record. When the payment API timed out between steps 1 and 3, inventory was decremented but no order existed -- "ghost deductions" that required manual reconciliation. Wrapping all three steps in a single transaction with a SAVEPOINT before the payment call eliminated the inconsistency. Failed payments now roll back cleanly.
BEGIN/COMMIT blocks.BEGIN/COMMIT boundaries.npx claudepluginhub intense-visions/harness-engineering --plugin harness-claudeACID properties, isolation levels, locks, deadlocks, and transaction design.
Explains ACID durability mechanisms: Write-Ahead Logging, fsync, and crash recovery. Helps tune durability vs. performance tradeoffs and debug data loss.
Enforces PostgreSQL patterns for transaction safety with TX_ naming, read-write separation, ULID UUID PKs, type-safe JSONB/decimal handling, and snake_case conventions.