SELECT ... FOR UPDATE and MAX()

Question

Do SELECT ... FOR UPDATE in this query lock rows in this table?

Table is in InnoDB and query is inside transaction

select max(id) from table1 FOR UPDATE

I have this code and it seems SELECT ... FOR UPDATE doesn't lock rows.

Answer 1

SELECT MAX(id) FROM table1 FOR UPDATE - does not lock any rows because it does not actually scan any rows. The required information is retrieved from the table index.

This is hinted if you prepend a DESCRIBE to the query, displaying:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Select tables optimized away'

SELECT id FROM table1 WHERE id = (SELECT MAX(id) FROM table1) FOR UPDATE - will lock this one row which matches MAX(id) because you explicitly retrieve that row.

SELECT id FROM table1 ORDER BY id DESC LIMIT 1 FOR UPDATE - will lock all rows and retrieve MAX(id) using a combination of ORDER BY and LIMIT 1.

This is will again be explained by a DESCRIBE query.