Bug #17426 Add support for skipping locks
Submitted: 15 Feb 2006 13:52 Modified: 13 May 2010 16:04
Reporter: Philip Ross Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:4.1 OS:Linux (Linux)
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[15 Feb 2006 13:52] Philip Ross
Description:
It would be nice if InnoDB could support an option to run a SELECT query that would skip any locked rows.

For example,

CREATE TABLE `queue` (
  `id` bigint(20) NOT NULL auto_increment,
  `item` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

INSERT INTO `queue` (`item`) VALUES ('one');
INSERT INTO `queue` (`item`) VALUES ('two');
INSERT INTO `queue` (`item`) VALUES ('three');

In transaction 1, run

SELECT * FROM `queue` ORDER BY `id` LIMIT 1 FOR UPDATE SKIP LOCKED;

which would return the row with `item`='one'.

Whilst transaction 1 is still active, transaction 2 could run the same query and have the row with `item`='two' returned.

Without SKIP LOCKED, transaction 2 will block until transaction 1 has finished.

This functionality is supported in other databases. MS SQL Server has a READPAST hint. Oracle has an undocumented SKIP LOCKED option that is used in Oracle Advanced Queuing.

How to repeat:
N/A

Suggested fix:
Heikki Tuuri made a suggestion for how this could be implemented on the MySQL Internals mailing list 4 years ago:

http://lists.mysql.com/internals/3090
[15 Feb 2006 13:55] Philip Ross
Changing category to InnoDB.
[15 Feb 2006 14:16] Miguel Solorzano
Thank you for the feature request, however I think should be asked to
newer version than 4.1
[15 Feb 2006 14:20] Philip Ross
I'd be happy for this to go into a newer version.

I filled in the version in the bug as 4.1 because I was testing against that version of MySQL.
[25 Jun 2016 18:22] Mark Callaghan
Now available in FB MySQL - https://github.com/pengtfb?tab=activity