In today’s most widely used in the Internet business of database and relational database MySQL, is use the word “or”, because in recent years have also made some progress in the field of national database, for example by TIDB, OceanBase distributed database, but they are not yet form absolute coverage, Therefore, I still need to continue to learn MySQL database to deal with some problems in the work and the investigation of database in the interview process.

Today’s content and you talk about the MySQL database about concurrency control, transactions and storage engine these most core issues. The knowledge graph involved in this content is as follows:

Concurrency control

Concurrency control is a huge topic. As long as there are multiple requests to modify data at the same time in the computer software system, concurrency control problems will occur, such as multi-thread safety problems in Java. In MySQL concurrency control, mainly discusses how the database control table data concurrent read and write.

For example, there is a table, userAccount, which has the following structure:

If the following TWO SQL statements send requests to the database at the same time:

SQL-A:

update useraccount t set t.account=t.account+100 where username='wudimanong';
Copy the code

SQL-B:

update useraccount t set t.account=t.account-100 where username='wudimanong'
Copy the code

When all of the above statements are executed, the correct result should be account=100, but in concurrent cases, something like this can happen:

How do you control concurrency in MySQL? In fact, like most concurrency control methods, MySQL also uses the lock mechanism to achieve concurrency control.

1. MySQL lock type

In MySQL, concurrency control is mainly achieved through “read/write locks”.

** Read lock: ** Also known as a share lock, multiple read requests can simultaneously share a lock to read data without blocking.

** Write lock: also called an exclusive lock, a write lock blocks all other lock requests until the write is complete and the lock is released.

Read/write locks can read simultaneously, but cannot write/read simultaneously. Transaction isolation, as discussed later, is based on read/write locks!

2. MySQL lock granularity

Read/write locks mentioned above are divided according to the lock type of MySQL. The granularity imposed by read/write locks in databases is mainly reflected in tables and rows, also known as table locks and row locks.

Table lock: the most basic lock policy in MySQL. It locks the entire table. In this way, the lock maintenance cost is minimal, but the read and write efficiency of the table is reduced. If a user uses a table lock to write to a table (insert, delete, update), the user needs to obtain a write lock on the table first. In this case, all other users’ reads and writes to the table will be blocked. Table locks are typically used in statements such as “ALTER TABLE”.

Row locks: Row locks maximize concurrent reads and writes, but can be expensive to maintain. Row locking is the most commonly used lock strategy in our daily life. Generally, row locking in MySQL is implemented by the storage engine, not the MySQL server (table locking is implemented at the MySQL server level).

3. Multi-version Concurrency Control (MVCC)

Concurrency Control for MultiVersion Concurrency Control (MVCC). In most of MySQL’s transaction engines (such as InnoDB), row-level locking is not simply implemented, otherwise the situation would be “while data A is being updated by one user (acquiring row-level write locks), other users reading the data (acquiring read locks) will be blocked”. But the reality is obviously not the case, because the storage engine of MySQL, based on the consideration of improving the concurrency performance, achieves read and write separation through THE multi-version control of MVCC data, so as to achieve read and write parallelism without locking data.

Take the MVCC implementation of InnoDB storage engine as an example:

InnoDB’s MVCC is implemented by storing two hidden columns at the end of each row. These two columns, one holds the creation time of the row and the other holds the expiration time of the row. Of course, they do not store the actual time value, but the system version number. Each time a new transaction is started, the system version will automatically increase. The system version number at the start of the transaction is used as the version number of the transaction and is compared with the version number of each row of records queried.

MVCC is implemented in MySQL by means of “undo log and read View “.

  • Undo log: The undo log is used to record multiple versions of a row of data.

  • Read View: Used to determine the visibility of the current version of data

Undo log will be covered later when we talk about transactions. The schematic diagram of the reading and writing principle of MVCC is as follows:

The MySQL InnoDB storage engine implements MVCC in REPEATABLE READ transaction isolation level by storing two additional system version numbers (row create version number and row delete version number) so that most READ operations do not need to be locked. This design makes the data read operation simpler and better performance.

How does the data read operation in MVCC ensure that the data read is correct? In the case of InnoDB, each row is checked according to two criteria:

  • Looking only for rows whose version number is less than or equal to the current transaction version ensures that the rows read by the transaction either existed before the transaction started or were inserted or repaired by the transaction itself.

  • The deleted version number of the row is either undefined or greater than the current transaction version number. This ensures that rows read by the transaction are not deleted before the transaction begins.

Only records that meet the above two criteria can be returned as a result of the query! Using the logic shown in the figure, when a write request changes account to 200, InnoDB inserts a new row (Account =200) and uses the current system version as the row creation version (createVersion=2). At the same time, delete the version number of the current system as the original line (deleteVersion=2), then there are two copies of the data of this data, as follows:

Select * from accout where accout=100; Select * from accout where accout=100;

The above process is the basic principle of InnoDB storage engine for MVCC implementation, but note that MVCC multi-version concurrency control logic only works at “REPEATABLE READ” and “READ COMMITED” transaction isolation levels. The other two isolation levels are incompatible with MVCC because READ UNCOMMITED always reads the latest row, not the row that matches the current transaction version; SERIALIZABLE locks all rows read, which is also against MVCC.

MySQL transaction

In the previous explanation about MySQL concurrency control process, also mentioned the transaction related content, next we will sort out the core knowledge about transactions more comprehensively.

I believe that you have used database transactions in the daily development process, the characteristics of transactions are also able to mouth – ACID. So how does it work inside a transaction? In the following content, we will talk about this problem with you in detail!

1. Transaction Overview

The effect of database transaction itself is mainly reflected in: **” reliability “and” concurrent processing “**.

  • Reliability: The database needs to ensure that data operations are consistent when an INSERT or update operation throws an exception, or when the database crashes.

  • Concurrent processing: When multiple concurrent requests come in, and one of the requests is to modify the data, in order to prevent other requests from reading the dirty data, the read and write between transactions need to be isolated.

There are three main technologies to implement the transaction function of MySQL database, namely log file (redo log and undo log), lock technology and MVCC.

Redo log and undo log

Redo log and undo log are the core technologies to implement MySQL transaction function.

1）、redo log

Redo logs are called redo logs and are key to transaction persistence. A redo log file consists of a redo log buffer and a redo log file.

In MySql, to improve database performance, every change is not synchronized to disk in real time. Instead, it is stored in a buffer Pool called a “Boffer Pool” and then synchronized between the buffer Pool and disk using background threads.

If you take this mode, you may have a problem: if there is an outage or power outage before the data has been synchronized, you may lose the modification information of some committed transactions! This situation is not acceptable for database software.

The primary function of the redo log is to record the changes of the successfully committed transaction. The redo log is persisted to disk in real time after the transaction is committed, so that the redo log can be read after the system restarts to recover the latest data.

SQL — SQL — SQL — SQL — SQL — SQL — SQL — SQL — SQL — SQL

As shown in the figure above, when the transaction to change a row is enabled, the MySQL storage engine reads data from disk to the buffer pool in memory for modification. At this time, the data in memory is modified and the data in disk is different. This difference is also called ** “dirty pages” **.

Instead of flushing dirty pages back to disk every time a dirty page is generated, a typical storage engine uses background threads ** “master threads” ** to flush disks roughly once per second or every 10 seconds. In this case, data that has not been flushed back to disk can be lost in the event of database outages or power outages.

The redo log is used to reconcile memory and disk speed differences. When a transaction is committed, the storage engine first writes the modified data to the redo log, then modifies the actual data page in the buffer pool and refreshes the data synchronization in real time. If the database hangs during this process, since the redo log physical log file already records the transaction changes, the redo log can be used to recover the transaction data after the database restarts.

2）、undo log

We talked about redo logs, which are used to recover data and maintain the persistence of committed transactions. Another very important type of log in MySQL is the undo log, or rollback log. The undo log is used to record data before changes are made, as opposed to the redo log, which records data after changes.

Undo log records the data of the version before the transaction modification. If the transaction is rolled back due to a system error or the rollback operation, the undo log can be used to rollback the data to the state before the modification.

Each time data is written or modified, the storage engine records the modified information to the Undo log.

3. Transaction implementation

Earlier we talked about locks, multi-version concurrency control (MVCC), redo logs, and undo logs, which are the basis for MySQL to implement database transactions. In terms of the four characteristics of transactions, the corresponding relationship is mainly reflected as follows:

In fact, the ultimate purpose of transaction atomicity, persistence, and isolation is to ensure the consistency of transaction data. ACID is just a concept, and the ultimate purpose of a transaction is to ensure data reliability and consistency.

Let’s take a closer look at how transaction ACID is implemented.

1) Implementation of atomicity

Atomicity refers to the fact that a transaction must be regarded as an indivisible minimum unit. All operations in a transaction either succeed or fail to be rolled back. It is impossible for a transaction to only perform part of the operations, which is the concept of transaction atomicity.

MySQL database atomicity is mainly achieved through the rollback operation. The undo log is used to restore data to the original state when an error occurs or when a rollback statement is explicitly executed. The specific rules are as follows:

  • Each data change (INSERT /update/ DELETE) operation is accompanied by an undo log, and the rollback log must be persisted to disk before the data.

  • The so-called rollback refers to the reverse operation based on the undo log. For example, the reverse operation of delete is INSERT, the reverse operation of insert is DELETE, and the reverse operation of update is update.

2) Implementation of persistence

Persistence refers to the fact that once a transaction commits its changes, they are permanently stored in the database so that the modified data will not be lost in the event of a system crash.

Transaction persistence is primarily achieved through redo log logging. The redo log makes up for data differences caused by cache synchronization because of the following characteristics:

  • Redo logs are stored sequentially, while cache synchronization is performed randomly.

  • Cache synchronization is based on data pages that are larger than the redo log.

The redo log logic for transaction persistence is described in the redo log section earlier in this article!

3) Realization of isolation

Isolation is one of the most complex properties of transaction ACID. There are four isolation levels defined in the SQL standard, and each isolation level specifies changes in a transaction, which are visible between transactions and which are not.

There are four MySQL isolation levels (from lowest to highest) :

  • READ UNCOMMITED;

  • READ COMMITED

  • REPEATABLE READ (REPEATABLE READ)

  • SERIALIZABLE

The lower the isolation level, the higher the concurrency that the database can perform, but the more complex and expensive the implementation is. A thorough understanding of the isolation level and how it is implemented is equivalent to understanding transaction isolation in ACID.

As mentioned earlier, atomicity, persistence, and isolation are all ultimately for data consistency, but isolation is different from the other two. Atomicity and persistence are mainly for data reliability, such as data recovery after outages and data rollback after errors. The core goal of isolation is to manage the access sequence of multiple concurrent read and write requests to achieve secure and efficient access to database data. In essence, it is a tradeoff game between data security and performance.

High reliability isolation level with low concurrency performance (for example, SERIALIZABLE isolation level because all reads and writes are locked); Low reliability, high concurrency (e.g. READ UNCOMMITED, because reads and writes are not locked at all).

Next, we will analyze the characteristics of the four isolation levels:

READ UNCOMMITTED

At the READ UNCOMMITTED isolation level, changes in one transaction are visible to other transactions even if they have not been committed, meaning that a transaction can READ UNCOMMITTED data.

Since reads do not add locks, a write operation that modifies data while reading can cause “dirty reads”. The schematic diagram of uncommitted read isolation level reads and writes is as follows:

As shown in the figure above, the write request changes account to 200, at which point the transaction is not committed; However, a read request can read uncommitted transaction data account=200; Then write request transaction failed rollback account=100; In this case, the data whose account=200 is read by the read request is dirty.

The advantages of this isolation level are parallel reads and writes and high performance. But the disadvantage is easy to cause dirty read. So this isolation level is not normally taken in MySQL databases!

READ COMMITED

This transaction isolation level is also called **” Non-repeatable read or commit read “. ** It features that all changes made by a transaction before it commits are invisible to other transactions; Other transactions can only read committed changes.

This isolation level seems perfect and fits most logical scenarios, but this transaction isolation level creates problems with “unreread” and “phantom “**.

** unreread: ** is when the same row is read multiple times within a transaction with different results. For example, if transaction A reads row A, and transaction B modifies row A and commits the transaction, then the next time transaction A reads row A, it will be different from the first time!

** Phantom: ** is a transaction that retrieves data according to the same query criteria, but retrieves data multiple times with different results. For example, transaction A retrieves data with condition x=0 for the first time and obtains 5 records; At this point, transaction B inserts a data into the table with x=0 and commits the transaction; The second time transaction A retrieves the data with condition x=0, it gets 6 records!

So why do unrepeatable reads and phantom reads occur at the READ COMMITED isolation level?

In fact, the non-repeatable read transaction isolation level also uses the MVCC (Multi-version Concurrency Control) mechanism we discussed earlier. However, MVCC at the READ COMMITED isolation level generates a new system version number for each select, so each select operation in a transaction reads not one copy but a different copy of data, so between each select, If another transaction updates and commits the data we read, then unrepeatable reads and phantom reads can occur.

The causes of non-repeatable reads are shown as follows:

REPEATABLE READ

REPEATABLE READ transaction Isolation Level REPEATABLE READ is the default transaction isolation level of the MySQL database. At this transaction isolation level, the results of multiple reads within a transaction are consistent. This isolation level can avoid query problems such as dirty reads and unrepeatable reads.

This transaction isolation level is achieved mainly through the read-write lock +MVCC mechanism. The specific schematic diagram is as follows:

As shown in the figure above, the MVCC mechanism at this transaction isolation level does not generate a new system version number for each query within a transaction, so multiple queries within a transaction have one copy of the data and therefore do not cause unrepeatable reads. Refer to the previous section for more details on MVCC at this isolation level!

Note, however, that this isolation level addresses the problem of unrepeatable reads, but does not solve the problem of phantom reads, so if A conditional query exists in transaction A and another transaction B adds or deletes the conditional data and commits the transaction in the meantime, transaction A will still generate phantom reads. So be aware of this when using MySQL!

SERIALIZABLE

This isolation level is the easiest to understand because it does not cause any data inconsistencies due to the exclusive locking of read and write requests. It is not very high performance, so very few databases use this isolation level!

4) Realization of consistency

Consistency refers to the consistency of database data through rollback, recovery, and isolation under concurrent conditions! All that atomicity, persistence, and isolation is ultimately about consistency!

MySQL Storage Engine

In the previous section, we discussed MySQL concurrency control and transaction respectively, but in fact, the details of concurrency control and transaction are dependent on the MySQL storage engine to achieve. The most important and distinctive feature of MySQL is its storage engine architecture, which separates data processing from storage so that users can choose the corresponding storage engine according to performance, features and other specific requirements.

That said, the InnoDB storage engine is used in most cases with MySQL databases, but that doesn’t prevent us from taking a proper look at the features of other storage engines. Next, I will give you a brief summary, as follows:

Above, we have briefly summarized the general characteristics of MySQL storage engines and their applicable scenarios. However, in fact, in addition to InnoDB storage engine, other storage engines are rarely seen in Internet business. Although MySQL has a variety of built-in storage engines for specific scenarios, most of them have corresponding alternative technologies. For example, the log application now has Elasticsearch, the data warehouse application now has Hive, HBase and other products, and the in-memory database has MangoDB, Redis and other NoSQL data products. So can give MySQL play only InnoDB!