In-depth understanding of MySQL global locks and table locks

Preface

According to the scope of locking, locks in MySQL can be roughly divided into global locks, table locks, and row locks.

Row locks have been mentioned in previous articles

1. Global lock

A global lock locks the entire database instance. MySQL provides a Flush tables with read lock (FTWRL) .

When you need to make the entire database read-only, you can use this command. After that, the following statements of other threads will be blocked: data update statements (addition, deletion, and modification of data), data definition statements (including table creation, table structure modification, etc.) and commit statements of update transactions.

1.1 Global lock usage scenarios

The typical usage scenario of global locks is to make a logical backup of the entire database (mysqldump). When you become the master and slave again

That is to select every table in the entire database and save it as text.

In the past, there was a method that used FTWRL to ensure that no other threads would update the database, and then backed up the entire database. Note that during the backup process the entire library is completely read-only.

The dangers of a database read-only state:

If you back up on the primary database, no updates can be performed during the backup period, and business can basically be stopped. If you back up on a slave, the slave cannot execute the binlog synchronized from the master during the backup, which will cause a delay between the master and the slave.

Note: The above logical backup does not include --single-transaction parameter

It seems that adding a global lock is not a good idea. But think about it, why does the backup need to be locked? Let’s see what problems will arise if we don’t lock it.

1.2 Problems caused by not locking

For example, mobile phone cards, package purchase information

There are two tables here: u_acount (for balance table) and u_pricing (for tariff package table)
step:
1. Data in the u_account table User A's balance: 300
Data in the u_pricing table User A package: empty

2. Initiate a backup. During the backup process, the u_account table is backed up first. After backing up this table, the u_account user balance is 300.
3. At this time, the user purchased a package with a tariff of 100, the meal purchase was completed, and the purchase was successful and the data during the backup period was written into the u_print package table.
4. Backup completed

As a result of the backup, the data in the u_account table has not changed, but the data in the u_pricing table has been updated to the purchased package of 100.

If user A uses the backup file to restore data at this time, he will earn 100. Wouldn't he be very happy? But you have to think about the interests of the company.

In other words, if it is not locked, the database backed up by the backup system is not at a logical time point, and the data is logically inconsistent.

1.3 Why do we need a global read lock (FTWRL)?

Some people may be wondering, the official logical backup tool is mysqldump. When mysqldump uses the parameter --single-transaction, a transaction is started before importing data to ensure a consistent snapshot view. And thanks to the support of MVCC, the data can be updated normally during this process.

Why do we need FTWRL?
Consistent reads are good, but the prerequisite is that the engine must support this isolation level. For example, for an engine like MyISAM that does not support transactions, if there is an update during the backup process, only the latest data can be obtained, which destroys the consistency of the backup. At this time, we need to use the FTWRL command.

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

1.4 Two methods of global lock

1. FLUSH TABLES WRITE READ LOCK

2. set global readonly=true

Since the entire database needs to be read-only, why not use the set global readonly=true method? It is true that the readonly method can also put the entire database into a read-only state, but I still recommend you to use the FTWRL method for several reasons:

First, in some systems, the readonly value is used for other logic, such as determining whether a database is a primary database or a backup database. Therefore, modifying global variables has a greater impact, and I do not recommend you to use it.

Second, there are differences in exception handling mechanisms. If the client is abnormally disconnected after executing the FTWRL command, MySQL will automatically release the global lock and the entire database will return to a state where it can be updated normally. After setting the entire database to readonly, if an exception occurs on the client, the database will remain in the readonly state. This will cause the entire database to be in an unwritable state for a long time, which is a high risk.

Third, readonly is invalid for super user permissions

Note: Business updates are not just about adding, deleting, and modifying data (DML), but may also involve operations such as adding fields to modify the table structure (DDL). Regardless of which method you use, once a database is globally locked, any table in it that you want to add a field to will be locked.

Even if it is not globally locked, adding fields is not smooth sailing. There are also table-level locks.

2. Table-level lock

There are two types of table-level locks in MySQL: one is table lock and the other is metadata lock (MDL).

2.1 Table Lock

lock tables table name read; #The table can be read, but cannot be added, deleted, or modified in ddl and dml. Only table data can be read

lock tables table name read; # can neither read nor write

The syntax for table lock is lock tables … read/write. Similar to FTWRL, you can use unlock tables to actively release the lock, or you can release it automatically when the client disconnects. It should be noted that the lock tables syntax not only restricts the reading and writing of other threads, but also limits the subsequent operation objects of this thread.

For example, if the statement lock tables t1 read, t2 write; is executed in thread A, then statements of other threads writing t1 and reading and writing t2 will be blocked. At the same time, before executing unlock tables, thread A can only perform operations such as reading t1 and reading and writing t2. Writing to t1 is not allowed, and naturally, other tables cannot be accessed.

Before finer-grained locks appeared, table locks were the most commonly used way to handle concurrency. For engines like InnoDB that support row locks, the lock tables command is generally not used to control concurrency. After all, locking the entire table has a huge impact.

2.2 MDL Lock

Another type of table-level lock is MDL (metadata lock). MDL does not need to be used explicitly, it is added automatically when accessing a table. The role 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 during execution another thread changes the table structure and deletes a column, then the result obtained by the query thread will not match the table structure, which is definitely not acceptable.

Therefore, MDL was introduced in MySQL 5.5. When adding, deleting, modifying, or querying a table, an MDL read lock is added; when a structural change is made to the table, an MDL write lock is added.

• Read locks are not mutually exclusive, so you can have multiple threads adding, deleting, modifying, and querying a table at the same time.
• Read-write locks and write locks are mutually exclusive and are used to ensure the security of operations that change table structures. Therefore, if two threads want to add fields to a table at the same time, one of them has to wait until the other one finishes before it can start executing.

Although the MDL lock is added by default, it is a mechanism that you cannot ignore.

For example, in the following example, I often see people fall into this pit: adding a field to a small table causes the entire database to crash.

You must know that adding a field to a table, or modifying a field, or adding an index requires scanning the entire table's data. When operating on large tables, you must be particularly careful to avoid affecting online services. In fact, even a small watch can cause problems if it is not operated carefully. Let's look at the following sequence of operations, assuming that table t is a small table.

show full processlist to view mdl lock details

We can see that session A starts first, and an MDL read lock is added to table t. Since session B also requires an MDL read lock, it can be executed normally.

Afterwards, session C will be blocked because the MDL read lock of session A has not been released, and session C needs the MDL write lock, so it can only be blocked.

It is not a big deal if only session C is blocked, but all subsequent requests for new MDL read locks on table t will also be blocked by session C. As mentioned earlier, all operations on the table need to apply for an MDL read lock first, which means that the table is now completely unreadable and writable.

If the query statements on a certain table are frequent and the client has a retry mechanism, that is, a new session will be started after a timeout and then request again, the threads of this library will soon be full.

The MDL lock in a transaction is applied at the beginning of statement execution, but it is not released immediately after the statement ends. Instead, it is released after the entire transaction is committed.
Note: Generally, row locks have a lock timeout period. However, the MDL lock has no timeout limit and will remain locked as long as the transaction is not committed.

2.2.1 How to solve this MDL lock

Didn’t I say it above, commit or rollback this transaction? So to find this transaction

How to find this transaction? Check the transaction execution time through information_schema.innodb_trx

# View transactions that exceed 60 secondsmysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60\G;
trx_started indicates when the transaction was executed. #View the current system timemysql> select now(); 

The transaction start time and the system current time show that the transaction has been executed for such a long time.

Check this thread id

How to deal with the thread id of this long transaction

First, look at the host field in show full processlist; to see who is connecting to the database. For example, I am in localhost environment, and I enter commit or rollback. If it is not localhost environment, the program is connected and it needs to be killed.

2.2.2 Interesting things that happened to me

Last time, a DBA asked me how to solve the problem of large master-slave delay.

I asked you how you solved the delay. Do you know the specific reasons for the master-slave delay?

He told me that multi-threading was enabled, but the delay was still very large and multi-threading was basically not used.

I asked him how he knew the master-slave delay needed to be solved by enabling multi-threaded replication, and he told me that someone else on the internet blog said this, which made me vomit blood.

Later I asked him what operations were performed when the master-slave delay was normal. He told me that he modified the alter table structure.

Then I asked him to see if it was caused by the MDL lock. I asked him show full processlist , and it turned out to be the MDL lock.

Then tell him to find the long transaction, and after finding it, discuss with the developer whether the operation of this long transaction can be killed.

Note: This is a real problem I encountered, and someone asked me this question. First of all, you need to know what caused this result and what you did beforehand, and then solve the problem. Most fundamentally, you still need to know the cause and avoid it next time.

Also, don't blindly believe the online environment, system version, application version, and the problem you encounter.

2.3 How to safely add fields to a small table?

First, we need to solve the problem of long transactions. If the transaction is not committed, the MDL lock will be occupied. In the innodb_trx table of the MySQL information_schema library, you can check the currently executing transactions. If a long transaction is in progress on the table where you want to perform DDL changes, consider pausing the DDL first, or killing the long transaction. This is why it is necessary to make DDL changes during off-peak periods. Of course, you also need to consider what specific DDL to use and refer to the official online DDL.

2.4 Online DDL Process

• Take MDL write lock
• Downgrade to MDL read lock
• Actually do DDL
• Upgrade to MDL write lock
• Release MDL lock

1, 2, 4, 5 If there is no lock conflict, the execution time is very short. Step 3 takes up most of the DDL time. During this period, the table can read and write data normally, so it is called "online"

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.