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

This article introduces the relevant knowledge of "what are the common and serious MySQL problems". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1 Review and reflection on the problems

1.1 SQLException: Lock wait timeout exceeded; try restarting transaction,please rollback!

When such an error occurs again, don't be proud to say that there is something wrong with the database, call DBA. (uat appears many times)

The reasons for the first problem are:

The transaction cannot be committed successfully because the disk space is full. (disk fullness is a very dangerous operation, which will cause binlog to be written bad, and the standby database cannot be synchronized, so it needs to be restored.)

An exclusive lock is generated when the update transaction does not commit normally, causing other update transactions to fail to acquire the lock and the transaction timed out.

1.2 condition query is stuck, no matter how to re-run can not pass, how to do, anxious to death (after the migration compared to the actual appearance).

CTRL + C terminated during the Truncate table process. There is a truncate transaction on the fragment that exists all the time, so that all operations on the table will time out.

1.3 query stuck, update stuck. Unexpectedly, the Alter table in front of you did not succeed.

The problem of DBProxy is not discussed in this article. When a query transaction does not commit normally and occupies a shared lock, it will also cause alter table not to acquire the MDL lock and keep waiting. The prompt is: Waiting fortable metadata lock (available in show processlist).

2 detailed analysis of the principle

2.1What is a MDL lock?

In order to maintain the data consistency of table metadata in a concurrent environment, metadata cannot be written when there are active transactions (explicit or implicit) on the table. Therefore, from the MySQL5.5 version, the MDL lock (metadata lock) is introduced to protect the metadata information of the table, and to solve or ensure the consistency between the DDL operation and the DML operation.

For the introduction of MDL, it mainly solves two problems, one is transaction isolation, for example, under the repeatable read isolation level, session A during two queries, session B makes changes to the table structure, the two query results will be inconsistent and can not meet the requirements of repeatable readability. Another is the problem of data replication. For example, when session An executes multiple update statements, another session B changes the table structure and submits it first, which will cause slave to redo alter first, and then redo update. There will be replication errors.

So when you do the above on the table, if there is an active transaction on the table (uncommitted or rolled back), the session requesting to write will wait at the Metadata lock wait. For example, the following is the case:

Without the protection of MDL locks, transaction 2 can perform DDL operations directly and cause an error in transaction 1, as is the case with version 5.1. Version 5.5 adds the MDL lock to protect this situation. Since transaction 1 opens the query, the MDL lock is obtained, and the lock mode is SHARED_READ. If transaction 2 wants to execute DDL, it needs to obtain the EXCLUSIVE lock, which is mutually exclusive, so transaction 2 needs to wait.

Note: Metadata Lock Wait wait occurs in both InnoDB engine tables that support transactions and MyISAM engine tables that do not support transactions. Once the Metadata Lock Wait wait occurs, all subsequent access to the table will be blocked on the wait, resulting in connection accumulation and business impact.

MySQL design: when setting the autocommit=0;read_commited, whether the first statement of the session is select or dml, start a transaction, and then hold the MDL lock until the end of the commit until the commit.

The design of Oracle: the transaction is created only when the first update statement of session is initiated, which reduces the possibility of blocking on read-much systems. Especially when the developer initiates the select statement, thinking that there is no update, it is no longer commit. But on MySQL, it's dangerous to start a select statement and forget about commit.

2.2 Common MDL lock scenarios and detailed explanations

1) currently, ALTRE operations are performed when DML operations are performed

2) when there is a long query to the table or when using mysqldump/mysqlpump, the use of alter will be blocked

3) uncommitted or rolled back after the transaction is displayed or implicitly opened, such as uncommitted or rolled back after the query is completed, will be blocked using alter

4) if there are failed query transactions on the table, such as query columns that do not exist, the statement failure returns, but the transaction is not committed, the alter will still be blocked.

Detailed test explanation:

1) currently, ALTRE operations are performed when DML operations are performed

# SESSION Amysql > insert into yetest2 select * from yetest1;# SESSION Bmysql > alter table yetest2 add yeColumn int; / / wait for SESSION A to finish execution; # SESSION Cmysql > show processlist +-+-| Id | User | Host | db | Command | Time | State | | Info | +-- +-| root | localhost | sbtest | Query | 7 | Sending data | insert into yetest2 select * from yetest1 | | 271 | root | localhost | sbtest | Query | 3 | Waiting for table metadata lock | alter table yetest2 add yeColumn int | | 272 | root | localhost | NULL | Query | 0 | starting | show processlist | | +-- -+ 3 rows in set (0.00 sec) # SESSION Dmysql > select * from yetest2 limit 10 / / wait for metadata lock; # SESSION Emysql > show processlist +-+-| Id | User | Host | db | Command | Time | State | | Info | +-- +-| 267 | root | localhost | sbtest | Query | 20 | Sending data | insert into sbtest2 select * from sbtest1 | | 271 | root | localhost | sbtest | Query | 13 | Waiting for table metadata lock | alter table yetest2 add yeColumn int | | 272 | root | localhost | NULL | Query | 0 | starting | show processlist | | 308 | root | localhost | sbtest | Query | 3 | Waiting for table metadata lock | select * from yetest2 limit 10 | +-- -+ 4 rows in set (0.00 sec)

Because transaction 1 opens the query, then the MDL lock is obtained, and the lock mode is SHARED_READ, and transaction 2 needs to acquire the EXCLUSIVE lock to execute DDL, which is mutually exclusive, so transaction 2 needs to wait. The query can be stuck, isn't it depressing? This is the scenario we migrated last time. Truncate table belongs to DDL, can be lock table metadata, and can even be upgraded from lock table to lock library.

3) uncommitted or rolled back after the transaction is displayed or implicitly opened, such as uncommitted or rolled back after the query is completed, will be blocked using alter

# SESSION Amysql > begin;mysql > select * from test2;# SESSION Bmysql > alter table test2 add test3 int; / / wait for SESSION A to finish execution; # SESSION Cmysql > show processlist +-+-| Id | User | Host | db | Command | Time | State | | Info | +-- +-| 267 | root | localhost | sbtest | Sleep | 36 | NULL | | 271 | root | localhost | sbtest | Query | 30 | Waiting for table metadata lock | alter table test2 add test3 int | | 272 | root | localhost | NULL | Query | 0 | starting | show processlist | | +-- -+ 3 rows in set (0.00 sec)

4) if there are failed query transactions on the table, such as query columns that do not exist, the statement failure returns, but the transaction is not committed, the alter will still be blocked.

# SESSION Amysql > begin;mysql > select error from test2; ERROR 1054 (42S22): Unknown column 'error' in' field list'# SESSION Bmysql > alter table test2 add test3 int; / / waiting for SESSION A to be submitted or rolled back; # SESSION Cmysql > show processlist +-+-| Id | User | Host | db | Command | Time | State | | Info | +-- +-| 267 | root | local | test | Sleep | 7 | NULL | 271 | root | local | test | Query | 3 | Waiting for table metadata lock | alter table test2 add test3 int | | 272 | root | local | NULL | Query | 0 | starting | show processlist | 311 | root | local | NULL | Sleep | | 413 | | NULL +-- + | -4 rows in set (0.00 sec) # SESSION Dmysql > select * from information_schema.innodb_trx Empty set (0.00 sec)

In fact, the transaction in SESSION An is not opened, but because the syntax of the statement used by select to obtain table metadata is valid, although the execution fails, it still does not release the metadata lock, thus causing the alter action of SESSION B to be blocked.

When you view the current open transaction through SESSION D, you will find that it is not there, so you cannot find the reason. So when this scenario occurs, how to determine which process is causing it, we can try to look at the table performance_schema. Events_statements_current, analyze the status of the process to determine.

Mysql > select * from performance_schema. Events_statements_current\ gateway * 1. Row * * THREAD_ID: 293 EVENT_ID: 32 END_EVENT_ID: 32 EVENT_NAME: statement/sql/select SOURCE: socket_connection.cc:211TIMER_START: 212721717099954000 TIMER_END: 212721717213807000 TIMER_WAIT : 113853000 LOCK_TIME: 0 SQL_TEXT: select error from test2 DIGEST: 0bbb2d5d1be45e77debea68111264885 DIGEST_TEXT: SELECT ERROR FROM `test2` CURRENT_SCHEMA: test OBJECT_TYPE: NULLOBJECT_ SCHEMA: NULLOBJECT_NAME: NULLMYSQL_ERRNO: 1054 RETURNED_SQLSTATE: 42S22 MESSAGE_TEXT: Unknown column 'error' in' field list' ERRORS: 1

Then find its sid, kill the session, or kill the session where the DDL is located to solve the problem.

In addition, SESSION A should explicitly open a transaction during the test, otherwise the query will be implicitly rolled back and the above scenario cannot be reproduced. After SESSION B executes the alter, it does not block immediately, but starts the copy to tmp table immediately. After this process is over, the MDL lock wait occurs. The execution of alter operation is mainly divided into three steps: create temporary new table-> insert data from old table-> temporary new table rename to old table. In this case, MDL locks are not needed until the last step, so there will be no blocking in the copy process. Since there is no query in progress, and the query does not enter the innodb layer (failure returns), show processlist and information_schema.innodb_trx have no information to refer to.

When the above situations occur, it will cause MDL if you do the following:

1. Create and delete indexes.

two。 Modify the table structure.

3. Table maintenance operations (optimize table, repair table, etc.).

4. Delete the table.

5. Gets the table-level write lock (lock table tab_name write) on the table.

This is the end of the content of "what are the common and serious MySQL problems". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.