Deep Understanding of MySQL Global Lock and Table Lock

Time：2019-7-6

Preface

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

Line locks have been mentioned in previous articles

1. Global Lock

A global lock is to lock the entire database instance. MySQL provides a way to add a global read lock. The command isFlush tables with read lock (FTWRL)。

When you need to make the whole library read-only, you can use this command, and then the following statements of other threads will be blocked: data update statements (data addition and deletion), data definition statements (including table building, table structure modification, etc.) and submission statements for updating class transactions.

1.1 Global Lock Use Scenario

A typical use scenario for global locks is mysqldump. Re-ownership

That is to say, every table in the whole library is selected and saved into text.

There used to be a way to ensure that no other threads would update the database through FTWRL, and then backup the entire database. Note that the entire library is completely read-only during the backup process.

The danger of database read-only state:

If you backup on the main repository, you can’t perform updates during the backup period, and the business can basically stop. If you are backing up from the library, the binlog synchronized from the main library cannot be executed from the library during the backup, which will cause the master-slave delay.

Note:The logical backup above is not added--single-transactionparameter

It doesn’t seem very good to add a global lock. But think about it, why do backups lock? Let’s see what’s wrong with unlocking?

1.2 Problems arising from unlocking

For example, mobile card, purchase package information

There are two tables, u_acount (for balance sheet) and u_pricing (tariff package).
Steps:
1. Data user A balance in u_account table: 300
Data User A Package in u_pricing Table: Empty

2. Initiate backup, backup u_account table first in the backup process, Backup this table, then u_account user balance is 300
3. At this time, the set user purchased a tariff package 100, and the purchase was completed, which was written into the u_print package table to purchase successfully, and the data during the backup period.
4. Backup Completion

As a result of the backup, the data in the u_account table has not changed, and the data in the u_price table has recently purchased the tariff package 100.

When this backup file is used to recover data, user A earns 100 yuan. Is the user comfortable? But think about the company’s interests.

That is to say, unlocked, the database backed up by the backup system is not a logical time point, and the data is logically inconsistent.

1.3 Why Global Read Lock (FTWRL)

Some may wonder that mysqldump is the official logical backup tool. When mysqldump uses the parameter single-transaction, a transaction is started before the data is imported to ensure that a consistent snapshot view is obtained. Due to the support of MVCC, the data can be updated normally in this process.

Why do we need FTWRL?
Consistency reading is good, but only if the engine supports this isolation level. For example, for MyISAM, an engine that does not support transactions, if there are updates in the backup process, only the latest data can always be retrieved, then the consistency of backups will be destroyed. At this point, we need to use the FTWRL command.

Therefore, the single-transaction method applies only to libraries where all tables use a transaction engine. If a table uses an engine that does not support transactions, then 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.

1.4 Global Lock: Two Methods

I. FLUSH TABLES WRITE READ LOCK

2. set global readonly = true

Why not use set global readonly = true since you want to read-only the whole library? It’s true that readonly can also make the whole library read-only, but I would recommend FTWRL for several reasons:

First, in some systems, readonly values are used for other logic, such as judging whether a library is a master or a standby. Therefore, the way to modify global variables has a greater impact, and I do not recommend that you use them.

Second, there are differences in exception handling mechanisms. 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. When the whole library is set to read only, if the client has an exception, the database will remain read only, which will cause the whole library to be in an unwritable state for a long time, and the risk is high.

Third, readonly is invalid for super user privileges

Note: Business updates are not only the addition and deletion of data (DML), but also the addition of fields and other operations to modify the table structure (DDL). Either way, after a library is globally locked, you will be locked if you add fields to any table in it.

Even if it’s not locked globally, it’s not easy to add fields. There are table-level locks.

2. Table level locks

There are two kinds of table level locks in MySQL: one is table lock, the other is meta data lock (MDL).

2.1 Table Lock

Lock tables table name read; This table can be read, can not be added or deleted in DDL and dml, can only read table data.

Lock tables table name read; neither read nor write

image

The grammar of table locks is lock tables. Read/write. Similar to FTWRL, unlock tables can be used to actively release locks or automatically release locks when the client is disconnected. It is important to note that the lock tables syntax not only limits the reads and writes of other threads, but also the next operation objects of this thread.

For example, if lock tables T1 read and T2 write are executed in thread A, the statements written by other threads T1 and T2 will be blocked. A t the same time, thread A can only read and write T1 and T2 before unlock tables are executed. Even writing T1 is not allowed, and other tables cannot be accessed naturally.

Table locks are the most common way to handle concurrency when there are no finer-grained locks. For InnoDB, which 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 large.

2.2 MDL Lock

Another type of table level lock is MDL (metadata lock). MDL does not need to be explicitly used 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 data in a table and another thread changes the table structure and deletes a column during execution, then the query thread will get a result that is different from the table structure.

Therefore, MDL is introduced in MySQL version 5.5. When adding, deleting and modifying a table, MDL read lock is added, and MDL write lock is added when changing the structure of a table.

• Read locks are not mutually exclusive, so you can have multiple threads to add, delete and modify a table at the same time.
• The read-write locks and write locks are mutually exclusive to ensure the security of the structure operation of the change table. Therefore, if two threads want to add fields to a table at the same time, one of them will wait for the other to finish executing before executing.

Although MDL locks are added by default, they are 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 hang.

Surely you know that adding fields to a table, or modifying fields, or indexing, requires scanning the entire table’s data. You must be very careful when operating large watches to avoid any impact on online services. In fact, even small tables can cause problems if they are operated carelessly. Let’s look at the following sequence of operations, assuming that table t is a small table.

image image

show full processlistView MDL lock details

image

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

Session C will then be blocked because session A’s MDL read lock has not been released, and session C needs MDL write lock, so it can only be blocked.

It doesn’t matter if only session C itself is blocked, but then all requests for new MDL read locks on table T will also be blocked by session C. As mentioned earlier, all additions, deletions and alterations to tables need to apply for MDL read locks first, and then they are locked, which means that the performance is completely unreadable.

If the queries on a table are frequent and the client has a retry mechanism, that is to say, a new session will be requested again after the timeout, the library’s threads will soon be full.

MDL locks in transactions are applied at the beginning of statement execution, but they are not released immediately after statement completion, but are released after the whole transaction is committed.
Note: In general, line locks have lock timeout time. However, there is no timeout limit for MDL locks, and as long as transactions are not committed, they will always be locked.

2.2.1 How to Solve the MDL Lock

Didn’t that say, commit or roll back the transaction? So we need to find this business.

How to find this transaction, pass through the ____________information_schema.innodb_trxView transaction execution time

# View transactions over 60s
mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60\G;
Trx_start indicates when the transaction is executed

# View the current time of the system
mysql> select now();

image

Transaction start time and system now time, look at how long the transaction has been executed.

View the thread ID

image

How to handle the thread ID of this long transaction

First, look at the show full process list; which field of host is in it, and who is actually connected to the database. Example: I have a localhost environment on top, go in commit or / rollback. If it’s not a localhost environment, the program will kill it when it connects.

2.2.2 Funny things happened to me

The last time a DBA asked me, it caused a lot of master-slave latency and said how to solve it.

I said how do you solve the delay? Do you know the specific reasons for the delay?

TA told me that multi-threading was turned on, but the latency was still very large, and it was rarely used.

I said how do you know master-slave delay, need to open multi-threaded replication to solve, TA told me, blogs on the Internet do not say that, I spit out old blood.

Later, I asked TA what the master-slave delays were doing under normal conditions, and Ta told me that the alter table structure had been modified.

Then let TA see if it was caused by MDL lock, let tashow full processlistAt first glance, it is the cause of MDL lock.

Then talk to ta about finding a long business, and then discuss with the developer after finding out what the long business is doing, can’t you kill it?

Note:This is what I really met. People asked me such questions. First of all, you need to know what you did beforehand, and then solve the problem. Basically, you need to know the reason, and then avoid it next time.

There are also online environments, system versions, application versions, problems encountered, and whether you are the same, sometimes do not blindly believe.

2.3 How to add fields to small tables safely?

First of all, we need to solve long transactions. If the transaction is not committed, it will always occupy the MDL lock. In the innodb_trx table of MySQL’s information_schema library, you can find the currently executing transactions. If you happen to have a long transaction executing on your DDL change table, consider suspending the DDL or killing the long transaction first. This is why it is necessary to make DDL changes during the low peak period. Of course, we should also consider what DDL to do, referring to the official online ddl.

2.4 online DDL process

• Write locks with MDL
• Degraded to MDL Read Lock
• Really do DDL
• Upgrade to MDL Write Lock
• Release MDL locks

1, 2, 4, 5 have very short execution time if there is no lock conflict. Step 3 takes up most of the DDL time, during which the table can read and write data normally, so it is called “online”

summary

Above is the whole content of this article. I hope the content of this article has some reference value for your study or work. Thank you for your support to developpaer.