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:
THREAD_ID
: This is the ID of the thread that holds the metadata lockLOCK_MODE
: Metadata locks have several modes that determine which operations are lockedLOCK_DURATION
: This indicates whether the metadata lock is valid for the duration of the transaction or statementLOCK_TYPE
: This indicates which object type is locked (for example, Table metadata lock
or Stored function metadata lock
)TABLE_SCHEMA
: This is the name of the schema containing the locked objectTABLE_NAME
: This is the name of the locked objectIn 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:
INT(3)
to INT(4)
ENUM
columns, adding a value at the end of the list; for example, ENUM('a', 'b')
to ENUM('a', 'b', 'c')
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.
Percona maintains a useful tool called
pt-schema-change, which is included in the Percona Toolkit. It creates an empty copy of a table, alters the structure of the new table, and then copies data into the new table. At the end of this process, the new table replaces the old one. This procedure requires much more time than a normal ALTER TABLE
, but does not lock the existing table. With MariaDB 10.0, in many cases this tool is no longer necessary. However, it is still useful for ALTER TABLE
operations that still require long table locks, and with older versions of MariaDB. Before using this tool, the Percona documentation should be read carefully.