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

What is the principle of concurrency control in mysql? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

1. The logical framework of mysql

The mysql logical framework diagram is as follows:

The top layer handles connections from the client.

Mainly do connection processing, authorization authentication, security and so on. Mysql maintains a thread pool at this layer to handle connections from clients. Mysql can use username and password authentication

You can also use SSL to authenticate based on X.509 certificates.

The second layer consists of three parts: query cache, parser and optimizer. The parser is used to parse the SQL statement, and the optimizer optimizes the parsed statement.

Before parsing the query, the server will first check the query cache, and if the corresponding query results can be found in it, the query results will be returned directly without the need for query parsing, optimization and other processes. Stored procedures, triggers, views, and so on are all implemented at this layer.

The third layer is the storage engine, which is responsible for storing data in MySQL, extracting data, opening a transaction, and so on. The storage engine communicates with the upper layer through API. These API shield the differences between different storage engines and make these differences transparent to the upper layer query process. The storage engine does not parse SQL. The most commonly used storage engine for mysql is InnoDB.

2. Concurrency control of mysql

If multiple threads operate on data at the same time, concurrency control problems may arise.

2-1. Read-write lock

If multiple threads just read data and can actually read it together without affecting each other, a "read lock", also known as a shared lock, should be used at this time.

The threads that acquire the read lock do not block each other and can read a resource at the same time.

If there is a thread that needs to write data, you should use a write lock, which is also an exclusive lock.

Write locks block other write and read locks until the write operation is complete.

2-2, lock granularity

First of all, let's make it clear that the less data that needs to be locked on a given resource, the higher the concurrency the system can carry.

But locking also consumes resources, if the system spends a lot of time managing locks instead of accessing data

Then the performance of the system may be affected.

So a good "locking strategy" is to strike a balance between lock overhead and data security. Mysql supports multiple storage engine architectures.

Each storage engine can implement its own locking policy and locking granularity.

2-3, table lock and row lock

As the name implies, a table lock is to lock the whole table. The table lock overhead is relatively small. When a write lock is added to the table, all read and write operations on the table by other users will be blocked.

In Mysql, although the storage engine can provide its own locks, Mysql sometimes uses table locks, such as statements like ALTER TABLE.

A write lock has a higher priority than a read lock, so a write lock request may be inserted in front of the read lock queue.

Row-level locking locks the entire row, which can support concurrent processing to the maximum extent, but the cost of adding and unlocking will also be high. Row-level locks are only implemented at the storage engine layer

All storage engines implement row-level locks in their own way.

3 、 MVCC

MVCC, which means "multi-version concurrency control", can be considered as a variant of row-level locking, but it avoids locking operations in many cases.

So the cost is lower.

All the mainstream relational databases implement MVCC, but the implementation mechanisms are different. In fact, MVCC does not have a unified standard.

However, most of them implement non-blocking read operations, and write operations only lock the necessary lines.

What MVCC guarantees is that the data seen during execution in each transaction is consistent.

However, different transactions may see different data at the same time for the same table because they start at different times.

The InnoDB engine in Mysql is achieved by keeping two hidden columns behind each row of records.

One saves the creation time of the row, and the other saves the expiration time (or deletion time) of the line.

In fact, what is stored is not an actual timestamp, but the 'system version number'.

Each time a transaction is opened, the system version number is incremented. At the beginning of the transaction, the system version number is used as the version number of the transaction to compare with the version number of the queried row.

Here's how the version number works in common CRUD operations:

INSERT

Save the current system version as the line version number

DELETE

Save the current system version number to the "deleted version" of this line of data.

UPDATE

Insert a row of new records, save the current system version number as the air version number, and save the current system version number to the "deleted version" of the original line.

SELECT

Only rows whose version is earlier than the current transaction version are found. This ensures that the rows read by the transaction either exist before

Either the transaction itself inserts or modifies it.

The delete version of the row is either undefined or greater than the current transaction version number. This ensures that the rows read by the transaction

It was not deleted before the transaction.

MVCC only works under two isolation levels, REPEATABLE READ and READ COMMITTED, and the other two isolation levels do not work.

Because READ UNCOMMITTED always reads the latest data defenses, rather than rows that match the current transaction version. SERIALIZABLE locks all rows read.

This is the answer to the question about what is the principle of concurrency control in mysql. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.