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

Lock, in real life, is a tool we want to hide from the outside world. In a computer, it is a mechanism for coordinating multiple processes or county towns to access a resource concurrently. In the database, in addition to the contention of traditional computing resources (CPU, RAM, Imax O, etc.), data is also a kind of resource for many users to share and access. How to ensure the consistency and effectiveness of data concurrent access is a problem that must be solved in all databases, and lock conflicts are also an important factor affecting the performance of database concurrent access. From this point of view, locks are particularly important for databases.

1. Locks in MySQL

There are the concepts of Lock and Latch in MySQL, both of which can be called "locks" in the database, but they have very different meanings.

Latch is commonly called a latch (lightweight lock) because it requires a very short lock time. If the duration is long, the performance of the application will be very poor. In the InnoDB engine, Latch can be divided into mutex (mutex) and rwlock (read-write lock). Its purpose is to ensure the correctness of the critical resources of concurrent threads, and there is usually no deadlock detection mechanism.

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).

For a more detailed explanation of Latch, please refer to: on the in-depth analysis and judgment of MySQL latch contention, this article mainly focuses on Lock locks.

Type of lock

In fact, there are only two kinds of operations on data, namely, read and write, and the database will use different locks for these two operations when implementing locks; InnoDB implements standard row-level locks, that is, shared locks (Shared Lock) and mutexes (Exclusive Lock).

A shared lock (read lock) that allows a transaction to read a row of data.

An exclusive lock (write lock) that allows a transaction to delete or update a row of data.

Their names also imply another feature: shared locks are compatible, while mutexes are not compatible with any other lock:

If you think about their use a little bit, you can figure out why they are designed this way, because the shared lock represents the read operation and the mutex represents the write operation, so we can read in parallel in the database, but can only write serially. Only in this way can we ensure that thread competition will not occur and achieve thread safety.

Lock granularity

According to granularity, Lock locks are mainly divided into table locks, page locks and row locks. Different storage engines have different lock granularity.

Watch lock

Table-level locking is the largest granularity locking mechanism among MySQL storage engines. The most important feature of the locking mechanism is that the implementation logic is very simple and the negative impact of the system is the least. So the speed of acquiring and releasing locks is very fast. Because table-level locks lock the entire table at once, it is a good way to avoid the deadlock problem that bothers us.

Of course, the biggest negative effect of large locking granularity is that the probability of locking resource contention will be the highest, which will greatly reduce the degree of concurrency.

Table-level locking is mainly used by some non-transactional storage engines such as MyISAM,MEMORY,CSV.

The syntax for table locks is simple:

# get the table lock LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type]. Lock_type: READ [LOCAL] | [LOW_PRIORITY] WRITE # release table lock UNLOCK TABLES

Before executing the query, MyISAM automatically performs the locking and unlocking operations of the table. In general, it does not need to be manually added or unlocked by the user, but sometimes it needs to be displayed and locked. For example: retrieve the amount of data in the T2 table at a certain time.

LOCK TABLE T1 read, T2 read;select count (t1.id1) as' sum' from T1 ~ select count (t2.id1) as' sum' from T2 * * unlock TABLES

Page lock

Page-level locking is a unique locking level in MySQL, and it is not very common in other database management software. The characteristic of page-level locking is that the locking granularity is between row-level lock and table-level lock, so the resource overhead required 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.

In the process of database resource locking, with the decrease of the granularity of locking resources, the amount of memory needed to lock the same amount of data is more and more, and the implementation algorithm will become more and more complex. However, with the decrease of the granularity of locking resources, the possibility of lock waiting for application access requests decreases, and the overall concurrency of the system increases.

Page-level locking is mainly used by the BerkeleyDB storage engine.

Row lock

The biggest feature of row-level locking is that the granularity of locked objects is very small, and it is also the smallest locking granularity implemented by major database management software at present. Because the locking granularity is very small, the probability of locking resource contention is the smallest, which can give applications as much concurrent processing ability as possible and improve the overall performance of some applications that need high concurrency.

Although it has great advantages in concurrent processing ability, row-level locking also brings a lot of disadvantages. Because the granularity of locking resources is very small, more needs to be done each time the lock is acquired and released, and the consumption is naturally greater. In addition, row-level locking is also the most prone to deadlocks.

Row-level locking is mainly used by the InnoDB storage engine.

Summary

Table-level lock: low overhead, fast locking; no deadlock; large lock granularity, the highest probability of lock conflict and the lowest concurrency.

Row-level locks: expensive and slow to add locks; deadlocks occur; locking granularity is the smallest, the probability of lock conflicts is the lowest, and the degree of concurrency is the highest.

Page lock: the overhead and locking time are between table lock and row lock; deadlocks occur; lock granularity is between table lock and row lock, and the concurrency is general.

From the point of view of locks, table-level locks are more suitable for applications that mainly rely on queries, with only a small amount of data updated according to index conditions, such as Web applications, while row-level locks are more suitable for applications with a large number of concurrent updates of a small amount of different data according to index conditions and concurrent queries, such as some online transaction processing (OLTP) systems.

2. Locks in InnoDB

Intention lock

The previous section mentioned that InnoDB supports multiple granularities of locks, that is, row and table locks. To support multi-granularity locking, the InnoDB storage engine introduces intentional locking (Intention Lock).

So what is an intention lock? We can give an example here: if there is no intention lock, when someone has used a row lock to modify a row in the table, if another request is to modify the whole table, then it is necessary to scan whether all rows are locked, in this case, the efficiency is very low. However, after the introduction of the intention lock, when someone uses a row lock to modify a row in the table, it will first add the intention mutex (IX) for the table, and then add the mutex (X) for the row record. At this time, if someone tries to modify the whole table, there is no need to determine whether every row of data in the table is locked, just wait for the intention mutex to be released.

Similar to the two types of locks mentioned in the previous section, there are also two types of intention locks:

Intention shared lock (IS): if a transaction wants to acquire a shared lock for some records in a table, it needs to add an intention shared lock to the table first.

Intention mutex (IX): if a transaction wants to obtain a mutex for some records in a table, it needs to add an intentional mutex to the table.

With the addition of intention locks, the compatibility matrix between lock types becomes more complex:

Intention locks do not actually block any requests other than full table scans, their main purpose is to indicate whether someone is requesting to lock a row of data in the table.

The algorithm of row lock

The InnoDB storage engine has three algorithms for row locking, which are:

Record Lock: a lock on a single row record.

Gap Lock: a gap lock that locks a range but does not include the record itself.

Next-Key Lock:Gap Lock+Record Lock, lock a range, and lock the record itself.

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.

Next-Key Lock is a locking algorithm that combines Gap Lock and Record Lock. Under the Next-Key Lock algorithm, InnoDB uses this locking algorithm for row queries. For example, if an index has four values of 10, 11, 13 and 20, then the interval of the index may be Next-Key Locking:

In addition to Next-Key Locking, there is also Previous-Key Locking technology. For the same values mentioned above, using Previous-Key Locking technology, the lockable range is:

However, not all indexes will be added with Next-key Lock, and if the column of the query is a unique index (including the primary key index), Next-key Lock will be degraded to Record Lock.

Next, let's explain it through an example.

CREATE TABLE z (an INT, b INT, PRIMARY KEY (a), / an is the primary key index KEY (b) / / b is the general index); INSERT INTO z select 1, 1 is insert INTO z select 3, 1 is insert INTO z select 5, 3 is insert INTO z select 7, 6 is insert INTO z select 10, 8

At this point, execute SELECT * FROM z WHERE b = 3 FOR UPDATE in session A, and the index lock is as follows:

At this time, all statements executed by session B that fall within the lock range will be waiting.

SELECT * FROM z WHERE a = 5 LOCK IN SHARE MODE;INSERT INTO z SELECT 4,2 * insert INTO z SELECT 6,5

Users can turn off Gap Lock by displaying it in two ways:

Set the isolation level of the transaction to READ COMMITED.

Set the parameter innodb_locks_unsafe_for_binlog to 1.

As you can see from the above example, the role of Gap Lock is to prevent multiple transactions from inserting records into the same scope, and it is designed to solve the Phontom Problem (phantom reading problem). Under MySQL's default isolation level (Repeatable Read), InnoDB uses it to solve phantom reading problems.

Illusory reading means that under the same transaction, two consecutive execution of the same SQL statement may lead to different results, and the second SQL may return rows that did not exist before, that is, during the first execution and the second execution, other transactions inserted new rows.

Consistent unlocked read

Consistent unlocked read (consistent nonlocking read) means that the InnoDB storage engine reads data from rows in the database at the current execution time through multiple version control (MVCC). If the row being read is performing a DELETE or UPDATE operation, the read operation does not wait for the lock to be released like a XS lock, but reads a snapshot data. I have already explained the knowledge related to MVCC in another article, so I won't do too much theoretical analysis here. Address: talk about the ACID features of MySQL InnoDB storage engine transactions

Under transaction isolation levels RC and RR, the InnoDB storage engine uses non-locked consistent reads. However, the definition of snapshot data is different. At the RC level, for snapshot data, inconsistent reads always read the latest snapshot data of locked rows. At the RR level, for snapshot data, inconsistent reads always read the row data version at the beginning of the transaction.

Let's use an example to see if you understand MVCC.

As you can see, steps 1 and 2 are very easy to understand, but after inserting a new piece of data in step 3 transaction B, transaction An is still not found in step 4, that is, it takes advantage of the features of MVCC. When transaction B is committed, the output of the query in step 5 is different at the RC and RR isolation levels, which is also mentioned in another blog post, because they created ReadView at different times.

But the weird thing is that in step 6, transaction A updates a record that it can't see, and then the query can query it out. Many people here are easily confused. Being invisible doesn't mean the record doesn't exist. It just takes advantage of visibility judgment and ignores it. After the update is successful, transaction A naturally records the Undo log of the record, and in the subsequent query, because it can see the visibility judgment of its own changes, it will be able to query it out. There are many nouns that need to be read in depth: talk about the ACID features of MySQL InnoDB storage engine transactions

Consistent lock read

As mentioned earlier, under the default isolation level RR, the SELECT operation of the InnoDB storage engine uses consistent unlocked reads. However, in some cases, users need to explicitly lock the database read operation to ensure the consistency of data logic. The InnoDB storage engine supports two consistent lock read (locking read) operations for SELECT statements.

SELECT... FOR UPDATE (X Lock)

SELECT... LOCK IN SHARE MODE (S lock)

3. Problems caused by locks

Through the locking mechanism, the requirement of transaction isolation can be realized, so that the transaction can work concurrently. Locks increase concurrency, but can cause potential problems. However, fortunately, there are transaction isolation requirements, different isolation levels to solve the lock problem is also different, here is only a simple introduction, not example analysis.

The InnoDB storage engine has solved all the problems at the RR level, but what's the difference between it and Serializable? The difference is that there is also a problem of missing updates at the RR level, while SERIALIZABLE locks both queries and updates.

As shown in the figure, the user's original amount is 100. If the judgment of transfer and deposit in the program is to query first and then update, there will be the problem of losing updates, that is, the subsequent updates overwrite the previous updates. If you want to avoid this problem, you can only do it each time you update it based on the latest value in the table. If you have to query before updating, you can determine the amount in the updated condition (optimistic lock), or you can use SERIALIZABLE with the highest isolation level.

4. Deadlock

Deadlock refers to the phenomenon that two or more transactions wait for each other due to competition for lock resources during execution. Here we directly put a deadlock problem encountered in the previous project and an in-depth analysis: the MySQL deadlock problem caused by an online problem will not be discussed here.

These are the details of the lock of MySQL, please pay attention to other related articles!

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.