Prerequisites

The FOR UPDATE clause operates exclusively within the InnoDB storage engine and requires an active transaction block (initiated with BEGIN or START TRANSACTION). By default, MySQL runs in autocommit mode. To simulate concurrent locking scenarios, autocommit must be disabled in the test sessions:

mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

This configuration is session-specific. Two distinct sessions are required: Session A to acquire the lock, and Session B to test the lock contention.

Database Setup

Consider a table named employees containing a primary key emp_id, a standard column username, and a unique column tax_code.

mysql> SELECT * FROM employees;
+--------+----------+-----------+
| emp_id | username | tax_code  |
+--------+----------+-----------+
|      1 | alice    | TX-1001   |
|      2 | bob      | TX-1002   |
+--------+----------+-----------+
2 rows in set (0.00 sec)

mysql> DESC employees;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| emp_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | NO   |     | NULL    |                |
| tax_code | varchar(15) | YES  | UNI | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

Scenario 1: Filtering by Primary Key

In Session A, begin a transaction and lock the row with emp_id = 1:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM employees WHERE emp_id = 1 FOR UPDATE;
+--------+----------+-----------+
| emp_id | username | tax_code  |
+--------+----------+-----------+
|      1 | alice    | TX-1001   |
+--------+----------+-----------+
1 row in set (0.00 sec)

In Session B, attempting to update the locked row results in a lock wait timeout:

mysql> UPDATE employees SET username = 'alice_new' WHERE emp_id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

However, updating a different row succeeds immediately:

mysql> UPDATE employees SET username = 'bob_new' WHERE emp_id = 2;
Query OK, 1 row affected (0.00 sec)

If Session A queries a non-existent primary key (e.g., emp_id = 3), no lock is acquired. Session B can freely modify any row.

Outcome: A specific primary key yielding data results in a row lock. No data yields no lock.

Scenario 2: Filtering by Primary Key and Non-Indexed Column

Session A locks using both the primary key and username:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM employees WHERE emp_id = 1 AND username = 'alice' FOR UPDATE;

Session B attempts to modify the target row and fails, while modifications to other rows succeed. If the query returns no data, no lock is established.

Outcome: Primary key combined with a non-indexed column yielding data results in a row lock. No data yields no lock.

Scenario 3: Filtering by Non-Indexed Column Only

Session A targets only the username column:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM employees WHERE username = 'alice' FOR UPDATE;
+--------+----------+-----------+
| emp_id | username | tax_code  |
+--------+----------+-----------+
|      1 | alice    | TX-1001   |
+--------+----------+-----------+
1 row in set (0.00 sec)

In Session B, updates to any row in the table are blocked:

mysql> UPDATE employees SET tax_code = 'TX-9999' WHERE emp_id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> UPDATE employees SET tax_code = 'TX-8888' WHERE emp_id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Outcome: Querying exclusively a non-indexed column yielding data results in a table lock. No data yields no lock.

Scenario 4: Filtering by Unique Key

Session A queries using the unique tax_code:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM employees WHERE tax_code = 'TX-1001' FOR UPDATE;

Session B finds that the locked row cannot be updated, but other rows can. If no data matches, no lock is acquired.

Outcome: A unique key yielding data results in a row lock. No data yields no lock.

Index Dependency of InnoDB Locks

InnoDB row-level locking is strictly tied to index usage. When a FOR UPDATE query utilizes a primary key, unique key, or standard index, InnoDB applies a row lock. Without an index, InnoDB cannot locate the specific row efficiently and escalates the lock to the entire table.

Adding an index to the username column changes the locking behavior:

mysql> ALTER TABLE employees ADD INDEX idx_username (username);
Query OK, 0 rows affected (0.04 sec)

Repeating Scenario 3 with the new index, Session A locks the row for username = 'alice'. Session B is now able to update other rows (e.g., emp_id = 2) without waiting, confirming the escalation from a table lock to a row lock.

Index Invalidation and Table Lock Escalation

Even if an index exists, certain query patterns bypass the index, forcing InnoDB to perform a full table scan and subsequently apply a table lock. Common causes of index invalidation include:

• Negative Conditions: Using operators like !=, <>, NOT IN, or NOT EXISTS.
• Leading Wildcards: Queries using LIKE '%pattern' cannot utilize B-tree indexes.
• OR Conditions: Connecting conditions with OR often negates index usage, causing full table scans.
• NULL Values: Searching on columns where the index does not account for NULL values can lead to unpredictable behavior.

Furthermore, if an index has very low cardinality (high duplication rate), the MySQL query optimizer may determine a full table scan is cheaper than using the index. In such cases, the index is ignored, and FOR UPDATE will trigger a table lock.