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.
Any chance this is in the works?