Metadata locks are a particular type of lock that has been supported since MariaDB 5.5. Transactions acquire metadata locks when they access a table or view for the first time. This includes non-transactional tables such as Aria tables. Metadata locks prevent transactions from dropping the locked object or modifying the structure. This is very important because if a transaction is using a table, you want to be sure that the table columns (or even the whole table) will not disappear in the middle. In some cases, stored programs are also locked.

If a connection tries to execute a DDL statement (such as ALTER TABLE) on a table that has a metadata lock, the connection will be put on hold until the locks are released. However, metadata locks use a timeout, which is defined by the lock_wait_timeout expressed in seconds. Note that the default value is 31536000, which corresponds to one year. If the timeout expires, the connection receives a 1205 error.

Since, as we mentioned before, metadata locks also work with non-transactional tables and views, it makes sense to use transactions to access any kind of entity. Also, if an application uses DDL statements on the existing tables and views, lock_wait_timeout should be set to a low value, and the applications should be prepared to receive a 1205 error.

Since MariaDB 10.0, a plugin called metadata_lock_info allows to see the existing metadata locks. The plugin is distributed with MariaDB, but is not installed by default. After installing it, a new table METADATA_LOCK_INFO is created in the information_schema database. This table contains the following columns:

In the SHOW PROCESSLIST statement output, connections that are waiting for a metadata lock to be released will appear with the Waiting for table metadata lock string in the Extra column.

Now, let's see a deadlock example. Again, we will use two mysql client instances. The first one will create a table, start a transaction, and insert a row. By doing so, it will acquire a metadata lock. We will use an Aria table to demonstrate that this mechanism also works on non-transactional tables. Then, the second connection will try to execute a RENAME TABLE, but it will have to wait. Next, the first connection will commit the transaction and the RENAME TABLE will be executed.

The code for the first connection is as follows:

MariaDB [test]> CREATE TABLE my_tab (a INT) ENGINE = Aria;
Query OK, 0 rows affected (0.28 sec) 
MariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec) 
MariaDB [test]> INSERT INTO my_tab VALUES (1);
Query OK, 1 row affected (0.03 sec) 

The code for the second connection is as follows:

MariaDB [test]> RENAME TABLE my_tab TO tab;

No output at this point; the connection is waiting for a metadata lock to be freed.

Now, let's see what metadata locks exist. For our convenience, we can do this from a third connection:

MariaDB [(none)]> SELECT * FROM information_schema.METADATA_LOCK_INFO G
*************************** 1. row *************************** 
    THREAD_ID: 5 
    LOCK_MODE: MDL_INTENTION_EXCLUSIVE 
LOCK_DURATION: MDL_STATEMENT 
    LOCK_TYPE: Global read lock 
 TABLE_SCHEMA: 
   TABLE_NAME: 
*************************** 2. row *************************** 
    THREAD_ID: 5 
    LOCK_MODE: MDL_INTENTION_EXCLUSIVE 
LOCK_DURATION: MDL_TRANSACTION 
    LOCK_TYPE: Schema metadata lock 
 TABLE_SCHEMA: test 
   TABLE_NAME: 
*************************** 3. row *************************** 
    THREAD_ID: 4 
    LOCK_MODE: MDL_SHARED_WRITE 
LOCK_DURATION: MDL_TRANSACTION 
    LOCK_TYPE: Table metadata lock 
 TABLE_SCHEMA: test 
   TABLE_NAME: my_tab 
3 rows in set (0.12 sec) 

We can see three locks. The first connection is the one with ID 4 and holds a shared write metadata lock on the test.t table, which presents other connections by modifying the table. The second connection has ID 5 and it holds two intention exclusive locks; this means that it is waiting to acquire exclusive locks on the metadata.

Now, the first connection can commit the transaction. But before and after that, it will execute a query on the table:

MariaDB [test]> SELECT * FROM t;
+------+ 
| a    | 
+------+ 
|    1 | 
+------+ 
1 row in set (0.00 sec) 
MariaDB [test]> COMMIT;
Query OK, 0 rows affected (0.00 sec) 
MariaDB [test]> SELECT * FROM t;
ERROR 1146 (42S02): Table 'test.t' doesn't exist 

The first query works, because RENAME TABLE is waiting. But after the commit, the same query does not work, because the table has been renamed.

Note that when a metadata lock is released, the DDL statements are executed in the order they were queued. Even if the blocking transaction terminates with ALTER TABLE, this command may fail because a similar command was already queued and thus, is executed first. The following example shows this behavior. The second connection queues ALTER TABLE, and the ALTER TABLE executed by the first connection fails. If an application uses DDL statements, this behavior may lead to problems that are difficult to debug.

Connection 1:

MariaDB [test]> CREATE TABLE t1 (a INT) ENGINE = Aria;
Query OK, 0 rows affected (0.22 sec) 
MariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec) 
MariaDB [test]> SELECT * FROM t1;
Empty set (0.00 sec) 

Connection 2:

MariaDB [test]> ALTER TABLE t1 ADD COLUMN b TINYINT SIGNED;
Stage: 2 of 2 'enabling keys'      0% of stage done 

The client shows a progress report for certain ALTER TABLE commands on the Aria tables. The progress report stops at the second stage because the connection is waiting for the metadata lock to be released.

Connection 1:

MariaDB [test]> ALTER TABLE t1 ADD COLUMN b BIGINT UNSIGNED; 
ERROR 1060 (42S21): Duplicate column name 'b' ALTER TABLE locks and algorithms. Running an ALTER TABLE statement against a big table can take a long time. It is not uncommon, on some databases, to see the building of a new index takes some hours. With MariaDB 10.0, some operations take much less time because they use a different algorithm. In some cases, a table structure change required a whole copy of the table before 10.0, but can be executed in-place since 10.0. Also, in some cases such operations used to block all connections from writing to the table, or even from reading the table. Sometimes, this lock is no more necessary with 10.0.

The operations that do not require a table copy anymore are the following:

Also, since MariaDB 5.5, adding or dropping an index on an InnoDB table does not require a table copy.

In MariaDB 10.0, some additional clauses were added to ALTER TABLE. One is ALGORITHM. It can be used to force a table copy (probably useful if we are affected by an ALTER TABLE bug) or require the use of an in-place algorithm. In this case, if the in-place algorithm cannot be used, an error is generated. The allowed values for ALGORITHM are COPY, INPLACE, or DEFAULT (which simply uses the best algorithm).

The ONLINE option is a synonym for ALGORITHM=INPLACE.

The LOCK clause can be used so that no locks are used, or only shared (read) locks or exclusive (write) locks are used. If a better locks strategy is available, it will not be used (probably useful if we are affected by some concurrency-related bug in ALTER TABLE). If the requested lock strategy cannot be used, an error will be issued. The allowed values for LOCK are NONE, SHARED, EXCLUSIVE, and DEFAULT (which uses the less restrictive available strategy).

Consider the following code for example:

ALTER ONLINE TABLE t ADD INDEX idx1 (col_name);
ALTER TABLE ALGORITHM = INPLACE, LOCK = NONE ADD INDEX idx1 (col_name);

For InnoDB tables, the information_schema INNODB_METRICS details show, among other information, the current status of the ALTER TABLE operations. For Aria tables, the mysql command-line client shows a progress report for some ALTER TABLE operations.