"MySQL" Lock problem best practices

More locks have been dealt with in recent times, including lock waits that cause business connections to accumulate or time out, deadlocks cause business failures, and so on, which can have a serious impact on the business, and users who have no experience in dealing with them often fail to do so. The following will be from the entire database design, development, operation and maintenance phase describes how to avoid the occurrence of lock problems, provide some best practices for readers to reference.

Design phase

In the database design phase, improper engine selection and index design can lead to more serious lock or deadlock problems after the business goes live.

1. The table engine chooses to use MyISAM, which raises table level lock wait.

Starting with version 5.5, MySQL officially shifted the default engine from MyISAM to InnoDB, the main difference between the two engines:

Because the MyISAM engine only supports table lock, during the use of the MyISAM engine table, when a query with a long execution time in the database becomes clogged with the update action on the table, it is often encountered that the thread session is in table-level lock waiting (waiting for table Lock), in severe cases where the application is not properly connected due to the full number of instance connections

CREATE TABLE ' T_myisam ' (' id ' int (one) default NULL) Engine=myisam default Charset=utf8;query |111 | User Sleep              | Select Id,sleep from T_myisam   | Query |108 | Waiting for table level lock | Update T_myisam set id=2 where id=1| Query |  3 | Waiting for table level lock | Update T_myisam set id=2 where id=1|

As can be seen from the above example, the T_myisam table is the MyISAM storage engine, when the table has a long execution time of the query statement at the time of execution, the table on the other updates are blocked, this time the application or database connection quickly exhausted, resulting in application request failure. The quick way to recover this time is to kill the thread id:111 (you can do show processlist to see all the connection states of the current database). In addition, the table index of the MyISAM storage engine causes the index to be corrupted when the instance is closed unexpectedly, and the table must be repair, which also blocks all requests on the table.

2. The table index was improperly designed, causing the database to deadlock.

Index design is a very important part of database design, not only related to the performance of subsequent business, if the design is improper can also lead to business deadlock. The following is a case of online system, the database in the concurrent update of the deadlock, the location of the update by locating using two indexes caused by the deadlock information as follows:

(1) transaction:transaction 29285454235, ACTIVE 0.001 sec fetching rowsmysql tables in use 3, locked 3LOCK WAIT 6 lock struct (s), heap size 1184, 4 row lock (s) MySQL thread ID 6641616, OS thread handle 0x2b165c4b1700, query ID 28190427937 10 .103.180.86 test_ebs searching rows for updateupdate test SET is_deleted = 1 WHERE group_id = 1332577 and test_id = 458060 5*** (1) Waiting for the This LOCK to be granted:record LOCKS Space ID, page no 37122 n bits 352 index ' PRIMARY ' of table ' Testdriver '. ' Test ' Trx ID 29285454235 lock_mode X locks Rec but not gap Waitingrecord lock, heap no 179 physical record:n _fields 8; Compact format; Info bits 0*** (2) transaction:transaction 29285454237, ACTIVE 0.001 sec fetching rows, thread declared inside InnoDB 4980 MySQL tables in use 3, locked 3 5 lock struct (s), heap size 1184, 3 row lock (s) MySQL thread ID 6639213, OS thread handle 0 x2b1694cc2700, Query ID 28190427939 10.103.180.113 test_ebs searching rows for updateupdate test SET is_deleted = 1 WHERE group_id = 1332577 and test_id = 4212859*** (2) holds the LOCK (S): RECORD LOCKS Space ID page no 37122 n bits 352 in Dex ' PRIMARY ' of table ' testdriver '. ' Test ' Trx ID 29285454237 lock_mode X locks Rec but not gaprecord lock, heap no 179 PH Ysical Record:n_fields 8; Compact format;

Table structure:

CREATE TABLE ' test ' (' ID ' bigint () not NULL auto_increment COMMENT ' primary key ', ' test_id ' bigint () DEFAULT NULL, ' group_id ' big int () default null COMMENT ' Id, corresponding to Test_group.id ', ' gmt_created ' datetime DEFAULT null COMMENT ' creation time ', ' gmt_modified ' DateTime default NULL COMMENT ' Modified time ', ' is_deleted ' tinyint (4) Default ' 0 ' COMMENT ' delete. ', PRIMARY key (' ID '), key ' Idx_testid ' (' test_id '), key ' Idx_groupid ' (' group_id ')) Engine=innodb auto_increment=7429111 ;

SQL Execution Plan:

Mysql>explain UPDATE Test SET is_deleted = 1 WHERE group_id = 1332577 and test_id = 4212859| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra | | 1 | Simple | Test | Index_merge | Idx_testid,idx_groupid | Idx_testid,idx_groupid | 9,9 | | 1 | Using intersect (IDX_TESTID,IDX_GROUPID); Using where; Using Temporary |

So the first transaction is based on the group_id index, the primary ID is locked, then the primary ID is locked according to the test_id index, the second transaction is based on the test_id index, the primary ID is locked, and then according to the group_id index, To lock the primary ID, so that a concurrent update could result in a dead index.

MySQL official has also confirmed this bug:77209, there are two ways to solve the problem:

• Plus test_id + group_id combination index, so you can avoid the index merge;
• Optimizes the index merge of the optimizer to close.

Development phase

Transaction processing time is too long, resulting in concurrent lock waits.

Concurrent transactions are often seen in the database of the application scenarios, in this scenario, the need to avoid large transactions, long transactions, complex transactions caused transactions in the database to run longer, transaction time is longer, resulting in transaction lock holding time is longer, affecting the overall database throughput. In the following example, a large number of update waits occur in the user's business database, resulting in a large number of business super-times errors:

Troubleshoot problems

1. Use show processlist to determine the thread in which the lock waits and the SQL information:

| Query|37|updating| UPDATE test_warning SET ... WHERE test_id = ' 46a9b '

2. Through the lock waits in the InnoDB's INFORMATION_SCHEMA database and the transaction attempt, the relevant lock information is detected:

Select r.trx_mysql_thread_id waiting_thread,       r.trx_id waiting_trx_id,       r.trx_query waiting_query,       B.trx _id blocking_trx_id,       b.trx_query blocking_query,       b.trx_mysql_thread_id blocking_thread,       B.trx_ started ,       b.trx_wait_started from  information_schema.innodb_lock_waits w INNER join Information_schema.innodb_trx b On b.trx_id= w.blocking_trx_id INNER join Information_schema.innodb_trx R on r.trx_id= w.requesting_trx_id \Gwaiting_ Thread:318984063waiting_trx_id:26432631waiting_query:update test_warning SET ..... WHERE test_id = ' 46a9b ' blocking_trx_id:26432630blocking_query:nullblocking_thread:235202017trx_started:2016-03-01 13:54:39

From the lock waiting information described, transaction 26432631 is blocked by 26432630, then we can from the general log to find out what the transaction 26432630 did.

3. From the general log of the early open database to find the above BLCOK UPDATE statement in the log location, found the root cause of the update Blcok: two different SQL in the log concurrently update the same record, So after the updated SQL will wait for the previous update of SQL, if SQL1 is located in the transaction 1 is not committed, then transaction 2 will wait, so that the status of the above updating

235202017 Query UPDATE test_warning ..... where test_id = ' 46a9b ' 318984063 Query UPDATE test_warning .... task_order_id = ' where test_id = ' 46a9b '

So we can look at the context of this transaction:

The SQL context for thread id=235202017:

235202017 Query set autocommit=0235202017 (13:54:39) query UPDATE test_warning SET .... WHERE test_id = ' 46a9b ' 235202017 Query commit

The SQL context for thread id=318984063:

318984063 Query SET autocommit=1318984063 (13:54:39) query SELECT .... From Test_waybill WHERE (test_id in (' 46a9b ')) 318984063 Query SELECT ... From Test_waybill WHERE test_id = ' 46a9b ' 318984063 Query UPDATE test_warning SET ..... WHERE test_id = ' 46a9b ' 318984063 (13:55:31) Query UPDATE test_waybill_current t ..... WHERE t.test_id in (' 46a9b ') 318984063 Query SET autocommit=0

You can see that transaction 1 starts from 13:54:39 until 13:55:30, and transaction 2 has the same record in update Transaction 1, so until transaction 1 commits, transaction 2 is executed, and with such a log, this information is sent to the user to find the problem quickly. In transaction 1, due to the existence of other business logic, the submission of transaction 1 was not completed, which led to the other business locks.

Maintenance phase

DDL operations are queried for block.

When the application goes online into the maintenance phase, there will be more database change operations, such as adding fields, adding indexes and other operations, this kind of operation caused the lock failure is very frequent, the following will introduce a case, a DDL operation is queried block, resulting in database connection accumulation:

Query |6 | User Sleep                       | Select ID, sleep from tquery |4 | Waiting for Table metadata lock  | ALTER TABLE t add column gmt_create Datetimequery | Waiting for Table metadata lock  | select * from t where Id=1query | Waiting for Table metadata lock  | select * from t where Id=2query | Waiting for Table metadata lock  | Update t set ID =2 where id=1

Metadata lock wait means: To maintain data consistency for table metadata in a concurrency environment, you cannot write to metadata when there are active transactions (explicit or implicit) on the table. So MySQL introduced metadata lock to protect the table's metadata information. Therefore, if there is an active transaction on the table (uncommitted or rollback) on the table, the requested write session waits at Metadata lock wait.

Common factors that cause Metadata lock Wait Wait include: active transaction, there is currently a long query on the table, show or implicitly open the transaction is not committed or rollback, such as after the completion of the query is not committed or rollback, the table has failed query transactions.

In the above case, the thread state of the query, update, and DDL operations is waiting for table metadata lock, the operation of table T is all blocked, the front-end flow of requests to reach the database, this time the database connection is easily filled, So let's analyze why these locks are waiting:

• Alter-action lock wait: Due to an action to add a field on table T, the operation will get a metadata lock on the table before the end, but there is already a query on the table that has not been completed, causing metadata lock to never get it, So the Add Field operation can only wait for the query to end, which explains why the alter operation has a status of waiting for table metadata lock.
• Lock waits for queries and updates: Because the alter operation previously attempted to acquire metadata lock on the T table, subsequent query and update operations on table T are blocked by the alter operation when acquiring metadata lock. This causes these thread states to be waiting for table metadata lock.

The workaround is to kill thread 6.

Summarize

Lock problem is a very common problem, we need to be in the database development, design, management of all stages need to pay attention to prevent the situation, to be aware of.

Design and development phase:

1. Table design to avoid using the MyISAM storage engine, switch to InnoDB engine;
2. Create an appropriate index for SQL to avoid multiple single-column index execution errors;
3. Avoid large transactions, long transactions, and complex transactions that cause transactions to run longer in the database.

Manage Operations Phase:

1. Perform the above operations during low peak periods, such as creating a delete index;
2. Before the structure changes, observe whether there are long-running SQL, uncommitted transactions in the database;
3. During a fabric change, there is a lock wait for the thread state of the monitoring database.

"MySQL" Lock problem best practices