A Brief Introduction to the transaction mechanism in MySQL

A Brief Introduction to the transaction mechanism in MySQL

Start with a question

Recently, the Bank has suffered a lot of troubles. Many depositors put their money in the bank, but the bank still does not care about it, saying it is the responsibility of the user. After a lawsuit, the user can still lose, this is socialism ". We are still not complaining about the state of affairs.

When talking about saving money in a bank, we have to talk about the simple process of getting money from a bank and taking money from an ATM. In fact, there are mainly the following steps:

Log on to the ATM and enter the password;

Connect to the database and verify the password;

The verification is successful and user information, such as deposit balance, is obtained;

Enter the amount to be withdrawn, and press OK;

Remove the corresponding amount from the user account from the background database;

ATM spit out money;

The user takes the money away.

A simple way to get money is mainly divided into the above steps. I don't know if you have thought about it "Naive". If the money has been lost in the back-end database in step 1, but the money has not been spit out by the ATM (although it has actually happened, but it is a low probability event.) What should I do?

The developers of the banking system have long thought about this issue. How did they solve this problem? This is about the transaction concept summarized today.

Simple talk about transactions

If an error occurs at one of the above steps, cancel the whole process. Simply put, the 7 steps are completed, or do nothing. In the database, transactions are the same.

A transaction consists of one or more SQL statements. These SQL statements are either executed or not executed during the operations in the transaction. This is the purpose of the transaction.

For a transaction, it must satisfy the ACID feature. The ACID feature of the transaction is briefly described below.

A Indicates atomicity. atomicity indicates that the entire database transaction is an inseparable unit of work. The execution of the entire transaction is successful only when all database operations in the transaction are successfully executed. If any SQL statement in the transaction fails to be executed, the SQL statement that has been successfully executed must also be revoked, and the database status should be returned to the status before the transaction is executed;

C Indicates consistency. That is to say, consistency means that the transaction changes the database from one State to another. The integrity constraints of the database are not damaged before and after the transaction starts;

I, indicating isolation; isolation is also called concurrency control, serializable or lock. The isolation of transactions requires that the objects of each read/write transaction and the operation objects of other transactions can be separated, that is, the transaction is invisible to other transactions before committing, which is usually implemented using locks;

D. Durability indicates that once a transaction is committed, the result is permanent, that is, the data has been written to the database. In the event of an accident such as a crash, the database can also recover data.

I have summarized some basic concepts of transactions. In MySQL, transactions are still divided into many categories. Let's take a look at what transactions are there.

What transactions are there?

Can you imagine it? Such a broken transaction will be divided into the following types:

Flat transactions;

Flat transactions with storage points;

Chain transactions;

Nested transactions;

Distributed transactions.

Now let's make a brief summary of these transactions in terms of concept.

Flat transactions

Flat transactions are the simplest and the most commonly used transactions in actual development. In such a transaction, all operations are at the same level. The most common method is as follows:

BEGIN WORK

Operation 1

Operation 2

Operation 3

...

Operation N

COMMIT WORK

Or this:

BEGIN WORK

Operation 1

Operation 2

Operation 3

...

Operation N

(Error Occured)

ROLLBACK WORK

The main disadvantage of a flat transaction is that it cannot commit or roll back a part of the transaction, or commit in several independent steps. For example, if I went to Shenzhen from Hohhot to make it cheaper, I may do this:

BEGIN WORK

Operation1: Hohhot --- train ---> Beijing

Operation2: Beijing --- aircraft ---> Shenzhen

ROLLBACK WORK

However, if Operation1, the train from Hohhot to Beijing is late and the flight is missed, what should I do? If I feel that flat transactions are characteristic, I need to roll back and I will return to Hohhot. Is the cost too high, so there is the second transaction-a flat transaction with a storage point.

Flat transactions with storage points

In addition to operations supported by flat transactions, such transactions allow rollback to an earlier state in the same transaction during transaction execution, this is because the errors that may occur during the execution of some transactions will not be invalid for all operations. abandoning the entire transaction does not meet the requirements, and the overhead is too large. The Save point is used to notify the system to remember the current state of the transaction so that the transaction can return to this State when an error occurs in the future.