Can a IX lock be upgraded to X lock if 2 actors concurrently hold IX lock?

Question

As per, https://mariadb.com/kb/en/innodb-lock-modes/

An IX lock is not granted if in X or S lock is held. It is granted if an IX or IS lock is held

This means that 2 threads can concurrently hold IX lock.

Again, from doc

An X lock is not granted if any other lock (X, S, IX, IS) is held.

This implies that if 2 threads have IX locks, then it cannot be upgraded to X lock.

I read here that IX locks are only for table level locks and it allows individual rows to still be locked while the IX is head at the table level. This is used to prevent DDL operations while a DML is in progress on that table.

If that's the case, then if 2 threads have IX locks on the table, how will either of them or a 3rd thread be able to acquire a X lock on table for a DDL operation? Does it mean that both the IX locks need to be released for the X lock to be acquired for the DDL operation?

Answer 1

The documentation is written very unclearly, because they use the term "X locks" ambiguously. In some documentation it means exclusive table locks, and in other places it means exclusive row locks.

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks says:

Intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.

An IX lock blocks an X table lock. That is, you can have many transactions doing INSERT/UPDATE/DELETE and holding row-level X locks. All of these transactions also hold IX table locks. Any client that tries to do DDL requires an table-level X lock, so if there are any transactions in progress, it blocks DDL.

It also works vice-versa. A table-level X lock also blocks any transaction from acquiring an IX lock. This means you can't do DML against a table while it's being ALTERed, for example.

This would have been easier if they had used a few more distinct terms for the different types of locks.