MySQL Bugs: #49763: Skip locked rows

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

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.

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.

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.

Thank you for the feedback.

Please provide example of query you use and want to lock rows.

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.

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.

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.

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.

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.

This feature request is captured at https://dev.mysql.com/worklog/task/?id=3597 (since 2006!)

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.

Note: I sent the OCA this evening.

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.

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.

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

Facebook has a patch for 5.6:
https://github.com/facebook/mysql-5.6/commit/193896c466d43fd905a62a60f1d73fd9c551a6e4