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

This article introduces the relevant knowledge of "what is the principle of Lock lock". In the operation of practical cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Premise

Row-level lock, table-level lock and page-level lock in MySQL are introduced. Row-level lock is the finest lock in Mysql, and row-level lock can greatly reduce the conflict of database operations. Row-level locks are divided into shared locks and exclusive locks. This article will introduce the concept, usage and matters needing attention of shared locks and exclusive locks in detail.

Lock the target

The database is a shared resource used by multiple users. When multiple users access data concurrently, multiple transactions will access the same data at the same time in the database. If there is no control over concurrent operations, incorrect data may be read and stored, and the consistency of the database will be destroyed.

Locks are used to manage concurrency control of common resources. In other words, in the case of concurrency, there will be resource competition, so it needs to be locked. Locking solves the problem of ensuring database integrity and consistency in a multi-user environment.

The objects of Lock are transactions, which are used to lock objects in the database, such as tables, pages, and rows. And general lock objects are released only after a transaction commit or rollback (the release time may vary with different transaction isolation levels).

Deadlock

Deadlock is a common problem in concurrent systems, and it also occurs in the scenario of concurrent read and write requests in database MySQL. A "deadlock" occurs when two or more transactions are waiting for each other to release the lock already held or a loop waiting for lock resources due to inconsistent locking order. A common error message is "Deadlock found when trying to get lock...".

Deadlock scene

For example, A transaction holds X1 lock, applies for X2 lock, B transaction holds X2 lock, and applies for X1 lock. An and B transactions hold the lock and apply for the lock held by the other party to enter the loop, resulting in a deadlock.

InnoDB lock type

In order to analyze deadlocks, it is necessary to have an understanding of the lock types of InnoDB.

The MySQL InnoDB engine implements standard row-level locks: shared locks (S lock) and exclusive locks (X lock)

Different transactions can add S locks to the same row record at the same time.

If a transaction adds an X lock to a row of records, other transactions cannot add an S lock or an X lock, causing the lock to wait.

If transaction T1 holds an S lock for row r, when another transaction T2 requests a lock for r, it does the following:

T2 request S lock is allowed immediately, as a result, T1 T2 holds the S lock of r line.

T2 request X lock cannot be allowed immediately

If T1 holds r's X lock, then T2 requests r's X and S locks cannot be allowed immediately. T2 must wait for T1 to release X locks, because X locks are not compatible with any locks. The compatibility of shared and exclusive locks is as follows:

Gap lock (gap lock)

The gap lock locks a gap to prevent insertion. Assuming that the index column has three values of 2J4 and 8, if you lock 4, you will also lock the gaps (2p4) and (4J8). Other transactions cannot insert records with index values between the two gaps.

However, there is one exception to the gap lock:

If the index column is a unique index, only the record is locked (only row locks are added), not gaps. For federated and unique indexes, a gap lock will still be added if the where condition includes only part of the federated index.

Conditions for producing gaps

Use normal index locking

Use multiple column unique indexes

Use a unique index to lock multiple rows of records.

Next-key lock (Record Lock + Gap Lock key lock)

Next-key lock is actually a combination of row locks plus the gap lock in front of this record. Assuming that there are index values of 10, 10, 11, 13 and 20, then possible next-key lock include:

-(negative infinity, 10]-(10 minutes 11)-(11 minutes 13)-(13 minutes 20)-(20, positive infinity)

Under the RR isolation level, InnoDB uses next-key lock primarily to prevent phantom reading problems.

The key lock is the combination of record lock and gap lock, and its blocking range locks both the record itself and the gap between indexes.

Note: the main purpose of key lock is to avoid Phantom Read. If the isolation level of the transaction is downgraded to RC, the critical lock will also fail.

Intention lock (Intention lock)

In order to support multi-granularity locking, InnoDB allows row locks and table locks to exist at the same time. In order to support locking operations at different granularities, InnoDB supports an additional locking method, called Intention Lock, which is automatically added by InnoDB without user intervention.

The intention lock is to divide the locked object into multiple levels, which means that the transaction wants to lock at a finer granularity.

There are two types of intention locks:

Intention shared lock (IS): the transaction intentionally adds a shared lock to some rows in the table: (the transaction intends to add a row shared lock to the data row, and the transaction must acquire the IS lock of the table before adding a shared lock to the data row.)

Intention exclusive lock (IX): the transaction intentionally adds an exclusive lock to some rows in the table: (the transaction intends to add an exclusive lock to the data row, and the transaction must acquire the IX lock of the table before adding an exclusive lock to the data row.)

Because the InnoDB storage engine supports row-level locks, intentional locks do not actually block any request except a full table scan. The compatibility of table-level intention locks with row-level locks is as follows: what is the use of intention locks? The main function is to deal with the contradiction between row locks and table locks, showing that "a transaction is holding a lock on a row, or is ready to hold a lock." when we need to add an exclusive lock, we need to determine whether any data rows in the table are locked according to the intention lock.

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. In order to solve this problem, a new lock type can be introduced at the table level to represent the locking of the row to which it belongs, which leads to the concept of "intention lock".

For example, if there are 100 million records in the table and transaction A locks several of the records, 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.

The algorithm of row lock Record Lock (single line record)

When a single index is locked, record lock always locks the index, not the data itself. If there is no index in the innodb table, a hidden clustered index is automatically created.

When a sql does not walk any indexes, an X lock will be added to each clustered index, which is similar to a table lock, but in principle it should be completely different from the table lock.

Conditions for recording locks

The condition field that hits a single-row record is a unique index or a primary index; update user_info set name=' Zhang San 'where id=1; / / where id is the only index, Record Lock is used

Record Lock always locks the index records, and if the InnoDB storage engine table is created without any indexes, then the InnoDB storage engine uses an implicit primary key to lock.

Insert intention lock (Insert Intention lock)

An insert intention lock is a gap lock set before inserting a row of records, which signals an insertion mode, that is, when multiple transactions are inserted in the same index gap, they do not have to wait for each other if they are not in the same position in the insertion gap. Suppose a column has an index value of 2Power6, as long as two transactions are inserted in different positions (such as transaction An insert 3, transaction B insert 4), then they can be inserted at the same time.

The lock mode compatibility matrix is the lock already held horizontally and the lock being requested vertically:

This is the end of the content of "what is the principle of Lock Lock"? thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.