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

Editor to share with you what the MySQL lock and locking rules are, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The purpose of introducing locks in MySQL is to solve the problem of concurrent writes. For example, if two transactions are allowed to write to the same record at the same time, dirty writes will occur. This is an exception that is not allowed at any isolation level. The purpose of locks is to make two concurrent writes execute in a certain order to avoid dirty writes.

First state the examples used in this article

CREATE TABLE `user` (`id` int (12) NOT NULL AUTO_INCREMENT, `name` varchar (36) NULL DEFAULT NULL, `age` int (12) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `age` (`age`) ENGINE = InnoDB AUTO_INCREMENT = 1 + insert into user values (5 dada, 5), (10 dada, 10), (15 hedgehog, 15); copy code

The examples described in this article are all under the MySQL InnoDB storage engine and the Repeatable Read isolation level.

1. Granularity classification of locks

From the perspective of lock granularity, locks in MySQL can be divided into global locks, table-level locks and row locks.

1.1 Global Lock

The global lock will lock the entire database, at which time the database will be read-only, and any statements that modify the database, including DDL (Data Definition Language) and DML (Data Manipulation Language) statements, will be blocked until the database global lock is released.

The most common place to use full lock is to back up the whole database. We can use the following statement to lock and release global lock:

-- add global lock flush tables with read lock;-- release global lock unlock table; replication code

If the client link is broken, the global lock is also automatically released.

1.2 Table level lock

Table-level locks add locks to the entire table. Table-level locks in MySQL include table locks, metadata locks (Meta Data Lock), intention locks (Intention Lock), and self-increment locks (AUTO-INC Lock).

1.2.1 Table lock

The lock and release mode of the table lock:

Lock: lock table tableName read/write; release lock: unlock table

It should be noted that the locking of the table lock also limits the operation permission of the same client link. For example, if a table-level read lock (lock table user read) is added, the same table (user table) can only be read and cannot be written before the table-level read lock is released in the same client link, while other client links can only read the table (user table) and cannot write to it.

If a table-level write lock (lock table user write) is added, the table can be read and written in the same client link, while other client links can neither read nor write.

1.2.2 metadata lock

The second table-level lock is the metadata lock (MDL, Meta Data Lock), which is automatically locked when the client accesses the table and releases the lock when the client commits the transaction, which prevents problems in the following scenarios:

SessionAsessionBbegin

Select * from user

Alter table user add column birthday datetime;select * from user

For example, in the above table, sessionA opens a transaction and makes a query, after which another client sessionB adds a birthday field to the user table, and then sessionA makes another query. If there is no metadata lock, it may appear in the same transaction. The number of columns in the table field is inconsistent with the records queried before and after, which obviously needs to be avoided.

The DDL operation adds a metadata write lock to the table, which is incompatible with the metadata read and write lock of other transactions; the DML operation adds a metadata read lock to the table, which can be shared with the metadata read lock of other transactions, but is not compatible with the metadata write lock of other transactions.

1.2.3 intention lock

The third table-level lock is the intention lock, which indicates that the transaction wants to acquire locks (shared or exclusive locks) for certain rows in a table.

The intention lock is to avoid the system consumption of scanning each row in the table for row locks when another transaction applies for the table lock when there is already a row lock in the table.

SessionAsessionBbegin

Select * from user where id=5 for update

Flush table user read

For example, when sessionA opens a transaction and adds a row-level exclusive lock to the row of id=5, sessionB will add a table-level exclusive lock to the user table (locking fails as long as a row in the user table is held by another transaction).

If there are no intention locks, sessionB scans every row in the user table to determine whether they are locked by other transactions before determining whether sessionB's table-level exclusive lock is successful.

After having the intention lock, when sessionB locks the user table, it will directly judge whether the user table has been locked by other transactions. If so, the lock will fail, and if not, table-level exclusive lock can be added.

Locking rules for intention locks:

Before a transaction acquires a row-level shared lock (S lock), it must acquire the intentional shared lock (IS lock) or intentional exclusive lock (IX lock) of the table. Before acquiring the row-level exclusive lock (X lock), the transaction must acquire the intentional exclusive lock (IX lock) 1.2.4 self-incrementing lock of the table.

The fourth table-level lock is the self-incrementing lock, which is a special table-level lock that exists only when it is set to an AUTO_INCREMENT self-incrementing column, such as the id column in the user table.

Since the lock is added, it is released immediately after the insert statement is executed. At the same time, the self-increment lock can be shared with the intention lock of other transactions, and is incompatible with the self-increment lock, shared lock and exclusive lock of other transactions.

1.3 Row Lock

Row locks are implemented by the storage engine. From the point of view of row lock compatibility, InnoDB implements two standard row locks: Shared Locks (S lock) and Exclusive Locks (X lock).

The compatibility of these two row locks is the same as that of the metadata locks above, which can be expressed in the table below.

Transaction A\ transaction B shared lock (S lock) exclusive lock (X lock) shared lock (S lock) compatible exclusive lock (X lock) conflict conflict

Further subdivided from the granularity of row lock, it can be divided into record lock (Record Lock), gap lock (Gap Lock) and Next-key Lock.

1.3.1 record Lock (Record Lock)

What we generally call a row lock refers to a record lock, which adds a lock to a specified record row in the database.

Suppose the following statement is executed in transaction A (uncommitted):

Begin;update user set name=' da Vinci 'where id=5; copy code

InnoDB will at least add a row-level exclusive lock (X lock) to the id=5 line, and no other transactions will be allowed to operate on the id=5 line.

It is important to note that this lock is added to the primary key index of the id column, that is, the row-level lock is added to the index.

Suppose there is another transaction B that wants to execute an update statement:

Update user set name=' Big Wave 'where id=5; copy code

At this point, the update statement will be blocked and transaction B can not continue execution until transaction A commits.

1.3.2 clearance lock (Gap Lock)

Gap lock, as the name implies, is to add a lock to the gap between records.

It is important to note that gap locks only exist at the Repeatable Read isolation level.

I wonder if you still remember phantom reading?

Phantom reading means that the same query statement is executed twice in a row in the same transaction, and the second query statement may return rows that did not exist before.

The gap lock is proposed to prevent the insertion of phantom records described in phantom reading, for example.

SessionAsessionBbegin

Select * from user where age=5; (N1)

Insert into user values (2, 'Big Wave', 5) update user set name=' da Vinci 'where age=5

Select * from user where age=5; (N2)

There are two queries N1 and N2 in sessionA, and their query condition is age=5. The only difference is that there is an update statement before the query at N2.

In theory, under the RR isolation level, if you query the same record twice in the same transaction, the result should be the same. However, after the current read query of the update statement (the number of rows affected by the update statement is 2), the query results of N1 and N2 are not the same, and the query of N2 also finds out the data inserted by sessionB, which is called phantom reading.

If you use gap locks on both queries in sessionA, for example, change to select * from user where age=5 for update. Then the current read query statement in sessionA will at least put a gap lock on id between (- ∞, 5) and (5,10), and will not allow other transactions to insert records in which the primary key id belongs to these two intervals, that is, it will block the insert statement of sessionB, and sessionB will not continue to execute until sessionA commits.

In other words, when the query at N2 is executed, the sessionB is still blocked, so the query results of N1 and N2 are the same, both (5, reshaping, 5), which solves the problem of phantom reading.

1.3.3 Next-key Lock

Next-key Lock is actually the product of the combination of the record lock and the gap lock in front of the record lock, which not only prevents other transactions from inserting in the gap, but also prevents other transactions from modifying records.

two。 Locking rule

I do not know if you have noticed that when I describe the specific records of record locks and gap locks in the row lock section, I use the word "at least", and do not specify which records are locked in detail, this is because the locking rules of record locks, gap locks and Next-key Lock are very complex, which is also the main discussion of this article.

The description of locking rules will be divided into three aspects: unique index columns, ordinary index columns and ordinary columns, each of which will be subdivided into equivalent query and range query.

It should be noted that the locks added here refer to exclusive locks.

Before we begin, let's review the sample table and the possible row-level locks in the table.

Mysql > select * from user;+----+ | id | name | age | +-+ | 5 | reshaping | 5 | 10 | Dada | 10 | 15 | hedgehog | 15 | +-+ 3 rows in set (0.00 sec) copy code

The row-level locks that may be contained in the table are first the record locks for each row-- (5, reshaping, 5), (10, Dada, 5), (15, hedgehog, 15).

Suppose that the index value of the user table has a maximum value maxIndex and a minimum value minIndex,user table may also have a gap lock (minIndex,5), (5) 10), (10) 15), (15 min maxIndex).

There are three record locks and four gap locks.

2.1 unique index column equivalence query

First of all, for the equivalent query of the unique index column, the equivalent query can be divided into two cases: hit and miss.

When an equivalent query for a unique index column is hit:

SessionAsessionBbegin

Select * from user where id=5 for update

Insert into user values (1), (6), (6), (11), (16), (16)

Update user set age=18 where id=5; (Blocked)

Update user set age=18 where id=10

Update user set age=18 where id=15

The result of the execution of sessionB in the above table is that all statements execute normally except that the update statement of the id=5 row is blocked.

The insert statement in sessionB is to check for gap locks, and the update statement is to check record locks (row locks). The execution results show that all gaps in the user table are not locked, and only the row id=5 in the record lock is locked.

Therefore, when the equivalent query of the unique index column is hit, only the hit record is locked.

When an equivalent query for a unique index column fails:

SessionAsessionBbegin

Select * from user where id=3 for update

Insert into user values (2 'mirror', 2); (Blocked)

Update user set age=18 where id=5

Insert into user values (Summer Sunshine, 6)

Update user set age=18 where id=10

Insert into user values (11pi 'sue five people', 11)

Update user set age=18 where id=15

Insert into user values (16 faces, 16)

The result of the execution of the above table is that the record insertion of id=2 in sessionB is blocked and other statements are executed normally.

According to the execution result, it can be known that the lock added by sessionA to the user table is a gap lock (1).

Therefore, when the equivalent query of the unique index column fails, a gap lock is added to the gap where the id value is located.

2.2 unique index column range query

A range query is more complex than an equivalent query, and it needs to take into account whether the boundary value exists in the table and whether it hits the boundary value.

First, let's look at the case where the boundary value exists in the table but misses:

SessionAsessionBbegin

Select * from user where id

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.