Database concurrency control spans a range of methods. At one extreme lies serializable isolation, which completely prevents race conditions but does so at the expense of performance and scalability. Conversely, weaker isolation levels allow race conditions but generally offer better performance and scalability. A middle ground exists known as Serializable Snapshot Isolation (SSI). This approach maintains full serializability while reducing the performance hit compared to basic snapshot isolation. SSI achieves this by utilizing a consistent snapshot for reading operations and incorporating mechanisms to identify writes that could breach serializability, subsequently aborting those transactions.

SSI is grounded in optimistic concurrency control principles, which assert that transactions can execute concurrently without blocking each other, based on the assumption that interference will be minimal. When it comes time to commit, a transaction checks if its result aligns with what would have occurred under serial execution. If any discrepancies arise, the transaction is aborted. In contrast, pessimistic concurrency control prevents concurrent transaction execution when there's a risk of race conditions, exemplified by serial execution where each transaction holds an exclusive lock on the database.

Optimistic concurrency control presents its own advantages and disadvantages. For example, in a high-contention environment, the chance of concurrent transactions interfering with each other rises significantly, leading to a greater rate of aborted transactions that need to be retried, unlike in low-contention scenarios where optimistic control can yield better throughput than pessimistic methods.

Avoiding Write Skew and Phantom Reads

Write skew occurs when a transaction executes a read query that influences its decision-making, but the data it reads might change due to other transactions' writes, leading to the initial transaction making decisions based on outdated information. To tackle this in the context of SSI, the database must identify instances where the outcomes of read queries shift before the transaction concludes, which could nullify subsequent writes. The database cannot definitively ascertain whether there's a causal relationship between the reads and writes, hence it must terminate transactions with altered read results.

The process for determining whether to abort a transaction hinges on two scenarios:

1. Imagine transaction X reads an object under snapshot isolation. The database snapshot doesn't capture any uncommitted writes. If another transaction Y concurrently writes to certain rows but doesn't commit, transaction X's read remains consistent until it completes. If transaction Y commits afterward, it renders transaction X's read obsolete.

In a multiversion concurrency control (MVCC) system, objects maintain multiple versions corresponding to various database snapshots. The database must identify uncommitted writes that occur after another transaction has executed a read query on the same object.

2. In another instance, if transaction Y writes to the same object that transaction X has read, and transaction Y commits before transaction X completes, transaction X's read becomes outdated.

Using our joint bank account example, suppose the account has a balance of $100, and two transactions attempt to transfer $100 each to different accounts, which should be disallowed. The sequence of events could play out as follows:

1. Transaction T1 reads the balance as $100 from a snapshot.
2. T1 transfers funds and attempts to update the balance to zero, leaving this write uncommitted.
3. Transaction T2 reads the balance as $100, unaware of T1's uncommitted write.
4. T1 commits.
5. T2 then tries to commit its transfer, but the transaction manager determines that T2's read is now stale, resulting in its abortion.

The transaction manager tracks ignored writes based on MVCC visibility rules. If any previously ignored writes commit before the transaction that executed the read completes, the read results are deemed stale, leading to an abort during subsequent write attempts.

Performance Considerations

While SSI boasts advantages, it requires substantial bookkeeping to monitor transaction reads and writes. This overhead can be mitigated by reducing the granularity of bookkeeping; however, this may result in more unnecessary transaction abortions to uphold serializable isolation. Overall, SSI's performance hinges on the transaction abort rate. Long-running transactions that involve both reading and writing are more susceptible to conflict and subsequent abortion. SSI tends to perform better for transactions that read and write within shorter time frames.

Unlike two-phase locking, SSI doesn't block transactions due to locks. Similar to snapshot isolation, it allows writers to proceed without blocking readers and vice versa. Furthermore, long-running read-only transactions can operate against a consistent database snapshot.

In summary, SSI offers a means to detect serialization conflicts across multiple systems, enabling database partitioning across various nodes and improving throughput compared to serial execution tied to a single CPU.

Your Comprehensive Interview Kit for Big Tech Jobs

• Grokking the Machine Learning Interview: This course covers essential skills and commonly asked interview problems at major tech firms.
• Grokking the System Design Interview: Prepares you for system design interviews with practice questions.
• Grokking Dynamic Programming Patterns for Coding Interviews: Streamlined preparation for coding interviews.
• Grokking the Advanced System Design Interview: Focuses on system design through architectural reviews of real systems.
• Grokking the Coding Interview: Patterns for Coding Questions: Fast-tracked preparation for coding interviews.
• Grokking the Object Oriented Design Interview: Prepares you for object-oriented design interviews with practice problems.
• Machine Learning System Design:
• System Design Course Bundle:
• Coding Interviews Bundle:
• Tech Design Bundle:
• All Courses Bundle: