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

Original address: http://blog.itpub.net/26250550/viewspace-1071987/

The timeout for metadata lock is lock_wait_timeout, not innodb_lock_wait_timeout

The Metadata lock: DDL statement was introduced in MySQL 5.5.3 to break the isolation level of transactions.

Prior to version 5.5.3, the locking of table structure metadata (Metadata) by MySQL transactions was statement-grained, that is, after the statement was executed, its table structure could be updated by other sessions, regardless of whether the transaction could be completed or not!

With the introduction of Metadata lock, the locking of table structure metadata (Metadata) becomes transaction granularity, that is, Metadata lock is released only at the end of the transaction.

Phenomenon:

Start transaction; after explicitly opening a transaction, the query statements (including select) within the transaction will occupy the metadata lock (profile:Opening tables phase) of the related table

Causes the DDL statement to be blocked because the metadata lock of the table cannot be obtained

Optimization after MySQL version 5.6.6

Metadata lock does not block DDL statements, but the original session will return the Error message "Table definition has changed, please retry transaction" when it accesses the table.

Case list

Session1mysql > start transaction

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from test1 where id=1

+-+ +

| | id | name |

+-+ +

| | 1 | 1 |

+-+ +

1 row in set (0.00 sec)

Session2

Mysql > set profiling=on

Query OK, 0 rows affected (0.00 sec)

Mysql > alter table test1 add column c2 int;--- jam

Mysql > show full processlist

+-+-- +

| | Id | User | Host | db | Command | Time | State | Info |

+-+-- +

| | 4 | root | localhost | test | Sleep | 10 | | NULL | |

| | 5 | root | localhost | test | Query | 6 | Waiting for table metadata lock | alter table test1 add column c2 int |

| | 6 | root | localhost | NULL | Query | 0 | NULL | show full processlist | |

+-+-- +

We can see that ALTER TABLE will block in response to Metadata lock. Once the transaction in Session 1 is committed or rolled back, the operation that releases the Metadata lock,Session2 of the table1 table can be performed immediately

After the ctrl+c is dropped, we check the profile execution plan.

Mysql > show profile for query 6

+-+ +

| | Status | Duration |

+-+ +

| | starting | 0.000045 | |

| | checking permissions | 0.000005 | |

| | checking permissions | 0.000005 | |

| | init | 0.000007 | |

| | Opening tables | 0.000061 | |

| | System lock | 0.000008 | |

| | setup | 0.000018 | |

| | creating table | 0.002776 | |

| | After create | 0.000063 | |

| | copy to tmp table | 0.000312 | |

| | rename result table | 1.459601 | |

| | query end | 0.000031 | |

| | closing tables | 0.000285 | |

| | freeing items | 0.002271 | |

| | cleaning up | 0.000073 | |

+-+ +

15 rows in set (0.00 sec)

As you can see, the step of getting stuck is to save the ALTER in the rename result table,MySQL background into a continuous operation "create temporary new table-> insert data from the old table-> temporarily fetch the new table to the old table (RENAME)", that is, the actual operation of changing the old table does not occur until the RENAME phase. And this step is the blocking step. So you can confirm that what Metadata lock affects is the action that actually modifies the table structure. (recall: the same reason as the pt-osc section, when a select is not finished, alter modifies the table structure to generate metadata lock)

Based on this, we can verify that the ALTER TABLE,RENAME TABLE,DROP TABLE is affected by Metadata lock, and the verification steps are similar to the above, omitted.

It is worth noting that creating an existing table is also affected by Metadata lock. If the Metadata lock of Table An is used in a transaction (such as SELECT the table) and the transaction is not completed, the creation of a table with the same name will also be stuck. This is mentioned in the referenced article. If you are interested, please refer to the discussion page, which is not discussed here.

Another side effect of metadata lock: as in the example above, the metadata lock of ALTER TABLE should be blocked, which causes other subsequent transactions to block the SELECT of the table as well! That is, the operation of ALTER TABLE will affect other SELECT operations. According to the above example:

Session2 mysql > alter table table1 add column C1 int

When this step has been waiting, query table1 in the new session.

Session3 mysql > select * from test1; # has been waiting

Session4 mysql > show full processlist

Mysql > show full processlist

+-+-- +

| | Id | User | Host | db | Command | Time | State | Info |

+-+-- +

| | 4 | root | localhost | test | Sleep | 1857 | | NULL |

| | 5 | root | localhost | test | Query | 504 | Waiting for table metadata lock | select * from test1 |

| | 6 | root | localhost | NULL | Query | 0 | NULL | show full processlist | |

| | 8 | root | localhost | test | Query | 511 | Waiting for table metadata lock | alter table test1 add column c2 int |

+-+-- +

Session1 mysql > commit

After the first session is submitted, Session2 and Session3 are also executed successfully. See at which step the select of Session3 needs to wait:

Session3 mysql > show profile

+-+

| | Status | Duration |

+-+

| | starting | 0.000018 | |

| | Waiting for query cache lock | 0.000003 | |

| | checking query cache for query | 0.000037 | |

| | checking permissions | 0.000007 | |

| | Opening tables | 22.502591 | |

| | System lock | 0.000013 | |

| | Waiting for query cache lock | 0.000024 | |

| | init | 0.000022 | |

| | optimizing | 0.000009 | |

| | statistics | 0.000011 | |

| | preparing | 0.000015 | |

| | executing | 0.000003 | |

| | Sending data | 0.000036 | |

| | end | 0.000009 | |

| | query end | 0.000005 | |

| | closing tables | 0.000006 | |

| | freeing items | 0.000007 | |

| | Waiting for query cache lock | 0.000003 | |

| | freeing items | 0.000015 | |

| | Waiting for query cache lock | 0.000002 | |

| | freeing items | 0.000002 | |

| | storing result in query cache | 0.000004 | |

| | logging slow query | 0.000002 | |

| | logging slow query | 0.000028 | |

| | cleaning up | 0.000003 | |

+-+

You can see that the SELECT statement first checks the query cache, misses, and then gets stuck during the Opening tables phase.

This leads to another feature. Based on the above scenario, if the subsequent SELECT can be hit in the query cache, it will not be stuck by ALTER TABLE, because no Opening tables operation is required. The SHOW PROFILE that hits the cache SELECT is as follows:

Mysql > show profile

+-+

| | Status | Duration |

+-+

| | starting | 0.000017 | |

| | Waiting for query cache lock | 0.000004 | |

| | checking query cache for query | 0.000006 | |

| | checking privileges on cached | 0.000004 | |

| | checking permissions | 0.000007 | |

| | sending cached result to clien | 0.000011 | |

| | logging slow query | 0.000002 | |

| | cleaning up | 0.000003 | |

+-+

8 rows in set (0.00 sec)

Remaining questions 1. Why did the record not change in the second query, but the result set returned empty?

This issue is discussed on this page, confirming that the reason is:

The actual background of ALTER operation in MySQL is "create new table"-> "copy old table data"-> "RENAME new table becomes old table". For the REPEATABLE READ isolation level of the client, the InnoDB engine uses "consistent read" based on the multi-version (Multi-version) function, that is, when the first query in the transaction takes a snapshot of the current version of the table, but the new table is actually created after the ALTER operation, this new table has no data for the previous snapshot, so the second query of the new table is based on the snapshot data and returns empty. After MySQL version 5.6.6, such a second query returns the Error message "Table definition has changed, please retry transaction". Please see here for details.

Remaining problems 2. Why is there no lock waiting for timeout?

We know that if the wait time of the InnoDB row lock exceeds the innodb_lock_wait_timeout, the timeout error will be reported. The metadata lock here has been waiting. Why didn't you time out? In fact, the timeout setting for metadata lock is another system variable lock_wait_timeout.

Lock_wait_timeout controls all operations involving metadata lock, including DML and DDL, as well as operations on tables, views, stored procedures, stored functions, as well as LOCK TABLES, FLUSH TABLES WITH READ LOCK, HANDLER, and so on.

The default setting of lock_wait_timeout is one year, which can be dynamically set and adjusted according to the actual scene.

A large number of references:

Http://www.mysqlperformanceblog.com/2013/02/01/implications-of-metadata-locking-changes-in-mysql-5-5/

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.