A comprehensive understanding of the transactions in Mysql _mysql

The item that has been doing the Order class recently, has used the transaction. Our database is selected MySQL, storage engine selection INNODB,INNODB for the business has good support. This article we come together to grilled a piece of business related knowledge.

Why should there be business?

Transactions are widely used in order systems, banking systems and many other scenarios. If there is one scenario: A and B users are depositors of the bank. Now a is going to transfer 500 yuan to B. Then there are a few things to do:

1. Check A's account balance >500 yuan;

2. A account deduction of 500 yuan;

3. B Account increase of 500 yuan;

Normal flow down, a account of the 500,B account has been added 500, happy. What if a account is deducted and the system fails? A lost 500 in vain, and B did not receive the 500 that should have belonged to him. In the above case, there is a prerequisite: a deduction of money and B plus money, either success or failure at the same time. This is the demand for business.

What is a business?

Rather than giving a definition of a transaction, it is better to say the nature of the transaction. As we all know, a transaction needs to meet acid four characteristics.

1. A (atomicity) atomic nature. The execution of a transaction is considered to be an indivisible smallest unit. The operation inside the transaction, either all successfully executed, or all failed to rollback, can not only execute a part of it.

2. C (consistency) consistency. The execution of a transaction should not break the integrity constraints of the database. If the 2nd operation in the above example crashes after execution, the sum of money for A and B is guaranteed to be unchanged.

3. I (Isolation) isolation. Generally speaking, the behavior between transactions should not affect each other. In reality, however, the degree of interaction between transactions is affected by the isolation level. The article will be detailed later.

4. D (durability) durability. After a transaction commits, the committed transaction needs to be persisted to disk. Even if the system crashes, the submitted data should not be lost.

Four isolation levels for transactions

As mentioned earlier, the isolation of a transaction is affected by the isolation level. So what is the isolation level of the transaction? The isolation level of a transaction can be considered the "selfish" degree of a transaction, which defines the visibility between transactions. The isolation level is divided into the following categories:

1.READ UNCOMMITTED (read not submitted). At the isolation level of RU, transaction A's modification of the data, even if it is not committed, is visible to transaction B, which is called dirty reading. This is a low degree of isolation isolation level, in the actual application will cause a lot of problems, so generally not used.

2.READ committed (Submit read). The problem of dirty reading does not occur under the isolation level of the RC. Transaction a changes to the data, after the submission will be visible to transaction B, for example, transaction B is opened to read Data 1, then transaction a open, change this data to 2, submit, B read this data again, will read the latest data 2. Under the isolation level of the RC, an issue that is not repeatable read occurs. This isolation level is the default isolation level for many databases.

3.REPEATABLE read (repeatable). At the isolation level of the RR, there is no problem with no repeatable reads. Transaction a changes to the data, and after submission, is not visible for transactions that are opened prior to transaction a. For example, when transaction B is opened, read the data 1, then transaction a opens, changes this data to 2, submits, B reads this data again, still can only read to 1. Under the isolation level of the RR, there is a problem with phantom reading. Phantom reading means that when a transaction reads a range of values and another transaction inserts a new record within that scope, the previous transaction reads the value of the range again and reads the newly inserted data. MySQL's default isolation level is RR, but MySQL's InnoDB engine clearance lock successfully solves the problem of Phantom reading.

4.SERIALIZABLE (Serializable). Serializable is the highest isolation level. This isolation level forces all things to execute serially, and at this isolation level, every row of data that is read is locked, leading to a large number of lock requisition problems and the worst performance.

To help understand the four isolation levels, here's an example. As shown in Figure 1, transaction A and transaction B are turned on, and data 1 is updated several times. Four little people at different times to open the transaction, you may see what the value of data 1?

Figure 1

The first villain, may read to 1-20 between any one. Because of the uncommitted read isolation level, the modification of the data by other transactions is also visible to the current transaction. The second villain may read 1, 10 and 20, and he can only read the data that other transactions have submitted. The third villain reads the data to be determined by the point at which the transaction is opened. How much is read when a transaction is opened, and what is the value that is read before the transaction is committed. The fourth villain, only open between a end and B start, is likely to read the data, while transactions A and transaction B are performed during the period is not read data. Because the four villain reading data is required to lock, transactions A and B during the execution, will occupy the data of the write lock, leading to the fourth villain waiting for the lock.

Figure 2 lists the problems faced by different isolation levels.

Figure 2

Obviously, the higher the isolation level, the greater the resource consumption (lock) it brings, and the lower its concurrency performance. To be exact, there is no concurrency under the Serializable isolation level.

Figure 3

Transactions in MySQL

The implementation of a transaction is a database-based storage engine. Different storage engines do not support transactions the same way. The storage engine that supports transactions in MySQL has InnoDB and NDB. The InnoDB is the MySQL default storage engine, the default isolation level is RR, and is further under the isolation level of the RR, through multiple versioning concurrency controls (mvcc,multiversion concurrency control To solve the problem of non repeatable reading, plus the gap lock (i.e. concurrency control) to solve the problem of phantom reading. Therefore, the RR isolation level of InnoDB actually achieves the serialization level effect, and retains the better concurrency performance.

The isolation of a transaction is achieved through a lock, whereas the atomicity, consistency, and durability of the transaction are implemented through the transaction log. When it comes to the transaction log, it is redo and undo that have to be said.

1.redo Log

In the InnoDB storage engine, the transaction log is implemented through the Redo (redo) log and the InnoDB storage engine's log buffer (InnoDB log buffer). When a transaction is turned on, the operations in the transaction are first written to the storage engine's log buffer, and before the transaction is committed, the cached logs need to be flushed to the disk for persistence, which is what DBAs often call "log-Ahead" (Write-ahead Logging). After the transaction is committed, the data files mapped in the buffer pool are slowly flushed to disk. If the database crashes or is down, then when the system restarts to recover, the database can be restored to a state prior to the crash, based on the logs recorded in the Redo log. Unfinished transactions, you can continue to commit, or you can choose to rollback, based on the recovery strategy.

When the system starts, it has allocated a contiguous storage space for the redo log, recording the redo log sequentially, and improving performance by sequential IO. All transactions share the storage space of the redo log, and their redo log is alternately recorded sequentially in the order in which they are executed. The following is a simple example:

Record 1:<TRX1, insert...>

Record 2:<TRX2, delete...>

Record 3:<TRX3, update...>

Record 4:<TRX1, update...>

Record 5:<TRX3, insert...>

2.undo Log

The Undo log is primarily a transaction rollback service. During the execution of a transaction, a certain amount of undo log is recorded in addition to the log redo log. The Undo log records the state of the data before each operation and, if it needs to be rolled back during the execution of the transaction, it can be rolled back according to the undo log. A rollback of a single transaction rolls back only the actions that are done by the current transaction and does not affect the operations done by other transactions.

The following is a simplified process for undo+redo transactions

Suppose there are 2 values, A and B, and the value is 1,2

1. Start transaction;

2. Record a=1 to undo log;

3. Update A = 3;

4. Record a=3 to redo log;

5. Record b=2 to undo log;

6. Update B = 4;

7. Record B = 4 to redo log;

8. Refresh Redo Log to disk

9. Commit

In any one step of 1-8, the system goes down, the transaction is not committed, and the transaction does not have any effect on the data on the disk. If you are down between 8-9, you can choose to rollback after recovery, or you can choose to continue completing the transaction submission, because the redo log is persisted at this time. If the system is down after 9, the data changed in the memory map is too late to brush back to the disk, then after the system is restored, the data can be brushed back to disk according to the redo log.

So, the redo log guarantees the persistence and consistency of the transaction, while the undo log guarantees the atomicity of the transaction.

Distributed transactions

There are many ways to realize distributed transaction, which can adopt the native transaction support provided by InnoDB, and can use message queue to realize the final consistency of distributed transaction. Here we talk about InnoDB's support for distributed transactions.

As pictured, the MySQL distributed transaction model. The model is divided into three blocks: the application (AP), the Resource Manager (RM), the transaction manager (TM).

The application defines the bounds of the transaction, specifying what transactions need to be done;

The resource Manager provides a way to access transactions, usually a database is a resource manager;

The transaction manager coordinates the various transactions in the global transaction.

A two-stage commit (two-phase commit) of a distributed transaction. The first stage all the transaction nodes are ready to tell the transaction manager ready. The second phase of the transaction manager tells each node whether it is a commit or a rollback. If a node fails, the global node is required to be all rollback to ensure the atomic nature of the transaction.

Summarize

When do I need to use a transaction? I think that as long as the business needs to meet the acid scenario, it needs the support of the transaction. Especially in the order system, the banking system, the business is indispensable. This article mainly introduces the characteristics of the transaction, and the MySQL InnoDB support for the transaction. Business-related knowledge is far more than the article said, this article only to make a point, the deficiencies also hope that the reader a lot of forgive me.