The Index-Lock Binding Mechanism in InnoDB

InnoDB attaches locks to index entries rather than physical rows directly. While this principle is well-known, the practical implications become less obvious when different transactions operate on the same row through different indexes. This analysis examines the underlying mechanics of lock conflicts across index types.

How Indexes Map to Physical Rows

Every InnoDB table possesses a clustered index built on the primary key. Secondary indexes store primary key values in their leaf nodes instead of physical row pointers. When accessing data through a secondary index, InnoDB follows this two-step process:

1. Locate the primary key value via the secondary index
2. Navigate to the clustered index to reach the actual row

For write operations, locks are applied to both the secondary index entry and the corresponding primary key entry. This dual-lock mechanism is why operations through different indexes can conflict—they converge at the same primary key entry.

Lock Categories in InnoDB

Lock Type	Target	Purpose
Record Lock	Individual index entries	Prevents modification of specific rows
Gap Lock	Spaces between index entries	Blocks phantom reads (RR/SR isolation only)
Next-Key Lock	Record Lock combined with Gap Lock	Default lock in RR isolation, covers both row and adjacent gaps
Table Lock	Entire table	Fallback when no usable index exists

Critical rule: Write operations (UPDATE/DELETE/INSERT) always lock the index entry used for access plus the corresopnding primary key entry. Read operations use MVCC by default but follow identical locking rules when explicitly locked (FOR UPDATE).

Lock Conflict Scenarios

Consider this test table structure:

CREATE TABLE `account` (
  `account_id` INT NOT NULL PRIMARY KEY COMMENT 'primary key (clustered)',
  `email` VARCHAR(50) NOT NULL UNIQUE COMMENT 'unique secondary index',
  `balance` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'account balance'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `account` VALUES (100, 'alice@example.com', 1000.00);

A single row exists: account_id=100, email=alice@example.com, balance=1000.00

Scenario 1: Concurrent Write Operations via Different Indexes

-- Transaction A (uses primary key)
BEGIN;
UPDATE `account` SET `balance` = 1500.00 WHERE `account_id` = 100;

-- Transaction B (uses secondary index)
BEGIN;
UPDATE `account` SET `balance` = 2000.00 WHERE `email` = 'alice@example.com';
-- This blocks!

Mechanism breakdown:

1. Transaction A locks the clustered index entry for account_id=100
2. Transaction B acquires a lock on the secondary index entry for email=alice@example.com
3. When Transaction B attempts to lock the corrresponding primary key entry (account_id=100), it discovers Transaction A already holds this lock
4. Transaction B blocks until Transaction A commits or rolls back

Conclusion: Write operations on the same row always conflict regardless of which index is used, because all paths eventually compete for the primary key entry lock.

Scenario 2: Write Operation and Unlocked Read

-- Transaction A (write via primary key, uncommitted)
BEGIN;
UPDATE `account` SET `balance` = 1500.00 WHERE `account_id` = 100;

-- Transaction B (read via secondary index, unlocked)
BEGIN;
SELECT `balance` FROM `account` WHERE `email` = 'alice@example.com';
-- No blocking occurs; returns the pre-update value 1000.00

Mechanism breakdown:

1. Transaction B traverses the secondary index to find primary key 100
2. No lock is acquired during this process
3. Transaction B reads from undo log, retrieving the historical version before Transaction A's modifications
4. Uncommitted changes remain invisible to unlocked reads

Conclusion: MVCC enables unlocked reads to proceed without contention from concurrent write operations.

Scenario 3: Write Operation and Locked Read (FOR UPDATE)

-- Transaction A (write via primary key, uncommitted)
BEGIN;
UPDATE `account` SET `balance` = 1500.00 WHERE `account_id` = 100;

-- Transaction B (locked read via secondary index)
BEGIN;
SELECT `balance` FROM `account` WHERE `email` = 'alice@example.com' FOR UPDATE;
-- This blocks!

Mechanism breakdown:

1. Transaction B first locks the secondary index entry email=alice@example.com
2. When attempting to lock the primary key entry account_id=100, it encounters Transaction A's lock
3. Transaction B blocks pending Transaction A's lock release

Conclusion: Explicit locked reads behave identically to write operations because they actively request row locks.

Scenario 4: Non-Unique Secondary Indexes and Gap Locks

Non-unique secondary indexes introduce gap locks into the locking equation (RR/SR isolation levels only).

ALTER TABLE `account` ADD INDEX idx_category (`category`);
INSERT INTO `account` VALUES (200, 'bob@example.com', 500.00, 'savings');
INSERT INTO `account` VALUES (300, 'carol@example.com', 2000.00, 'checking');

-- Transaction A (updates via non-unique index)
BEGIN;
UPDATE `account` SET `balance` = 600.00 WHERE `category` = 'savings';
-- In RR mode, acquires Next-Key Lock covering ('checking', 'savings']

-- Transaction B (updates via primary key)
BEGIN;
UPDATE `account` SET `balance` = 700.00 WHERE `account_id` = 200;
-- This blocks!

Mechanism breakdown:

1. Transaction A's update on non-unique index category='savings' triggers Next-Key Lock acquisition
2. This lock covers the range from the previous index entry to the current one (inclusive of the current row)
3. The lock extends to the primary key entry account_id=200
4. Transaction B attempting to lock account_id=200 encounters the blocking Next-Key Lock

Technical note: Unique index equality searches "degrade" to simple record locks. Non-unique index searches retain Next-Key Lock behavior, which is essential for preventing phantom reads.

Scenario 5: Operations Without Index Coverage (Table Lock Degradation)

When queries cannot utilize any index, InnoDB degrades to table-level locking:

-- Transaction A (full table scan due to unindexed column)
BEGIN;
UPDATE `account` SET `balance` = 1500.00 WHERE `balance` < 1000.00;
-- Acquires table lock

-- Transaction B (updates different row via primary key)
BEGIN;
UPDATE `account` SET `balance` = 2000.00 WHERE `account_id` = 200;
-- This blocks!

Mechanism breakdown:

1. Without an accessible index, InnoDB cennot identify specific rows to lock
2. The engine locks all index entries across the entire table
3. Any subsequent operation on the table—including updates to unrelated rows—becomes blocked

This represents the most severe lock contention scenario and must be avoided in production systems.

Practical Implications and Guidelines

Lock Conflict Summary

Operation Type	Conflict Across Different Indexes	Reason
WRITE (UPDATE/DELETE)	Yes, blocks	All writes ultimately lock the primary key entry
Unlocked READ (SELECT)	No conflict	MVCC reads historical versions without acquiring locks
Locked READ (FOR UPDATE)	Yes, blocks	Explicitly requests primary key entry lock
Unindexed WRITE	Global contention	Table lock affects all rows

Development Best Practices

Ensure query predicates utilize available indexes

Full table scans cause table-level locking, creating bottlenecks even for unrelated operations. Verify execution plans exclude type=ALL (full table scan).

Prefer primary key access for critical operations Clustered index access avoids the secondary mapping step required by secondary indexes. While lock conflict behavior remains consistent, primary key access reduces lock acquisition overhead.

Understand gap lock implications in RR isolation Non-unique index writes acquire Next-Key Locks that may affect operations on seemingly unrelated rows. Evaluate whether row locking alone suffices for your use case.

Keep transactions short-lived under high concurrency Extended transactions hold locks longer, increasing the probability of lock wait timeouts. Break large operations into smaller atomic units.

Reserve explicit locking for genuinely necessary scenarios FOR UPDATE and LOCK IN SHARE MODE reduce concurrency by actively acquiring locks. Use these constructs only when read-modify-write atomicity is required (e.g., inventory deduction).

Addressing Common Interview Questions

Q: InnoDB locks index entries rather than rows directly. Why would operations via different indexes conflict on the same row?

A: InnoDB's row-level locking attaches to index entries, but all secondary index operations must resolve to the primary key for actual row access. Write operations and locked reads acquire both the index entry used for access and the corresponding primary key entry. Since the primary key entry for any given row is unique and mutually exclusive, operations converging on that entry—whether through the primary key directly or through a secondary index—will always conflict.

Q: Why do unlocked reads never conflict with concurrent writes?

A: Standard SELECT statements leverage InnoDB's MVCC architecture. These reads access historical versions stored in undo log rather than acquiring any locks. This design achieves "write-blocking isolation" where writes don't block reads and vice versa.

Q: What strategies minimize lock contention from cross-index operations?

A: Four primary approaches: (1) Verify all write operations use indexed columns to prevent table lock degradation; (2) Execute transactions quickly to minimize lock hold duration; (3) In high-concurrency situations, prefer primary key access over secondary indexes; (4) For non-critical paths, consider READ COMMITTED isolation to eliminate gap locks.