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

Today, I will talk to you about how to achieve concurrency control in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Lock

Locks are divided into read locks and write locks, also known as shared locks and exclusive locks.

Because multiple reads at the same time will not destroy the data, so the read lock is shared, and multiple reads can be performed at the same time without interfering with each other.

In order to prevent multiple write operations from jointly executing and destroying data, the write lock is exclusive, and one write lock blocks other write locks and read locks, thus ensuring that only one write operation is being performed on the same resource at any one time. And prevent other users from reading the resource being written.

In terms of lock granularity, MySQL includes two types: table lock and row lock. The smaller the granularity of the lock, the more conducive to the concurrent execution of database operations. But managing locks also consumes more resources. If the system spends a lot of time managing locks instead of storing data, the performance of the system will also be affected.

Table locks lock the entire table, which is the least expensive strategy. Statements such as ALTER TABLE use table locks.

Row locks support concurrent operations to a certain extent, but it also brings * overhead. InnoDB implements row locks.

Locks are not the only way to maintain concurrency control in MySql.

Isolation level of the transaction

The concept of transaction is not introduced here. I think you can also think of a transaction as part of concurrency-- a transaction contains a set of operations that can be executed in parallel between transactions. Transactions and concurrency between transactions share the same resources as ordinary concurrent operations, so that concurrently executed transactions interact with each other. According to the different influence degree between transactions, the concept of isolation level of transactions is put forward, which is READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE respectively.

READ UNCOMMITTED means that the modification of shared data by one transaction can be immediately perceived by another transaction, that is, no special processing is done to the modification operation.

SERIALIZABLE forces transactions to execute serially by locking, which avoids phantom reading. But this approach will lead to a lot of lock contention problems.

READ COMMITTED and REPEATABLE READ are implemented based on MVCC.

MVCC multi-version concurrency control

MySql does not simply use row-level locks to implement concurrency control between transactions. MySql does not lock on read operations, but locks modified resources only on write operations.

MVCC saves multiple snapshots of data resources at different points in time. Depending on the time the transaction starts, each transaction sees a different version of the data snapshot.

MVCC implementation in InnoDB: the storage engine maintains a system version number globally, which is incremented each time a new transaction is opened. The system version number at the beginning of the transaction will be used as the version number of the transaction itself. In each row of records, the storage engine saves two hidden columns after each row, saving the start version number and the expired version number of the row, respectively. Under the REPEATABLE READ isolation level, the specific operations of MVCC are as follows:

INSERT

The storage engine saves the current system version number for each newly inserted row as the starting version number for that line.

UPDATE

The storage engine inserts a new row of records, and the current system version number is the starting version number of the new record line. At the same time, the expired version number of the original line is set to the current system version number.

DELETE

The storage engine sets the expired version number of the deleted record line to the current system version number.

SELECT

When reading a record, the storage engine selects the rows that meet the following two conditions as the reading result.

1. The start version number of the read record row must be earlier than the version number of the current transaction. That is, this record already exists before the current transaction starts. Rows inserted after the start of the transaction will not be seen by the transaction.

two。 The expired version number of the read record row must be later than the version number of the current transaction. That is, the record has not expired at the beginning of the current transaction. Rows of data that have expired before the transaction begins will not be seen by the transaction.

From the above description, you can see that multiple versions of a row of data are stored at the same time in the storage engine. Each transaction chooses to read the appropriate data row based on its own version number and the start and expiration version number of each data row.

After reading the above, do you have any further understanding of how to implement concurrency control in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.