Support SKIP LOCKED Syntax

[shenli]

Feature Request

Is your feature request related to a problem? Please describe:

Introduce a new syntax to handle hot rows

Describe the feature you'd like:

https://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/

Starting with MySQL 8.0.1 we are introducing the SKIP LOCKED modifier which can be used to non-deterministically read rows from a table while skipping over the rows which are locked.  

Describe alternatives you've considered:

Teachability, Documentation, Adoption, Migration Strategy:

[jackysp]

I think we can actually implement it, not just syntax support.

[zz-jason]

@cfzjywxk @zhangjinpeng1987 PTAL

[kassah]

The above blog page has been removed. This is a link to the relevant documentation: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html#idm45429418133392

[you06]

I'll write an RFC about this issue.

[PATROMO]

@jackysp what is the current status?

[yahonda]

Recently, Rails 8.0 Beta 1 has been announced that includes a new job running framework, called Solid Queue.

https://rubyonrails.org/2024/9/27/rails-8-beta1-no-paas-required

This Solid Queue feature is making use of UPDATE SKIP LOCKED feature. Supporting this feature is really appreciated.

Solid Queue replaces the need for not just Redis, but also a separate job-running framework, like Resque, Delayed Job, or Sidekiq, for most people. For high-performance installations, it’s built on the new FOR UPDATE SKIP LOCKED mechanism first introduced in PostgreSQL 9.5, but now also available in MySQL 8.0 and beyond. For more modest needs, it also works with SQLite, which makes it ideal for that no-dependency path to the first HELLO WORLD dopamine hit you get from seeing your work in production right away.

[jackysp]

Recently, Rails 8.0 Beta 1 has been announced that includes a new job running framework, called Solid Queue.

https://rubyonrails.org/2024/9/27/rails-8-beta1-no-paas-required

This Solid Queue featureis making use of UPDATE SKIP LOCKED feature. Supporting this feature is really appreciated.

Solid Queue replaces the need for not just Redis, but also a separate job-running framework, like Resque, Delayed Job, or Sidekiq, for most people. For high-performance installations, it’s built on the new FOR UPDATE SKIP LOCKED mechanism first introduced in PostgreSQL 9.5, but now also available in MySQL 8.0 and beyond. For more modest needs, it also works with SQLite, which makes it ideal for that no-dependency path to the first HELLO WORLD dopamine hit you get from seeing your work in production right away.

cc @you06 @cfzjywxk PTAL, thx!