• Posts – 44, Articles – 0, Comments – 439
• Cnblogs
• Dashboard
• Login

• Home
• Contact
• Gallery
• RSS

Fluttering red scarf

2018-01-15 08:29 by Fluttering red Scarf, 4974 reading, 13 comments,collection.The editor

directory

1, the background

2, table lock caused by slow query problems

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

4. Analysis of a deadlock problem

5. Analysis of lock waiting problems

6, summary

1, the background

For the database system in multi-user concurrent conditions improve the concurrency and ensure the consistency of the data has always been the goal of the database system, to meet the needs of a large number of concurrent access and to ensure that under the condition of the data security, in order to satisfy this aim most database by locks and transaction mechanism, the MySQL database is no exception. However, we still encounter a variety of difficult problems in the process of business development. This article will demonstrate common concurrency problems and analyze solutions by way of case studies.

2, table lock caused by slow query problems

First let’s look at a simple example, query a user’s information by ID:

mysql> select * from user where id=6;

This table had a total of three records, but it took 13 seconds to execute.

MySQL > select * from ‘MySQL’;

The select statement is waiting for a table lock. What query is creating the table lock? No direct correlation is shown in this result, but we can assume that the update statement is most likely the result (since there are no other suspicious SQL statements in the process). To verify our guess, let’s examine the user table structure:

Table user uses MyISAM storage engine. MyISAM locks the table before the operation, and then automatically unlocks the table. If the operation is a write operation, the table lock type is write lock; if the operation is a read operation, the table lock type is read lock. As you can understand, the write lock blocks other operations (including reads and writes), which makes all operations serial; In the read lock case, the read – read operation can be parallel, but the read – write operation is still serial. The following example demonstrates an explicit table lock (read lock), read-read parallel, and read-write serial.

To explicitly enable/disable table locking, run the lock table user read/write command. unlock tables;

session1:

Session2:

You can see that session 1 enables the table lock (read lock) to perform the read operation, while session 2 can perform the read operation in parallel, but the write operation is blocked. Then look at:

session1:

session2:

After session1 is unlocked, Seesion2 immediately starts to perform write operations (read/write serial).

Conclusion:

If the table lock is a write lock, it will cause other users to operate the table in serial. If the table lock is a read lock, other users’ read operations can be parallel. So sometimes we run into a simple query that takes a long time to see if this is the case.

Solutions:

1) Try not to use MyISAM storage engine. In MySQL8.0, all tables of MyISAM storage engine have been removed. InnoDB storage engine is recommended.

2) If you must use MyISAM storage engine, reduce the write operation time;

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

If one day the business system needs to increase the length of a field, can it be directly modified online? Before we answer that question, let’s take a look at an example:

The above statement attempted to change the length of the name field in the user table, but blocked. As usual, let’s check the current process:

It can be seen from the process that the ALTER statement is waiting for a metadata lock, which is most likely caused by the select statement above, which it is. When performing DML (SELECT, UPDATE, DELETE, INSERT) operations, a metadata lock is added to the table to ensure that the table structure will not be modified during the query, so the above ALTER statement is blocked. What if the order of execution is reversed, with the ALTER statement executed first and the DML statement executed later? Can DML statements be blocked? For example, if I am modifying the table structure in the online environment, will the DML statement on the online environment be blocked? The answer is: not sure.

Online DDL functionality was introduced in MySQL5.6 to allow concurrent DDL and DML statements, and has been enhanced in the current 5.7 release to allow most DDL operations to take place online. See: dev.mysql.com/doc/refman/…

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

Summary: This example gives us a basic understanding of metadata locking and online DDL. If we need to modify the table structure online during business development, we can refer to the following solutions:

1, try to carry out in the time period of low business volume;

2. Check the official document to make sure that the table modification can be concurrent with DML without blocking online services;

The pt-online-schema-change tool from Percona is more powerful than the official ONLINE DDL. The basic principle is: insert… The select… The statement makes a full copy of the table and records the increments generated during the change of the table structure through triggers.

For example, to change table A, the main steps are as follows:

Create an empty table of the destination table structure, A_new; Create trigger on A table, including add, delete, change trigger; Through the insert… The select… After the Copy is complete, rename the A_new table to table A.

4. Analysis of a deadlock problem

Deadlocks occur occasionally in online environments. Deadlocks occur when two or more transactions wait for each other to release the lock, so that the transaction never terminates. To analyze the problem, we will simulate a simple deadlock situation and summarize some analysis ideas from it.

Demo environment: MySQL5.7.20 Transaction isolation level: RR

Table the user:

Here is how transactions 1 and 2 work:

This is a simple deadlock scenario where transaction 1 and transaction 2 wait for each other to release the lock. InnoDB storage engine detects the deadlock and rolls back transaction 2. This makes transaction 1 no longer wait for transaction B’s lock and can continue execution. So how does InnoDB storage engine detect deadlocks? To figure this out, let’s check InnoDB’s state:

show engine innodb status\G

————————

LATEST DETECTED DEADLOCK

————————

2018-01-14 12:17:13 0x70000f1cc000

*** (1) TRANSACTION:

TRANSACTION 5120, ACTIVE 17 sec starting index read

mysql tables in use 1, locked 1

LOCK 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 updating

update user set name=’haha’ 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 waiting

Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000004; asc ;;

1: len 6; hex 0000000013fa; asc ;;

2: len 7; hex 520000060129a6; asc R ) ;;

3: len 4; hex 68616861; asc haha;;

4: len 4; hex 80000015; asc ;;

*** (2) TRANSACTION:

TRANSACTION 5121, ACTIVE 12 sec starting index read

mysql tables in use 1, locked 1

3 lock struct(s), heap size 1136, 2 row lock(s)

MySQL thread id 11, OS thread handle 123145555853312, query id 2765 localhost root updating

update 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 gap

Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000004; asc ;;

1: len 6; hex 0000000013fa; asc ;;

2: len 7; hex 520000060129a6; asc R ) ;;

3: len 4; hex 68616861; asc haha;;

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 waiting

Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000003; asc ;;

1: len 6; hex 0000000013fe; 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)

InnoDB state has many indicators, here we intercept the deadlock related information, we can see InnoDB can output the latest deadlock information, in fact, many deadlock monitoring tools are based on this function.

WAITING FOR THIS LOCK TO BE GRANTED (blue) and holding THE LOCK(S) (green).

WAITING FOR THIS LOCK TO BE GRANTED Transaction 1 is WAITING FOR a row LOCK whose HEAP NO is 5 and transaction 2 is WAITING FOR a row LOCK whose heap NO is 7.

HOLDS THE LOCK(S) : HOLDS THE LOCK(S) of THE current transaction. THE heap no of transaction 2 is 5 rows.

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

So how does InnoDB detect deadlocks?

The simplest approach we can think of is if a transaction is waiting for a lock, and if the wait time exceeds a set threshold, the transaction fails. This avoids the situation where multiple transactions are waiting for each other for a long time. The innodb_lock_WAIT_timeout parameter is used to set the lock wait time.

InnoDB storage engine provides a better algorithm to solve the deadlock problem, the wait-for graph algorithm. To put it simply, when multiple transactions start waiting for each other, the wait-for graph algorithm is enabled. The algorithm determines that one of the transactions is a deadlock and immediately rolls back the deadlock. The advantage of this method is that the inspection is more active and the waiting time is shorter.

Here are the basics of the wait-for Graph algorithm:

For the sake of understanding, consider a deadlock as a scenario where four cars are blocking each other:

Four vehicles act as four transactions, waiting for each other’s locks, resulting in deadlocks. The principle of wait-for graph algorithm is to regard transactions as nodes, and the lock-waiting relationship between transactions is represented by directed edges. For example, if transaction A waits for the lock of transaction B, A directed edge is drawn from node A to node B. In this way, if A directed graph composed of A, B, C, and D forms A ring, it is judged to be deadlocked. This is the basic principle of the Wait-for Graph algorithm.

Conclusion:

1. How to check out deadlocks in our business development? By monitoring InnoDB state, you can create a widget that collects deadlock records for later viewing.

2. How should the business system deal with deadlocks? InnoDB displays a Deadlock found when trying to get lock to the client. If the transaction information is restarted and the transaction is rolled back, the application ends need to restart the transaction based on the information and save onsite logs for further analysis to avoid the next deadlock.

5. Analysis of lock waiting problems

In business development, the occurrence probability of deadlock is small, but the occurrence probability of lock wait is large. Lock wait is because one transaction occupies lock resources for a long time, while other transactions wait for the previous transaction to release the lock.

Innodb_lock_wait_timeout: innodb_lock_WAIT_timeout: innodb_lock_wait_timeout: Innodb_lock_wait_timeout; If we encounter lock wait in business development, it will not only affect performance, but also pose a challenge to your business process, because your business side needs to adapt to the situation of lock wait logic processing, whether to retry operations or rollback transactions.

Transaction and lock waits information is collected in MySQL metadata tables such as INNODB_LOCKS, INNODB_TRX, and INNODB_LOCK_WAITS in the Information_SCHEMA database. You can use these tables to monitor lock waits in your business system. You can also easily query the relationship between transaction and lock wait by using the following statement:

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

Conclusion:

1. Please monitor the lock waiting of your business system, which will help you understand the current database lock situation and provide help for you to optimize business programs;

2. Logical judgment should be made on lock timeout in business system.

6, summary

This article introduces several common MySQL concurrency problems through a few simple examples, and tries to get our troubleshooting ideas for these problems. This article covers transactions, table locks, metadata locks, and row locks, but there are far more concurrency issues, such as transaction isolation levels, GAP locks, and so on. Real concurrency problems can be numerous and complex, but the troubleshooting ideas and methods are reusable. In this article, we used show ProcessList; show engine innodb status; And query metadata tables to troubleshoot problems. If problems involve replication, use master/slave monitoring to assist.

References:

InnoDB Storage Engine by Jiang Chengyao

MySQL Troubleshooting Guide by Hongzhe Li

Insert into hedengcheng.com

= = = = = = = = = = = = = = = = = = = = = = = = I am advertising = = = = = = = = = = = = = = = = = = = = = = =

About

Fluttering red scarf



Six years and five months



Recommend the blog



846



0
+ add attention

The latest comments

• Re: Analysis and design of commodity model in e-commerce system
  
  (The previous post was intended to be posted yesterday, but the result was that the new registered user did not post any comments that day.) In fact, the specification table should also be bound to the classification, binding rules and attributes. Otherwise, if I input iPhone, how can package 1, package 2, as well as the differences between the goods and the Hong Kong version appear in the specifications? After all, it would be weird to choose a dress. — function0917
• Re: Analysis and design of commodity model in e-commerce system
  
  # binding question classification and attribute tables, know about the # — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — classification of commodity classification is infinite, and attributes of goods should be flat. For example, shoes (cid=1) – shoes (cid=1001); shoes (cid=1001); shoes (cid=1001); The category ID (field CID) in my product property table should be the CID of shoes or high heels. — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — books read hundreds of times its just see, suddenly think of a solution in the process of typing, “high”, just as its name implies is that if the shoes and high heels can has the properties such as color, size, higher level then the binding of cid. In the future, high heels can have attributes = their own special attributes (generally high + relatively high + extremely low + I really can’t think of more high heels. + All attributes of the parent category (no matter how many parents, take it. — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — the problem of bloggers? — function0917
• Re: In-depth understanding of the JVM (7) – performance monitoring tools
  
  Mark — Xiaoyu 2017
• Re: In-depth understanding of the JVM (I) — Fundamentals
  
  Hello, can I reprint your article? It will indicate the author of the original text, link to the original text. Thank you ~ – Engineer – stranded
• Re: In-depth understanding of the JVM (iv) – garbage collection algorithms
  
  May I ask what hareachable objects and unreachable objects refer to? — Mainthing

Archives of essays

• January 2018 (2)
• October 2017 (1)
• September 2017 (4)
• August 2017 (7)
• June 2015 (1)
• January 2015 (2)
• October 2014 (2)
• September 2014 (2)
• May 2014 (1)
• March 2014 (2)
• January 2014 (1)
• September 2013 (1)
• August 2013 (2)
• May 2013 (1)
• April 2013 (1)
• March 2013 (1)
• December 2012 (1)
• November 2012 (1)
• September 2012 (1)
• June 2012 (2)
• May 2012 (4)
• March 2012 (1)

The calendar

My label

• Maven(3)
• Jenkins(2)
• Nexus(2)
• Sonar(2)
• Svn(2)
• Tomcat(2)
• Concurrent (1)
• Concurrent optimistic lock pessimistic lock (1)
• Large Sites (1)
• PMD JDepend Eclemma Metric(1)
• More and more

Classification of essays

• Apache Mina(1)
• Eclipse(1)
• Hibernate(2)
• Java(19)
• JVM(8)
• MongoDB(2)
• MySQL(4)
• RCP/SWT/Jface(1)
• SOA(1)
• Spring(3)
• Continuous Integration (4)
• Large Sites (3)
• Multithreading (1)
• Open Source Project (2)
• Agile (1)
• Other (7)
• Design Patterns (1)
• Data Structure/Algorithm (1)
• System Architecture (3)
• (1)
• Refactoring (1)

Recommended leaderboard

• 1. Evolution of Large Web Site Architecture (211)
• 2. The Soul of large Websites — Performance (63)
• 3. Analysis and Design of Commodity Model in E-commerce System — Continued (51)
• 4. Analysis and Design of commodity Model in E-commerce System (47)
• 5. Made two database monitoring tools and planned to open source in the near future (39)

Reading leaderboards

• 1. Evolution of Large Web Site Architecture (48975)
• 2. Analysis and Design of Commodity Model in E-commerce System (15338)
• 3. The Soul of large Websites – Performance (15165)
• 4. Build continuous integration environment with Maven+Nexus+Jenkins+Svn+Tomcat+Sonar (1)
• 5. In-depth Understanding of the JVM (I) — Fundamentals (14068)

www.spiga.com.mx

Copyright ©2018 Fluttering red scarf

Blog garden