Shulou(Shulou.com)06/01 Report--

Online DDL from the name is easy to mislead the novice, thinking that no matter what the situation, modify the table structure will not lock the table, the ideal is very plump, the reality is very bony, pay attention to this pit!

There are two situations where performing a DDL operation will lock a table, Waiting for table metadata lock (metadata table lock)

1. Adding or deleting fields or indexes will not lock the whole table, deleting the primary key and changing the field properties will lock the whole table, as shown in the following figure:

2. When adding a field alter table table, the addition, deletion, modification and query of the table will not lock the table. Before that, when the table has been accessed, you need to wait for its execution before you can execute alter table. For example, in session 1, deliberately execute a query with large results, and then execute the add field age in session 2. The table lock will also appear, as shown in the following figure:

For the second case, MariaDB10.3 adds the AliSQL patch-DDL FAST FAIL to make its DDL operation fail quickly.

The syntax is:

ALTER TABLE tbl_name [WAIT n | NOWAIT]... CREATE... INDEX ON tbl_name (index_col_name,...) [WAIT n | NOWAIT]... DROP INDEX... [WAIT n | NOWAIT] DROP TABLE tbl_name [WAIT n | NOWAIT]... LOCK TABLE... [WAIT n | NOWAIT] OPTIMIZE TABLE tbl_name [WAIT n | NOWAIT] RENAME TABLE tbl_name [WAIT n | NOWAIT]... SELECT. FOR UPDATE [WAIT n | NOWAIT] SELECT... LOCK IN SHARE MODE [WAIT n | NOWAIT] TRUNCATE TABLE tbl_name [WAIT n | NOWAIT]

Example:

If a slow SQL operates on the table online, you can use WAIT n (set wait in seconds) or NOWAIT to explicitly set the lock wait timeout in the statement, in which case, if the lock cannot be acquired, the statement will fail immediately. WAIT 0 is equivalent to NOWAIT.

Reference: https://jira.mariadb.org/browse/MDEV-11388

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.