Mysql concurrency control principle

Mysql is a mainstream open source relational database that provides high-performance data storage services. When doing back-end development,

sometimes you will encounter performance bottlenecks. Sometimes these bottlenecks do not come from the application itself, but from the database level.

So mastering some of the underlying principles of Mysql will help us better understand Mysql, perform performance tuning on Mysql, and

thus develop high-performance back-end services.

1. The logical framework of mysql

The logical framework diagram of mysql is as follows:

The top layer handles the client. connected.

Mainly does 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,

can also use SSL based 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 will optimize 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, the query result will be returned directly without the need for query parsing, optimization, etc. Stored procedures, triggers, views, etc. are all implemented in this layer.

The third layer is the storage engine. The storage engine is responsible for storing data in MySQL, extracting data, starting a transaction, etc. 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 will not parse SQL. The most commonly used storage engine for mysql is InnoDB.

2. Concurrency control of mysql

If multiple threads operate data at the same time, it may cause concurrency control problems.

2-1. Read-write lock

If multiple threads only read data, they can actually read it together without affecting each other. At this time, you should use "read Lock", also known as shared lock.

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, it should use a "write lock", which also becomes an exclusive lock.

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

2-2. Lock granularity

First clarify a concept: on a given resource, the less data that needs to be locked, the more concurrency the system can carry. The higher it is.

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 "lock strategy" is to find a balance between lock overhead and data security. Mysql supports multiple storage engine architectures,

Each storage engine has You can implement your own lock strategy and lock granularity.

2-3. Table lock and row lock

Table lock, as the name suggests, locks the entire table. Table lock overhead is relatively small. After adding a write lock 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 such as ALTER TABLE.

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

Row-level locking locks the entire row, which can support concurrent processing to the greatest extent, but the cost of unlocking will also be relatively 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 is "multi-version concurrency control". It can be considered that MVCC is a variant of row-level locking, but it avoids the need to increase the number of row-level locks in many cases. Lock operation,

so the overhead is lower.

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

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.

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

The InnoDB engine of Mysql is implemented by saving 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 row.

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

Every time a transaction is started, the system version number will be incremented. When a transaction starts, the system version number will be used as the version number of the transaction and used to compare with the version number of the queried row.

The following introduces 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 of records, save the current system version number as the navigation version number, and save the current system version number to the "delete version" of the original row.

SELECT

Find only rows whose version is earlier than the current transaction version. This ensures that the rows read by the transaction either exist before,

or were inserted or modified by the transaction itself. The "delete version" of row

is either undefined or greater than the current transaction version number. This ensures that the rows read by the transaction have not been deleted before the transaction.

MVCC only works under the two isolation levels of

and READ COMMITTED, and the other two isolation levels cannot work. Because

always reads the latest data, rather than the data rows that match the current transaction version. And SERIALIZABLE will lock all rows read. The above are some issues about concurrency control compiled for you. For more related issues, please visit the relevant tutorials on the PHP Chinese website.

Recommended video tutorial:

The above is the detailed content of Mysql concurrency control principle. For more information, please follow other related articles on the PHP Chinese website!