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

In this issue, the editor will bring you about how to achieve transaction ACID in Mysql. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Atomicity

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

What happens if atomicity cannot be guaranteed?

OK, there will be data inconsistency, An account minus 50 yuan, while B account increase 50 yuan failed. The system will lose 50 yuan for no reason.

Isolation

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

What happens if there is no guarantee of isolation?

OK, suppose An account has 200 yuan and B account has 0 yuan. Account A transfers to account B twice with an amount of 50 yuan, which is carried out in two transactions. If isolation cannot be guaranteed, the following situation occurs

As shown in the figure, if isolation is not guaranteed, A deducts money twice, while B only increases it once, disappearing 50 yuan out of thin air, and the data is still inconsistent!

Ps: as some careful readers may have found, mysql relies on locks to solve isolation problems. Well, we'll explain later.

Persistence

By definition, persistence means that once a transaction is committed, its changes to the database should be permanent. Other operations or failures that follow should not affect it in any way.

What happens if there is no guarantee of durability?

In Mysql, in order to solve the problem of inconsistency between CPU and disk speed, Mysql loads data from the disk into memory, operates on memory, and then writes back to the disk. Well, suppose there is an outage at this time, and all the data modified in memory is lost, and persistence cannot be guaranteed.

Imagine that the system prompts you to transfer money successfully. But you find that there is no change in the amount, and at this time the data has an illegal data state, which we regard as a situation of data inconsistency.

Consistency

By definition, consistency means that the data is in a legal state before and after the transaction is executed, which is semantic rather than grammatical.

So what is the legal data status?

OK, the state that satisfies the predetermined constraints is called the legal state, and more generally, the state is defined by yourself. Meet this state, the data is consistent, do not meet this state, the data is inconsistent!

What happens if consistency cannot be guaranteed?

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

Example 2: an account 200 yuan, transfer 50 yuan to B account, An account money deducted, but B account due to various accidents, the balance did not increase. You also know that the data are inconsistent at this time. Why? Because you define a state, it requires that the balance of AbeliB must remain the same.

Actual combat solution

Question 1: how does Mysql ensure consistency?

OK, this problem is divided into two levels.

From the database level, the database ensures consistency through atomicity, isolation and persistence. In other words, among the four characteristics of ACID, C (consistency) is the end, A (atomicity), I (isolation) and D (persistence) are the means to ensure consistency, and the means provided by the database. The database must implement the three features of AID before it is possible to achieve consistency. For example, atomicity cannot be guaranteed, and obviously consistency cannot be guaranteed.

However, if you deliberately write code that violates constraints in a transaction, consistency cannot be guaranteed. For example, if you deliberately do not add money to your B account in your code in the case of money transfer, there is still no guarantee of consistency. Therefore, it must also be considered from the perspective of the application layer.

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

Question 2: how does Mysql guarantee atomicity?

OK is the undo log that uses Innodb.

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

For example

(1) when you delete a piece of data, you need to record the information of this piece of data. When you roll back, insert this old piece of data

(2) when you update a piece of data, you need to record the old value, and when you roll back, perform the update operation according to the old value.

(3) when there is a piece of data in insert, the primary key of this record is needed. When rolling back, the delete operation is performed according to the primary key.

Undo log records the information needed for these rollbacks, and when the transaction fails or rollback is called, causing the transaction to need to be rolled back, you can use the information in undo log to roll back the data to what it was before modification.

Ps: you can write an article about what the specific undo log log looks like. And write it out, not many people read it, so let's have such a simple understanding first.

Question 3: how does Mysql guarantee persistence?

OK is the redo log that uses Innodb.

As mentioned earlier, Mysql loads the data on the disk into memory, modifies the data in memory, and then brushes it back to disk. If there is a sudden downtime at this time, the data in memory will be lost.

How to solve this problem?

It's easy. Just write the data to disk before the transaction is committed.

What's wrong with this?

If you modify only one byte in a page, you will brush the entire page into disk, which is a waste of resources. After all, the size of a page 16kb, you only change a little thing, you have to brush the contents of 16kb into disk, it sounds unreasonable.

After all, the SQL in a transaction may involve the modification of multiple data pages, and these data pages may not be adjacent, that is, they belong to random IO. Obviously, the operation of random IO will be relatively slow.

Therefore, I decided to use redo log to solve the above problem. When the data is modified, the operation is not only operated in memory, but also recorded in redo log. When a transaction commits, the redo log log is flushed (the redo log is partly in memory and partly on disk). When the database is down and restarted, the contents of redo log will be restored to the database, and then the data will be rolled back or submitted based on the contents of undo log and binlog.

What are the benefits of adopting redo log?

In fact, the advantage is that brushing redo log is more efficient than comparing data pages, as shown below

Redo log is small in size, after all, it only records which page has been modified and what has been modified, so it is small in size and quick to swipe.

Redo log is appended all the way to the end and belongs to sequential IO. Efficiency is obviously faster than random IO.

Ps: I don't want to talk about what redo log looks like, because there's too much content.

Question 4: how does Mysql ensure isolation?

OK, which makes use of locks and MVCC mechanisms. Or take the example of money transfer to illustrate that there is an account table as follows

Table name t_balance

Iduser_idbalance1A2002B0

Where id is the primary key, user_id is the account name, and balance is the balance. Take two transfers as an example, as shown in the following figure

As for MVCC, that is, multi-version concurrency control (Multi Version Concurrency Control), there are multiple versions of a row of record data for snapshot data, which are in undo log.

If a row read by a transaction is doing a DELELE or UPDATE operation, the read operation does not wait for the lock on the row to be released, but reads the snapshot version of the row.

Since the MVCC mechanism has different MVCC representations in readable (Repeateable Read) and read submitted (Read Commited), I will not elaborate.

However, it is stated that when the transaction isolation level is read committed (Read Commited), it is not quarantined that one transaction can read the committed data of another transaction. However, when the transaction isolation level is repeatable read (Repeateable Read), isolation is satisfied.

The above is the editor for you to share how to achieve transaction ACID in Mysql, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.