MySQL Bugs: #17426: Add support for skipping locks

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

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

Changing category to InnoDB.

Thank you for the feature request, however I think should be asked to
newer version than 4.1

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.

Now available in FB MySQL - https://github.com/pengtfb?tab=activity