SKIP LOCKED are syntaxes added in MySQL 8.0 and MariaDB 10.3. The idea came from AliSQL (MySQL fork by Alibaba). It was revisited in MySQL, and I am not aware if MariaDB used the original implementation. EDIT: As Morgan Tocker points out in a comment, originally Ali Baba filed a feature request to MySQL.
While MySQL and MariaDB syntaxes are similar, there are important differences and the compatibility is only apparent. This article discusses these differences.
This syntax is only available in MariaDB. It means that, if a row or table that we want to read is write-locked, we can wait up to the specified number of seconds. If the lock is not released after the timeout occurs, the query will fail.
If a table or row we need to read is write-locked, the query will not be queued; instead, it will fail immediately.
- MariaDB supports this syntax for some DDL statements (
ALTER TABLEand its shortcuts),
LOCK TABLES, and
SELECT. MySQL only supports it for
- MySQL only supports this syntax in combination with
FOR SHARE. In order to introduce an incompatibility, they sacrificed the support of this feature for SELECTs in
SERIALIZABLEmode, that have an implicit
LOCK IN SHARE MODEclause. Fortunately this is an edge case, but it is another case where Oracle marketing strategies affect users in a bad way.
- MySQL implements
FOR UPDATE OFand
FOR SHARE OF. This is interesting, and not only for the
NOWAITfeature, because it allows us to
JOINmultiple tables without locking them all. Thanks, Oracle engineers.
- MySQL and MariaDB report different error codes and messages.
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
This is only in MySQL. It excludes locked rows from the resultset, making our queries fast. The documentation warns us that the resultset will be inconsistent. This is implicit in the future, but it is worth emphatizing. However, consistency is not always so important, and skipping rows seems to me a great way to solve some performance problems.
EDIT: Morgan’s comment points out that
SKIP LOCKED is also non-deterministic. Again, I believe this is clear if you understand what this feature does, but still, maybe I should point it out. I think it could be compare to
READ UNCOMMITTED: they are different optimizations, but in both cases the results you get depend on what other connections are doing. So results are inconsistent and not deterministic by nature.
MariaDB implementation of
NOWAIT is clearly more complete. They have
WAIT syntax to set a timeout; they implemented this syntax in more SQL statements; and the syntax is compatible with implicit
LOCK IN SHARE MODE.
Despite this, IMHO, MySQL wins. They have
SKIP LOCKED, which is very interesting. Also the above mentioned
FOR UPDATE OF syntax is a nice feature.
Once again, Oracle spent some resources to add incompatibilities. This does not affect the quality of MySQL, but it’s still a damage to the community, which should be able to use both MySQL and MariaDB in the same environments, with the same tools and the same libraries. But the distance between these DBMS’s is growing constantly.
- MariaDB: WAIT and NOWAIT
- MySQL: Locking read concurrency with NOWAIT and SKIP LOCKED
- Use cases explained on mysqlserverteam.com
- Morgan’s comment to this post.