A brief introduction to the transaction mechanism in MySQL

Start from a problem

Recently, the bank of this matter more serious ah, many depositors of the money in the bank, it disappeared, and the bank does not ask, said the user's responsibility, litigation, users can also lose, this is "socialism." Let's not complain, a variety of trees, do not talk about the affairs of the.

When it comes to bank savings, the simple thing about taking money from a bank, and taking money from an ATM, is essentially a few steps:

Login ATM machine, enter password;

Connect the database, verify the password;

Validation success, access to user information, such as deposit balance;

The user enters the amount which needs to withdraw, presses the confirmation key;

Reduce the corresponding amount in the user account from the background database;

ATM spits out money;

The user took the money away.

A simple take money, mainly divided into the above steps. Do not know whether we have "naïve" thought, if in the 5th step, the background database has been reduced money, but the ATM is not spit out the money (although the actual also happened, but after all is a low probability event), this How to do?

On this issue, the developers of the banking system have long thought about it, so how do they deal with the problem? This is about the concept of the business summarized today.

Simple Talk about business

For the above to take money this matter, if there is a step out of the error, then cancel the whole move to withdraw money, in simple terms, is to withdraw money these 7 steps, either complete, or do nothing. In a database, the same is true of transactions.

A transaction consists of one or more SQL statements, operations in a transaction, either executed or not executed, which is the purpose of the transaction.

For a transaction, it needs to satisfy the acid characteristics, briefly speaking of the acid properties of the transaction.

A, to denote atomicity; atomicity means that the entire database transaction is an indivisible unit of work. The execution of the entire transaction succeeds only if all database operations in the transaction are executed successfully. If any of the SQL statements in a transaction fails, the SQL statement that has executed successfully must also be revoked, and the database state should be returned to the state before the execution of the transaction;

C, consistency; that is, consistency means that a transaction transforms a database from one state to another in a consistent state, and that the integrity constraints of the database are not compromised before the transaction begins and after the transaction ends;

I, the isolation is also called concurrency control, serializable or lock. The isolation of a transaction requires that each read-write transaction object be detached from the other transaction's operations object, that is, the transaction is not visible to other transactions before it is committed, which is usually implemented using a lock;

D, persistence, indicating that once a transaction is committed, the result is permanent, that is, the data has been written to the database, and if there have been downtime and other accidents, the database will be able to restore the data.

Summed up some of the basic concepts of the transaction, in MySQL, the transaction is divided into a lot of, below to see what exactly the transaction.

What are the transactions

Can you imagine? It's a broken business. There are so many things that can be divided into the following:

Flat business;

A flat transaction with a save point;

Chain transaction;

nested transactions;

Distributed transactions.

Now it's time to make a simple summary of these issues from a conceptual level.

Flat transaction

Flat transactions are one of the simplest, and are the most commonly used in the actual development of a transaction. In this transaction, all operations are at the same level, and the most common way is as follows:

BEGIN WORK

Operation 1

Operation 2

Operation 3

...

Operation N

COMMIT WORK

Or is this:

BEGIN WORK

Operation 1

Operation 2

Operation 3

...

Operation N

(Error occured)

ROLLBACK WORK

The main disadvantage of flat transactions is that it is not possible to commit or rollback a part of the transaction, or to commit it in several separate steps. For example, I go to Shenzhen from Hohhot, for the sake of cheap, I may do so:

BEGIN WORK

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

Operation2: Beijing---aircraft---> shenzhen

ROLLBACK WORK

But, if Operation1, the train from Hohhot to Beijing is late, missed the flight, how to do? Feel flat transaction characteristics, then I need to roll back, I went back to Hohhot, so the cost is not too high ah, so there is the following second kind of transaction-with the point of the flat transaction.

Flat transaction with Save point

This transaction, in addition to support for flat transaction support operations, allows rolling back to an earlier state in the same transaction during the execution of a transaction, because it is possible that errors that occur during the execution of some transactions do not invalidate all operations, and that the entire transaction is not up to the requirements and the overhead is too high. The savepoint is used to inform the system that the current state of the transaction should be remembered so that the transaction can return to that state when an error occurs later.