Add support for SKIP LOCKED and NOWAIT

[kocoten1992]

Feature Description

Add suport for new feature skip locked and no wait in mysql 8, https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

Use Case(s)

Skip locked especially useful in building a queue system (like RabbitMQ or AWS SQS - but better).

Let me much elaborate on the idea and how it work.

Imagine we have a tasks table and each row is a task need to be done, eg:

Create Table: CREATE TABLE `tasks` (
  `id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `task_id` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'queue',
  `prio` int(11) NOT NULL DEFAULT '5',
  `executor` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_touch` timestamp NULL DEFAULT NULL,
  `extra` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `retry_count` tinyint(4) NOT NULL DEFAULT '0',
  `retry_reasons` json NOT NULL,
  `lock_token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `debug` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `tasks_task_id_foreign` (`task_id`),
  KEY `tasks_lock_token_index` (`lock_token`),
  CONSTRAINT `tasks_task_id_foreign` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`),
  CONSTRAINT `chk_tasks_prio` CHECK (((`prio` >= 0) and (`prio` <= 24))),
  CONSTRAINT `chk_tasks_retry_count` CHECK (((`retry_count` >= 0) and (`retry_count` <= 3))),
  CONSTRAINT `chk_tasks_status` CHECK (((`status` = _utf8mb4'queue') or (`status` = _utf8mb4'prepare') or (`status` = _utf8mb4'doing') or (`status` = _utf8mb4'waiting') or (`status` = _utf8mb4'done') or (`status` = _utf8mb4'invalid')))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

The table is a rip off of real table (but you get the idea), in table above field executor is important, when task already registered to a executor, other executors will not get that tasks anymore.

And we have like 100 executors to get each row and execute the task, but when multiple executors try query, it is possible multiple executors might get a single task is available and try to register itself to do that task (concurrency issue).

The solution in this case is select for update, example:

select * from tasks limit 1 for update;

When executor A try select for update on a single row of task, other executors (B, C...) try to query will get hanged up until executor A either commit or rollback, which is working and it good enough, but with skip locked, this pattern can do better (we don't have to wait for executor A to finish it's transaction)

Example:

# we have 2 rows in tasks table
# 1. row 1
# 2. row 2
# executor A
select * from tasks limit 1 for update skip locked;

# result is row 1

# executor B
select * from tasks limit 1 for update skip locked;

# result is row 2

It would achieve better concurency control in this case (noted only work in READ COMMITED mode, https://www.percona.com/blog/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/)

Currently vitess support select for update, it mostly good enough, but it will be greate if we having skip locked altogether.

[sougou]

The use case you describe is addressed by the messaging feature https://vitess.io/docs/reference/messaging/. But maybe skip locked can serve other purposes. So, we can keep the issue open for it.

[derekperkins]

We definitely have use cases where we would like to use skip locked, so I'd love to see support. It seems like Vitess can just push that down and not have any special handling.

@kocoten1992 Messaging works awesome, I highly recommend it.

[morgo]

If I understand messaging correctly, this is a bit different.

Messaging is a pub/sub, but SKIP LOCKED / NO WAIT is good for handling concurrent access to hot rows: https://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/

[derekperkins]

I haven't tried it yet, but I am hoping that this PR is allowing it through now. #5336

[aquarapid]

I haven't tried it yet, but I am hoping that this PR is allowing it through now. #5336

it does not; this is still a TODO

[L3o-pold]

We may be interested by this for unsharded keyspace too.

[dbussink]

Reopening since it was only fixed on GitHub's fork, but it still needs to be upstreamed here in Vitess.