Understanding MySQL SELECT ... FOR UPDATE Locking: Row vs. Table Locks

Hello everyone, I am Peng Lei.

A plain SELECT statement does not lock rows, but SELECT ... FOR UPDATE not only queries data but also acquires a pessimistic lock.

Whether the lock is a row lock or a table lock depends on whether the query uses an indexed column or a primary key. Without an index or primary key, the statement locks the entire table; otherwise it locks only the matching rows.

Verification:

Table creation SQL:

// id is the primary key
// name is a unique index
CREATE TABLE user (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) DEFAULT NULL,
  age INT(11) DEFAULT NULL,
  code VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_age (age) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8;

Auto‑commit must be disabled for the test: SET @@autocommit=0; (0 = manual commit, 1 = automatic).

Combined Example Verification

Example 1

Query using the primary‑key id in one transaction, then start another transaction that attempts to update the same row. The update is blocked because the first transaction holds a row lock on the row with id=1 .

Figure 1 shows the first transaction (not yet committed). Figure 2 shows the second transaction attempting the update and being blocked. Figure 3 shows the second transaction eventually timing out.

Example 2

Start a transaction that updates a different row (id=2). The update proceeds without blocking because the first transaction locked only the row with id=1.

Example 3 (Index)

The table was created with a unique index on the age column.

Example 4

Use a non‑indexed column code in the SELECT ... FOR UPDATE query.

Another transaction updates a different row. If the update succeeds, a row lock was taken; if it fails, the statement locked the whole table.

Result

If the query condition uses an indexed column or primary key, SELECT ... FOR UPDATE acquires a row lock.

If the condition uses a non‑indexed column, the statement locks the entire table.