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

1. Background

For the database system, it has always been the goal of the database system to improve the concurrency under the condition of multi-user concurrency while ensuring the consistency of the data. it is necessary to meet the needs of a large number of concurrent access and ensure the security of the data under this condition. in order to meet this goal, most databases are achieved through locking and transaction mechanisms, and MySQL databases are no exception. In spite of this, we will still encounter a variety of difficult problems in the process of business development. This article will demonstrate common concurrency problems in the way of cases and analyze solutions.

2. Slow query caused by table lock

First, let's look at a simple case where we query a piece of user information according to ID:

Mysql > select * from user where id=6

The total number of records in this table is 3, but it has been executed for 13 seconds.

The first thing that comes to mind with this problem is to look at the current state of the MySQL process:

You can see from the process that the select statement is waiting for a table lock, so what query generated the table lock? No direct correlation is shown in this result, but we can speculate that it is probably caused by that update statement (because there are no other suspicious SQL in the process). To confirm our guess, check the structure of the user table:

Sure enough, user tables use the MyISAM storage engine, MyISAM will generate a table lock before performing the operation, and then automatically unlock it after the operation is completed. If the operation is a write operation, the table lock type is a write lock, and if the operation is a read operation, the table lock type is a read lock. As you understand, write locks block other operations (including reads and writes), which makes all operations serial; in the case of read locks, read-read operations can be parallel, but read-write operations are still serial. The following example demonstrates the explicit specification of a table lock (read lock), read-read parallel, and read-write serial.

Explicitly turn on / off table locks, using lock table user read/write; unlock tables

Session1:

Session2:

You can see that session 1 enables table locks (read locks) to perform read operations, and session 2 can perform read operations in parallel, but write operations are blocked. Go on to see:

Session1:

Session2:

When session1 performs unlocking, seesion2 immediately begins to perform write operations, that is, read-write serial.

Summary:

At this point, we basically analyze the causes of the problem and summarize-- when the MyISAM storage engine performs operations, a table lock will occur, which will affect other users' operations on the table. If the table lock is a write lock, it will cause other users to operate serially, and if it is a read lock, other users' read operations can be carried out in parallel. So sometimes we come across a simple query that takes a long time to see if this is the case.

Solution:

1) try not to use the MyISAM storage engine. All the tables of the MyISAM storage engine have been removed in the MySQL8.0 version. It is recommended to use the InnoDB storage engine.

2) if you must use the MyISAM storage engine, reduce the time for write operations

3. What are the risks of modifying the table structure online?

If one day the business system needs to increase the length of a field, can it be modified directly online? Before answering this question, let's take a look at a case study:

The above statement attempts to modify the name field length of the user table, and the statement is blocked. By convention, let's check the current process:

You can see from the process that the alter statement is waiting for a metadata lock, which is probably caused by the above select statement, which is exactly the case. When performing DML (select, update, delete, insert) operations, a metadata lock is added to the table to ensure that the table structure is not modified during the query, so the above alter statement is blocked. What if you execute the alter statement first and then the DML statement in reverse order? Will the DML statement be blocked? For example, if I am modifying the table structure online, will the online DML statement be blocked? The answer is: not sure.

The online ddl function is provided in MySQL5.6, allowing some DDL statements and DML statements to be concurrent, and online ddl has been enhanced in the current version 5.7, which allows most DDL operations to be performed online. For details, see: https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

Therefore, whether the DML will be blocked during the execution of DDL for a particular scenario depends on the scenario.

Summary: through this example, we have a basic understanding of metadata lock and online ddl. If we need to modify the table structure online during business development, we can refer to the following solution:

1. Try to do it during the time when the volume of business is small.

2. Check the official documents and make sure that the table changes to be made can be concurrently with DML and will not block online business.

3. It is recommended to use percona's pt-online-schema-change tool, which is more powerful by the official online ddl. Its basic principle is: through insert. Select... A full copy of the statement is made, and the increment generated in the process of changing the table structure is recorded by the trigger, so as to achieve the purpose of changing the table structure.

For example, to make changes to table A, the main steps are:

Create an empty table with destination table structure, A_new

Create triggers on table A, including add, delete, and modify triggers

Through insert... Select... Limit N statement copies data to destination table in fragments

When the Copy is complete, rename the A_new table to table A.

4. Analysis of a deadlock problem

Deadlocks occur occasionally in an online environment, where two or more transactions wait for each other to release the lock, resulting in a situation in which the transaction can never be terminated. In order to analyze the problem, we will simulate a simple deadlock and then summarize some analysis ideas.

Demo environment: MySQL5.7.20 transaction isolation level: RR

Table user:

CREATE TABLE `user` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (300) DEFAULT NULL, `age` int (11) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

The following demonstrates the work of transaction 1 and transaction 2:

Transaction 1 transaction 2 transaction monitoring

T1

Begin

Query OK, 0 rows affected (0.00 sec)

Begin

Query OK, 0 rows affected (0.00 sec)

T2

Select * from user where id=3 for update

+-- +

| | id | name | age | |

+-- +

| | 3 | sun | 20 | |

+-- +

1 row in set (0.00 sec)

Select * from user where id=4 for update

+-- +

| | id | name | age | |

+-- +

| | 4 | zhou | 21 | |

+-- +

1 row in set (0.00 sec)

Select * from information_schema.INNODB_TRX

By querying the meta-database innodb transaction table, it is monitored that the number of running transactions is 2, that is, transaction 1 and transaction 2.

T3

Update user set name='' where id=4

Because the record of id=4 has been locked by transaction 2, this statement will block

The number of currently running transactions monitored is 2. T4 blocking state

Update user set name='hehe' where id=3

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

The record of id=3 has been locked by transaction 1, and the transaction holds the row lock of the record of id=4. At this time, the InnoDB storage engine detects a deadlock and the transaction is rolled back.

Transaction 2 is rolled back, transaction 1 is still running, and the number of monitoring transactions currently running is 1. T5

Query OK, 1 row affected (20.91 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Because transaction 2 is rolled back, the originally blocked update statement continues to execute.

The number of running transactions for monitoring is 1. T6

Commit

Query OK, 0 rows affected (0.00 sec)

Transaction 1 has been committed, transaction 2 has been rolled back, and the number of monitoring transactions currently running is 0.

This is a simple deadlock scenario in which transaction 1 and transaction 2 wait for each other to release the lock, and the InnoDB storage engine detects that the deadlock occurs and causes transaction 2 to roll back, which makes transaction 1 no longer wait for transaction B's lock and can continue execution. So how does the InnoDB storage engine detect deadlocks? To understand this problem, let's first check the status of the InnoDB at this time:

Show engine innodb status\ G

-LATEST DETECTED DEADLOCK----2018-01-14 12:17:13 0x70000f1cc000cycles * (1) TRANSACTION:TRANSACTION 5120, ACTIVE 17 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 3 lock struct (s), heap size 1136, 2 row lock (s) MySQL thread id 10, OS thread handle 123145556967424 Query id 2764 localhost root updatingupdate user set name='' where id=4*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user `trx id 5120 lock_mode X locks rec but not gap waitingRecord lock, heap no 5 PHYSICAL RECORD: n_fields 5 Compact format; info bits 00: len 4; hex 80000004; asc; 1: len 6; hex 00000013fa; asc; 2: len 7; hex 520000060129a6; asc R); 3: len 4; hex 68616861; asc ;;4: len 4; hex 80000015; asc

* (2) TRANSACTION:TRANSACTION 5121, ACTIVE 12 sec starting index readmysql tables in use 1, locked 13 lock struct (s), heap size 1136, 2 row lock (s) MySQL thread id 11, OS thread handle 123145555853312, query id 2765 localhost root updatingupdate user set name='hehe' where id=3*** (2) HOLDS THE LOCK (S): RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.user` trx id 5121 lock_mode X locks rec but not gapRecord lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4 Hex 80000004; asc; 1: len 6; hex 00000013fa; asc; 2: len 7; hex 520000060129a6; asc R); 3: len 4; hex 68616861; asc ;;4: len 4; hex 80000015; asc

* (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5121 lock_mode X locks rec but not gap waitingRecord lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 80000003; asc; 1: len 6; hex 00000013fee; asc; 2: len 7; hex 5500000156012f; asc U V /; 3: len 4; hex 68656865; asc hehe;;4: len 4; hex 80000014 asc;

* WE ROLL BACK TRANSACTION (2)

There are many indicators of InnoDB status. Here we can intercept the information related to deadlock, and we can see that InnoDB can output the deadlock information recently. In fact, many deadlock monitoring tools are also developed based on this feature.

In the deadlock information, it shows information about two transactions waiting for locks (blue for transaction 1 and green for transaction 2), focusing on: WAITING FOR THIS LOCK TO BE GRANTED and HOLDS THE LOCK (S).

WAITING FOR THIS LOCK TO BE GRANTED indicates the lock information that the current transaction is waiting for. From the output, transaction 1 is waiting for a row lock with heap no 5, and transaction 2 is waiting for a row lock with heap no 7.

HOLDS THE LOCK (S): indicates the lock information held by the current transaction. It can be seen from the output that transaction 2 holds a heap no of 5 rows of locks.

As you can see from the output, InnoDB finally rolls back transaction 2.

So how does InnoDB detect deadlocks?

We think that the easiest way is if a transaction is waiting for a lock, and if the waiting time exceeds the set threshold, then the transaction operation fails, which avoids the situation that multiple transactions wait for each other for a long time. The parameter innodb_lock_wait_timeout is used to set the wait time for this lock.

If you follow this method, it will take time to solve the deadlock (that is, wait to exceed the threshold set by innodb_lock_wait_timeout). This method is slightly passive and affects system performance. InnoDB storage engine provides a better algorithm to solve the deadlock problem, wait-for graph algorithm. To put it simply, when multiple transactions begin to wait for each other, the wait-for graph algorithm is enabled. The algorithm rolls back one of the transactions immediately after determining that it is a deadlock, and the deadlock is released. The advantage of this method is that the check is more active and the waiting time is shorter.

Here are the basic principles of the wait-for graph algorithm:

To make it easier to understand, we think of deadlocks as four cars blocking each other:

Four cars are treated as four transactions, waiting for each other's lock, resulting in a deadlock. The principle of wait-for graph algorithm is that transactions are regarded as nodes, and the lock waiting relationship between transactions is represented by directed edges. For example, transaction A waits for the lock of transaction B, and a directed edge is drawn from node A to node B. if the directed graph composed of A, B, C, D forms a loop, it is judged to be deadlock. This is the basic principle of wait-for graph algorithm.

Summary:

1. How to detect deadlocks if there is a deadlock in our business development? It has just been introduced that by monitoring the status of InnoDB, you can make a gadget to collect deadlock records for hindsight.

2. If there is a deadlock, how should the business system respond? From the above, we can see that when InnoDB detects a deadlock, it reports a Deadlock found when trying to get lock; try restarting transaction message to the client and rolls back the transaction. The application needs to restart the transaction according to this information, and save the field log for further analysis to avoid the next deadlock.

5. Analysis of lock waiting problem.

In business development, the probability of deadlock is small, but the probability of lock waiting is higher. Lock waiting is because one transaction takes up lock resources for a long time, while other transactions have been waiting for the previous transaction to release the lock.

Transaction 1 transaction 2 transaction monitoring

T1

Begin

Query OK, 0 rows affected (0.00 sec)

Begin

Query OK, 0 rows affected (0.00 sec)

T2

Select * from user where id=3 for update

+-- +

| | id | name | age | |

+-- +

| | 3 | sun | 20 | |

+-- +

1 row in set (0.00 sec)

Other query operations

Select * from information_schema.INNODB_TRX

By querying the meta-database innodb transaction table, it is monitored that the number of running transactions is 2, that is, transaction 1 and transaction 2.

T3 other query operations

Update user set name='hehe' where id=3

Because the record of id=3 is locked by transaction 1, the statement will block (that is, lock wait)

The number of currently running transactions monitored is 2. T4 other query operations

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The lock wait time exceeded the threshold and the operation failed. Note: transaction 2 is not rolled back at this time.

The number of currently running transactions monitored is 2. T5 commit; transaction 1 has been committed, transaction 2 has not been committed, and the number of currently running transactions monitored is 1.

From the above, it can be seen that transaction 1 holds the row lock of id=3 for a long time, transaction 2 produces lock waiting, and the operation is interrupted when the waiting time exceeds innodb_lock_wait_timeout, but the transaction is not rolled back. If we encounter lock waiting in business development, it will not only affect performance, but also challenge your business process, because your business side needs to do logical processing to adapt to lock waiting, whether to retry the operation or roll back the transaction.

In the MySQL metadata table, you can collect information about transactions and lock waiting, such as INNODB_LOCKS, INNODB_TRX and INNODB_LOCK_WAITS under the information_schema database. You can observe the lock waiting of your business system through these tables. You can also use the following statement to easily query the relationship between transactions and lock waits:

SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query wating_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query 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

Results:

Waiting_trx_id: 5132

Waiting_thread: 11

Wating_query: update user set name='hehe' where id=3

Blocking_trx_id: 5133

Blocking_thread: 10

Blocking_query: NULL

Summary:

1. Please monitor your business system for lock waiting, which will help you understand the current database lock situation and help you optimize your business program.

2. In the business system, appropriate logical judgment should be made on the situation of lock waiting timeout.

6. Summary

This paper introduces several common MySQL concurrency problems through several simple examples, and tries to find out how to solve these problems. Transaction, table lock, metadata lock and row lock are involved in this paper, but the concurrency problems are far more than these, such as transaction isolation level, GAP lock and so on. The real concurrency problems may be many and complex, but the troubleshooting ideas and methods can be reused. In this paper, we use show processlist;show engine innodb status; and query metadata table methods to troubleshoot and find problems. If the problem involves replication, we need to use master/slave monitoring to help.

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.