The Interaction of Locks and Indexes

In this lesson, you will learn how the presence (or absence) of an index dictates whether your database locks a single specific row or an entire chunk of your table.

The Index-Lock Relationship

When you execute a query, the database engine must find the rows that meet your criteria. To do this, it uses either an index scan or a full table scan. The "granularity" of the locks taken during this process is heavily influenced by how effectively the database can pinpoint the target data.

If you have a well-indexed column, the engine can quickly traverse the B-Tree to find exactly one row. It then places a lock on that specific entry (or the corresponding heap page). However, if no index exists, the database must perform a full table scan. In many storage engines, a full table scan forces the database to acquire locks on every row—or even the entire table—to ensure consistency while it searches, creating massive bottlenecks.

A Concrete Example

Imagine a table of orders with millions of rows. You want to update one order status:

UPDATE orders 
SET status = 'shipped' 
WHERE order_id = 502;

1. With an Index on order_id: The database traverses the index, finds the single pointer for order_id = 502, and places an exclusive lock only on that row. Other users can continue modifying other rows simultaneously.
2. Without an Index: The database must scan every row to check if order_id = 502. To maintain isolation, it may escalate to a table-level lock or lock every row it inspects until the operation finishes. This prevents any other transactions from modifying any orders, effectively freezing the table for the duration of the scan.

Why It Matters

Index selection is not just about query speed; it is about concurrency. If an index is missing, a query that should be localized becomes a global operation, causing "lock contention" where multiple users find themselves waiting on the same resource. By ensuring your WHERE clauses are backed by indexes, you reduce the scope of your locks, allowing the database to perform granular updates without disrupting other operations.

Key Takeaway: Well-chosen indexes allow the database to lock only the specific rows involved in your transaction, preventing unnecessary contention and performance degradation caused by table-wide scans.