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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With