Bug #25914 MySQL optimizer should be aware that SELECT ... FOR UPDATE is not key read
Submitted: 29 Jan 2007 13:03 Modified: 13 Nov 2007 17:19
Reporter: Heikki Tuuri Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.1 OS:Any (All)
Assigned to: CPU Architecture:Any
Triage: D5 (Feature request)

[29 Jan 2007 13:03] Heikki Tuuri
Description:
This is from:

http://forum.mysqlperformanceblog.com/s?t=rview&goto=644#msg_644

It makes sense always to lock the row itself in the FOR UPDATE query. If you are going to update the row, it is best to have the X-lock on the row, so that there is less probability of a deadlock.

For LOCK IN SHARE mode there is no reason to lock the row itself always. We just want to 'freeze' the data so that our SELECT query returns the very latest data.

The MySQL optimizer should be made aware that FOR UPDATE is never a 'key read'.

How to repeat:
See above.

Suggested fix:
The optimizer should be aware that a SELECT ... FOR UPDATE query does not use a 'key read'.
[29 Jan 2007 13:08] Heikki Tuuri
Hmm, actually, since a FOR UPDATE query should lock as few rows as possible, if we use in 5.1 the default REPEATABLE READ isolation level, it makes sense always to use a secondary index if possible.

In 5.1, when using the READ COMMITTED isolation level, MySQL/InnoDB does release the X-lock on rows that do not match. Since in the future, most users will probably change to READ COMMITTED and row-based replication.

Thus, the optimization should on the REPEATABLE READ isolation level favor always a secondary index scan, even if that is slow. But on the READ COMMITTED level it should choose a primary index scan if that is faster than a secondary index scan.
[13 Nov 2007 17:19] Valeriy Kravchuk
Thank you for a reasonable feature request.