Bug #49763 Skip locked rows
Submitted: 17 Dec 2009 9:43 Modified: 22 Dec 2009 6:44
Reporter: Mukesh Koli Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: row lock
Triage: Triaged: D5 (Feature request)

[17 Dec 2009 9:43] Mukesh Koli
Description:
Is there a provision in mysql to skip locked rows. so that if say user from one session has locked first 5 rows of a data the user from another session can see next 5 rows thus skipping the locked rows by other session.

How to repeat:
Is there a provision in mysql to skip locked rows. so that if say user from one session has locked first 5 rows of a data the user from another session can see next 5 rows thus skipping the locked rows by other session.
[17 Dec 2009 9:51] Valeriy Kravchuk
Please, check if READ UNCOMMITTED transaction isolation level will help. Read will not be blocked, but for changed rows their previous state will be used.

Read http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html for the details.
[17 Dec 2009 10:34] Mukesh Koli
I had already tried using 
set transaction isolation level read uncommitted;
but still the after the row selection done by one session the other request for selection of rows is blocked untill the locked rows are not released.
[17 Dec 2009 10:58] Sveta Smirnova
Thank you for the feedback.

Please provide example of query you use and want to lock rows.
[17 Dec 2009 11:14] Mukesh Koli
set SESSION transaction isolation level read uncommitted;
BEGIN WORK;
select id from responses order by id desc limit 0,5 for update;

gives out the records with id 1,2,3,4,5;

see with above query i have selected 5 records for viewing and which are locked and not COMMITED yet.

Now suppose from some other session 
same above queries are executed they have to wait for the first session to commit rather then skipping the locked row and selecting one's those are not locked for eg. should give out the records with id 6,7,8,9,10. which does not happens.

hope I am clear in explaining the process.
[17 Dec 2009 11:19] Sveta Smirnova
Thank you for the feedback.

Not, such locking is not supported.

But you can add UNIQUE ID and use WHERE clause to get needed behavior. Please read about gap at http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html also.
[17 Dec 2009 11:51] Mukesh Koli
Thank you for all the feed backs.
I got your point.
But is there any query or way in mysql to check which rows are locked or say unlocked?.

Thanks Again.
[21 Dec 2009 14:58] Sinisa Milivojevic
I can see no benefit from a feature like this. It would be quite harmful, actually.

A proper and very useful feature request is to come up with a storage engine that would do fully optimistic locking.
[22 Dec 2009 6:44] Mukesh Koli
Thank you for the reply Sinisa, But image a situation where user would select 10 records to be edited out of say 100. Then the next user who log in to edit the records on same where condition should select only out of the remaining 90 unlocked records rather than again selecting the first 10 or waiting for the first user to unlock those 10 records which are going to be updated by the first user.
[29 Oct 2015 3:29] Kyle Oppenheim
This feature request is captured at https://dev.mysql.com/worklog/task/?id=3597 (since 2006!)
[19 Nov 2015 17:18] Kyle Oppenheim
I made an initial attempt at implementing SELECT FOR UPDATE ... SKIP LOCKED. Here's a work-in-progress diff:

https://github.com/mysql/mysql-server/compare/5.7...koppenheim:5.7-skip_locked

I'm not a MySQL developer and I'm unfamiliar with the InnoDB source code, so I would appreciate feedback if this is roughly on the right track from somebody with more experience.
[20 Nov 2015 1:32] Kyle Oppenheim
Note: I sent the OCA this evening.
[27 Nov 2015 13:49] Martin Hansson
Hi Kyle,
thank you very much for your contribution! We are doing similar work in this area and I will take a closer look to see where your patch fits in.
[12 Dec 2015 22:54] Kyle Oppenheim
Thanks for taking a look, Martin. I have some cycles to work on this over the next few weeks. Let me know if I should steer one direction or another to make it easier to merge.
[30 Dec 2015 2:05] Kyle Oppenheim
I updated my patch with tests. The updated diff is on the same branch at https://github.com/mysql/mysql-server/compare/5.7...koppenheim:5.7-skip_locked
[17 Mar 2017 16:00] Kyle Oppenheim
Facebook has a patch for 5.6:
https://github.com/facebook/mysql-5.6/commit/193896c466d43fd905a62a60f1d73fd9c551a6e4