Transactions, Concurrency & The NoSQL Tradeoff
Navigating transaction isolation levels, database deadlocks, the CAP theorem, and the realities of eventual consistency.
The Concurrency Problem: Isolation Levels
The ‘I’ in ACID stands for Isolation—the guarantee that concurrent transactions will not interfere with each other. However, mathematically perfect isolation requires the database to lock rows, effectively forcing concurrent requests into a single-file, sequential line. This destroys performance.
To balance data integrity with system throughput, SQL databases offer configurable Isolation Levels:
- Read Uncommitted: A transaction can read uncommitted changes from other active transactions (Dirty Reads). Highly dangerous; rarely used.
- Read Committed: The default in Postgres and SQL Server. A query only sees data committed before the query began. However, if a transaction runs two identical
SELECTstatements, another transaction might commit an update in between them, resulting in a Non-Repeatable Read. - Repeatable Read: Guarantees that if a transaction reads a row twice, it sees the exact same data. However, new rows matching a query might appear if added by a concurrent transaction (Phantom Reads).
- Serializable: The strictest level. The database guarantees the result is exactly the same as if the transactions were executed one after the other in serial order.
Serializable mode by default. It does not. If you are executing complex financial or inventory mathematics across multiple queries within a transaction, you must explicitly elevate the isolation level or use explicit row locks (SELECT ... FOR UPDATE), or you will suffer from race conditions.Database Deadlocks
Just as threads can deadlock in an operating system, transactions can deadlock in a database when they attempt to acquire row locks in conflicting orders.
To prevent database deadlocks, applications must enforce a strict, global ordering pattern for how they modify tables (e.g., always update users before orders).
The CAP Theorem & Distributed State
Relational databases scale vertically—you buy a bigger server with more RAM and CPU. Eventually, physical hardware reaches its limit, and data must be distributed across multiple servers (scaling horizontally).
When data is partitioned across a network, system design is bound by the CAP Theorem. It states that a distributed data store can only simultaneously provide two of the following three guarantees:
- Consistency (C): Every read receives the most recent write or an error.
- Availability (A): Every request receives a non-error response (but without the guarantee that it contains the most recent write).
- Partition Tolerance (P): The system continues to operate despite an arbitrary number of messages being dropped by the network.
Because network partitions (P) are a physical reality of the internet, engineers cannot choose “CA”. You must choose between CP (rejecting reads/writes to ensure accuracy) or AP (returning available, but potentially outdated, data).
The NoSQL Tradeoff: BASE over ACID
NoSQL databases (like MongoDB, Cassandra, DynamoDB) were engineered to solve the horizontal scaling problem by intentionally abandoning strict ACID guarantees and complex relational JOINs.
Instead, they embrace BASE semantics:
- Basically Available: The system guarantees availability (AP).
- Soft State: The state of the system may change over time, even without input.
- Eventual Consistency: Given enough time, all nodes will eventually contain the same data.
NoSQL is not an upgrade from SQL; it is a specialized architectural tradeoff. You use NoSQL for massive-throughput, unstructured, or highly available data (e.g., caching, shopping carts, social media feeds). You use SQL for strict ledger mathematics and referential integrity (e.g., billing, order management).