Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to achieve global locks and table locks in MySQL. Many people may not know much about it. In order to make you understand better, the editor summed up the following contents for you. I hope you can get something from this article.

1. Global lock

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.

1.1 Global Lock usage scenario

A typical usage scenario for global locking is to do a full library logical backup (mysqldump). To make a new decision.

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.

The dangers of database read-only status:

If you back up on the main library, no updates can be performed during the backup, and the business can basically stop. 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.

Note: the above logical backup does not add the-- single-transaction parameter.

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.

1.2 problems caused by unlocking

For example, mobile phone card, purchase package information

Here is divided into two tables u_acount (for balance table) and u_pricing (tariff package table)

Steps:

1. Balance of data user An in u_account table: 300

Package A for data users in u_pricing table: empty

two。 Start a backup. During the backup process, back up the u _ assign table first, and then back up the table. At this time, the balance of u_account users is 300

3. At this time, the set user buys a fee package of 100. the meal purchase is completed and the data during the backup period is written into the u_print package table.

4. Backup completed

You can see that the result of the backup is that the data in the u_account table has not changed, and the data in the u_pricing table has nearly purchased the tariff package.

At this time, if you use this backup file to recover data, user An earns 100. Is the user very comfortable? But you have to think about the interests of the company.

In other words, without locking, the resulting library backed up by the backup system is not a logical point in time, and the data is logically inconsistent.

1.3 Why do you need a global read lock (FTWRL)

Some people may be wondering that 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 snapshot view is obtained. Due to the support of MVCC, the data can be updated normally during this process.

Why do you need FTWRL?

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.

1.4 two methods of global locking

I. FLUSH TABLES WRITE READ LOCK

II. Set global readonly=true

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 several 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.

Third, readonly is invalid for super user rights.

Note: 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.

Even if it is not locked globally, adding fields is not plain sailing, and there are table-level locks.

two。 Table level lock

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

2.1 Table lock

Lock tables table name read;# this table can be read, cannot be added, deleted or modified in ddl and dml, only table data can be read

The lock tables table name read;# cannot be read or written

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. However, for InnoDB, an engine that supports row locks, lock tables commands are generally not used to control concurrency. After all, the impact of locking the entire table is still too great.

2.2 MDL lock

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 5.5.When adding, deleting, changing and querying a table, MDL read lock is added, and MDL write lock is added when the table structure is to be changed.

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

Read-write locks and write locks are mutually exclusive, which is used 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.

Show full processlist View mdl Lock details

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

The sessionC will then be blocked because the MDL read lock of session A has not been released, while the sessionC 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 mentioned 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.

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.

Note: there is a lock timeout for general row locks. However, the MDL lock has no time-out limit and will remain locked as long as the transaction is not committed.

2.2.1 how to solve this MDL lock

As it says above, commit or roll back the transaction. So to find this transaction,

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

# View transactions with more than 60s transactions mysql > select * from information_schema.innodb_trx where TIME_TO_SEC (timediff (now (), trx_started)) > 60\ Gbot trxtransactions indicates when this transaction was executed # View system current time mysql > select now ()

The transaction start time and the system current time, a look at the transaction has been executed for so long.

Check out this thread id

How to deal with this long transaction thread id?

First, take a look at which host field in show full processlist; and who connects to the database. Ex.: I have the localhost environment above. Go to commit or / rollback. If it is not the localhost environment, but the program is connected, then the kill will be dropped.

2.2.2 the funny things that happened to me

Last time, a DBA asked me how to solve the problem, which caused a great delay between master and slave.

I said, how do you solve the delay? do you know the specific cause of the master-slave delay?

Ta told me that multithreading is turned on, but the delay is still very large, basically not much use of multithreading.

I said, how do you know the master-slave delay, need to turn on multi-thread replication to solve, ta told me, other people on the Internet blog this do not say, I spit out a mouthful of old blood.

Later, I asked ta what the master-slave delay normally did, and ta told me that the structure of the alter table had been modified.

Then let ta see if it is caused by the mdl lock, and let tashow full processlist see that it is really the cause of the mdl lock.

Then tell ta to find a long transaction, find it and discuss with the developer later on whether you can kill what the long transaction is doing.

Note: this is what I really met. I was asked such a question. First of all, you need to know what you have done to cause this result, and then to solve the problem, you still need to know the cause, and then avoid it next time.

There are online environment, system version, application version, encounter problems, and you are not the same, sometimes do not blindly believe.

2.3 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. This is why you need to make ddl changes during the trough, and of course you have to consider what ddl to do, refer to the official online ddl.

2.4 online ddl process

Take the MDL write lock

Downgrade to MDL read lock

Really be a DDL

Upgrade to MDL write lock

Release the MDL lock

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

After reading the above, do you have any further understanding of how to implement global locks and table locks in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.