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

This article focuses on "how to understand locks in MySQL". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "how to understand locks in MySQL"!

01. How to know "Lock"

Simply put, locking is a mechanism in a database that handles collaborative relationships between multiple transactions.

It can be thought of as a mark of a database to certain records or data tables to indicate whether the current state of the resource is occupied by some transaction.

02. Classification of "lock"

According to the idea of locking, optimistic lock (optimistic locking) and pessimistic lock (pessimistic locking) can be distinguished-- this is a fictional concept.

According to the locking strategy, it can be divided into record lock (record locking), gap lock (gap locking) and key lock (next-key locking), in which key lock = record lock + gap lock

According to the locking granularity, it can be divided into row lock (row-level locking) and table lock (table-level locking), in which InnoDB can add row lock or table lock; MyISAM can only add table lock.

According to the influence of locking, shared lock (share locking,S lock) and exclusive lock (exclusive locking,X lock) can be distinguished, which are also called read lock and write lock respectively.

A transaction issues a "request" before adding a lock, so there is an intention lock (intention locking), which is equivalent to sending a lock to the engine: it can be subdivided into shared intention lock (intention share locking,IS) and exclusive intention lock (intention exclusive locking,IX). If the request succeeds (the target of the request is not occupied), it becomes the corresponding S lock or X lock, otherwise it will be in a waiting state or time out.

03. Add "lock" process

The locking process is generally divided into two stages, namely locking phase and unlocking phase, so it is also called two-stage lock (two-phase locking).

The scope of a lock is a transaction, so locking can only be triggered by certain SQL statements after the transaction is opened, and the lock is released when the transaction is committed or rolled back

04. Who should be "locked"?

Not all SQL statements are locked, such as DDL (data definition language) and DCL (data Control language). Since they do not involve transactions, there is no lock problem.

Not all DQL (especially data query language, such as select). ) are locked, for example, ordinary select statements are not locked, but rely on MVCC (multi-version concurrency control) to achieve "some" consistency of transactions

An ordinary select statement does not have a lock. If you want to add a lock, you only need to specify "for share" or "for update" after the select statement, where the former is a shared lock (S lock), also known as a read lock, and the latter is an exclusive lock (X lock), also known as a write lock

But all DML statements (data manipulation languages, insert, update, and delete) are automatically locked, with exclusive locks (X locks).

05. Add "lock" purpose

The purpose of locking is for the stability and consistency of the database, but its side effect is to reduce the concurrency ability, so the locking strategy often has to compromise between consistency (consistency) and concurrency ability (concurrency).

Locking is a trade-off between data consistency and concurrency. The way to implement this mechanism without locking in MySQL is MVCC, the famous multi-version concurrency control. Correspondingly, the concurrency mechanism implemented with locking is called LBCC (locking-based concurrency control).

06. Add "lock" object

Table lock, which locks the entire table. If it is a virtual view (view) or trigger (trigger), all tables associated with it will be locked.

Row lock, the actual locked object is not a row, but is locked by index, that is, the lock does not locate a record, but acts indirectly on the record by restricting the index.

07. Locks and transactions

The SQL general standard defines four ACID attributes of a transaction, namely, atomicity Atomcity, consistency Consistency, isolation Isolation, and persistence Durability.

In order to achieve isolation and ensure consistency, transactions need to be implemented, and the implementation of transactions depends on the storage engine. Among the two commonly used engines of MySQL, the default engine InnoDB supports transactions, while MyISAM does not.

As mentioned earlier, ordinary query statements do not have any locks, and the innoDB engine relies on the MVCC mechanism to achieve database isolation and consistency. MVCC, to put it simply, is to add hidden fields with version information, such as timestamps, to records that may be concurrent and controversial, to ensure the consistency of data queried multiple times.

The status of consistency varies with different isolation levels. The SQL92 standard (general database standard, not unique to MySQL) defines four isolation levels:

a. Read uncommitted (Read Uncommitted,RU), that is, a transaction can read data that other transactions have operated but not committed. When this operation is rolled back, a dirty read occurs.

b. Read committed (Read Committed,RC), that is, a transaction can only read the data committed by other transactions, which ensures that the data is real and avoids dirty reading, but it may lead to inconsistent query results in this transaction window, that is, it cannot be read repeatedly.

c. Repeatable Read,RR, that is, it can be read repeatedly. Based on the MVCC mechanism, a snapshot version is recorded when the first query in the current transaction is made, and the results of the current snapshot version are used for subsequent queries during the same transaction, so even if the data has been committed by other transactions, the snapshot version will not be read after the first snapshot version of this transaction. Note that the snapshot "version number" taken by the current transaction here depends on the time of the first query, not the time to start the transaction.

d. Serialization (Serializable, SE), which strictly restricts concurrency, executes serially among multiple transactions when there is data competition, which has the strongest data stability and consistency, but the concurrency ability is greatly limited. Note that this refers to the serial between transactions when there is a data conflict, otherwise it can still be concurrent.

Not all databases must contain these four isolation levels (for example, Oracle databases mainly support RC and SE isolation levels), and different databases are implemented in different ways. MySQL supports all 4 isolation levels, default to RR level

By default, every SQL statement executed by MySQL is automatically committed, and if you want to execute the transaction explicitly, there are two ways:

The other is to turn off autocommit 5SET autocommit = 0 67 transaction # to end the transaction 8COMMIT / ROLLBACK

For SQL statements that do not explicitly open a transaction, you can think of it as automatically opening and committing a transaction before and after the statement, namely:

1select... 2 is equivalent to 3START TRANSACTION; 4selece. ; 5COMMIT

08. "Reading Image"

Read phenomena, the English writing given in the official document, has not found the relevant authoritative translation nouns. It especially refers to the side effects in the process of MySQL reading, such as dirty reading, phantom reading, etc.

Read phenomena mainly refers to the reading results of three "errors" in the database:

Dirty reading: dirty read, that is, A transaction reads the information changed by B transaction but not committed, which mainly occurs at the RU isolation level.

Can not be read repeatedly, non-repeatable read, that is, because B transaction changed data and committed during A transaction, inconsistent results were read before and after A transaction.

Phantom reading, phantom read, that is, A transaction has a record that does not appear in the previous query.

In view of the confusion in the interpretation of phantom reading and unrepeatable reading in some materials, here is the difference between phantom reading and unrepeatable reading:

Unrepeatable reading, as the name implies, refers to the inconsistency between the two readings. The inconsistency here covers a wide range, in other words, as long as it is inconsistent, it is unrepeatable. The main reason is that during the execution of a transaction, other transactions change and commit the data table (if it is not committed, it can be read more badly, belonging to dirty reading), which mainly occurs at the RC isolation level, because RC means "read committed", so whenever the committed data of other transactions is updated, the transaction can detect it, and the results may of course be inconsistent.

And phantom reading, as the name implies, refers to reading records that have not been found before, of course, in a sense, it must be unrepeatable, so it is correct to understand itself, but the emphasis of the two is different. Illusory reading focuses on the fact that during the execution of this transaction, other transactions insert new records, causing this transaction to read transactions that have not been found in the previous period, just like hallucinations, which is called illusion.

It should be noted that MySQL relies on the snapshot mechanism of MVCC. To some extent, the RR isolation level has avoided phantom reading, but it can still be triggered, which is also explained in the official documentation. Please read the following actual combat cases for details.

09. Snapshot read and current read

Snapshot read, snapshot read, also known as consistent read or unlocked read, consistent nonlocking read, which does not rely on locking to ensure query data consistency, is the default query statement execution mode under the RR and RC levels in MySQL. Read operations are performed according to the "snapshot" version number through the MVCC mechanism. The principles of taking snapshots at RR level and RC level are different, which is why there are different "readings" (unrepeatable or phantom readings) between the two isolation levels, where:

The RR level takes the time of the first read operation after entering the transaction as the snapshot version (note that it is the time of the first read operation, regardless of the start time of the transaction). Once the snapshot version is determined, the snapshot result will be applied in the subsequent read operations of this transaction.

The RC level is that snapshots are taken on each read operation, so it can take new snapshots in time when other transactions are committed.

In ordinary query statements, the RC level is not a consistent read because of dirty reads.

Because the SE level achieves data consistency by locking (adding S locks to ordinary select statements by default), it can ensure that the results are consistent, but it is no longer the original consistent reading.

The current read, current read, also known as locked read, or locking read, refers to adding "for share" or "for update" after a normal query statement to specify a shared or exclusive read operation, where:

For share, which adds an S lock, allows multiple transactions to acquire the S lock at the same time, which is called sharing.

For update, that is, adding an X lock, which is only used to obtain the transaction operation of the X lock, is called exclusive.

Since locking reads are based on transactions, locking reads makes sense only after the transaction is explicitly opened, otherwise because of the transaction's

Actual combat cases

All of the following cases rely on the Navicat Primium12 tool. Initial table statement:

1create table test (id int, name varchar (20), primary key (id)); 2insert into test values (1,'A'); 3insert into test values (3,'C')

10. Three kinds of "readings"

Dirty reading, unrepeatable reading and phantom reading should be a common conceptual problem that bothers many people, especially the difference between the latter two, which is illustrated by several cases.

Dirty reading, dirty read

First, let's take a look at the definition given in the official documentation:

An operation that retrieves unreliable data, data that was updated by another transaction but not yet committed. It is only possible with the isolation level known as read uncommitted.

Main idea: data updated by other transactions but not yet committed is processed in an operation, which is unreliable and occurs only at the RU isolation level.

Case study:

There are dirty reads in RU: transaction A reads data changed by transaction B but not committed.

Unrepeatable, non-repeatable read

The definition given in the official document:

The situation when a query retrieves data, and a later query within the same transaction retrieves what should be the same data, but the queries return different results (changed by another transaction committing in the meantime).

During the query period of a transaction, the results of the two queries are inconsistent because other transactions are committed at the same time.

Case study:

RC avoids dirty reading, but it can not be repeated.

Fantasy reading, phantom read

A row that appears in the result set of a query, but not in the result set of an earlier query. For example, if a query is run twice within a transaction, and in the meantime, another transaction commits after inserting a new row or updating a row so that it matches the WHERE clause of the query.

The results of the previous query do not exist, but the records obtained by the later query are called phantom readings. For example, a query is executed twice, during which another transaction inserts or updates records and commits, resulting in inconsistent results of the two queries of the previous transaction.

From a personal point of view, phantom reading itself certainly belongs to a kind of unrepeatable reading. after all, the results of the two readings are "inconsistent". But phantom reading focuses on the specific operation that there is no new line before and then illusory.

Case study:

①, RR level avoids unrepeatable read problems in RC level:

There is no non-repeatable read data in RR.

②, which can still trigger phantom reading under special circumstances

At the RR level, special operations can still trigger phantom reads (update snapshots)

In fact, the MVCC mechanism only takes a snapshot way to ensure the read results, so it can be read repeatedly, but when performing insert, update and delete operations, it will still actually detect the latest record status in the current database: it will still have an impact when the latest data submitted by other transactions meets the conditions for the addition, deletion and modification operations in this transaction.

This is not difficult to understand, after all, to ensure the state consistency of the database, but it is surprising that after the update, the snapshot version in the transaction is updated. For example, in the case shown in the figure, there are 2 records in the initial query, 3 are actually updated in update, but the results are also updated to 3 in the second query. And, more importantly, this phenomenon is not universal: the snapshot version is updated only when the transaction performs a update operation, while for delete and insert operations, the snapshot version is only detected and not updated.

The insert operation of the transaction does not update the snapshot version

More generally, the influence of other additions, deletions and modifications performed by transaction B on whether transaction A updates the snapshot version is further tested, and the following experimental conclusions are obtained by pairwise combination:

As mentioned above, the illusory reading only occurs in the query after the new record is inserted and committed by another transaction, and the data is updated by this transaction.

Of course, the official documentation notes on this:

Snapshot snapshot applies only to query statements, not to DML (data manipulation language, that is, add, delete and modify operations). Other transactions perform delete or update operations and commit, and although these changes are "invisible" to the current transaction, they are visible after performing their own update or delete operations. Although this note is sufficient to explain the conclusion of the above case, the author still has some doubts in the above table.

Finally, it should be pointed out that the MVCC mechanism is based on the concurrency control of the snapshot version, which corresponds to LBCC. When LBCC is used to read data, the latest data can always be read. Of course, this does not contradict the RR isolation level and the MVCC mechanism.

Locked reads always read the latest results, but do not affect the snapshot version

11. Snapshot version

MVCC is based on multi-version concurrency control, and the query result is based on the snapshot version. However, the collection principles of snapshot versions with different isolation levels are not consistent. In the RR isolation level, the repeatable read problem in the same transaction is realized through the MVCC mechanism, and the snapshot is the version number information collected during the first query, and has nothing to do with the timing of starting the transaction.

The snapshot version is created by the first query in the RR level

Moreover, once a snapshot version is established in the RR level, the snapshot version is used as the result in subsequent queries of the transaction (of course, there are exceptions found in the previous case); correspondingly, in the RC level, each query takes the latest snapshot version as the result, so naturally there is the problem of non-repeatable reading.

twelve。 Lock type

First of all, the record lock, gap lock and key lock are briefly introduced.

Record lock

The record lock locks the corresponding record based on the index, even when no index is established in the corresponding table. In fact, there are indexes in all InnoDB tables, and when the user builds the table without explicitly setting the index, the engine will automatically establish a hidden index, which is also due to the fact that the underlying InnoDB accesses the whole record based on the clustered index.

Record lock locks only those records whose index meets the query criteria

Gap lock

If the record lock is to lock the hit record, then the gap lock is to book and lock the records that are within the query range but do not exist. For example, in the following figure, it is assumed that the records of id=2 and 3 do not exist in the table, but a gap lock will be added because the query range is satisfied.

The gap lock locks the record gaps that meet the query conditions.

Obviously, the gap lock is at the expense of a certain concurrency performance in exchange for high consistency. In fact, this is what all locks do, that is, to achieve some balance between consistency and concurrency.

It should be pointed out that:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Gap locks exist only in range queries, but are not applicable to equivalent queries. For example, changing the query condition to where id=1 or id=4 in the above example will not add gap locks to potential id=2 and 3.

When the query condition is an equivalent query, but the query condition is a federated index (an index created in multiple columns), a gap lock is also added to the potential records that meet the requirements.

Gap locks exist only at specific isolation levels, there is a gap lock by default at the RR level, but not at the RC level

Adjoining key lock

On the basis of record lock and gap lock, key lock = record lock + gap lock.

Key lock = record lock + gap lock

In RC isolation level, there is only record lock, but no gap lock and key lock; in RR level, if it is equivalent query, it is record lock, and range query is key lock (record lock + gap lock), which can be set by global parameter before version 5.6, but this variable has been removed in version 8.0.

RC isolation level default setting record lock

RR isolation level with temporary key lock by default

13. The influence of index type on locking

After defining the type of locking, you also need to consider the impact of different indexes on locking. First of all, it is pointed out that under the InnoDB engine, even if the index is not explicitly specified when the table is created, the engine will automatically generate hidden indexes for clustering and storing record data. Based on this, the effects of indexes on locking are as follows (quoted from official documents):

Consistent read (that is, snapshot read, unlocked read, based on MVCC), no locks are added to all isolation levels except for the SE isolation level

The current read (locked read, for share or for update) locks all records that meet the conditions and releases those that do not meet the conditions. For some complex statements, such as containing Union statements, the lock is not immediately released for records that do not meet the query criteria because temporary tables are involved in summarizing the results. At the same time, whether to add a record lock or a key lock depends on the index type and query conditions. Only a record lock is added when it corresponds to an equivalent query under a unique index, otherwise it will be upgraded to a key lock.

The update statement adds a temporary key lock (X lock) to each statement that satisfies the record, but only the record lock is added when the unique index and equivalent query are satisfied.

The locking principle of delete statement is consistent with that of update statement.

The insert statement only adds record locks (X locks) to inserted rows without any gap locks. In fact, the insert statement adds an intentional lock first, and the request succeeds before it is inserted, otherwise it will not block other transactions. In special cases, when multiple transactions insert the same index record at the same time, index duplicate conflicts will occur, which may lead to deadlock. See the next section for details.

For details of locking analysis under different types, see the Chinese file in reference 2 at the end of the article, which is fully explained and widely forwarded, so I will not play tricks here.

14. Lock competition and deadlock

Generally speaking, locks are exclusive. If it is a shared lock (S lock), it can be owned at the same time as another shared lock (S lock), but not with an exclusive lock (X lock); for an X lock, it cannot be concurrently with any other lock. When multiple transactions attempt to occupy a resource at the same time, lock competition or even deadlock may occur.

Lock contention occurs when multiple transactions attempt to occupy the same resource at the same time, but only in time but not in resource occupation:

Multiple transactions compete for the same resource

In the above case, three transactions in turn request to add an X lock to the data table, where transaction A successfully requests, transaction B and transaction C wait. When transaction A commits the transaction, although transaction B and transaction C compete for locking at the same time, because of the FIFO (First In First Out, first-in, first-out) feature of MySQL for transaction scheduling, they will not have deadlock, but will first satisfy the locking request of transaction B, and then satisfy the locking request of transaction C after transaction B commits the transaction.

Deadlock, similar to but different from lock competition, deadlock also occurs when multiple transactions compete for the same resource at the same time, but these resources can not be solved simply through time, but there are logical conflicts:

①, lock contention + index duplicate conflict causes deadlock:

There are index duplicates in the competitive resources of three transactions.

This case is similar to but different from the example in lock contention: suppose transaction A, transaction B, and transaction C request to insert a piece of data at the same time (insert statements are all with X locks), not only because of lock conflicts, but also because of index duplication. Transaction B and transaction C fall into a deadlock once transaction A rolls back and releases the lock. This is a special cause of deadlock trigger.

②, there is an endless cycle of competing for the same resource:

The two transactions compete first and then deadlock

In this case, when transaction An and transaction B lock the record of id=1 and id=2 respectively, and then transaction A continues to lock the record request of id=2, transaction A can only wait because the record is already occupied by transaction B. But at this time, transaction B attempts to add X locks to the id=1 records already occupied by transaction A, causing transaction An and transaction B to attempt to occupy the locked resources of each other on the basis of their respective possession of certain resources, logical conflict, difficult to get off the tiger, and the engine can not be solved through time scheduling, so deadlock occurs.

After the deadlock occurs, the engine selects one to roll back according to the importance of the relevant transactions (including the amount of resources occupied, timing, etc.): for example, in the above example, transaction A requests an X lock ahead of transaction B. transaction B can be regarded as the direct cause of the deadlock, so it is chosen to roll back B and allow A to lock successfully.

At this point, I believe you have a deeper understanding of "how to understand 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.