MySQL concurrency control principle knowledge points

Mysql is a mainstream open source relational database that provides high-performance data storage services. When doing backend development, you may sometimes encounter performance bottlenecks. These bottlenecks sometimes do not come from the application itself, but from the database level.

Therefore, mastering some of the underlying principles of MySQL will help us better understand MySQL and optimize its performance.

Thereby developing high-performance backend services.

1. MySQL logical framework

The mysql logic framework diagram is as follows:

The top layer handles connections from the client.

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

SSL can also be used based on X.509 certificate authentication.

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

Before parsing the query, the server will first check the query cache. If the corresponding query result can be found in it, there is no need to perform query parsing, optimization, etc., and the query result is returned directly. Stored procedures, triggers, views, etc. are all implemented at this layer.

The third layer is the storage engine, which is responsible for storing data in MySQL, extracting data, starting a transaction, and so on. The storage engine communicates with the upper layer through APIs. These APIs shield the differences between different storage engines, making 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, it may cause concurrency control problems.

2-1. Read-write lock

If multiple threads are just reading data, they can actually read together without affecting each other. At this time, a "read lock", also known as a shared lock, should be used.

Threads that acquire read locks will not block each other and can read a resource at the same time.

If a thread needs to write data, a "write lock", also known as an exclusive lock, should be used.

A write lock blocks other write locks and read locks until the write operation is completed.

2-2. Lock granularity

First, let's clarify a concept: for a given resource, the less data that needs to be locked, the higher the concurrency the system can handle.

However, locking also consumes resources. If the system spends a lot of time managing locks instead of accessing data,

Then the system performance may be affected.

Therefore, a good "lock strategy" is to find a balance between the lock overhead and the security of the data. MySQL supports multiple storage engine architectures.

Each storage engine can implement its own locking strategy and lock granularity.

2-3. Table locks and row locks

As the name implies, a table lock locks the entire table. Table lock overhead is relatively small. After adding a write lock to a table, all read and write operations of other users on this table will be blocked.

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

Write locks have higher priority than read locks, so a write lock request may be inserted at the front of the read lock queue.

Row-level locks lock the entire row, which can support concurrent processing to the greatest extent, but the overhead of unlocking and adding locks will also be relatively large. Row-level locks are only implemented at the storage engine level.

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

3. MVCC

MVCC stands for "Multi-version Concurrency Control". It can be considered a variant of row-level locks, but it avoids locking operations in many cases.

Hence the overhead is lower.

Mainstream relational databases all implement MVCC, but the implementation mechanisms are different. In fact, there is no unified standard for MVCC.

But most of them implement non-blocking read operations, and write operations only lock necessary rows.

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

However, since different transactions start at different times, the data seen for the same table at the same time may be different.

In MySQL's InnoDB engine, this is achieved by saving two hidden columns after each row of records.

One holds the row creation time, and the other holds the row expiration time (or deletion time).

What is actually stored is not a real timestamp, but a 'system version number'.

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

The following describes how the version number works in common CRUD operations:

INSERT

Save the current system version as the row version number

DELETE

Save the current system version number to the "Delete version" of this row of data.

UPDATE

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

SELECT

Only find rows with versions earlier than the current transaction's version. This ensures that the rows read by the transaction either already exist or

Either it is inserted or modified by the transaction itself.

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

Was not deleted before the transaction.

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

Because READ UNCOMMITTED always reads the latest data, rather than the data row that conforms to the current transaction version. SERIALIZABLE will lock all read rows.

The above is the detailed content of the concurrency control principle of MySQL. If you have any additions, please contact the editor of 123WORDPRESS.COM.