A brief introduction to the transaction mechanism in Mysql _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:

1. Login ATM machine, enter password;
2. Connect the database, verify the password;
3. Validation success, access to user information, such as deposit balance;
4. The user enters the amount which needs to withdraw, presses the confirmation key;
5. Reduce the corresponding amount in the user account from the background database;
6. ATM spits out money;
7. 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?

The developers of the banking system have long thought about this, 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 that? There are so many things that can be divided into the following:

1. Flat business;
2. A flat transaction with a save point;
3. Chain transaction;
4. nested transactions;
5. 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.
Chain Transactions
chain transactions, that is, the rollback, only to the most recent savepoint, while a flat transaction with a savepoint can be rolled back to any correct savepoint.
Nested transactions
look at this and you'll see what a nested transaction is:

   Begin WORK
     SubTransaction1:
         begin WORK
           suboperationx
         COMMIT WORK
     SubTransaction2:
         begin WORK
           suboperationy
         COMMIT WORK ...
     Subtransactionn:
         BEGIN WORK
           suboperationn
         commit WORK
   commit WORK

This is the nested transaction in which the transaction is nested, and the transaction at the root node is called the top-level transaction. The precursor of a transaction is called a parent transaction, and other transactions are called child transactions. The predecessor of a transaction is called a parent transaction, and the next layer of the transaction is called a child transaction.

A child transaction can be committed or rolled back, but its commit does not take effect immediately, unless it is committed by its parent transaction. It is therefore possible to determine that any child transaction is actually committed after the top-level transaction has been committed. Similarly, the rollback of any transaction causes all of its child transactions to be rolled back together.
Distributed Transactions
a distributed transaction usually refers to a flat transaction running in a distributed environment, so it is necessary to access the different nodes in the network according to the location of the data, for example: through the construction Bank to China Merchants Bank transfer, CCB and China Merchants Bank certainly use is not the same database, At the same time the database is not on a network node, then when the user transfer across the line, it is through distributed transactions to ensure that the data acid.

Using transactions in MySQL

The theory sums up again good, after all must through the practice to carry on the understanding. Here's how the transaction is used in MySQL.

Under the MySQL command line's default settings, transactions are committed automatically, that is, a commit is executed immediately after the execution of the SQL statement. Therefore, to open a transaction to display, use the command begin or start TRANSACTION, or execute the command set Autocommit=0 to prevent the use of autocommit from the current session.

Let's see what transaction control statements we can use.

1. Begin or start TRANSACTION; open a transaction;
2. Commit; You can also use commit WORK, but the two are equivalent. A commit commits a transaction and makes all modifications made to the database known as permanent;
3. ROLLBACK can use ROLLBACK WORK, but the two are equivalent. Rollback closes the user's transaction and revokes any uncommitted changes that are in progress;
4. SavePoint Identifier;savepoint allows a savepoint to be created in a transaction, with multiple savepoint in a transaction;
5. Release savepoint identifier; Deletes a save point for a transaction, and when there is no specified savepoint, executing the statement throws an exception;
6. ROLLBACK to identifier; Rolls the transaction back to the marker point;
7. Set TRANSACTION, which sets the isolation level for the transaction. The InnoDB storage engine provides transaction isolation levels for READ UNCOMMITTED, Read committed, Repeatable read, and serializable.

You don't have to "tube" them.

Sometimes some SQL statements produce an implicit commit operation, that is, after the execution of these statements, there is an implicit commit. There are the following SQL statements that you do not need to "tube":

• DDL statements, Alter DATABASE, ALTER EVENT, ALTER PROCEDURE, ALTER TABLE, ALTER VIEW, CREATE table, DROP table, RENAME table, TRUNCATE table, etc.;
• Modify the MySQL schema statement, CREATE user, DROP user, GRANT, RENAME user, REVOKE, SET PASSWORD;
• Management statements, ANALYZE table, CACHE index, CHECK table, LOAD INDEX into CACHE, OPTIMIZE table, REPAIR table and so on.

All of these SQL operations are implicit commit operations and do not require manual explicit submission.
Isolation level of the transaction

It's also mentioned that set transaction is used to set the isolation level of a transaction. What is the isolation level of that transaction?

In the database operation, the transaction isolation level is proposed in order to effectively guarantee the correctness of concurrent read data.

The InnoDB storage engine provides transaction isolation levels for READ UNCOMMITTED, Read committed, Repeatable read, and serializable. The differences between these isolation levels are as follows:

• Dirty reads: One transaction reads data that is not submitted by another transaction;
• For example, the transaction T1 updated the contents of a row, but did not commit the changes. The transaction T2 read the T1 updated row, and then T1 the rollback operation to cancel the changes just made. Now the rows read by T2 are invalid;
• Non-repeatable reads: In the same transaction, two times read the same data, the content is different;
• For example, a transaction T1 reads a row of records, followed by a transaction T2 the line of records that T1 just read. T1 then reads the line again and finds that the result is different from what was just read. This is referred to as "non-repeatable" reading, because the line of records that T1 originally read has changed;
• Phantom reads: In the same transaction, read two times with the same operation, the number of records is different;
• For example: Transaction T1 reads a result set returned by a specified where clause. The transaction T2 then inserts a new row of records that exactly satisfies the condition of the WHERE clause in the query criteria used by T1. T1 then uses the same query to retrieve the table again, but at this point it sees the new row that the transaction T2 just inserted. This new line is called a "phantom" because for T1 this line is like a sudden appearance.

The lower the isolation level, the fewer locks the transaction requests or the shorter the time to keep the lock. The default support isolation level for the INNODB storage engine is repeatable READ, where the isolation requirements of the transaction are fully guaranteed under this default transaction isolation level, which is the serializable level isolation that reaches the SQL standard.

We can use the SET TRANSACTION statement to change the isolation level of a single session or all new incoming connections. Its syntax is as follows:

SET [Session | GLOBAL] TRANSACTION Isolation Level {READ UNCOMMITTED | READ Committed | Repeatable READ | SERIALIZABLE}

Note: The default behavior (with no session and global) is to set the isolation level for the next (not started) transaction. If you use the Global keyword, the statement sets the default transaction level globally for all new connections created from that point, except for nonexistent connections. You need super privileges to do this. Use the session keyword to set the default transaction level for future transactions that are performed on the current connection. Any client can freely change the session isolation level (even in the middle of a transaction), or set the isolation level for the next transaction.

Mysql> Set Session transaction ISOLATION level repeatable read;
Query OK, 0 rows Affected (0.00 sec)

mysql> select @ @tx_isolation;
+-----------------+
| @ @tx_isolation |
+-----------------+
| Repeatable-read |
+-----------------+
1 row in Set (0.00 sec)

Summarize

This article, basically is the accumulation of theoretical concepts, the actual combat of things basically did not. But none of this is a problem, and it doesn't stop the article from becoming a reader's favorite article, right. Well, this article on MySQL in the business of this end, if there is something new, then summed up.