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

Today, I will introduce to you what lock knowledge is in MySQL. The content of the article is good. Now I would like to share it with you. Friends who feel in need can understand it. I hope it will be helpful to you. Let's read it along with the editor's ideas.

I. Preface

MySQL locks can be divided into global locks, table locks and row locks according to their scope, in which row locks are implemented by the database engine, and not all engines provide row locks, so MyISAM does not support row locks, so this article introduces row locks by taking InnoDB engine as an example.

2. Global lock

MySQL provides global locks to lock the entire database instance.

Syntax:

FLUSH TABLES WITH READ LOCK

This statement is usually used for backup. When this statement is executed, all open tables in the database will be closed, and all tables in the database will be locked with a global read lock. At the same time, update statements (additions, deletions and modifications) from other threads, data definition statements (table creation, table structure modification) and transaction commits of update classes will be blocked.

After mysql 8.0, mysql can use backup locks directly for backups.

Statement:

LOCK INSTANCE FOR BACKUPUNLOCK INSTANCE

This lock has a broader scope, and it prevents file creation, renaming, and deletion, including REPAIR TABLE TRUNCATE TABLE, OPTIMIZE TABLE operations, and account management. Of course, these operations can be performed for memory temporary tables, so why are memory tables not subject to these restrictions? Because memory tables do not need to be backed up, there is no need to meet these conditions.

Third, watch lock

Mysql table-level locks are divided into two types, one is metadata lock (Metadata Lock,MDL), and the other is table lock.

Metadata locks (MDL) do not need to be used explicitly and are automatically added when a table is accessed. This feature needs to be supported by the MySQL5.5 version or above. When you add, delete or change a table, the table will be added with a MDL read lock; when you make structural changes to the table, add a MDL write lock. There are some rules for MDL locks:

Read locks are not mutually exclusive, so you can add, delete, modify and check multiple threads in the same table.

Read-write locks and write locks are mutually exclusive, in order to ensure the security of table structure changes, so if multi-threads add fields to the same table and other table structure operations, it will become serialization and need to wait for locks.

The priority of the MDL write lock is higher than that of the MDL read lock, but the max_write_lock_count system variable can be set to change this situation. When the write lock request exceeds the number set by this variable, the MDL read lock has a higher priority than the MDL read lock. (by default, this number is large, so you don't have to worry about the write lock falling in priority.)

Lock release for MDL must not be released until the end of the transaction

So we must be careful not to use long transactions when manipulating the database table structure. What does it mean exactly? Let me give you an example:

The figure above shows four session execution statements. First, SessionA enables the transaction not to commit, and then sessionB executes the query. Because it acquires the MDL read lock, it does not affect each other and can be executed normally. SessionC adds a field. Because MDL write and read are mutually exclusive, SessionC will be blocked, and then SessionD starts to execute a query statement. SessionD is also blocked due to the blocking of SessionC. Therefore, the transaction of the SessionA we simulated is a long transaction, and then the table structure is modified, which makes all subsequent read and write operations on the table infeasible. Therefore, in the actual scenario, if the business requests are more frequent, the modification of the table structure may cause the threads of the library to be blocked.

The syntax for table locks is as follows:

LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type]... lock_type: {READ [LOCAL] | [LOW_PRIORITY] WRITE} UNLOCK TABLES

Table locks are divided into read locks and write locks. Read locks are not mutually exclusive, but acquiring read locks cannot write data, and other session that do not acquire read locks can also read tables, so the purpose of read locks is to restrict table writes. If the table is locked by a read lock, executing the insert statement will report an error as follows:

1099-Table 'XXXX' was locked with a READ lock and can't be updated

After the write lock is acquired, the table can be read and written, and the write lock is mutually exclusive. Once one session acquires the write lock of the table, another session cannot access the table until the write lock is released.

The table can be unlocked using unlock tables or the client port can be unlocked automatically. The lock tables lock table will lock the table exclusively, restricting not only other threads' reading and writing to the table, but also the next operands of this thread.

Fourth, row lock (InnoDB)

Row locks in MySQL are implemented at the engine level, so row locks under InnoDB engines are also discussed here. Here are several common row locks under InnoDB.

4.1 shared Lock

The shared lock allows the transaction to read after acquiring the lock, and the shared lock is not mutually exclusive. After one transaction acquires the shared lock, another transaction can also acquire the shared lock, and the write operation cannot be performed after acquiring the shared lock.

4.2 exclusive lock

The exclusive lock allows the transaction to update or delete a row after acquiring the lock. The exclusive lock is mutually exclusive as its name implies. After one transaction acquires the exclusive lock, other transactions cannot acquire the exclusive lock until the lock is released.

4.3 intention lock

InnoDB supports multiple granularity locks, allowing row locks and table locks to coexist. The intention lock here is actually a table-level lock, but I put it in a row lock because it will not exist alone, and its appearance will certainly be accompanied by a row lock (shared lock or exclusive lock). Its main purpose is to indicate that the rows in the table will be locked or are being locked.

According to the combination of intention lock and row lock, it can be divided into:

Intention exclusive lock: indicates that the exclusive lock will be acquired in some rows in the table

Intention shared lock: indicates that the shared lock will be acquired in some rows in the table

The intention lock must be acquired before the row lock is acquired, that is, the shared intention lock must be acquired before the shared lock is acquired, and the same is true for exclusive locks.

So what exactly is the use of this intention lock?

Before we explain this, let's take a look at the compatibility relationship between intention locks and row locks:

-- exclusive lock (X) intention exclusive lock (IX) shared lock (S) intention shared lock (IS) exclusive lock (X) conflict intention exclusive lock (IX) conflict compatibility conflict compatibility sharing lock (S) conflict compatibility intention sharing lock (IS) conflict compatibility

Let's assume that there are two transactions An and B, and the transaction acquires the shared lock and locks a row in the table, which can only be read but not written. now transaction B has to apply for a write lock for the entire table. If transaction B requests successfully, then it must be possible to write to all rows in the table, which must conflict with the row lock acquired by A. In order to avoid this kind of conflict, the database will conduct conflict detection, so how to detect it? There are two ways:

Determine whether the table has been locked by other transactions with table-level locks.

Determines whether each row in the table is locked by a row lock.

Each row in the judgment table needs to traverse all the records, which is too inefficient, so the database uses the first way to do conflict detection, that is, the intention lock.

Global locks and table locks are implemented by MySQL itself, while row locks are implemented at the engine level. Row locks under InnoDB are mainly divided into shared locks and exclusive locks. After a shared lock request, rows can only be read and shared locks are not mutually exclusive. Exclusive locks can update and delete rows after acquisition, and exclusive locks are mutually exclusive with other locks. Finally, I mentioned the intention lock on the basis of the row lock, which mainly indicates that the row is being locked or is about to be locked, in order to improve the efficiency in lock conflict detection.

These are all the contents of lock knowledge in MySQL, and you can search the previous articles or browse the following articles to learn more about lock knowledge in MySQL. I believe the editor will add more knowledge to you. I hope you can support it!

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.