The foreword 0.

According to the scope of MySQL lock, there are global lock, table lock, row lock.

In this article, we introduce MySQL global locking and table-level locking.

The important practice boils down to how to safely change the table structure of a table.

A global lock is a lock on the entire database instance.

Global lock syntax:

Flush tables with read lock (FTWRL)

When you use this command, the entire library is read-only, and data update statements (DML) and data definition statements (DDL) from other threads are blocked.

Scenario: An engine that does not support transactions (such as MyISAM) does a logical backup of the full library.

However, we generally use InnoDB, this lock will not touch, I will not expand on the details.

2. The table level lock

Meta Data Lock meta data lock meta data lock (MDL)

2.1 table locks

The syntax for table locking is:

Lock tables… read/write

Unlock tables actively releases locks

Table locks can also be released automatically when the client is disconnected.

It is important to note that in addition to limiting reads and writes by other threads, lock Tables also restricts subsequent operations by the current thread.

Similarly, innoDB does not use table locks for row locking engines, so this section is only a brief introduction.

2.2 MDL lock

Meta Data Lock meta Data lock is also a table-level lock introduced in MySQL 5.5.

When we perform CRUD operations on a table, we add an MDL read lock; Add an MDL write lock to a table when making structural changes to the table.

• Read locks are not mutually exclusive, so we can have multiple threads adding, deleting, modifying and checking a table at the same time.

• Read and write locks and write locks are mutually exclusive to ensure the security of changing the table structure. Therefore, whenever one thread wants to add a field to a table, the other threads must wait for that thread to complete the table structure change.

Seeing this, I have to give you an example of a hole that you would step on every day.

For large table DDLS, people tend to be cautious, while for small tables, they tend to be casual. But small table DDLS can also cause database crashes.

Session A is the first to start A transaction, start the transaction, commit the transaction immediately, imitate A long transaction, at this point, Session A has not released the MDL read lock on the table TT.

Session B is followed by a SELECT, which succeeds because MDL read locks are not mutually exclusive.

Session C then wanted to execute an ALTER statement on table TT, and needed to acquire the MDL write lock. However, Session A held the MDL read lock, and the read and write locks were mutually exclusive, so Session C was blocked.

Session D also wants to perform a select, and since Session C is blocked, Session D will also be blocked.

So at this point, all subsequent threads can’t read or write.

If there are frequent subsequent queries on the table, and the client has a retry mechanism, a new session will be initiated after the timeout, and the database thread will soon overflow.

Here are a few minor issues that need a little more explanation:

1) As we know from Session A, the MDL lock in the transaction is not released immediately after the end of the statement, but after the whole transaction is committed.

Session D (DDL) is blocked by SessionA (DDL) and Session B (DDL). SessionA (DDL) is blocked by Session D (DDL). The reason for this is that on the MySQL Server side, there’s a queue for Session C and Session D to decide which one executes first.

MySQL 5.6 supports Online DDL Why are there all kinds of jams here?

First, let’s clarify what Online DDL is.

In the process of Online DDL, the lock acquisition is divided into five steps (the specific Online DDL process is complicated and will not be explained in this article) :

1) Get the MDL write lock

2) Degrade to an MDL read lock

3) Actually do DDL

4) Upgrade to MDL write lock

5) Release MDL lock

1, 2, 4, and 5 are all very short execution times if there are no lock conflicts. Most of the time is taken up by step 3, during which time the table is available for reading and writing, so it is called Online DDL.

In our example, we actually blocked in the first step.

3.So, how to make a safe table structure change

In fact, whether large table small table table structure changes, are used to cause our attention.

With that in mind, you probably know the three key points:

Avoid long transactions!

Avoid long transactions!

Avoid long transactions!

In particular, the current transaction execution can be viewed in the Innodb_TRX table of the Information_SCHEMA library before performing a table structure change. If a long transaction is in progress, delay the execution of the change or manually kill the long transaction first.

Also, try to ensure that table structure changes occur during low database traffic peaks, such as at night, to better avoid risk.

So, how do you make a safe table structure change?

1) Avoid long transactions

2) At low flow peak

It’s that simple.

Next time, we’ll talk about line lock. Be there or be square

Reference:

MySQL tutorial 45

[MySQL]

MySQL > alter table count(*);

2. Why does the total storage size of MySQL become larger?

See the end, the original is not easy, point a concern, point a like it ~

Scan my official account “Ahmaru Notes” to get the latest updates as soon as possible. At the same time free access to a large number of Java technology stack e-books, each large factory interview questions oh.