Databases do not just store data. They guarantee that data remains correct, complete, and recoverable even when servers crash, network connections drop, and hundreds of users write simultaneously. These guarantees do not happen by accident — they are enforced through four properties collectively known as ACID.
ACID was formalized by Jim Gray and Andreas Reuter in 1983. Before it, database systems had inconsistent behaviour during failures — a server crash mid-operation could leave data in an unknown state with no way to determine what succeeded and what did not. ACID defined the contract: if a database claims to be ACID-compliant, you know exactly what will happen in every failure scenario.
Every major relational database — PostgreSQL, MySQL (InnoDB), SQL Server, Oracle, SQLite — implements ACID. Understanding each property tells you what the database protects you from automatically and what you must protect yourself from in application code.
Loading FreshCart DB…
Ctrl + Enter to run
Loading FreshCart database in your browser…
// Part 02
Atomicity — All or Nothing
A
Atomicity
A transaction is indivisible — it either fully completes or fully does not happen
What it guarantees
Every SQL statement inside a BEGIN...COMMIT block is treated as a single unit. Either ALL statements execute and their changes are committed, or NONE of them take effect. There is no state where some statements committed and others did not — partial transactions are impossible.
How the database enforces it
Transaction log (WAL — Write-Ahead Log). Before any change is applied to data files, the intended change is written to the log. If the transaction commits, the log records the commit. If the server crashes mid-transaction, on restart the database reads the log: uncommitted transactions are rolled back, committed transactions are replayed. The log is the ground truth.
What breaks without it
Without atomicity, a payment that deducts ₹500 from a customer's wallet but crashes before crediting the merchant leaves ₹500 permanently lost — deducted from one account, never arrived at another. The money exists in neither place. Any multi-step operation that must either fully succeed or fully fail needs atomicity.
FreshCart scenario
A FreshCart order involves: INSERT into orders, INSERT into order_items (one per product), UPDATE products to reduce stock, INSERT into payment_log. Atomicity means if the payment_log INSERT fails (duplicate key, constraint violation), the order INSERT, order_items INSERT, and stock UPDATE are all rolled back. The customer's order either fully exists or does not exist — never half-created.
Atomicity in action — the all-or-nothing guarantee
-- WITHOUT a transaction (autocommit — each statement commits independently)
UPDATE orders SET order_status = 'Delivered' WHERE order_id = 1001;
-- ↑ committed immediately and permanently
-- <<< server crashes HERE >>>
INSERT INTO payment_log (order_id, amount, status)
VALUES (1001, 850.00, 'settled');
-- ↑ never executes — order is 'Delivered' but has no payment record
-- Result: corrupted state with no way to fix it automatically
-- WITH a transaction (atomic — both or neither)
BEGIN;
UPDATE orders SET order_status = 'Delivered' WHERE order_id = 1001;
INSERT INTO payment_log (order_id, amount, status)
VALUES (1001, 850.00, 'settled');
COMMIT;
-- If crash happens before COMMIT: on restart, both are rolled back
-- order_status is still 'Processing' — no corrupted state
Loading FreshCart DB…
Ctrl + Enter to run
Loading FreshCart database in your browser…
// Part 03
Consistency — Only Valid States
C
Consistency
A transaction can only move the database between valid states
What it guarantees
Consistency means the database is always in a valid state — before a transaction begins and after it commits. 'Valid state' is defined by all the rules declared in the schema: NOT NULL constraints, UNIQUE constraints, CHECK constraints, FOREIGN KEY constraints, and any triggers or rules. A transaction that would violate any of these is rejected in its entirety.
How the database enforces it
Constraint checking at commit time (and in many cases, statement-by-statement). When you try to INSERT a row with a NULL in a NOT NULL column, the database rejects it before the INSERT completes. When you try to INSERT an order_item referencing a product_id that does not exist, the FOREIGN KEY constraint rejects it. Constraints are the database's definition of what 'valid' means.
What breaks without it
Without consistency enforcement, you could have orders referencing customers who do not exist (broken foreign keys), negative stock quantities (violated CHECK constraints), duplicate primary keys (two orders with the same order_id), or NULL values in columns that require a value. These corrupt states cause cascading failures in queries, reports, and application logic that assume the data is valid.
FreshCart scenario
FreshCart's order_items table has a FOREIGN KEY to products. Consistency ensures you cannot insert an order_item for a product that was deleted — the database rejects it. The products table has stock_quantity >= 0 as a CHECK constraint — no transaction can decrement stock below zero. These rules are enforced at the database level, not just the application layer.
Consistency — constraints that define valid states
-- SQLite constraint examples (FreshCart-style)
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name TEXT NOT NULL, -- NOT NULL: every product needs a name
unit_price REAL NOT NULL CHECK (unit_price > 0), -- price must be positive
stock_qty INTEGER NOT NULL DEFAULT 0
CHECK (stock_qty >= 0), -- stock cannot go negative
in_stock INTEGER NOT NULL DEFAULT 1
);
CREATE TABLE order_items (
order_id INTEGER NOT NULL REFERENCES orders(order_id), -- FK: order must exist
product_id INTEGER NOT NULL REFERENCES products(product_id), -- FK: product must exist
quantity INTEGER NOT NULL CHECK (quantity > 0), -- must order at least 1
line_total REAL NOT NULL CHECK (line_total > 0),
PRIMARY KEY (order_id, product_id) -- UNIQUE: no duplicate lines
);
-- Attempts that VIOLATE consistency (all rejected by the database):
INSERT INTO products (product_name, unit_price) VALUES (NULL, 50.00);
-- → ERROR: NOT NULL constraint failed: products.product_name
INSERT INTO products (product_name, unit_price) VALUES ('Milk', -10.00);
-- → ERROR: CHECK constraint failed: unit_price > 0
INSERT INTO order_items (order_id, product_id, quantity, line_total)
VALUES (99999, 1, 2, 200.00);
-- → ERROR: FOREIGN KEY constraint failed (order_id 99999 does not exist)
Loading FreshCart DB…
Ctrl + Enter to run
Loading FreshCart database in your browser…
// Part 04
Isolation — Concurrent Without Conflict
I
Isolation
Concurrent transactions behave as if they ran one at a time
What it guarantees
Isolation means each transaction executes as if it were the only transaction running, even when hundreds of transactions run simultaneously. Changes made by an in-progress transaction are not visible to other transactions until the first transaction commits. This prevents one transaction from reading or depending on the intermediate, possibly-to-be-rolled-back work of another.
How the database enforces it
MVCC (Multi-Version Concurrency Control) in PostgreSQL: instead of one version of each row, the database maintains multiple versions. Each transaction sees the version that was current when the transaction started (or when the statement started, depending on the isolation level). Readers never block writers. Writers never block readers. Only write-write conflicts require locking.
What breaks without it
Without isolation, two sessions simultaneously reading inventory could both see stock = 1, both decide to sell the last item, both decrement the stock, and leave it at -1 (an impossible state). Reports could read a row mid-update and see an inconsistent intermediate value. Financial aggregations could include partial payments.
FreshCart scenario
Two FreshCart customers simultaneously try to buy the last Amul Butter (stock = 1). Without isolation: both read stock = 1, both decrement by 1, stock = -1. With isolation at SERIALIZABLE: the first transaction commits stock = 0, the second tries to decrement but the database detects the conflict and forces it to retry — it reads stock = 0 and fails with 'out of stock'. The impossible state never exists.
Default in PostgreSQL. Each statement sees only committed data. Prevents dirty reads. A long-running transaction may see different values if it reads the same row twice after another transaction commits between reads.
REPEATABLE READAllows: Phantom reads (in standard SQL; PostgreSQL also prevents these)
A transaction sees a snapshot taken at its start. The same row always returns the same value within the transaction, even if other transactions commit changes to it. Good for long-running reports.
SERIALIZABLEAllows: None — all anomalies prevented
Strongest isolation. The database guarantees the result is identical to running transactions one at a time in some serial order. Detects and rejects transactions that would create a serialization anomaly.
Loading FreshCart DB…
Ctrl + Enter to run
Loading FreshCart database in your browser…
// Part 05
Durability — Survived Crashes
D
Durability
Once committed, data survives any failure
What it guarantees
Once a transaction's COMMIT succeeds, its changes are permanent. A server crash, power failure, or OS restart immediately after COMMIT will not lose the committed data. When the database restarts, it recovers all committed transactions and discards all uncommitted ones. The COMMIT response to the client is only sent after the data is guaranteed to survive a crash.
How the database enforces it
Write-Ahead Logging (WAL). Before any change is applied to the actual data files, the change is first written to the WAL — a sequential append-only log on durable storage. On crash recovery, the database reads the WAL: transactions with a COMMIT record are replayed (applied to data files), transactions without a COMMIT record are rolled back. The WAL is fsynced to disk before COMMIT returns — this is why COMMIT on large transactions can be slightly slow.
What breaks without it
Without durability, a confirmed order could disappear after a server restart. A payment marked 'success' in the database could vanish. Customers could receive order confirmations for orders that no longer exist in the database. This is catastrophic for any financial or e-commerce system.
FreshCart scenario
A FreshCart customer places an order at 11:59 PM. The COMMIT succeeds and the app sends the order confirmation email. The database server crashes at 12:00 AM. When it restarts, the order is still there — COMMIT means it was written to the WAL before the crash. The order data survived because durability guarantees that every committed transaction is recoverable.
How WAL protects committed data
-- Timeline of a COMMIT and what durability guarantees:
-- T=0: Client sends COMMIT
-- T=1: Database writes WAL record to disk (fsync)
-- WAL record includes: transaction ID, COMMIT marker, all changes
-- T=2: Database acknowledges COMMIT to client → "committed"
-- Client receives success response
-- <<< Server crashes at T=3 >>>
-- T=10: Server restarts
-- T=11: Database reads WAL
-- → Finds transaction with COMMIT record → REDO (apply changes to data files)
-- → Finds transactions without COMMIT record → ROLLBACK (undo partial changes)
-- T=12: Database is back online — all committed data intact
-- PostgreSQL WAL configuration (durability vs. performance tradeoffs):
-- synchronous_commit = on → wait for WAL fsync (full durability, default)
-- synchronous_commit = off → don't wait for WAL fsync (faster, risk ~100ms of data loss on crash)
-- fsync = on → flush WAL to OS buffers (full durability, default)
-- fsync = off → DANGEROUS: no durability guarantee — never use in production
-- SQLite equivalent: WAL mode
-- PRAGMA journal_mode = WAL; -- use WAL for better concurrent reads + durability
-- PRAGMA synchronous = FULL; -- fsync on every commit (default, safe)
-- PRAGMA synchronous = NORMAL; -- fsync less often (slightly faster, still safe)
Loading FreshCart DB…
Ctrl + Enter to run
Loading FreshCart database in your browser…
// Part 06
ACID Together — How the Four Properties Interact
The four ACID properties are not independent — they reinforce each other. Atomicity without Durability is useless (you committed but the data vanished). Isolation without Consistency is incomplete (transactions don't interfere but can still violate constraints). In practice, they form a single contract the database maintains:
Atomicity + Consistency
Atomicity ensures no partial transaction applies. Consistency ensures every complete transaction produces a valid state. Together: only valid, complete state changes ever reach the database.
Isolation + Atomicity
Isolation hides in-progress transaction changes. Atomicity ensures those changes either fully appear (on commit) or never appear (on rollback). Together: other transactions never see partial work.
Durability + Atomicity
Atomicity ensures only committed transactions take effect. Durability ensures committed transactions survive crashes. Together: exactly what was committed will be there after any failure.
Isolation + Consistency
Isolation prevents concurrent transactions from reading each other's partial work. Consistency ensures each transaction's completed work is valid. Together: concurrent transactions produce the same result as serial execution.
All four ACID properties in one transaction
-- A FreshCart loyalty tier upgrade — all four properties at work
BEGIN;
-- ISOLATION: this SELECT sees only committed data (READ COMMITTED default)
-- Other sessions cannot see our changes until we COMMIT
SELECT customer_id, loyalty_tier, first_name
FROM customers
WHERE customer_id = 42;
-- CONSISTENCY: CHECK constraints on loyalty_tier values are enforced
-- ATOMICITY: both UPDATEs are part of one indivisible unit
UPDATE customers
SET loyalty_tier = 'Gold'
WHERE customer_id = 42;
INSERT INTO loyalty_log (customer_id, old_tier, new_tier, upgraded_at)
VALUES (42, 'Silver', 'Gold', date('now'));
-- DURABILITY: once COMMIT returns, both changes survive any crash
-- ATOMICITY: if INSERT fails, the UPDATE is also rolled back
COMMIT;
// Part 07
ACID vs BASE — The NoSQL Tradeoff
Not all databases are ACID-compliant. Many NoSQL databases (Cassandra, DynamoDB, CouchDB) trade ACID guarantees for higher availability and partition tolerance. The alternative model is called BASE: Basically Available, Soft state, Eventually consistent.
ACID
Strong consistency — reads always see the latest committed write
Transactions are all-or-nothing
Isolation prevents concurrent anomalies
Durability guarantees no data loss on commit
Best for: financial data, e-commerce orders, medical records
Tradeoff: lower write throughput under heavy concurrent load
BASE
Eventual consistency — reads eventually see the latest write (may lag)
No transactions — individual writes are atomic, not multi-row
No isolation — concurrent writes may conflict, resolved later
Availability prioritized over consistency (stays up during network splits)
Best for: social media feeds, recommendations, click tracking, logs
Tradeoff: application must handle stale or conflicting reads
💡 Note
The choice between ACID and BASE is not about which is better — it is about which fits the workload. FreshCart orders, wallet balances, and inventory require ACID: no eventual consistency is acceptable when ₹1,000 is at stake. FreshCart's product recommendation engine or user activity log can tolerate BASE: a slightly stale recommendation or a 500ms lag in logging a page view is fine.
// Part 08
Real Failures ACID Prevents
Abstract properties are hard to remember. Concrete failures are not. Here are the five most common real-world database failures, and exactly which ACID property would have prevented each.
Double-spend / partial paymentFixed by: Atomicity
Failure: A payment service deducts ₹500 from the customer's wallet (committed) but the server crashes before crediting the merchant. ₹500 is permanently lost.
ACID fix: Wrap both the debit and credit in a single transaction. Either both commit or both roll back. The crash cannot leave one committed and one not.
Failure: Two customers simultaneously see stock = 1 for the last item, both purchase it, both decrement stock. Final stock = -1. Both customers receive order confirmations. Only one item exists.
ACID fix: SERIALIZABLE isolation detects that both transactions read and wrote the same row and forces one to retry. The retry reads stock = 0 and rejects the purchase.
Order with invalid product referenceFixed by: Consistency
Failure: A product is deleted from the products table. An existing order_items row references that product_id. Reports and queries now fail or return NULL for the product name.
ACID fix: FOREIGN KEY constraint (Consistency) prevents the product from being deleted while order_items rows reference it — or cascades the delete to order_items. The database enforces the relationship.
Lost order after server crashFixed by: Durability
Failure: A customer places an order, receives a confirmation email, and the server crashes 50ms after COMMIT returns. On restart, the order is gone — it was only in memory.
ACID fix: Durability via WAL ensures the COMMIT is written to durable storage before returning success. The crash-restart cycle replays the WAL and restores the order.
Failure: A financial report runs for 10 seconds. Mid-report, a batch update changes 500 order amounts. The report reads some rows before the update and some after — totals are based on a mix of old and new values.
ACID fix: REPEATABLE READ or SERIALIZABLE isolation gives the report transaction a consistent snapshot taken at its start. All reads within the transaction see the same point in time.
// Part 09
Interview Prep — 5 Questions With Complete Answers
Q: What does ACID stand for and why does it matter?
ACID stands for Atomicity, Consistency, Isolation, and Durability — the four properties that define the reliability guarantees of relational database transactions.
Atomicity means a transaction is all-or-nothing. Either all SQL statements in the transaction execute and commit, or none of them take effect. If any statement fails, the entire transaction is rolled back. This prevents partial state changes — a payment deduction without the corresponding credit is impossible.
Consistency means a transaction can only move the database between valid states. The database enforces all declared constraints (NOT NULL, UNIQUE, FOREIGN KEY, CHECK) at commit time. A transaction that would violate any constraint is rejected, preserving the integrity rules built into the schema.
Isolation means concurrent transactions behave as if they ran one at a time. Each transaction sees a consistent view of the data without being affected by in-progress writes from other transactions. This prevents anomalies like dirty reads (reading uncommitted data) and phantoms (seeing rows inserted by another transaction mid-query).
Durability means once a transaction commits, its changes are permanent. A server crash immediately after COMMIT will not lose the data. The database achieves this through write-ahead logging — changes are written to durable storage before COMMIT returns.
ACID matters because data integrity cannot be compromised in financial, e-commerce, and medical applications. Without ACID, a system can leave money in neither the sender's nor receiver's account, create orders for products that don't exist, or lose confirmed transactions entirely after a crash.
Q: What is the difference between Atomicity and Consistency?
Atomicity and Consistency are related but address different concerns. Atomicity is about completeness — a transaction either fully commits all its changes or fully reverses all of them. It is enforced through the transaction mechanism (BEGIN/COMMIT/ROLLBACK) and the write-ahead log. Atomicity says nothing about whether the committed data is valid — only that it is complete.
Consistency is about validity — the data must satisfy all declared rules (constraints, foreign keys, check conditions) before and after every transaction. It is enforced through schema-level constraints. A transaction can be atomic (all statements completed) but still violate consistency if the constraints reject it. In practice, an atomicity failure rarely happens — it is the database's job to ensure all-or-nothing. A consistency violation is what happens when your application logic allows bad data that the constraints catch.
Example: INSERT INTO order_items with an invalid product_id violates Consistency (FK constraint fails). The INSERT is rejected. The transaction is rolled back — which is also Atomicity in action. But Consistency was the rule that identified the violation, and Atomicity was the mechanism that ensured no partial state was written. They work together: Consistency defines what "valid" means, Atomicity ensures only valid-or-nothing states ever persist.
Q: How does Isolation differ from Consistency?
Consistency is a property of the data itself — it concerns whether the data satisfies defined rules at rest. Isolation is a property of concurrent execution — it concerns whether concurrent transactions interfere with each other's execution.
Consistency says: after every committed transaction, all constraints and rules are satisfied. Isolation says: while a transaction is in progress, other transactions cannot see its uncommitted changes (at standard isolation levels), and each transaction sees a consistent snapshot of the database.
You can have Consistency without strong Isolation — each transaction individually satisfies constraints, but two concurrent transactions could still read each other's partial work (low isolation level). And you can have strong Isolation without perfect Consistency — the isolation mechanisms prevent concurrent interference, but if your constraints are wrong or missing, invalid data can still be committed. Both are independently necessary. Consistency prevents invalid data from being written. Isolation prevents concurrent transactions from creating anomalies through their interaction.
Q: What is eventual consistency and when is it acceptable?
Eventual consistency is a weaker guarantee offered by many distributed and NoSQL databases. Instead of guaranteeing that every read sees the latest committed write (strong consistency), eventual consistency guarantees only that if no new writes occur, all replicas will eventually converge to the same state. Between a write and that convergence, different nodes or readers may return different, stale, or conflicting values.
Eventual consistency is acceptable when: (1) the data is not financial or inventory-critical — a user's "likes" count being 1 second stale is acceptable, (2) operations are idempotent or easily reconciled — appending to a log can handle duplicates, (3) availability is more important than consistency — the system should stay up even if some nodes are unreachable, even if that means stale data.
Eventual consistency is NOT acceptable for: wallet balances (a stale balance could allow overdrafts), order inventory (stale stock could allow overselling), payment records (a lost write means lost money), or medical records (stale data could affect patient safety). FreshCart's orders, payments, and stock all require strong consistency. FreshCart's product browsing history, search analytics, or recommendation click-through rates could tolerate eventual consistency. The choice is workload-driven, not ideological.
Q: How does a database achieve Durability — what is WAL?
Durability is achieved through Write-Ahead Logging (WAL). The core principle: before any change is applied to the actual database data files, the intended change is first written to the WAL — a sequential, append-only log on durable storage (disk). The WAL entry records everything needed to redo or undo the change: the transaction ID, the before-value (for rollback), the after-value (for replay), and eventually a COMMIT or ABORT record.
When a client issues COMMIT, the database performs an fsync — it flushes the WAL to durable storage (past the OS buffer cache, all the way to the physical disk). Only after this fsync succeeds does the database send the COMMIT acknowledgment to the client. The client only hears "committed" after the data is guaranteed to survive a crash.
On crash recovery, the database reads the WAL from the last checkpoint: any transaction with a COMMIT record is redone (its changes are applied to the data files if not already there). Any transaction without a COMMIT record is undone (its partial changes are removed). This brings the database to a consistent state matching exactly what was committed.
In SQLite, the same concept exists as the rollback journal (default) or WAL mode (PRAGMA journal_mode = WAL). In WAL mode, writes go to a WAL file first; readers can still read from the main database file concurrently. Periodic checkpoints merge the WAL back into the main database file. The durability guarantee is identical: COMMIT only returns after the WAL is fsynced to disk.
// Part 10
Errors You Will Hit — And Exactly Why They Happen
Inconsistent data: order status = 'Delivered' but no payment_log record exists
Cause: Two separate DML statements were executed in autocommit mode (each committed independently). The first UPDATE committed successfully. The server crashed, the connection dropped, or the second INSERT raised an error — but since it was a separate transaction, the first UPDATE is already permanent. Partial state is now stored in the database.
Fix: Always wrap multi-statement operations that must atomically succeed together in an explicit BEGIN...COMMIT block. The fix is to rewrite the code path so both the UPDATE and INSERT are inside one transaction. Add a monitoring query that checks for this invariant (delivered orders with no payment_log) to catch historical violations. Going forward, the transaction prevents the partial state from occurring.
ERROR: FOREIGN KEY constraint failed
Cause: An INSERT or UPDATE references a value in a parent table that does not exist. This is the Consistency property working correctly — the database is preventing you from creating an orphaned row that would violate referential integrity. Common triggers: inserting an order_item for a product_id that was deleted, inserting an order for a customer_id that was never created, or inserting with an incorrect ID due to an application bug.
Fix: Check that the referenced row exists before inserting. Use a SELECT to verify the parent row exists: SELECT 1 FROM products WHERE product_id = ?. If the parent row should exist but does not, fix the code that should have created it. If deletes cascade, add ON DELETE CASCADE or ON DELETE SET NULL to the FOREIGN KEY definition. Never disable FK enforcement (PRAGMA foreign_keys = OFF in SQLite) to work around this error — the constraint is telling you about a real data integrity problem.
Data visible in one session that was rolled back in another
Cause: The isolation level is set to READ UNCOMMITTED, or there is a bug where a transaction committed when it should have rolled back. At READ UNCOMMITTED (not supported in PostgreSQL — maps to READ COMMITTED), other sessions can read your uncommitted changes. A successful read of data that was then rolled back is a dirty read — the data the reader saw never officially existed.
Fix: Use READ COMMITTED or higher (the default in most databases). Verify your application code does not have a bug where COMMIT is called when it should ROLLBACK. In PostgreSQL, READ UNCOMMITTED is not available — if you observe apparently dirty reads, the transaction that wrote the data committed before your read, not after. Investigate the commit ordering, not the isolation level.
Report total changes between two runs — same query, different result
Cause: The report runs a long query across many tables. Between two executions of a sub-query, another transaction commits changes to the rows being aggregated. At READ COMMITTED, each statement sees a fresh snapshot — the second execution of the same sub-query sees the newly committed data. The report is computing over two different snapshots of the database.
Fix: Run the entire report inside a single transaction at REPEATABLE READ isolation: BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; [all report queries]; COMMIT; All reads within this transaction see the same consistent snapshot, regardless of what other transactions commit during the report's execution. This is the correct pattern for any report that must be internally consistent.
Try It Yourself
Write queries that demonstrate all four ACID properties using the FreshCart schema. For each property, write a query or code block that: (A) shows Atomicity by simulating a payment that must debit and credit atomically; (B) shows Consistency by attempting an INSERT that violates a constraint; (C) shows Isolation by writing two queries that represent what two concurrent sessions would each see; (D) shows Durability by describing the WAL check query. Wrap the Atomicity demo in a BEGIN...ROLLBACK block.
✓Atomicity is enforced by the transaction mechanism and WAL rollback on crash. Every statement in a BEGIN...COMMIT block is part of one indivisible unit.
✓Consistency is enforced by schema constraints: NOT NULL, UNIQUE, CHECK, FOREIGN KEY. A transaction that violates any constraint is rejected entirely.
✓Isolation is enforced by MVCC and locking. Four levels: READ UNCOMMITTED, READ COMMITTED (default), REPEATABLE READ, SERIALIZABLE. Higher isolation = fewer anomalies, lower concurrency.
✓Durability is enforced by WAL (Write-Ahead Logging). COMMIT only returns after changes are fsynced to disk. Crash recovery replays the WAL to restore all committed transactions.
✓BASE (Eventually Consistent) trades ACID guarantees for higher availability and write throughput. Acceptable for non-critical data; never for financial or inventory data.
✓The five ACID-preventable failures: double-spend (Atomicity), overselling (Isolation), orphaned FK rows (Consistency), lost confirmed orders (Durability), inconsistent reports (Isolation).
✓Always wrap multi-statement operations in explicit transactions. Never rely on autocommit for operations that must be atomic.
What comes next
In Module 49, you learn Stored Procedures — reusable named programs stored in the database, with parameters, control flow, and exception handling.