Any chance this is in the works?

https://mariadb.com/kb/en/library/wait-and-nowait/ ?

NOWAIT is not the same thing; it returns an error when it runs into a locked row.

SKIP LOCKED moves on past the locked row and returns other rows which are not locked. This is very useful when you have multiple workers processing records.

Right now we have to emulate this using HAVING GET_LOCK(..., 0) calls which is messy, so I'd very much like to see SKIP LOCKED myself too.

I agree - when having multiple workers processing records the "SKIP LOCKED" is just perfect.
Would love to see this in 10.3 soon.

I am just wondering if there is any update on this. Perhaps was it prioritized?
I've seen this in MySQL 8.0 and wanted to upgrade from 5.7, however there is no Galera support for 8.0.
That's how I came across the MariaDB project, since it supports Galera even for 10.3. If it has SKIP LOCKED implemented, that would be total winner

Will this feature be implemented anytime soon? It is a critical feature for us.

This is a must-to-have feature for us, when it will be available?

We also need this future

Are there any updates regarding this - its a topic from 2018 .. so im curious - will it come or not. I just migrated from mariadb to mysql because of this one feature.
Will Bryant wrote about HAVING GET_LOCK, i could not find any example regarding this solution.

Perhaps Will Bryant will comment further as his original comment piqued my interest and led me to find ways of improving MariaDB performance for certain SKIP LOCKED use cases that does not involving switching to MySQL. I was unable to find a solution to my problem based on user locks in the HAVING clause. IIRC such checks are executed too late so workers still thunder on the same lock resulting in no overall improvement in performance...

I was however able to find an alternative solution for my use case for SKIP LOCKED using user locks in a WHERE clause (handling an interesting caveat). I outline it here both as an example of a specific case where SKIP LOCKED is critical for performance (versus alternatives such as NOWAIT) and because it may be a suitable workaround for some problems experienced by others' whilst they wait on this feature (no pun intended).

Background:

In FreeRADIUS we allocate IP addresses to devices from a common pool. For various reasons we normally want to give out the least recently freed IP address. The table of IP addresses, sorted by expiry_time, and filtered with ensure that the expire time is in the past (i.e. the lease has expired and is therefore free), can be thought of as a queue from which multiple workers SELECT. The strict requirement is to ensure that no two workers can every select the same IP address so as to avoid issuing duplicate IP addresses.

A scenario is that a regional power failure may cause tens of thousands of devices to simultaneously request an address. The FreeRADIUS process manages a pool of database connections that may be as wide as 200 connections (or more) serving thousands of frontend threads.

Each connection runs a query that returns the least recently expired, available IP address. Using the standard SELECT ... FOR UPDATE query (without SKIP LOCKED) the simultaneous queries would all wait on the read lock held on the head of the queue (i.e. the first row of the sorted set) by the winning connection. This IP is allocated (expiry_time bumped into the future so that it no longer satisfied the original query) and the lock released. Then all of the queries being evaluated by the remaining connections consider the next row and all but one will have to wait, ad nauseam... If fact, there does not appear to be fairness in determining which connection gets each subsequent lock, so we see that many connections are starved and timeout.

SKIP LOCKED (or emulation thereof) is the classic solution that allows the connections to proceed with selecting unique IP address without on each other.

Here's how we emulate SKIP LOCKED using user locks in this scenario:

https://github.com/FreeRADIUS/freeradius-server/blob/c66e9dbd504323bb3266d583600926f398ba3a4a/raddb/mods-config/sql/ippool/mysql/procedure-no-skip-locked.sql#L99-L144

In tests, without any further optimisation, we experience >10x the IP allocation rate and no thread starvation.

The comment regarding the need for the REPEAT ... UNTIL can be further clarified as follows: The user lock effectively defines a critical section that begins AFTER the SELECT expression is evaluated rather than guarding the SELECT itself. Therefore multiple threads can still race between a record UPDATE (inside the critical section) and the evaluation of the SELECT condition (outside the critical section) such that the condition is violated at time that the lock is acquired. Therefore we must detect and handle this case, i.e. re-evaluate the condition within the critical section to ensure that it is still true (which we do here in the UPDATE rather than as a separate SELECT, for efficiency reasons). In practise, this violation occurs rarely (measured at less than once every 100,000 IP allocations), and we have a guarantee in such cases that the other thread has made progress, so we do not care to limit the number of iterations around the loop - but you could in the general case if you would rather abort if local progress wasn't being made for some reason.

Hopefully the above will be helpful to someone in advance of a working SKIP LOCKED feature.

I would be interested if anyone has found a simpler alternative.

Hello Terry,
first of all thank you for your fast answer.
i just read your solution with the example of an "emulation of SKIP LOCKED". now i understand how it is possible to work around with GET_LOCK but to be honest, yes its a solution, but what kind of solution.. its like fixing a hole in a ship body with a tape -> 30 lines and what else crazy compared to "FOR UPDATE SKIP LOCKED" (10 additional chars)..
i like mariadb especially when i use databases with lot of readings using Aria Engine.. but for this case i suggest everyone to change to mysql - its a clear solution.
hopefully mariadb will move foward supprt SKIP LOCKED .. its a kind a shame its not implemented yet && and mysql implement it first..

I'm also using an awkward workaround for the lack of "SKIP LOCKED" - I wish I had time to contribute toward that feature.

I've just uploaded a port of the facebook skip locked patch, avoiding the NOWAIT part as already implemented. If someone could take a look or review i will appreciate.

@Federico thank you. I've been waiting for this for a long time. I know it's not part of the original spec. But it would also be great if the NO LOCK would be directly support on UPDATE queries, so that you can omit the SELECT step, and just update a bunch of records which aren't locked.
For example;
UPDATE tbl1 SET c1='x' WHERE c2!=1 LIMIT 10 SKIP LOCKED;
Any chance such a thing would become possible?

@Federico I've tested this against the current version 10.5 on GitHub and it works well.

As well as checking functional correctness, I've exercised it under load using it as the data store for FreeRADIUS IP pool allocation. My tests show that it raises MariaDB performance in this scenario to be on a par with that of MySQL using SKIP LOCKED.

We would be very glad to see this feature merged in to MariaDB.

fsantulli, your work was ported to 10.6 by danblack, in https://github.com/grooverdan/mariadb-server/tree/bb-10.6-danielblack-MDEV-13115-select-for-update-skip-locked-nowait

I think that it would be cleaner to use the low-level mechanism as in MySQL Bug #3300 and avoid changing anything in the InnoDB lock subsystem. That is, attempt to create a record lock normally. If DB_LOCK_WAIT is returned and we are in SKIP LOCKED mode, then we would invoke lock_trx_handle_wait() and handle the 3 possible outcomes:

• DB_SUCCESS: Meanwhile, the lock was granted, and we can proceed to access the record.
• DB_DEADLOCK: Our transaction was chosen as deadlock victim (possibly due to a different lock that it is holding), and we must abort execution.
• DB_LOCK_WAIT: The waiting lock request was canceled, and we must skip the record.

Note that in MDEV-24671, MDEV-20612, MDEV-24738 the locking code was heavily changed in 10.6.

I've requested permission from Facebook as the originator of the patch to use it. We are dependent on their permission. If that doesn't eventuate we'll take what you have written and fill then gaps.

with the implementation you suggest to have 3 possible outcome states, would it be possible to also easily apply this feature on `UPDATE` queries, like; `UPDATE tbl1 SET c1='x' WHERE c2!=1 LIMIT 10 SKIP LOCKED;`?

olafbuitelaar I appreciate the desire for an `UPDATE` version with SKIP LOCKED. At the moment its going to take a heroic effort to get the SELECT version of this in before the 10.6 feature freeze. If by some miracle there is time you can assist in the creation of `UPDATE ... SKIP LOCKED` by:

• creating a new MDEV linking to this.
• describing the use case and what aspect of SELECT .. FOR UPDATE SKIP LOCKED makes it hard to implement this. Maybe this is covered in terryburton's well written use case (I should read this more).
• attempt to create a patch for sql/sql_yacc.yy to create the syntax for UPDATE SKIP locked like the attached patch; and/or
• create a test case for UPDATE ... SKIP LOCKED like mysql-test/suite/innodb/t/innodb_lock_skip_locked_nowait.test

Ideally we'd like a RocksDB implementation too.

traganowicz et al, this should be fine with Galera, its optimistic locking and replicated in ROW format so there should be no problems with Galera inter-operatibility.

Right now I'm going to see how well I can take marko's review comments into making the current fork I have into mature, stable, simple and minimal that he will accept.

bb-10.6-danielblack-MDEV-13115-select-for-update-skip-locked-nowait updated - https://github.com/grooverdan/mariadb-server/commit/40f4cfe4dad32db538cb576e8a7d17d0a8cd690a

Still have one troublesome line https://github.com/grooverdan/mariadb-server/commit/7f131ff099f24811ff824bf1bc92475061956767 that when enabled corrects

CURRENT_TEST: innodb.innodb_lock_skip_locked_nowait

mysqltest: At line 223: query 'SELECT * from t2 where x >= 0 and x <= 3 FOR UPDATE SKIP LOCKED' failed: 1205: Lock wait timeout exceeded; try restarting transaction

however breaks:

CURRENT_TEST: innodb.innodb_lock_skip_locked_nowait

--- /home/dan/repos/mariadb-server-10.6/mysql-test/suite/innodb/r/innodb_lock_skip_locked_nowait.result	2021-03-10 15:27:01.481478116 +1100

+++ /home/dan/repos/mariadb-server-10.6/mysql-test/suite/innodb/r/innodb_lock_skip_locked_nowait.reject	2021-03-10 19:27:21.786826888 +1100

@@ -26,7 +26,7 @@

 id	x

 lock will not wait, expecting error

 SELECT * from t0 where id = 1 FOR UPDATE NOWAIT;

-id	x

+ERROR HY000: Lock wait timeout exceeded; try restarting transaction

 connection con1;

 commit;

 connection con2;

@@ -55,7 +55,7 @@

 5	5

 lock will not wait, expecting error

 SELECT * from t0 where id = 1 FOR UPDATE NOWAIT;

-id	x

+ERROR HY000: Lock wait timeout exceeded; try restarting transaction

 connection con1;

 commit;

 connection con2;

@@ -86,7 +86,7 @@

 5	5

 lock will not wait, expecting error

 SELECT * from t1 where id = 1 FOR UPDATE NOWAIT;

-id	x

+ERROR HY000: Lock wait timeout exceeded; try restarting transaction

 connection con1;

 commit;

 connection con2;

@@ -119,7 +119,7 @@

 5

 lock will not wait, expecting error

 SELECT x from t2 where x = 1 FOR UPDATE NOWAIT;

-x

+ERROR HY000: Lock wait timeout exceeded; try restarting transaction

 connection con1;

 commit;

 connection con2;

@@ -152,7 +152,7 @@

 5

 lock will not wait, expecting error

 SELECT x from t3 where x = 1 FOR UPDATE NOWAIT;

-x

+ERROR HY000: Lock wait timeout exceeded; try restarting transaction

 connection con1;

 commit;

 connection con2;

@@ -185,7 +185,7 @@

 5	5

 lock will not wait, expecting error

 SELECT * from t2 where id = 1 FOR UPDATE NOWAIT;

-id	x

+ERROR HY000: Lock wait timeout exceeded; try restarting transaction

 connection con1;

 commit;

 connection con2;

note to add to https://mariadb.com/kb/en/unsafe-statements-for-statement-based-replication/#unsafe-statements when complete.

bb-10.6-danielblack-MDEV-13115-select-for-update-skip-locked-nowait there.

One GIS test case that haven't debugged on the end that's broken.

Rest seems basically working. Could be more test cases around UNION/PROCEDURE. Suggestions welcome.

danblackour use case is indeed quite similar as described by terryburton's description. With the exception that we don't directly require an result from the database. Our setup is a follows; We've a set of MariaDB instances where we store the tasks/data in a raw format and simple format. The data is then processed by a set of threads/workers and then inserted into another (master)MariaDB instance using XA transactions. Each thread/worker is general and can process any kind of raw data. The worker process consists of 2 phases, so that locks on the table are minimized and in case the (master)MariaDB isn't available or slow, insertion of new data isn't affected by this.
In the first phase each worker/thread assigns a id to the raw record, thus essentially assigning it self to do the work in the future. This process is our critical phase, and is protected by

GET_LOCK('shared-id-between-workers')

. So that only 1 worker at the time can assign raw data to itself. This is where i would like to use the SKIP LOCKED feature like in an UPDATE. thus instead of:

SELECT GET_LOCK('shared-id-between-workers');

UPDATE rawdata SET workerid=1 LIMIT 1000;

DO RELEASE_LOCK('shared-id-between-workers');

have:

UPDATE rawdata SET workerid=1 LIMIT 1000 SKIP LOCKED;

The second phase simply does a

SELECT * FROM rawdata where workerid=1

, which theoretically just cause a read lock on only it's owning record (sporadically i think i suffer from a GAP lock), and not be in the way of any other workers or the ongoing insertion of new data.

As requested i've created a new issue for this; MDEV-25338
I've checked the code files, but i don't think i'll be able to create a PR for this. But if i can contribute in any other way i'm happy todo so.

Will be out in 10.6.0. Thanks for your patience all.

Folks, can you comment on MDEV-25388 if have strong opinions if SKIP LOCKED should be blocked, or not, by table level locks (like 'LOCK TABLES' or a concurrent 'ALTER TABLE'). What should the behavior be?

Also I'm removing the interoperability of WAIT/NOWAIT with SKIP LOCKED reverting to MySQl-8.0 behaviour of an implicit no WAIT. If you have a desire for `WAIT` and `SKIP LOCKED` please so say so on MDEV-25433

Personally i don't have a strong opinion about this. I would say if you choose for SKIP LOCKED, a whole table lock would include this, and thus no records would return. But i can imagine a whole table lock are for special cases like backups or alter's, in which case you do want to wait till it complets. I would leave it to the application to check if a full table lock is present, and decides what to do if these cases are important. But generally i would say, if you opt for lock skipping, that is what you would get, regardless the kind of lock.
I also see the point of not having the WAIT being interoperable with the SKIP LOCKED, as they are more or less are mutually exclusive.