Detailed explanation of the implementation principle of ACID transaction in Mysql

introduction

As usual, let's start with a scene~

Interviewer: "Do you know the four characteristics of transactions?"
You: "Yes, ACID, Atomicity, Consistency, Isolation, Durability!"
Interviewer: "You use MySQL database, right? Can you briefly explain how these four features are implemented in InnoDB?"
You: "I only know how isolation is done balabala~~"
Interviewer: "You should go back and wait for notification~"

OK, back to the topic. When it comes to the four major characteristics of transactions, atomicity, consistency, isolation, and durability, many people understand them. But let's get a little more specific. How are these four features implemented in the database? Then few people can answer this question. Therefore, this article focuses on the implementation principles of the four major features in MySQL.

text

Let's take the example of transferring 50 yuan from account A to account B to illustrate the four major characteristics of ACID.

Atomicity

By definition, atomicity means that a transaction is an indivisible unit of work in which all operations are performed or none of them are performed. That is, either the transfer is successful or it fails, there is no middle state!

What happens if atomicity cannot be guaranteed?

OK, there will be a data inconsistency situation, account A will be subtracted by 50 yuan, while account B will be increased by 50 yuan, and the operation will fail. The system will lose 50 yuan for no reason~

Isolation

By definition, isolation means that when multiple transactions are executed concurrently, the operations within the transaction are isolated from other transactions, and the concurrently executed transactions cannot interfere with each other.

What happens if isolation cannot be guaranteed?

OK, suppose there is 200 yuan in account A and 0 yuan in account B. Account A transferred money to account B twice, with a total amount of 50 yuan, which was executed in two transactions respectively. If isolation cannot be guaranteed, the following situations may occur

As shown in the figure, if isolation is not guaranteed, A deducts money twice, but B only adds money once, 50 yuan disappears out of thin air, and data inconsistency still occurs!

ps: Careful readers may have discovered that MySQL relies on locks to solve isolation problems. Well, we’ll explain it later.

Persistence

By definition, durability means that once a transaction is committed, its changes to the database should be permanent. Subsequent other operations or failures should not have any effect on it.

What happens if persistence cannot be guaranteed?

In MySQL, in order to solve the problem of inconsistent CPU and disk speeds, MySQL loads the data on the disk into the memory, operates on the memory, and then writes it back to the disk. Well, suppose the server crashes at this time, all the data modified in the memory is lost, and persistence cannot be guaranteed.

Imagine that the system prompts you that the transfer was successful. But you find that the amount has not changed at all. At this time, the data is in an illegal state. We regard this state as data inconsistency.

consistency

By definition, consistency means that the data is in a legal state before and after the transaction is executed. This state is semantic rather than syntactic.
So what is the legal data status?

oK, this state is called a legal state if it satisfies the predetermined constraints. In more popular terms, this state is defined by you. If this state is met, the data is consistent. If this state is not met, the data is inconsistent!

What happens if consistency cannot be guaranteed?

Example 1: Account A has 200 yuan, and 300 yuan is transferred out. At this time, the balance of Account A is -100 yuan. You will naturally find that the data is inconsistent at this time. Why? Because you defined a state, the balance column must be greater than 0.

Example 2: Account A has 200 yuan, and 50 yuan is transferred to Account B. The money in Account A is deducted, but the balance in Account B does not increase due to various unexpected events. You also know that the data is inconsistent at this time, why? Because you defined a state that requires the balance of A+B to remain unchanged.

Practical answers

Question 1: How does Mysql ensure consistency?

OK, this question can be divided into two levels.

From the database level, the database ensures consistency through atomicity, isolation, and persistence. That is to say, among the four major characteristics of ACID, C (consistency) is the purpose, A (atomicity), I (isolation), and D (persistence) are means, which are provided by the database to ensure consistency. The database must implement the three major characteristics of AID to achieve consistency. For example, atomicity cannot be guaranteed, and obviously consistency cannot be guaranteed either.

However, if you deliberately write code that violates constraints in a transaction, consistency cannot be guaranteed. For example, in your transfer example, if your code deliberately does not add money to account B, the consistency cannot be guaranteed. Therefore, it is also necessary to consider the application layer.

From the application level, use the code to determine whether the database data is valid, and then decide whether to roll back or commit the data!

Question 2: How does Mysql ensure atomicity?

OK, it is to use Innodb's undo log.

Undo log is called rollback log, which is the key to achieve atomicity. When a transaction is rolled back, it can undo all successfully executed SQL statements. It needs to record the corresponding log information you want to roll back.

For example

• (1) When you delete a piece of data, you need to record the information of this data. When rolling back, insert the old data.
• (2) When you update a piece of data, you need to record the old value. When rolling back, perform the update operation based on the old value.
• (3) When inserting a piece of data, the primary key of the record is required. When rolling back, the delete operation is performed based on the primary key.

The undo log records the information needed for rollback. When a transaction fails or rollback is called, causing the transaction to be rolled back, the information in the undo log can be used to roll back the data to the state before the modification.

ps: What does the specific undo log look like? This can be written into an article. Moreover, not many people will read it after writing it down, so let’s just keep it simple for now.

Question 3: How does Mysql ensure persistence?

OK, it uses Innodb's redo log.

As mentioned before, MySQL first loads the data on the disk into memory, modifies the data in memory, and then flushes it back to disk. If the computer crashes suddenly at this time, the data in the memory will be lost.

How to solve this problem?

It's simple. Just write the data to disk before committing the transaction.

What's wrong with doing this?

• To modify just one byte in a page, the entire page must be flushed to disk, which is a waste of resources. After all, a page is 16kb in size. If you only change a little bit of it, you have to flush 16kb of content to disk, which doesn't sound reasonable.
• After all, the SQL in a transaction may involve the modification of multiple data pages, and these data pages may not be adjacent, which means random IO. Obviously, the speed of random IO operation will be slower.

Therefore, it was decided to use redo log to solve the above problems. When data is modified, it is not only performed in memory, but also recorded in the redo log. When a transaction is committed, the redo log is flushed to disk (part of the redo log is in memory and part is on disk). When the database crashes and is restarted, the contents of the redo log will be restored to the database, and then a decision will be made to roll back or commit the data based on the contents of the undo log and binlog.

What are the benefits of using redo log?

In fact, the advantage is that flushing redo logs to disk is more efficient than flushing data pages to disk. The specific performance is as follows

• The redo log is small in size, after all, it only records which page has been modified, so it is small in size and quick to flush.
• The redo log is appended to the end all the time and belongs to sequential IO. The efficiency is obviously faster than random IO.

ps: I don’t want to talk about what the redo log looks like in detail because there is too much content.

Question 4: How does Mysql ensure isolation?

OK, the lock and MVCC mechanism are used. Let's take the example of transfer to illustrate. There is an account table as follows

Table name t_balance

Among them, id is the primary key, user_id is the account name, and balance is the balance. Let’s take the example of transferring money twice, as shown in the following figure

As for MVCC, which stands for Multi Version Concurrency Control, a row of record data has multiple versions of snapshot data, and these snapshot data are in the undo log.

If a transaction reads a row that is being DELETEd or UPDATEd, the read operation does not wait for the lock on the row to be released, but instead reads a snapshot version of the row.

Since the MVCC mechanism is expressed differently in Repeatable Read and Read Commited MVCC, I will not go into details.

However, one thing to note is that when the transaction isolation level is Read Committed, a transaction can read data that has been committed by another transaction, which does not meet the isolation requirement. However, when the transaction isolation level is Repeatable Read, isolation is satisfied.

Summarize

This article explains the implementation principles of the four major ACID characteristics of MySQL transactions. I hope you will benefit from it.

Well, that’s all for this article. I hope the content of this article will be of certain reference value to your study or work. Thank you for your support of 123WORDPRESS.COM.