Shulou(Shulou.com)05/31 Report--

This article mainly explains "the analysis of business hang housing problems caused by mysql autocommit=0". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the analysis of business hang housing problems caused by mysql autocommit=0".

Background

A user reported that a normal select statement was held up by hang and the execution timed out. After finding out, it is found that it is caused by the improper use of autocommit. Here, case is simplified to explain the steps and reasons for recurrence.

Reappearance

Session1 builds tables and inserts data: create table if not exists t (id int primary key, c int); set autocommit=0;insert into t values (1Magne1); insert into t values (2Magin2); insert into t values (3Power3); commit;select count (*) from t; the purpose of this execution process is very intuitive, creating tables, inserting data, and querying results. There seems to be no problem. To maintain session1, create a new connection session2 and execute create table if not exists t (id int primary key, c int); the statement is in a waiting state. Create a new connection session3 and execute select count (*) from t; the statement is waiting. So from a business point of view, a select statement is resided by hang.

Cause analysis

MySQL Tips: if some statements in the service cannot be executed, the first step in tracking down the problem is to keep the site first. Pstack > tmplog is a common method. The stack of the two waiting threads, such as # 0 0x000000310ce0b7bb in pthread_cond_timedwait@@GLIBC_2.3.2 () from / lib64/libpthread.so.0#1 0x000000000063ba46 in MDL_wait::timed_wait (THD*, timespec*, bool, char const*) () # 2 0x000000000063e095 in MDL_context::acquire_lock (MDL_request*, unsigned long) (), can be seen blocking MDL_wait. Briefly explain what MDL is. Imagine that if a statement changes the table structure during the execution of a query on a table, or if the table is drop, you will get an incorrect result. Therefore, the accessed table structure needs to be protected during the duration of a transaction. This is meta data lock (MDL). It is easy to understand that to add, delete, change and check table data, you need to add read locks to MDL, and write locks to modify table structure, delete tables, and so on. MySQL Tips: MDL is a mechanism added only 5.5 years ago, and the case of this article will not be reproduced in version 5.1. MySQL Tips: the time to apply for MDL in a transaction is when it is used for the first time, and the time to release is after the end of the transaction. In other words, the reason for the case at the beginning of the article is that session2 is waiting to add a write lock. Although session3 only adds a read lock, it conflicts with session2 and needs to wait.

Transactions of session1

That is to say, session1 also holds the MDL read lock of table t. But our business has obviously been commit. A common misunderstanding is involved here. I have read an article before that it is possible to start a transaction with set autocommit=0. In fact, this description is not accurate. MySQL Tips: set autocommit=0 sets this thread to non-autocommit mode. At the end of each transaction, a new transaction is automatically created at the beginning of the next statement. This means that the last select count (*) operation of session1 actually implied a begin operation before. Because the transaction is not committed, session1 holds the MDL read lock for table t. Therefore, the advice for the business side is to commit these read transactions in a timely manner, or disconnect. MySQL Tips: when the connection is disconnected, MySQL automatically rolls back the currently uncommitted transactions. Since the last transaction of session1 in this case is only a select statement, the rollback does not affect the business. Thank you for your reading, the above is the content of "the analysis of business hang housing problems caused by mysql autocommit=0". After the study of this article, I believe you have a deeper understanding of the analysis of business hang housing problems caused by mysql autocommit=0, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.