Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SELECT ... FOR UPDATE and MAX()

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.

like image 299
user006779 Avatar asked Jul 06 '11 08:07

user006779


1 Answers

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.

like image 140
dezlov Avatar answered Nov 15 '22 07:11

dezlov