What is Mysql table metadata lock wait queue ordering

Question

I have a bunch of connections doing SELECT within a transaction and one executing a DDL. The mysql manual is pretty clear about how the metadata lock is taken within transactions:

To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table's structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

This makes sense so, I did this test:

connectionA$ begin;
connectionA$ select * from facebook_authorizations;
connectionA$ ....
connectionB$ alter table facebook_authorizations add column foo int default null;
connectionC$ begin;
connectionC$ select * from facebook_authorizations;
connectionA$ commit;

On my system, when connectionA commits, connectionC executes and connectionB is still hung: it is starved from executing by SELECT-based transations. I was expecting the metadata lock waiting list to be processed roughly in FIFO order but it does not seem so.

Is there documentation about the order in which the metadata wait queue is processed ?

Answer 1

For the specifics about the scenario:

Trying to reproduce the case, I got session B blocked, and then session C blocked ... Note that if the session B was in fact waiting on something else before acquiring a metadata lock on the table, session C has no reasons to wait.

Now, in general:

Metadata locks already granted are visible in the performance_schema, in table metadata_locks, with a LOCK_STATUS as GRANTED.

See the doc: https://dev.mysql.com/doc/refman/8.0/en/metadata-locks-table.html

This is helpful to see which session owns which lock.

Metadata locks a session is waiting for are also visible in the same table, with a LOCK_STATUS as PENDING.

This is helpful to see what a session is waiting for.

A (blocked) session waits for a lock on something, which in turn might be already locked (with various LOCK_TYPE and LOCK_DURATION) by other sessions, but there is no direct "Session X waits for session Y" relation here, is is implied by the lock already in place.

When several sessions all wait for the same resource, and when the resource is made available (a session released a metadata lock), trying to anticipate the processing order is (in my opinion) risky, and an application logic should not depend on that: as far as I know, the current implementation is indeed a FIFO, but this may change any time, and is not documented.

The rational here is that the server must have some degree of freedom, so that implementing a different scheduling policy, for example for performance reasons, is feasible. If somehow some application "expects" a given order, it will break and prevent any changes.