13 - Designing Databases for Concurrent Use — Module Topics

Introduction to Multi-User Database Environments

• What is a Multi-User Database Environment? — A multi-user database environment is one in which two or more users can access and interact with the same database at the same time.
• The Core Challenge: Simultaneous Data Modification — When multiple users attempt to read and write the same data at the same time, conflicts can arise that corrupt or misrepresent the stored information.
• Common Concurrency Problems — Database researchers have identified several recurring categories of problems that emerge specifically from concurrent access.
• Why Single-User Design Assumptions Break Down — Database schemas and queries designed with a single user in mind often implicitly assume that data does not change between successive operations, an assumption that is invalid under concurrent use.
• The Foundational Need for Concurrency Control — Concurrency control is the set of mechanisms a DBMS uses to manage simultaneous operations so that data remains accurate and consistent for all users.

Transactions and ACID Properties

• What Is a Database Transaction? — A database transaction is a logical unit of work that groups one or more database operations into a single, indivisible sequence.
• Atomicity: All or Nothing — Atomicity guarantees that every operation within a transaction is completed fully, or none of them are applied to the database.
• Consistency: Preserving Valid Data States — Consistency ensures that a transaction brings the database from one valid state to another, always respecting all defined rules, constraints, and integrity conditions.
• Isolation: Shielding Concurrent Transactions — Isolation ensures that the operations of one transaction are not visible to other concurrent transactions until the transaction has been committed.
• Durability: Surviving System Failures — Durability guarantees that once a transaction has been committed, its changes are permanently recorded and will survive system crashes, power failures, or other disruptions.
• How ACID Properties Work Together — The four ACID properties are interdependent and collectively guarantee that database transactions are processed reliably and predictably, even in multi-user environments.

Concurrency Problems and Data Conflicts

• Dirty Reads — A dirty read occurs when one transaction reads data that has been modified by another transaction that has not yet been committed.
• Non-Repeatable Reads — A non-repeatable read happens when a transaction reads the same row twice but gets different values because another transaction modified and committed that row in between.
• Phantom Reads — Phantom reads occur when a transaction re-executes a query and finds additional rows that were inserted by another committed transaction since the first execution.
• Lost Updates — A lost update occurs when two transactions read the same value and then both write back updated versions, causing the first transaction's changes to be silently overwritten by the second.
• How Data Conflicts Compromise Integrity — Each concurrency problem represents a specific way that interleaved transaction execution can violate the accuracy and reliability of a database.

Locking Mechanisms

• Shared and Exclusive Locks — Shared and exclusive locks are the two fundamental lock types that control how transactions access data simultaneously.
• Intent Locks — Intent locks signal a transaction's intention to acquire finer-grained locks lower in the resource hierarchy, enabling efficient compatibility checks at higher levels.
• Lock Granularity — Lock granularity refers to the size or scope of the resource being locked, ranging from individual rows up to entire tables or databases.
• Lock Escalation — Lock escalation is the process by which a database engine automatically converts many fine-grained locks into a single coarser-grained lock to conserve system memory and management overhead.
• Trade-offs Between Concurrency and Data Protection — Every locking decision involves a fundamental trade-off: stronger data protection typically comes at the cost of reduced concurrency, and vice versa.

Deadlocks and Conflict Resolution

• What Is a Deadlock? — A deadlock occurs when two or more transactions are each waiting for the other to release a lock, creating a cycle of dependency that prevents any of them from proceeding.
• How Deadlocks Form: The Mutual Blocking Cycle — Deadlocks arise from a specific set of conditions involving resource holding, waiting, and circular dependency among transactions.
• Deadlock Detection — Detection-based strategies allow deadlocks to occur but identify them after the fact using tools such as wait-for graphs, then resolve them by breaking the cycle.
• Deadlock Prevention Strategies — Prevention strategies restructure how transactions request locks or order resources so that the conditions necessary for a deadlock can never be met.
• Timeout Policies for Deadlock Resolution — Timeout-based resolution automatically aborts a transaction that has been waiting for a lock beyond a configured threshold, breaking potential deadlocks without requiring explicit detection.
• Deadlock Victim Selection — When a deadlock is detected, the database must choose one transaction to abort — the 'victim' — in order to break the cycle and allow the remaining transactions to proceed.
• Best Practices for Minimizing Deadlocks — While deadlocks cannot always be fully eliminated, thoughtful database and application design can significantly reduce their frequency and impact.

Transaction Isolation Levels

• Overview of Transaction Isolation — Transaction isolation defines the degree to which the operations of one transaction are shielded from those of other concurrent transactions. The SQL standard formalizes four distinct isolation levels, each offering a different trade-off between data consistency and system performance.
• Read Uncommitted — Read Uncommitted is the lowest isolation level, allowing a transaction to read data that has been modified but not yet committed by another transaction. This can lead to dirty reads, where a transaction sees intermediate or rolled-back data.
• Read Committed — Read Committed ensures that a transaction can only read data that has been permanently committed, eliminating dirty reads. It is the default isolation level in many popular database systems, including PostgreSQL and Oracle.
• Repeatable Read — Repeatable Read guarantees that if a transaction reads a row, any subsequent reads of that same row within the same transaction will return the same data. This prevents both dirty reads and non-repeatable reads.
• Serializable — Serializable is the strictest isolation level, ensuring that the outcome of concurrent transactions is identical to some sequential execution of those same transactions. It prevents dirty reads, non-repeatable reads, and phantom reads.
• Concurrency Anomalies Compared Across Levels — Each isolation level is characterized by which concurrency anomalies it permits or prevents. Understanding these anomalies helps designers select the appropriate isolation level for a given use case.
• Practical Considerations for Choosing an Isolation Level — Selecting the right isolation level requires balancing the application's tolerance for data anomalies against its performance and concurrency requirements. Most applications do not need the strongest level for every transaction.

Concurrency Control Strategies and Best Practices

• Optimistic Concurrency Control (OCC) — Optimistic concurrency control assumes that conflicts between concurrent transactions are rare, allowing transactions to proceed without locking resources upfront.
• Pessimistic Concurrency Control (PCC) — Pessimistic concurrency control assumes conflicts are likely and prevents them by acquiring locks on data before any read or write operation occurs.
• Multiversion Concurrency Control (MVCC) — MVCC maintains multiple versions of data simultaneously so that readers never block writers and writers never block readers, dramatically increasing concurrency.
• Choosing the Right Strategy for Your Workload — Selecting between optimistic, pessimistic, and multiversion approaches depends on the ratio of reads to writes, acceptable latency, and the cost of conflict resolution.
• Practical Design Patterns for Consistency Without Performance Loss — Good schema and application design can reduce contention and make any concurrency control strategy more effective.
• Monitoring and Tuning Concurrency in Production — Even a well-designed concurrency strategy requires ongoing monitoring to detect lock contention, deadlock frequency, and version bloat before they degrade performance.