Database Locking and Deadlocks

Why Do We Need Locks?

As we've discussed, when multiple transactions access the same data concurrently, several issues can arise, such as Dirty Reads, Lost Updates, and Non-Repeatable Reads. Locks are the database's fundamental tool for implementing transaction isolation and preventing these anomalies.

A lock is essentially a marker that a transaction places on a piece of data (a resource). This marker signals to other transactions that the resource is in use and enforces rules about how they can interact with it, thereby ensuring data integrity.

To start, let's get a clear overview of these concepts.

Lock Granularity: The Concurrency vs. Overhead Trade-off

A database doesn't just have one type of lock; it can apply locks at different levels of "granularity." This choice represents a critical trade-off between concurrency and overhead.

To summarize the key points on granularity:

• Table-Level Lock:

  • What it is: Locks an entire table.
  • Concurrency: Very low. If one transaction locks a table, no other transaction can operate on it.
  • Overhead: Very low. The database only needs to manage one lock for the whole table.
  • Use Case: Ideal for operations that affect the entire table structure, like ALTER TABLE.

• Row-Level Lock:

  • What it is: Locks a single row.
  • Concurrency: Very high. Different transactions can modify different rows in the same table simultaneously.
  • Overhead: High. If a transaction updates thousands of rows, the database must acquire and manage thousands of individual locks, which consumes memory and CPU.
  • Use Case: The default for most OLTP (Online Transaction Processing) workloads, like updating a specific user's profile or processing a single order.

• Page-Level Lock:

  • What it is: Locks a data page, which is a fixed-size block of storage on disk that contains multiple rows.
  • Concurrency & Overhead: A compromise between table and row-level locks.
  • Note: This is often an internal implementation detail of the database engine itself, rather than something you directly control.

The fundamental tension is that fine-grained locks (like row-level) allow for more parallelism but come with higher management costs. Coarse-grained locks (like table-level) are cheap to manage but severely limit concurrency.

Types of Locks and How They Interact

Now that we understand where locks can be placed, let's look at what they do. The two most fundamental types are:

1. Shared (S) Lock: Acquired for read operations. Multiple transactions can hold a shared lock on the same resource simultaneously. Think of it as a "readers' lock."
2. Exclusive (X) Lock: Acquired for write operations (UPDATE, DELETE, INSERT). Only one transaction can hold an exclusive lock on a resource at any given time. An exclusive lock is incompatible with all other locks, including shared locks. Think of it as a "writer's lock."

This relationship is often shown in a lock compatibility matrix:

Modern databases like PostgreSQL have a much more nuanced and optimized set of locks. Since your goal is to go to the deepest level possible, we'll dive into the specific lock modes used by PostgreSQL. This will give you a concrete understanding of how a real-world database manages this complexity.

The key insight from the video is that even a simple SELECT or UPDATE involves acquiring locks at multiple levels (e.g., a table-level "intention" lock and a row-level lock). The database uses this complex system to maximize concurrency while guaranteeing correctness.

The Inevitable Problem: Deadlocks

While locks are essential for preventing data corruption, they introduce a new problem: deadlocks. A deadlock occurs when two or more transactions are stuck in a circular wait, each waiting for a resource that the other holds.

How Deadlocks Occur

Let's formalize the example from the diagram:

1. Transaction A starts and acquires an exclusive lock on row 1.
2. Transaction B starts and acquires an exclusive lock on row 2.
3. Transaction A now attempts to acquire an exclusive lock on row 2, but it's held by Transaction B. So, Transaction A waits.
4. Transaction B now attempts to acquire an exclusive lock on row 1, but it's held by Transaction A. So, Transaction B waits.

Now, A is waiting for B, and B is waiting for A. This is a circular dependency, and neither transaction can ever complete on its own.

Deadlock Detection and Resolution

Databases are designed to handle this. They periodically run a deadlock detection algorithm, which typically involves building a wait-for graph. In this graph, nodes are transactions, and a directed edge from A to B means A is waiting for a lock held by B. A cycle in this graph indicates a deadlock.

Once a deadlock is detected, the database must resolve it by choosing one transaction as the victim. It aborts the victim transaction, which rolls back all its changes and releases all its locks. This breaks the cycle, allowing the other transaction(s) to proceed. The application connected to the victim transaction receives an error and typically must retry the entire transaction.

Deadlock Prevention: The Developer's Responsibility

While the database can resolve deadlocks, they are disruptive and hurt performance. The best approach is to write application code that avoids them in the first place.

This video provides a fantastic, practical demonstration of creating a deadlock and then applying two key prevention strategies.

The two most effective strategies for preventing deadlocks are:

1. Consistent Lock Ordering: This is the golden rule. Ensure that all parts of your application that modify multiple resources do so in the same, predefined order. For example, always update the users table before the orders table. This prevents circular waits from ever forming.
2. Keep Transactions Short: The longer a transaction holds locks, the higher the probability of conflict. Do expensive computations or external API calls outside of the database transaction. The transaction itself should be as quick as possible: begin, read/write, commit.

Conclusion

In this lesson, we've pulled back the curtain on one of the most critical aspects of database concurrency control.

Key Takeaways:

• Locks are for Isolation: They are the primary mechanism databases use to prevent concurrent transactions from interfering with each other and corrupting data.
• Granularity is a Trade-off: Locks can be applied at the table, page, or row level. The choice balances concurrency (favoring fine-grained locks like row-level) against management overhead (favoring coarse-grained locks like table-level).
• Lock Types Define Behavior: The most basic lock modes are Shared (read) and Exclusive (write), but real-world databases like PostgreSQL use a more sophisticated system of lock modes to maximize performance.
• Deadlocks are Circular Waits: They occur when two or more transactions are stuck, each waiting for a lock held by the other.
• Databases Detect and Resolve Deadlocks: Using a wait-for graph, a database can identify deadlocks and resolve them by aborting a "victim" transaction.
• Prevention is Key: As a developer, you can prevent most deadlocks by enforcing a consistent lock order across your application and keeping transactions short and focused.

Preview of the Next Lesson

We've seen how transactions use locks for isolation. But what about the 'D' in ACID—Durability? How does a database guarantee that once a transaction is committed, the changes are permanent, even if the server crashes immediately after? In our next lesson, we will explore the mechanism that makes this possible: Write-Ahead Logging (WAL).