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

This article mainly introduces "the concept of MySQL global lock, table lock and row lock". In daily operation, I believe many people have doubts about the concept of MySQL global lock, table lock and row lock. The editor consulted all kinds of data and sorted out simple and useful operation methods. I hope it will be helpful to answer the doubts about "the concept of MySQL global lock, table lock and row lock". Next, please follow the editor to study!

Global lock

As the name implies, a global lock is a lock on the entire database instance. MySQL provides a way to add a global read lock, and the command is Flush tables with read lock (FTWRL). You can use this command when you need to make the entire library read-only, and then the following statements from other threads will be blocked: data update statements (data additions and deletions), data definition statements (including creating tables, modifying table structures, etc.), and commit statements for update transactions.

A typical usage scenario for a global lock is to make a logical backup of the entire library. That is, select each table of the whole library and save it as text.

In the past, it was a practice to make sure that no other thread would update the database through FTWRL, and then back up the entire library. Note that the entire library is completely read-only during the backup. But making the whole library read-only sounds dangerous:

If you back up on the main database, no updates can be performed during the backup, and the business will basically have to shut down.

If you are backing up on the slave library, the binlog synchronized from the master library cannot be executed from the slave library during the backup, which will cause master-slave delay.

It doesn't seem to be good to add a global lock. But when you think about it, why should backups be locked? Let's see what's wrong with leaving it unlocked.

Suppose you want to maintain CSDN's purchase system now, focusing on user account balance tables and user course schedules. Now initiate a logical backup. Suppose that during the backup, there is a user who buys a course, deducts his balance in the business logic, and then adds a course to the purchased course.

What if the chronological order is to back up the account balance table (u_account), then the user buys it, and then backs up the user's course schedule (u_course)? You can take a look at this picture:

As you can see, in this backup result, the data status of user An is that the account balance is not deducted, but there is already one more course in the user's course schedule. If you use this backup to recover the data later, user A will find that he has made money.

Don't think it's good to be a user. Think about it: what might happen if you back up the user's course schedule and then the account balance table in the reverse order?

That is, without locking, the resulting library backed up by the backup system is not a logical point in time, and this view is logically inconsistent.

Speaking of views, you must remember that when we talked about transaction isolation earlier, there was actually a way to get a consistent view, right? Is to start a transaction under the repeatable readable isolation level.

The official logical backup tool is mysqldump. When mysqldump uses the parameter-single-transaction, a transaction is initiated before the import data to ensure that a consistent view is obtained. Due to the support of MVCC, the data can be updated normally during this process.

You must be wondering why you still need FTWRL with this feature. Consistent reading is good, but only if the engine supports this isolation level. For example, for an engine that does not support transactions such as MyISAM, if there are updates during the backup, only the latest data can always be fetched, which breaks the consistency of the backup. At this point, we need to use the FTWRL command.

Therefore, the single-transaction method applies only to libraries where all tables use the transaction engine. If some tables use an engine that does not support transactions, then the backup can only be done through the FTWRL method. This is often one of the reasons why DBA requires business developers to use InnoDB instead of MyISAM.

You might ask, since you want the entire library to be read-only, why not use set global readonly=true? It is true that readonly can also make the entire library read-only, but I would recommend you to use FTWRL for two main reasons:

First, in some systems, the value of readonly is used for other logic, such as to determine whether a library is a master or a standby. Therefore, the way you modify the global variable has a greater impact, and I do not recommend you to use it.

Second, there are differences in exception handling mechanism. If the client disconnects abnormally after executing the FTWRL command, MySQL automatically releases the global lock and the entire library returns to a state that can be updated normally. After the whole library is set to readonly, if an exception occurs on the client, the database will remain in the readonly state all the time, which will cause the whole library to be unwritable for a long time and the risk is high.

The update of business is not only the addition, deletion and modification of data (DML), but also the operation of adding fields and other operations to modify the table structure (DDL). Either way, after a library is locked globally, you will be locked if you add fields to any table in it.

However, even if it is not globally locked, adding fields is not plain sailing, because you will encounter the table-level locks we will introduce next.

Table level lock

There are two types of table-level locks in MySQL: table locks and metadata locks (meta data lock,MDL).

The syntax of a table lock is lock tables. Read/write . Similar to FTWRL, locks can be released actively with unlock tables or automatically when the client is disconnected. It should be noted that the lock tables syntax not only restricts the reading and writing of other threads, but also defines the next operation objects of this thread.

For example, if the statement lock tables T1 read, T2 write; is executed in one thread A, the statements written by other threads to T1 and read to T2 will be blocked. At the same time, thread A can only read T1 and write T2 before executing unlock tables. You are not even allowed to write T1, and naturally you cannot access other tables.

In the absence of finer-grained locks, table locks are the most common way to handle concurrency. For InnoDB, an engine that supports row locks, the lock tables command is generally not used to control concurrency. After all, the impact of locking the entire table is still too great.

Another type of table-level lock is MDL (metadata lock). MDL does not need to be used explicitly and is automatically added when accessing a table. The function of MDL is to ensure the correctness of reading and writing. You can imagine that if a query is traversing the data in a table and another thread changes the table structure and deletes a column during execution, then the result obtained by the query thread does not match the table structure.

Therefore, MDL is introduced in MySQL version 5.5. when adding, deleting, changing and checking a table, add MDL read lock; when you want to change the structure of the table, add MDL write lock.

Read locks are not mutually exclusive, so you can have multiple threads to add, delete, change and query a table at the same time.

The read-write lock and the write lock are mutually exclusive to ensure the security of the operation of the change table structure. Therefore, if two threads want to add fields to a table at the same time, one of them will have to wait for the other to finish execution before starting execution.

Although MDL locks are added by default, it is a mechanism that you can't ignore. For example, in the following example, I often see people fall into this pit: add a field to a small table, causing the whole library to fail.

You must know that adding fields to a table, or modifying fields, or indexing, requires scanning the data of the entire table. When operating on a large table, you must be very careful not to affect the online service. In fact, even if it is a small watch, there will be problems with careless operation. Let's take a look at the following operation sequence, assuming that table t is a small table.

We can see that session A starts first, and then a MDL read lock is added to table t. Because session B also requires an MDL read lock, it can be executed normally. Session C will then be blocked because session A's MDL read lock has not been released, while session C needs a MDL write lock, so it can only be blocked.

It doesn't matter if only session C is blocked, but then all requests for new MDL read locks on table t will also be blocked by session C. As we said earlier, all additions, deletions, changes and queries to the table need to first apply for a MDL read lock, which is locked, which means that it can not be read or written at all.

If there are frequent query statements on a table and the client has a retry mechanism, that is, a new session request will be issued after the timeout, the threads of the library will soon be full. You should know by now that the MDL lock in a transaction is applied at the beginning of statement execution, but is not released immediately at the end of the statement, but is not released until the entire transaction is committed.

How to safely add fields to a small table?

First of all, we have to solve the long transaction, the transaction does not commit, it will always occupy the MDL lock. You can find the currently running transactions in the innodb_trx table of MySQL's information_schema library. If the table on which you want to make DDL changes happens to have a long transaction, consider pausing DDL first, or kill dropping the long transaction.

But consider this scenario. If the table you want to change is a hot spot table, although the amount of data is small, but the above requests are very frequent, and you have to add a field, what should you do?

Kill may not work at this time, because a new request will come soon. The ideal mechanism is to set the waiting time in the alter table statement. If you can get the MDL write lock within this specified waiting time, it is best to give up if you can't get it or block the following business statements. The developer or DBA then repeats the process with a retry command.

MariaDB has incorporated this feature of AliSQL, so both open source branches currently support the syntax DDL NOWAIT/WAIT n.

ALTER TABLE tbl_name NOWAIT add column... ALTER TABLE tbl_name WAIT N add column... Row lock

MySQL's row locks are implemented by each engine itself at the engine layer. However, not all engines support row locks. For example, MyISAM engines do not. Not supporting row locks means that concurrency control can only use table locks. For tables of this kind of engine, only one update can be performed on the same table at any time, which will affect the degree of business concurrency. InnoDB supports row locks, which is one of the important reasons why MyISAM has been replaced by InnoDB.

Today we will mainly talk about InnoDB's row locks and how to improve business concurrency by reducing lock conflicts.

As the name implies, a row lock is a lock for a row record in a data table. This is easy to understand, for example, transaction A updates a row, and transaction B updates the same row at this time, it must wait for transaction A to complete the operation before updating.

Of course, there are some less obvious concepts and designs in the database, which, if understood and used improperly, can easily lead to unexpected behavior of the program, such as two-phase locks.

Starting from the two-stage lock

Let me give you an example. In the following operation sequence, what happens when the update statement of transaction B is executed? Assume that the field id is the primary key of table t.

The conclusion of this question depends on which locks transaction A holds after executing two update statements and when it is released. You can verify that transaction B's update statement is actually blocked and transaction B cannot continue execution until transaction An executes commit.

Knowing this answer, you must know that the row locks of both records held by transaction A were released at the time of commit. That is, in InnoDB transactions, row locks are added when needed, but not immediately when they are not needed, but not until the end of the transaction. This is the two-phase locking protocol.

Knowing this setting, how does it help us to use transactions? That is, if you need to lock multiple rows in your transaction, try to put back the locks that are most likely to cause lock conflicts and affect concurrency. Let me give you an example.

Suppose you are in charge of implementing an online movie ticket trading business, and customer A wants to buy movie tickets at cinema B. To simplify, this business needs to involve the following operations:

Deduct the movie fare from the balance of customer An account

Increase the ticket price of this movie to the account balance of Cinema B

Keep a transaction log.

In other words, to complete the transaction, we need to update two records and insert one record. Of course, in order to ensure the atomicity of the transaction, we need to put these three operations in one transaction. So, how would you arrange the order of these three statements in the transaction?

Imagine that if another customer C wants to buy a ticket at cinema B at the same time, then the conflict between the two transactions is sentence 2. Because they want to update the balance of the same cinema account, they need to modify the same line of data.

According to the two-phase locking protocol, no matter how you arrange the statement order, all the row locks required by the operation are released when the transaction is committed. So, if you put statement 2 at the end, for example, in the order of 3, 1, 2, then the cinema account balance has the least lock time. This minimizes lock waiting between transactions and improves concurrency.

Well, now because of your correct design, the line lock of the cinema balance line will not stay in a transaction for long. However, this does not completely solve your problem.

If this cinema does activities, you can pre-sell all movie tickets for a year at a low price, and the activity is only for one day. So at the beginning of the activity time, your MySQL hung up. As soon as you log on to the server, the CPU consumption is close to 100%, but the entire database executes less than 100 transactions per second. What is the reason for this?

Here, I'm going to talk about deadlocks and deadlock detection.

Deadlock and deadlock detection

When different threads in the concurrent system have cyclic resource dependencies, and the threads involved are waiting for other threads to release resources, it will cause these threads to enter a state of infinite waiting, which is called deadlock. Here I'll use the row lock in the database as an example.

At this point, transaction An is waiting for transaction B to release id=2 's row lock, while transaction B is waiting for transaction A to release id=1 's row lock. Transaction An and transaction B enter a deadlock state while waiting for each other's resources to be released. When a deadlock occurs, there are two strategies:

One strategy is to go straight into waiting until it times out. This timeout can be set by the parameter innodb_lock_wait_timeout.

Another strategy is to initiate deadlock detection and actively roll back a transaction in the deadlock chain after the deadlock is found so that other transactions can continue to execute. Setting the parameter innodb_deadlock_detect to on means that this logic is turned on.

In InnoDB, the default value for innodb_lock_wait_timeout is 50s, which means that if the first strategy is adopted, it will take 50s for the first locked thread to exit after a deadlock, and then other threads can continue to execute. For online services, this waiting time is often unacceptable.

However, it is impossible for us to directly set this time to a very small value, such as 1s. In this way, when there is a deadlock, it can be unlocked very quickly, but what if it is not a deadlock, but a simple lock waiting? Therefore, if the timeout setting is too short, there will be a lot of accidental injuries.

Therefore, normally we still have to adopt the second strategy, that is, active deadlock detection, and the default value of innodb_deadlock_detect itself is on. Active deadlock detection can be quickly detected and processed when a deadlock occurs, but it also has an additional burden.

You can imagine this process: whenever a transaction is locked, it is necessary to see if the thread it depends on is locked by someone else, so as to determine whether there is a loop waiting, that is, a deadlock. What if all the transactions we mentioned above have to update the same line?

Each new blocked thread has to determine whether it has caused a deadlock due to its own joining, which is an operation with a time complexity of O (n). Assuming that there are 1000 concurrent threads updating the same row at the same time, the deadlock detection operation is of the order of 1000 to 1000 = 1 million. Although the final test result is that there is no deadlock, it consumes a lot of CPU resources during this period. As a result, you will see that CPU utilization is high, but not a few transactions can be performed per second.

Based on the above analysis, let's discuss * * how to solve the performance problems caused by such hot row updates? * * the crux of the problem is that deadlock detection consumes a lot of CPU resources.

One way to take a stop-gap measure is to turn off the deadlock detection temporarily if you can make sure that the business is free of deadlocks. However, this operation itself carries certain risks, because when the business is designed, deadlocks are generally not regarded as a serious error. After all, when deadlocks occur, roll back, and then retry through the business is generally no problem, which is business lossless. Turning off deadlock detection means that a large number of timeouts may occur, which is detrimental to the business.

Another idea is to control the degree of concurrency. Based on the above analysis, you will find that if concurrency can be controlled, compared to a maximum of 10 threads updating at the same time, then the cost of deadlock detection is very low and this problem will not occur. A direct idea is to do concurrency control on the client side. However, you will soon find that this method is not feasible because there are so many clients. I have seen an application with 600 clients, so that even if each client is controlled to only 5 concurrent threads, the peak number of concurrency may reach 3000 after summing up to the database server. Therefore, this concurrency control should be done on the database server. If you have middleware, you can consider implementing it in middleware; if your team has someone who can modify the MySQL source code, you can also do it in MySQL. The basic idea is that for peer updates, queue up before entering the engine. In this way, there will not be a lot of deadlock detection work within InnoDB.

You may ask, if there are no database experts in the team for the time being, can you optimize the design if you can't implement such a solution?

You can consider reducing lock conflicts by changing one line to logical multiple lines. Or take the cinema account as an example, you can consider putting it on multiple records, such as 10 records. The total amount of the cinema account is equal to the sum of the values of these 10 records. In this way, every time you have to add money to the cinema account, randomly select one of the records to add. In this way, the probability of each collision is changed to the original 1max 10, which can reduce the number of lock waits and reduce the CPU consumption of deadlock detection.

This scheme seems to be lossless, but in fact, this kind of scheme needs to be designed in detail according to the business logic. If the account balance may decrease, such as the refund logic, then you need to consider that the code has special handling when part of the line record becomes zero.

Summary

Global locks are mainly used in logical backups. For libraries that are all InnoDB engines, I suggest you choose to use the-single-transaction parameter, which is more application-friendly.

Table locks are generally used when the database engine does not support row locks. If you find a statement like lock tables in your application, you need to track it down, which is more likely to be:

Either your system is still using an engine that does not support transactions such as MyISAM, so arrange an upgrade for the engine.

Either your engine has been upgraded, but the code hasn't been upgraded yet. I have seen such a situation where the final business development is to change lock tables and unlock tables to begin and commit, and the problem is solved.

MDL will not be released until the transaction is committed. When making table structure changes, you must be careful not to cause online queries and updates to be locked.

The row lock of MySQL involves two parts: two-phase lock protocol, deadlock and deadlock detection. Among them, I used the two-phase agreement as a starting point to discuss with you how to arrange the correct transaction statements during development. The principle here / my advice to you is: if you need to lock multiple lines in your transaction, delay the application time for locks that are most likely to cause lock conflicts and affect concurrency as far as possible.

However, adjusting the order of statements does not completely avoid deadlocks. So we introduce the concepts of deadlock and deadlock detection, and provide three solutions to reduce the impact of deadlock on the database. The main direction of reducing deadlocks is to control the number of concurrent transactions accessing the same resource.

Question 1

Backups are usually performed on the standby database. In the process of making a logical backup with the-single-transaction method, if a small table on the main database makes a DDL, such as adding a column to a table. At this time, what phenomenon will you see from the repository?

Assuming that this DDL is for table T1, here I list several key statements in the backup process:

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT;/* other tables * / Q3:SAVEPOINT sp;/* time 1 * / Q4:show create table `t1`; / * time 2 * / Q5:SELECT * FROM `t1`; / * time 3 * / Q6:ROLLBACK TO SAVEPOINT sp;/* time 4 * / / * other tables * /

To ensure the RR (repeatable readable) isolation level at the beginning of the backup, set the RR isolation level (Q1) again

To start the transaction, you can get a consistent view (Q2) by using WITH CONSISTENT SNAPSHOT to make sure that the statement is finished.

It is important to set a SavePoint (Q3)

The purpose of show create is to get the table structure (Q4), then formally import the data (Q5) and roll back to SAVEPOINT sp, where the role is to release T1's MDL lock (Q6). Of course, this part belongs to the "super class", which is not mentioned in the text above.

The time DDL passed from the main database was played for four times, depending on the effect. The title is set to a small table, and we assume that after arrival, if the execution starts, the execution will be completed quickly.

The reference answers are as follows:

If it arrives before the execution of the Q4 statement, the phenomenon: no effect, the backup gets the table structure after DDL.

If the table structure is changed when "time 2" arrives, when Q5 executes, report Table definition has changed, please retry transaction, phenomenon: mysqldump terminates

If it arrives between "time 2" and "time 3", mysqldump occupies the MDL read lock of T1, and binlog is blocked, the phenomenon: master-slave delay until Q6 execution is completed.

Starting from "moment 4", mysqldump released the MDL read lock, the phenomenon: no impact, the backup gets the table structure before DDL.

Question 2

If you want to delete the first 10000 rows of data in a table, there are three ways to do this, but that is better:

First, execute delete from T limit 10000 directly; second, execute delete from T limit 500 20 times in one connection; and third, execute delete from T limit 500 in 20 connections at the same time.

Scenario 1: if the transaction is relatively long, the lock will take longer, which will cause other clients to wait for resources for a long time.

Scheme 2: serial execution, dividing a relatively long transaction into several relatively short transactions, the time for each transaction to occupy the lock is relatively short, and the time for other clients to wait for the corresponding resources is also shorter. This operation, which also means that resources are used in pieces (using different fragments of resources for each execution), can improve concurrency.

Plan 3: create lock competition by yourself to aggravate the amount of concurrency.

At this point, the study of "the concept of MySQL global lock, table lock and row lock" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.