MySQL vs. MariaDB: WAIT, NOWAIT, SKIP LOCKED

NOWAIT, WAIT and 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.

WAIT

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.

NOWAIT

If a table or row we need to read is write-locked, the query will not be queued; instead, it will fail immediately.

Incompatibilities:

  • MariaDB supports this syntax for some DDL statements (ALTER TABLE and its shortcuts), LOCK TABLES, and SELECT. MySQL only supports it for SELECT.
  • MySQL only supports this syntax in combination with FOR UPDATE or FOR SHARE. In order to introduce an incompatibility, they sacrificed the support of this feature for SELECTs in SERIALIZABLE mode, that have an implicit LOCK IN SHARE MODE clause. 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 OF and FOR SHARE OF. This is interesting, and not only for the NOWAIT feature, because it allows us to JOIN multiple tables without locking them all. Thanks, Oracle engineers.
  • MySQL and MariaDB report different error codes and messages.
    MySQL says: ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set
    MariaDB says: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

SKIP LOCKED

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.

My conclusions

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.

References

Federico

Advertisements

6 thoughts on “MySQL vs. MariaDB: WAIT, NOWAIT, SKIP LOCKED

  1. Hi Federico,

    It is one of my favorite new features as well. A couple of comments though:

    – Props to Ali for adding it first to MySQL, but for full credit, this feature is modeled on a similar feature in other databases: https://dev.mysql.com/worklog/task/?id=3597

    – You can do a WAIT equivalent in MySQL 8.0 by using a SET_VAR hint for lock wait timeout:
    https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout

    You could argue of course that it should have the ‘simple syntactic sugar’. This is one of the complicated parts of feature design; aliases can hurt usability, as you have to explain when there are two options which one is better. My philosophy while on the team was “support major use cases”; for which I considered NOWAIT to be one, but not WAIT.

    On a side-note, if there is a use case for WAIT, imho it should be specified in fractional seconds (i.e. wait 100ms), which as I understand neither can do.

    – I think it is more correct to say SKIP LOCKED is non-deterministic rather than non-consistent. It doesn’t violate any constraints, it just skips locked rows. I don’t know, maybe others have differing opinions here.

    – It doesn’t sound correct to say Oracle spent resources to add incompatibilities. AFAIK, this is not part of the SQL standard, and as you can see from the comments in BUG #49763 (and age of WL#3597) was probably in development at Oracle before MariaDB. Did you want to survey how it exists in PostgreSQL / SQL Server / Oracle before making that claim?

    At the same time, MySQL has never claimed compatibility with MariaDB (the reverse does, which is dubious for more reasons than this).

    • Hi Morgan, thank you for your corrections. I am going to edit the post.
      Of course MySQL does not claim compatibility with MariaDB. And MariaDB shouldn’t claim compatibility with MySQL – this has been said many times, and they should listen. BUT, there is a sort of de facto compatibility, and users rely on it. Which is correct, to some extent. And it’s also correct that MySQL and MariaDB follow different roadmaps, which increases incompatibilities over time. BUT, in many cases they implement the same features, more or less at the same time. When MySQL implements a feature which has already been implemented in MariaDB, they often use slightly different syntaxes or variable names to do exactly the same things. (I can look for examples if you want, one is the syntax for virtual columns) This could be good for their business, but as a user I don’t care about this. What I see is more differences, without any technical reason.
      In the specific case, I agree that WAIT is syntax sugar, but NOWAIT is also syntax sugar. Let’s put it in this way: this is one of the few cases when I like syntax sugar, because it makes clear that you can do something.
      It is common practice to try to be compatible with other database systems, so I don’t need to explain why it is desirable 🙂 So please Oracke, don’t introduce incompatibilities with MariaDB when you implement the same features.
      Federico

      • Hi Federico,

        Yes – I agree NOWAIT is syntax sugar. My point was that you can (and should) add it when it maps to a well-known use case. The way that WAIT exists (based on seconds) does not make sense to me; 100x more users will use NOWAIT. Compatibility could be an example of a use case too, if that is a stated goal. The best negative example of adding aliases everywhere is in unique option prefixes for CLI tools. But there are others.

        I think your edit on SKIP LOCKED is not quite correct: it is not a READ COMMITTED. You simply do not see the other rows that are in flight. Think of a message queue like pattern where you can select the next N rows to process. Because of skip locked, you can design this multi-threaded where each worker sees a different set of rows (it is often combined with SELECT.. FOR UPDATE).

        • I didn’t mean it is the same. They do different things and serve different purposes. But they are similar in that your results will depend on what other connections are doing at this time – do you agree?

          • I don’t see them as the same. With a SKIP LOCKED + FOR UPDATE + LIMIT I can have multi-threaded set of workers, each consistently seeing a different result set (of which each rows are consistent). Previously I would have had to use a single-thread master worker, and multi-threads behind that, or had some algorithm where I each worker only looked for row-id’s to process that are divisible by total worker count + thread offset (I’m not sure anyone did this). It’s about the free multi-threaded coordination of reading hot rows.

            • But I’m not saying anything different. I run it once and I get 3 rows. You lock one of those rows, I run it again, and I get 2 rows – a result which is not consistent with what I asked. The difference is impossible to predict because it depends on other sessions activity. The only other case I know when I can get “wrong” results impossible to predict, is READ UNCOMMITTED – this is where the similarity begins and ends.

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s