MySQL Bugs: #25914: MySQL optimizer should be aware that SELECT ... FOR UPDATE is not key read

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

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'.

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.

Thank you for a reasonable feature request.