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

Today, the editor will share with you the relevant knowledge points about MySQL locks and classification. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article. Let's take a look.

1. Database concurrency scenario

In high concurrency scenarios, without considering other middleware, the following scenarios exist in the database:

Read: there are no problems and there is no need for concurrency control.

Read and write: there are thread safety problems, which may cause transaction isolation problems, may encounter dirty reading, phantom reading, and can not be read repeatedly.

Write: there are thread safety problems, there may be update loss problems, such as the first type of update loss, the second type of update loss.

In view of the above problems, the SQL standard stipulates that the problems that may occur under different isolation levels are different:

The four isolation levels of MySQL:

Isolation level dirty read not repeatable read READ UNCOMMITTED: uncommitted read may occur READ COMMITTED: committed read solution may occur REPEATABLE READ: repeatable read solution may occur SERIALIZABLE: serializable solution may occur

It can be seen that at the REPEATABLE READ isolation level, MySQL actually solves the problem of unrepeatability and basically solves the problem of phantom reading, but it still exists in extreme cases.

So is there any way to solve it? Generally speaking, there are two options:

1 ️read operation MVCC, write operation locked

For reading, under RR-level MVCC, a ReadView is generated when a transaction is opened, and then a qualified historical version is found through ReadView. This version is built by undo logs, and when ReadView is generated, a snapshot is actually generated, so the SELECT query at this time is snapshot read (or consistent read). We know that under RR During the execution of a transaction, only the first SELECT operation will generate a ReadView, and subsequent SELECT operations will reuse the ReadView, thus avoiding the problem of unrepeatable reading and phantom reading to a large extent.

For writes, since no records in the table are locked during snapshot reads or consistent reads, and the transaction of ReadView is a historical version, and there is no conflict with the latest version of write operations, other transactions are free to make changes to the records in the table.

2 ️read and write operations are locked

If some of our business scenarios are not allowed to read the old version of the record, but must read the latest version of the record every time, for example, in the transaction of bank deposits, you need to read out the balance of the account first, then add it to the amount of this deposit, and then write it to the database. After reading out the account balance, you do not want other transactions to access the balance, and other transactions will not be able to access the account balance until this deposit transaction is completed. In this way, the record needs to be locked when it is read, which means that read and write operations are queued like write-write operations.

For dirty reading, it is because the current transaction reads a record written by another uncommitted transaction, but if another transaction locks the record while writing the record, then the current transaction cannot continue to read the record, so there will be no dirty reading problems.

For non-repeatable reading, it is because the current transaction reads a record first, and after another transaction makes changes to the record and commits, the current transaction will get a different value when it is read again. If the record is locked when the current transaction reads the record, then another transaction cannot modify the record, and of course, non-repeatable reading will not occur.

For phantom reading, it is because the current transaction reads a range of records, and then another transaction inserts new records into that range, and when the current transaction reads the records in that range again, it finds the newly inserted new records. we call the newly inserted records phantom records.

How do you understand this range? As follows:

Suppose there is only one piece of id=1 data in the table user.

When transaction An executes a query operation with id = 1, it can query the data. If it is a range query, such as id in (1 and 2), only one piece of data will be queried.

At this point transaction B performs a new operation with id = 2 and commits.

At this point, transaction An executes the query of id in (1Magol 2) again, and two records are read out, resulting in a phantom reading.

Note: because RR can be read repeatedly, the record of id = 2 cannot be found, so if you execute a update. Where id = 2, and then go to the scope query to find out.

It is not easy to solve the phantom reading problem by locking, because those phantom records do not exist when the current transaction reads the records for the first time, so locking is a bit troublesome because you don't know who to lock.

So how does InnoDB solve it? Let's first take a look at what locks the InnoDB storage engine has.

2. Locks and classification in MySQL

In the MySQL official documentation, the InnoDB storage engine describes the following locks:

Again, it still seems confusing, but we can classify it according to the way we learn locks in JDK:

3. Granularity classification of locks

What is the granularity of locks? The so-called granularity of the lock is the scope of the lock you want to lock.

For example, when you go to the bathroom at home, all you have to do is lock the bathroom. You don't need to lock up the whole house to keep your family out. The bathroom is your locking granularity.

What is the reasonable locking granularity?

In fact, the bathroom is not only used to go to the toilet, but also can take a bath and wash hands. This involves the problem of optimizing the locking granularity.

When you take a bath in the bathroom, in fact, other people can wash their hands in it at the same time, as long as it is isolated, if the toilet, bathtub and wash table are all separated and relatively independent. In fact, the bathroom can be used by three people at the same time, of course, three people can not do the same thing. This refines the lock size so that as long as you close the bathroom door when you take a bath, others can still go in and wash their hands. If the original design of the toilet did not separate the different functional areas, it would not be possible to maximize the use of toilet resources.

Similarly, there is a granularity of locks in MySQL. There are usually three types, row lock, table lock and page lock.

3.1 Row lock

The introduction of shared lock and exclusive lock is actually for a row of records, so it can also be called a row lock.

Locking a record affects only that record, so the locking granularity of row locks is the finest in MySQL. The InnoDB storage engine default lock is a row lock.

It has the following characteristics:

The lock collision probability is the lowest and the concurrency is high.

Because of the small granularity of row locks, the probability of locking resource contention is the least, so the probability of lock conflicts is lower and the concurrency is higher.

High cost and slow locking

Locks are very performance-consuming. Just imagine, if you lock multiple pieces of data in the database, it will inevitably take up a lot of resources, and you need to wait for the lock to be released before it can be locked.

There will be a deadlock.

You can look down on what a deadlock is.

3.2 Table lock

Table-level lock is a table-level lock, which will lock the whole table, which can avoid deadlock and is the largest granularity locking mechanism in MySQL.

The default lock for the MyISAM storage engine is a table lock.

It has the following characteristics:

Low overhead and fast locking

Because the whole table is locked, the speed must be faster than that of a single piece of data.

There is no deadlock.

If the whole table is locked, other transactions can't get the lock at all, and naturally there will be no deadlock.

The lock size is large, the probability of lock conflict is high, and the concurrency is low.

3.3 Page Lock

Page-level lock is a unique locking level in MySQL, which is not common in other database management software.

The granularity of page-level locks is between row-level locks and table-level locks, so the resource overhead required to acquire locks and the concurrent processing power that can be provided are also between the above two. In addition, page-level locks are the same as row-level locks, deadlocks occur.

Row lock table lock page lock granularity between slow and fast locking efficiency between low probability of conflict between high concurrency performance and general performance overhead whether the deadlock is 4. Compatibility classification of locks

Data reading in MySQL is mainly divided into current reading and snapshot reading:

Snapshot read

Snapshot read, read is snapshot data, unlocked ordinary SELECT belong to snapshot read.

SELECT * FROM table WHERE...

Current read

The current reading is to read the latest data, not historical data, locked SELECT, or to add, delete or modify the data will do the current reading.

SELECT * FROM table LOCK IN SHARE MODE;SELECT FROM table FOR UPDATE;INSERT INTO table values... DELETE FROM table WHERE... UPDATE table SET...

In most cases, we operate the database in the current read situation, while in concurrent scenarios, we need to use shared locks and exclusive locks in MySQL to allow read-read situations not to be affected, but also to block write-write, read-write, or write-read operations.

4.1 shared and exclusive locks

Shared lock (Shared Locks), also known as read lock, or S lock for short. Data can be read concurrently, but no transaction can modify the data.

Exclusive lock (Exclusive Locks) can also be called exclusive lock or write lock, or X lock for short. If something adds an exclusive lock to a row, it can only be read and written by this transaction. before the end of the transaction, other transactions cannot add any locks to it, other processes can read and cannot write, and need to wait for it to be released.

Let's analyze the situation of acquiring locks: if there are transaction An and transaction B

Transaction An acquires the S lock of a record, and transaction B also wants to acquire the S lock of the record, then transaction B can also acquire the lock, that is, transaction An and transaction B hold the S lock of the record at the same time.

If transaction B wants to acquire the X lock for the record, the operation is blocked until the S lock is released after transaction A commits.

If transaction A first acquires an X lock, no matter whether transaction B wants to acquire the record's S lock or X lock, it will be blocked until transaction A commits.

Therefore, we can say that S lock and S lock are compatible, S lock and X lock are not compatible, X lock and X lock are not compatible.

4.2 intention lock

Intention shared lock (Intention Shared Lock), or IS lock for short. When a transaction is ready to add an S lock on a record, you need to add an IS lock at the table level first.

Intention exclusive lock (Intention Exclusive Lock), referred to as IX lock. When a transaction is ready to add an X lock on a record, you need to add an IX lock at the table level first.

Intention locks are table-level locks, which are proposed only to quickly determine whether the records in the table are locked or not when table-level S-locks and X-locks are added, so as to avoid traversing to check whether there are locked records in the table. That is to say, IS lock and IS lock are compatible, IX lock and IX lock are compatible.

Why do you need an intention lock?

InnoDB's intention to lock the situation where the primary user has multiple granularity locks. For example, transaction A should add an S lock to a table, and if a row in the table has been locked by transaction B, then the application for that lock should also be blocked. If there is a lot of data in the table, it will be expensive to check the lock flag row by row, and the performance of the system will be affected.

For example, if there are 100 million records in the table and transaction A locks several of the records on rows, transaction B needs to add a table-level lock to the table, and if there is no intention lock, then go to the table to find out whether the 100 million records are locked. If there is an intention lock, then if transaction A first adds an intention lock and then an X lock before updating a record, transaction B first checks whether there is an intention lock on the table and whether the existing intention lock conflicts with the lock it is going to add. If there is a conflict, wait until transaction An is released without having to detect it one by one. When transaction B updates the table, it doesn't need to know which row is locked, it just needs to know that one row is locked anyway.

To put it bluntly, the main function of an intention lock is to handle the contradiction between a row lock and a table lock, to show that a transaction is holding a lock on a row, or is ready to hold a lock.

Compatibility of various locks at the table level:

SISXIXS compatible incompatible incompatible IS compatible incompatible X incompatible IS compatible incompatible incompatible 4.3.Lock of read operation

For MySQL read operations, there are two ways to lock.

1 ️SELECT * FROM table LOCK IN SHARE MODE

If the current transaction executes this statement, it adds an S lock to the read record, allowing other transactions to continue to acquire the S lock of those records (for example, other transactions also use SELECT. LOCK IN SHARE MODE statement to read these records), but cannot acquire the X lock of these records (for example, using SELECT. FOR UPDATE statement to read these records, or modify them directly.

If other transactions want to acquire the X locks of these records, they block until the S locks on these records are released after the current transaction commits

2 ️SELECT FROM table FOR UPDATE

If the current transaction executes this statement, it adds an X lock to the read record, which does not allow other transactions to acquire the S lock of these records (for example, other transactions use SELECT. LOCK IN SHARE MODE statement to read these records), nor is it allowed to acquire X locks for these records (for example, using SELECT. FOR UPDATE statement to read these records, or modify them directly.

If other transactions want to acquire the S or X locks of these records, they block until the X locks on these records are released after the current transaction commits.

4.4 Lock for write operation

For MySQL write operations, the commonly used are DELETE, UPDATE, INSERT. Implicitly lock, automatically lock, unlock.

1 ️DELETE

The process of DELETE a record is to locate the location of the record in the B+ tree, then acquire the X lock of the record, and then perform the delete mark operation. We can also think of the process of locating the location of the record to be deleted in the B+ tree as a locked read to acquire the X lock.

2 ️INSERT

In general, the operation of inserting a new record is not locked, and InnoDB uses an implicit lock to protect the newly inserted record from being accessed by other transactions before the transaction commits.

3 ️UPDATE

There are three situations when UPDATE a record:

① if the key value of the record is not modified and the storage space occupied by the updated column does not change before and after the modification, the location of the record is located in the B+ tree, then the X lock of the record is acquired, and finally the modification operation is carried out at the location of the original record. In fact, we can also think of the process of locating the location of the record to be modified in the B+ tree as a lock read to acquire the X lock.

② if the key value of the record is not modified and the storage space occupied by at least one updated column changes before and after the modification, first locate the location of the record in the B+ tree, then acquire the record's X lock, delete the record completely (that is, move the record completely into the junk list), and finally insert a new record. The process of locating the record to be modified in the B+ tree is treated as a lock read to acquire an X lock, and the newly inserted record is protected by an implicit lock provided by the INSERT operation.

If ③ modifies the key value of the record, it is equivalent to another INSERT operation after the DELETE operation on the original record, and the locking operation needs to be carried out in accordance with the rules of DELETE and INSERT.

PS: why can other transactions be read when a write lock is on?

Because InnoDB has a MVCC mechanism (multi-version concurrency control), snapshot reads can be used without blocking.

4. Granularity classification of locks

What is the granularity of locks? The so-called granularity of the lock is the scope of the lock you want to lock.

For example, when you go to the bathroom at home, all you have to do is lock the bathroom. You don't need to lock up the whole house to keep your family out. The bathroom is your locking granularity.

What is the reasonable locking granularity?

In fact, the bathroom is not only used to go to the toilet, but also can take a bath and wash hands. This involves the problem of optimizing the locking granularity.

When you take a bath in the bathroom, in fact, other people can wash their hands in it at the same time, as long as it is isolated, if the toilet, bathtub and wash table are all separated and relatively independent. In fact, the bathroom can be used by three people at the same time, of course, three people can not do the same thing. This refines the lock size so that as long as you close the bathroom door when you take a bath, others can still go in and wash their hands. If the original design of the toilet did not separate the different functional areas, it would not be possible to maximize the use of toilet resources.

Similarly, there is a granularity of locks in MySQL. There are usually three types, row lock, table lock and page lock.

4.1 Row Lock

The introduction of shared lock and exclusive lock is actually for a row of records, so it can also be called a row lock.

Locking a record affects only that record, so the locking granularity of row locks is the finest in MySQL. The InnoDB storage engine default lock is a row lock.

It has the following characteristics:

The lock collision probability is the lowest and the concurrency is high.

Because of the small granularity of row locks, the probability of locking resource contention is the least, so the probability of lock conflicts is lower and the concurrency is higher.

High cost and slow locking

Locks are very performance-consuming. Just imagine, if you lock multiple pieces of data in the database, it will inevitably take up a lot of resources, and you need to wait for the lock to be released before it can be locked.

There will be a deadlock.

You can look down on what a deadlock is.

4.2 Table lock

Table-level lock is a table-level lock, which will lock the whole table, which can avoid deadlock and is the largest granularity locking mechanism in MySQL.

The default lock for the MyISAM storage engine is a table lock.

It has the following characteristics:

Low overhead and fast locking

Because the whole table is locked, the speed must be faster than that of a single piece of data.

There is no deadlock.

If the whole table is locked, other transactions can't get the lock at all, and naturally there will be no deadlock.

The lock size is large, the probability of lock conflict is high, and the concurrency is low.

4.3 Page Lock

Page-level lock is a unique locking level in MySQL, which is not common in other database management software.

The granularity of page-level locks is between row-level locks and table-level locks, so the resource overhead required to acquire locks and the concurrent processing power that can be provided are also between the above two. In addition, page-level locks are the same as row-level locks, deadlocks occur.

Row lock table lock page lock granularity between slow and fast locking efficiency between low probability of conflict between high concurrency performance and general performance overhead whether the deadlock between the two is 5. Algorithm to realize classification

As for the introduction of the lock above, we can actually know that the main difference is in the granularity of the lock, and the lock used in InnoDB is a row lock, also known as a record lock, but note that this record refers to by locking the index entry on the index.

The row lock implementation feature of InnoDB means that InnoDB uses row-level locks only if the data is retrieved by index conditions, otherwise InnoDB will use table locks.

Whether using a primary key index, a unique index, or a normal index, InnoDB uses row locks to lock the data.

Row locks can be used only if indexes are actually used in the execution plan: even if index fields are used in the condition, it is up to MySQL to decide whether or not to use indexes to retrieve data by judging the cost of different execution plans. If MySQL believes that full table scans are more efficient, such as for very small tables, it will not use indexes, in which case InnoDB will use table locks instead of row locks.

At the same time, when we retrieve data with a range condition instead of an equal condition and request a lock, InnoDB locks the index entries of the existing data records that meet the criteria.

But even row locks are divided into various types in InnoDB. In other words, even if a row lock is added to the same record, the effect will be different if the type is different. There are usually the following common types of row locks.

5.1 Record Lock

Record lock, lock on a single index record.

Record Lock always locks the index, excluding the record itself, even if there is no index on the table, then innodb creates a hidden clustered primary key index in the background, then the hidden clustered primary key index is locked.

Record locks can be divided into S-locks and X-locks. When a transaction acquires an S-type record lock of a record, other transactions can continue to acquire the S-type record lock of the record, but can not continue to acquire the X-type record lock; when a transaction acquires an X-type record lock of a record, other transactions can neither continue to acquire the S-type record lock of the record nor continue to acquire the X-type record lock.

5.2 Gap Locks

Gap lock, lock the gap before and after the index, not the index itself.

MySQL can solve the phantom reading problem under the REPEATABLE READ isolation level, and there are two solutions, either using the MVCC solution or using a locking solution. However, there is a problem when using the locking scheme, that is, when the transaction performs a read operation for the first time, those phantom records do not yet exist, and we cannot add a record lock to these phantom records. So we can lock it with a gap lock.

If there is such a table:

CREATE TABLE test (id INT (1) NOT NULL AUTO_INCREMENT, number INT (1) NOT NULL COMMENT 'digital', PRIMARY KEY (id), KEY number (number) USING BTREE) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;# insert the following data INSERT INTO test VALUES (1,1); INSERT INTO test VALUES (5,3); INSERT INTO test VALUES (7,8); INSERT INTO test VALUES (11,12)

As follows:

Start a transaction A:

BEGIN;SELECT * FROM test WHERE number = 3 FOR UPDATE

At this point, there will be a lock between ((1) (1), (5)) and ((5)), (7)).

If you open a transaction B to insert data at this time, it is as follows:

BEGIN;# blocking INSERT INTO test (id, number) VALUES (2Jing 2)

The results are as follows:

Why can't you insert it? Because the record (2) is to be inserted, insertion is not allowed because it falls right between ((1) number), (5)) and ((5)), (7)), and is locked. If it is outside the scope, of course, it can be inserted, such as:

INSERT INTO test (id, number) VALUES (8); 5.3 Next-Key Locks

Next-key locks is the combination of the record lock on the index record and the gap lock on the gap before the index record, including the record itself. Each next-key locks is the interval between the front and the back, that is to say, the gap lock is only the gap of the lock, and the gap lock does not lock the record row. Next-key locks is to lock the right boundary row on the basis of the gap lock.

By default, InnoDB runs at the REPEATABLE READ isolation level. In this case, InnoDB uses Next-Key Locks locks for search and index scanning, which prevents phantom reads from happening.

6. Optimistic lock and pessimistic lock

Optimistic lock and pessimistic lock are not concrete locks, but a kind of lock idea, which is not only reflected in MySQL, but also can be applied in common middleware such as Redis.

6.1 optimistic lock

The so-called optimistic lock is to hold an optimistic attitude, when we update a record, assuming that no one else is manipulating the data during this period of time.

Common ways to implement optimistic locks

A common way to achieve this is to add a version field to the table, control the version number, and + 1 each time the data is modified.

Before updating the data, first query the version version number of the data, then perform the business operation, and then compare the found version number with the version number in the current database before updating the data. if the same, it means that no other thread has modified the data, otherwise the corresponding exception handling will be made.

6.2 pessimistic lock

The so-called pessimistic lock is to hold a pessimistic attitude and assume from the beginning that the revised data will be modified by others.

There are two ways to realize pessimistic lock.

Shared lock (read lock) and exclusive lock (write lock), see above.

7. Deadlock

Refers to two or more processes in the implementation process, due to competition for resources or communication with each other caused by a blocking phenomenon, without external force, they will not be able to move forward. At this point, it is said that the system is in a deadlock state or the system has a deadlock.

Conditions of production

Mutually exclusive condition: a resource can only be used by one process at a time

Request and retention condition: when a process is blocked by a request for resources, it holds on to the resources it has acquired

No deprivation conditions: resources that have been acquired by the process cannot be forcibly deprived until they are used up.

Loop wait condition: a relationship of resources that loop waiting for each other between multiple processes.

The same is true in MySQL, as shown in the following table:

CREATE TABLE `user` (`id` bigint NOT NULL COMMENT 'primary key', `name` varchar (20) DEFAULT NULL COMMENT 'name', `sex` char (1) DEFAULT NULL COMMENT 'gender', `age`varchar (10) DEFAULT NULL COMMENT 'age', `url`varchar (40) DEFAULT NULL, PRIMARY KEY (`id`), KEY `suf_index_ url` (`name` (3) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 # data INSERT INTO `user` (`id`, `name`, `sex`, `age`, `url`) VALUES ('1customers,' averse, '1users,' 18cycles, 'https://javatv.net');INSERT INTO `user` (`id`, `name`, `sex`, `age`, `url`) VALUES (' 2levels, 'breads,' 1bands, '180s,' https://javatv.net');

Execute in the following order:

AB ① BEGIN

two

BEGIN ③ SELECT * FROM user WHERE name='a' FOR UPDATE

four

SELECT * FROM user WHERE name='b' FOR UPDATE ⑤ SELECT * FROM user WHERE name='b' FOR UPDATE

six

SELECT * FROM user WHERE name='a' FOR UPDATE

1. Open two transactions An and B

2. First A queries the data of name='a', then B also queries the data of name='b'.

3. When B does not release the lock, A tries to lock the data of name='b', which will block

4. If transaction B attempts to lock the data of name='a' without releasing the lock, a deadlock occurs.

At this point, MySQL detects the deadlock and ends the execution of the transaction in B. at this point, it switches back to transaction An and finds that the execution of the originally blocked SQL statement is complete. Deadlocks can be viewed at show engine innodb status\ G.

How to avoid

As can be seen from the above case, the key to deadlock is that the locking order of two (or more) Session is not the same, so we should keep the locking order consistent when performing SQL operations, and lock the required data rows at once as much as possible.

These are all the contents of the article "what are the MySQL locks and classifications?" Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

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.