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

This article focuses on "what are the modes and types of locks in MYSQL". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are the patterns and types of locks in MYSQL?"

In our daily development work, we need to deal with the database almost every day. As a SQL BOY who only knows CRUD, in addition to automatically generating DAO layer code with mybatis-generator every day, we hardly need to go to the care database to deal with concurrent requests. But suddenly one day MYSQL database alarm, there is a deadlock, our hearts panic, can not help but ask: this is not an ordinary query, why is it locked?

In order to prevent the panicked expression from being captured by the supervisor, we need to know in advance what locks are in the database.

In MySQL, locks are actually divided into two categories: lock type (lock_type) and lock mode (lock_mode).

The lock type describes the lock granularity, that is, where the lock is added, while the lock pattern describes what lock is added, whether it is a read lock or a write lock. Lock mode is usually used in conjunction with the lock type.

Divide according to the lock mode

Read lock

Read lock, also known as shared lock / S lock / share locks.

A read lock is a lock created by a transaction (such as transaction A) during a read operation (such as reading a table or reading a row), and other transactions can read this data concurrently (locked). However, the data cannot be modified (unless the user holding the lock has released the lock).

After transaction An adds a read lock to the data, other transactions can still add a read lock (share) to it, but not a write lock.

Add a read lock to the record

InnoDB supports table and row locks, adding locks on rows (that is, records) instead of locking the record, but on the corresponding index of the record. If there is no index in the where condition, all records are locked.

The explicit lock statement is:

Note: the reading mentioned here refers to the current reading, and snapshot reading does not require locking. Ordinary select readings are generally snapshot readings. In addition to explicit locking statements such as select...lock in share mode, it becomes the current read. At the serializable level of the InnoDB engine, ordinary select reads also become snapshot reads.

In addition, it should be noted that for the analysis of the locking process of a row lock, it is necessary to combine the analysis according to the transaction isolation level, whether an index is used (which type of index), the existence of records and other factors to determine where the lock is added.

Several cases of adding read Lock in innodb engine

A normal query adds an S lock to the record at the isolation level of serializable. But it also depends on the scenario: non-transactional reads (auto-commit) need not be locked at the Serializable isolation level

Serializable isolation level: if the query condition is a unique index and the only equivalent query: add an S lock on the record; non-unique condition query (when the query will scan multiple records): record itself + record gap (need to analyze the scope of the gap), add S lock

Select... In share mode, which adds an S lock to the record, but the locking behavior varies depending on the isolation level:

RC isolation level: is to add an S lock to the record. RR/Serializable isolation level: if the query condition is a unique index and the only equivalent query: the S lock is added on the record; the non-unique condition query (when the query scans multiple records): the record itself + the gap of the record (the scope of the gap needs to be analyzed specifically), plus S lock

Insert operations are usually unlocked, but if duplicate key is detected (or there is a marked deleted duplicate key) when inserting or updating records, an S lock is added for normal insert/update, while for things like replace into or insert... SQL statements such as on duplicate add an X lock.

Insert... When select inserts data, it will add S lock to the data scanned on select table.

Foreign key check: when we delete a record on a parent table, we need to check whether there are reference constraints, which will scan the corresponding record on the child table and add an S lock.

Add a read lock to the table

Table locks are implemented by the MySQL server and can be used regardless of the storage engine. It is common to lock the entire table when executing a DDL statement, such as ALTER TABLE. You can also explicitly lock a table when you execute a SQL statement.

The explicit lock statement to the table is:

When using the MYISAM engine, we usually do not need to lock manually, because the MYISAM engine automatically locks our sql statements, and the whole process does not require user intervention:

Query statement (select): automatically adds a read lock to the table involved

Update statements (update, delete, insert): write locks are automatically added to the tables involved.

Write lock

Write lock, exclusive lock / X lock / exclusive locks. The blocking of the write lock is much stricter than the read lock. After a transaction adds a write lock to the data, other transactions can neither read nor change the data.

In the same range as a read lock, a write lock can be added to a record or to a table.

Put a write lock on the record

To put a write lock on the record, the engine needs to use InnoDB.

Usually, ordinary select statements will not be locked (except for the isolation level of Serializable). To add an exclusive lock in a query, you need to use the following statement:

Add a write lock when querying:

Like read locks, write locks are added to the index.

Add a write lock when updating:

Put a write lock on the table

The statement to explicitly lock the table is:

When the engine selects myisam, the insert/update/delete statement automatically adds an exclusive lock to the table.

Read-write lock compatibility:

The read lock is shared, it does not block other read locks, but it blocks other write locks

The write lock is exclusive and blocks other read and write locks

Summary: reading is not mutually exclusive, reading and writing are mutually exclusive.

Intention lock

An intention lock is a table-level lock that does not conflict with a row-level lock, indicating the type of lock (S lock or X lock) required by the records in the table (which actually tells you that a row lock (the type of row lock) already exists in this table, so it's called an intention lock. InnoDB supports multiple granularities of locks, allowing the coexistence of row-level locks and table-level locks.

Intention locks are divided into:

Intention shared lock (IS lock): the IS lock indicates that the current transaction intends to set the shared lock on the row in the table

When the following statement is executed, the IS lock is acquired first, because this operation acquires the S lock: acquire S lock: select. Lock in share mode

Intention exclusive lock (IX lock): the IX lock indicates that the current transaction intends to set an exclusive lock on the row in the table

The IX lock is acquired first when the following statement is executed, because this operation acquires the X lock: acquire the X lock: select. For update

Before a transaction can acquire S and X locks on a table, it must acquire the corresponding IS locks and IX locks, respectively.

What is the purpose of the intention lock:

If another transaction attempts a shared or exclusive lock at that table level, it is blocked by the table-level exclusive lock controlled by the first transaction. The second transaction does not check individual page or row locks before locking the table, but only checks the intention locks on the table.

Example: table test_user:

Transaction An acquired an exclusive lock for a row and did not commit

Transaction B wants to acquire the table shared lock of the test_user table

Because shared locks and exclusive locks are mutually exclusive, transaction B must ensure that when trying to add a shared lock to the test_user table:

Currently no other transaction holds an exclusive lock on the users table (table exclusive lock).

Currently no other transaction holds an exclusive lock (row exclusive lock) for any row in the users table.

In order to detect whether the second condition is met, transaction B must detect whether there is an exclusive lock on each row of the test_user table while ensuring that there are no exclusive locks. This is obviously an inefficient approach, but with intention locks, the situation is different:

Because transaction An acquires two locks at this time: the intention exclusive lock on the users table and the exclusive lock on the data row with id 28.

Transaction B wants to acquire the shared lock of the test_user table:

Transaction B only needs to detect whether transaction A holds the intention exclusive lock of the test_user table to know that transaction A must hold the exclusive lock of some data rows in the table, then transaction B's lock request to the test_users table will be excluded (blocked), so that there is no need to detect whether every row of data in the table has an exclusive lock.

Transaction C also wants to acquire an exclusive lock for a row in the users table:

Transaction C detected that transaction A holds an exclusive lock on the test_user table.

Intention locks are not mutually exclusive, so transaction C acquires the intention exclusive lock of the test_user table

Because there is no exclusive lock on the data row with an id of 31, transaction C finally successfully acquires the exclusive lock on that data row.

There is no mutual exclusion between the intention lock and the intention lock, but there is some compatibility mutual exclusion between the intention lock and other table locks, as follows:

Compatibility and repulsion between intention locks:

Intention locks and normal exclusive / shared locks are mutually exclusive:

Self-increasing lock

When we design a table structure, we usually set the primary key to self-growing (think about why? ).

In the InnoDB storage engine, a self-growing counter is set for each self-growing field. We can execute the following statement to get the current value of this counter:

When we perform the insert operation, the operation will perform the + 1 operation according to the current value of the self-growing counter and assign the self-growing column, which we call the auto-inc Locking, that is, the self-growing lock, which actually uses a special table lock mechanism. If the insert operation occurs in a transaction, the lock is released immediately after the insert operation is completed, rather than waiting for the transaction to commit.

Classified by type of lock

Global lock

The so-called global lock is actually locking the entire database instance.

A database instance is different from a database:

A database is a warehouse that stores data. In mysql, a database is actually a collection of data files (that is, what we usually call database, such as creating a database statement is create database...).

Database instance refers to the application that accesses the database. In Mysql, it is the mysqld process.

To put it simply, the database instance contains the various databases you have created.

Putting a global lock on the database instance will cause the entire library to be read-only (which is very dangerous).

In general, a typical use scenario for global locks is for full-library backups, that is, select all the tables in the database. Note, however, that making the entire library read-only can cause some serious problems:

Add a global lock to the main library. During the locking period, no update operation can be performed, and basically many functions of the business are unavailable.

If a global lock is added to the slave library, the master-slave synchronization cannot be performed during the locking period, which will cause the master-slave synchronization delay.

The locking statement for a global lock is:

The way to unlock the global lock is:

Just disconnect the session that executes the global lock

Execute unlocked sql statement: unlock tables

If you need a database backup, you can use the official logical backup tool mysqldump.

Why do you need FTWRL when you already have the dump tool? Consistent reading is good, but only if the engine supports this isolation level. For example, MyISAM is an engine that does not support transactions. At this point, we need to use the FTWRL command.

If there is a read and write before the FTWRL, the FTWRL will wait for the read and write to be completed before execution.

When FTWRL executes, it brushes dirty pages of data to disk. To maintain data consistency, FTWRL is executed when all transactions are committed.

The implementation of global locks still depends on metadata locks.

Metadata lock

Metadata locks (MetaData Lock), also known as MDL locks, are used to protect metadata information, and system-level locks cannot be actively controlled. In the MySQL5.5 version, MDL locks were introduced, mainly to maintain the consistency of metadata under the simultaneous operation of DDL and DML in the concurrent environment. For example, this is the case:

Isolation level: RR

Without the protection of metadata locks, transaction 2 can perform DDL operations directly, resulting in an error in transaction 1. The MDL lock was added in the MYSQL5.5 version to protect this from happening. Because transaction 1 opens the query, then the metadata lock is obtained, and the mode of the lock is MDL read lock. Transaction 2 needs to obtain the MDL write lock if it wants to execute DDL. Because the read and write locks are mutually exclusive, transaction 2 needs to wait for transaction 1 to release the read lock.

When adding, deleting and changing the records in the table (DML operation), the MDL read lock is automatically added.

When the structure of the table (DDL operation) is modified, MDL write locks are automatically added.

Granularity of MDL lock

MDL locks are implemented at the Mysql server level, not in the storage engine plug-in. According to the scope of locking, MDL locks can be divided into the following categories:

Mode of MDL lock

Page level lock

In MySQL, a lock whose granularity is between a row-level lock and a table-level lock. The speed of table-level lock is fast, but the conflict is more, the row-level conflict is less, but the speed is slow. So take the eclectic page level and lock the adjacent set of records one at a time. Different storage engines support different locking mechanisms. Depending on the storage engine, the characteristics of locks in MySQL can be roughly summarized as follows:

Page-level lock is a unique locking level in MySQL, which is applied to BDB engine, and its concurrency is general. The characteristic of page-level lock is that the lock granularity is between row-level lock and table-level lock, so the resource overhead needed to obtain lock and the concurrent processing power that can be provided are also between the above two. In addition, page-level locking is the same as row-level locking, deadlocks occur.

Lock granularity comparison: table level lock > page level lock > row level lock

Table level lock

Table lock as we have introduced above, compared to fine-grained locking of row locks, table locks lock the entire table. Because the locking of the whole table is not as complicated as that of row locking, locking is faster than row locking, and deadlock does not occur (because the transaction acquires the table lock that you want to add at once). However, the table lock also has some problems: the scope of the lock is too large, in the case of high concurrency, it will lead to a higher collision probability of the lock, so the concurrency performance will be greatly reduced.

The locking mode of the table lock

When the engine selects MYISAM

The MYISAM engine only supports table locks, not row locks.

The statement to manually add a table-level lock is as follows:

When using the MYISAM engine, we usually do not need to lock manually, because the MYISAM engine automatically locks our sql statements, and the whole process does not require user intervention:

Query statement (select): automatically adds a read lock to the table involved

Update statements (update, delete, insert): automatically add write locks to the tables involved

When the engine selects InnoDB

The InnoDB engine supports both row-level and table-level locks, and the default is row-level locks.

The table of the InnoDB engine is manually locked, and the read / write lock is also added using the lock table {tableName} read/write statement.

In addition, innodb supports a table-level lock: intention lock (described above).

In general, the table-level locks of the InnoDB engine contain five lock modes:

LOCK_IS: ideographic read lock

LOCK_IX: ideographic write lock

LOCK_S: table read lock

LOCK_X: table write lock

LOCK_AUTO_INC: self-adding lock

Row level lock

In the process of writing business code, we are most exposed to row-level locks (table-level locks are generally not recommended due to performance problems). Row-level locks have significant performance advantages over table-level locks:

Fewer conflicts: there are only a few lock conflicts when accessing different records in multithreading

The granularity of the lock is small: you can lock a single row for a long time without affecting other rows, so the concurrency is the highest.

However, when using row locks, deadlocks are very easy to occur if you don't pay attention to them (there is no deadlock in table locks), so you need to pay attention to the order and scope of locking when using row locks.

InnoDB's row locks are implemented by locking index items, which means that row locks are used only when querying records through the index, and performance is greatly compromised if table locks are used if the index query data is not removed.

Remember: row locks are also called record locks, and record locks are added to the index.

The where condition specifies the primary key index: lock on the primary key index

The wehre condition specifies a secondary index: record locks are added not only to the secondary index, but also to the clustered index corresponding to the secondary index.

If the where condition cannot go through the index: MySQL will lock all data rows of the entire table, and the storage engine layer will return all records to the MySQL server for filtering.

Record lock: LOCK_REC_NOT_GAP (lock records only)

Record locks are the simplest row locks. For example, at the RR isolation level, when the select * from t_user where id = 1 for update statement is executed, the record id = 1 (where id is the primary key) is actually locked (the lock is added to the clustered index).

Record locks are always added to the index, and even if a table is not indexed, the database implicitly creates an index. If the column specified in the WHERE condition is a secondary index, the record lock is added not only to the secondary index, but also to the clustered index corresponding to the secondary index.

Note that if the SQL statement cannot use the index, the full table scan will be performed with the primary index, and MySQL will add record locks to all rows of data in the entire table.

If a WHERE condition cannot be quickly filtered by the index, the storage engine layer will lock all records and return them, which will be filtered by the MySQL Server layer. When there is no index, it will not only consume a lot of lock resources, increase the cost of the database, but also greatly reduce the concurrent performance of the database, so the update operation must remember to take the index (because the update operation will add X lock).

Several types of row-level locks:

Gap lock: LOCK_GAP (lock gap only)

Gap lock is a kind of interval lock. Locks are applied to free space that does not exist, or between two index records, or the first index record, or the space after the last index, to indicate that only a range is locked (typically when a range query is made and the isolation level is at RR or Serializable intervals).

GAP locks are generally used under the RR isolation level. GAP locks are used mainly to prevent phantom readings. Inserting or updating data is not allowed in the interval locked by the GAP lock.

The generation condition of gap lock: the isolation level of innodb is Repeatable Read or Serializable.

The scope of the gap lock describes:

Isolation level: RR

Take the Student table as the sample data, id as the primary key and stu_code as the student number to add a general index.

Gap lock area definition:

Look for the nearest value A to the left according to the retrieval condition. As the left interval, look for the nearest value B to the right. As the right interval, the gap lock is (Aline B).

If you can't find the nearest value A to the left, it is infinitesimal. As the left interval, look for the nearest value B to the right. As the right interval, the gap lock is (infinitesimal, B).

To the left to find the nearest value A, as the left interval, to the right to find the nearest value B, that is, infinity, as the right interval, the gap lock is (A, infinity).

Example of an interval (A _ quotient B):

Transaction 1:

Select * from student where stu_code = 4 for update

Transaction 2:

Insert into student vaues (2, 2,'A'); insert into student values (4, 5,'B')

According to the sql statement analysis of transaction 1, the scope of the gap lock is: the stu_code = 4 record exists, so the nearest index value in the left interval is stu_code = 3, and the right interval is the nearest index value is stu_code = 7, so the gap range is: (3) 7), so the two insert statements of transaction 2, one is outside the scope, the other is in the range, and the one in the range can be inserted, while the one in the range is blocked. 2,'A') can be inserted successfully (4d5,'B') insert blocking.

Example of an interval (infinitesimal, B):

Transaction 1:

Select * from student where stu_code = 1 for update

Transaction 2:

Insert into student vaues (2, 0,'c'); insert into student vaues (2, 2,'r'); insert into student vaues (5, 2,'o')

According to the sql statement analysis of transaction 1, the scope of the gap lock is: stu_code = 1 exists, there is no recent record on the left, so it is infinitesimal on the left, and the nearest index value on the right is stu_code = 3, so the gap lock range is: (infinitesimal, 3). So the execution of the first and second insert sql statements of transaction 2 is blocked and is within the scope of the gap lock. The third insert sql statement can be executed successfully and is not within the scope of the gap lock.

Example of interval (A, infinity):

Transaction 1:

Select * from student where stu_code = 7 for update

Transaction 2:

Insert into student vaues (2,2,'m'); insert into student vaues (20,22,'j')

According to the sql statement analysis of transaction 1, the scope of the gap lock is: stu_code = 7 exists, the nearest index value on the left is stu_code = 4, and there is no index value on the right, so the range of the gap lock is: (4, infinity), the first inset statement can be executed successfully, not within the gap range; the second insert statement execution is blocked, is in the gap lock range.

If the query statement is not recorded in the database, how do you lock it?

The above query is recorded, if the query statement is not recorded in the database, then how to lock it? Let's move on:

Transaction 1:

Update student set stu_name = '000' where stu_code = 10

Transaction 2:

Insert into student vaues (2,2,'m'); insert into student vaues (20,22,'j')

According to the above execution statement, no record can be found, so take the most recent record to the left as the left interval, that is, the scope of the gap lock is: (7, infinity), the first insert statement is not within the interval and can be executed successfully; the second insert execution statement is blocked in the interval and fails. If the where condition of transaction 1 is greater than 10, it is also to the left to find the nearest record value as the left interval, so the gap lock range is also: (7, infinity)

Summary: conditions for the generation of gap locks

Under the RR/Serializable isolation level: Select. When Where...For Update:

InnoDB uses row locks when only unique index queries are used and only one record is locked.

A Next-Key Lock is generated when only a unique index query is used, but the retrieval condition is range retrieval, or when the search result does not exist (trying to lock data that does not exist).

When using ordinary index retrieval, no matter what kind of query it is, as long as it is locked, it will produce a gap lock.

When using both a unique index and a normal index, a gap lock will also occur because the data rows are sorted first according to the normal index and then by the unique index.

Next key lock: LOCK_ORDINARY, also known as Next-Key Lock

The Next-Key lock is a combination of record lock and gap lock. As with gap locks, there is no Next-key lock under the RC isolation level (unless it is forced to open by changing the configuration), only the RR/Serializable isolation level.

MySQL InnoDB works under the repeatable readable isolation level (RR) and locks data rows in the way of Next-Key Lock, which can effectively prevent the occurrence of phantom reading. Next-Key Lock is a combination of row lock and gap lock. When InnoDB scans an index record, it will first add a row lock (Record Lock) to the index record, and then add a gap lock (Gap Lock) to the gap on both sides of the index record. After the gap lock is added, other transactions cannot modify or insert records in this gap.

When the index of a query contains unique attributes (unique index, primary key index), the Innodb storage engine optimizes next-key lock to record lock, that is, only the index itself is locked, not the range.

Insert intent lock: LOCK_INSERT_INTENSION

Insert intention lock, used when inserting records, is a special gap lock. This lock, which indicates the intention to insert, is available only when the insert statement is executed.

Assuming that the values of indexed records are id = 1 and id = 5 (there is no record between 1 and 5), individual transactions try to insert id = 2 and id = 3, respectively. Before obtaining an exclusive lock for inserting rows, each transaction locks the space between 1 and 5 with an insert intent lock, but does not block each other. Because there is no conflict between inserting intention locks.

Inserting an intention lock conflicts with a gap lock or Next-Key lock: the purpose of a gap lock is to lock the interval to prevent other transactions from inserting data to cause illusory reading.

In the above scenario, assuming that transaction An acquires the gap lock of id in the interval (1d5) ahead of time, transaction B will first attempt to acquire the insert intention lock when it tries to insert id = 2, but due to the insertion intention lock and gap lock conflict, the insertion fails and the phantom reading is avoided.

At this point, I believe you have a deeper understanding of "what are the patterns and types of locks in MYSQL?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.