A Brief Introduction to the transaction mechanism in MySQL and a brief introduction to mysql transactions

A Brief Introduction to the transaction mechanism in MySQL and a brief introduction to mysql transactions

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:

1. Log on to the ATM and enter the password;
2. Connect to the database and verify the password;
3. The verification is successful and user information, such as deposit balance, is obtained;
4. Enter the amount to be withdrawn, and press OK;
5. Remove the corresponding amount from the user account from the background database;
6. ATM spit out money;
7. 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:

1. Flat transactions;
2. Flat transactions with storage points;
3. Chain transactions;
4. Nested transactions;
5. 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 --- airplane ---> 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.
Chain transactions
A chain transaction refers to a flat transaction with a storage point that can only be restored to the nearest storage point during rollback, while a flat transaction with a storage point can be rolled back to any correct storage point.
Nested transactions
Now you can understand 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 a nested transaction, which is nested in the transaction. The transaction at the root node is called the top-level transaction. A transaction is called a parent transaction, and other transactions are called subtransactions. A transaction is called a parent transaction, and the next layer of the transaction is called a subtransaction.

A subtransaction can be committed or rolled back, but its commit operation does not take effect immediately Unless committed by its parent transaction. Therefore, it can be determined that any sub-transaction is actually committed only after the top-level transaction is committed. Similarly, the rollback of any transaction will cause all its sub-transactions to roll back together.
 Distributed transactions
Distributed transactions usually refer to flat transactions that run in a distributed environment. Therefore, you need to access different nodes in the Network Based on the Data Location. For example, you need to transfer money to China Merchants Bank through China Construction Bank, china Construction Bank and China Merchants Bank certainly do not use the same database, and the databases of the two are not on the same network node. When a user transfers money across lines, the ACID of the data is guaranteed through distributed transactions.

Use transactions in MySQL

Even better theoretical summary, we should all understand it through practice. The following describes how to use transactions in MySQL.

In the default settings of MySQL command line, transactions are automatically committed, that is, after the SQL statement is executed, the COMMIT operation is immediately executed. Therefore, to enable a TRANSACTION explicitly, you must use the BEGIN or start transaction command, or run the set autocommit = 0 command to disable automatic submission of the current session.

To see which transaction control statements can be used.

1. BEGIN or start transaction; START a TRANSACTION explicitly;
2. You can also use commit work, but they are equivalent. COMMIT commits transactions and makes all modifications made to the database permanent;
3. ROLLBACK; you can use rollback work, but they are equivalent. Rollback ends the user's transaction and revokes all uncommitted changes in progress;
4. SAVEPOINT identifier; SAVEPOINT allows you to create a storage point in the transaction. A transaction can have multiple savepoints;
5. Release savepoint identifier; delete a transaction's storage point. If no storage point is specified, executing this statement throws an exception;
6. Rollback to identifier; rolls back the transaction TO the mark point;
7. Set transaction; used to SET the TRANSACTION isolation level. The InnoDB Storage engine provides transaction isolation levels including read uncommitted, read committed, repeatable read, and SERIALIZABLE.

You don't need to worry about this"

Sometimes some SQL statements generate an implicit COMMIT operation, that is, after these statements are executed, there will be an implicit COMMIT operation. You do not have to worry about the following SQL statements ":

• DDL statements: alter database, alter event, alter procedure, alter table, alter view, create table, drop table, rename table, and truncate table;
• Modify MYSQL schema statements, such as create user, drop user, GRANT, rename user, REVOKE, and set password;
• Manage statements, including analyze table, cache index, check table, load index into cache, optimize table, and repair table.

The preceding SQL operations are implicitly submitted without manual explicit submission.
Transaction isolation level

As mentioned above, set transaction is used to SET the TRANSACTION isolation level. What is the isolation level of the transaction?

In database operations, the transaction isolation level is proposed to effectively ensure the correctness of Concurrent Data Reading.

The InnoDB Storage engine provides transaction isolation levels including read uncommitted, read committed, repeatable read, and SERIALIZABLE. The differences between these isolation levels are as follows:

• Dirty read: one transaction reads data not committed by another transaction;
• For example, transaction T1 updates the content of a row of records, but does not commit the modifications. Transaction T2 reads the updated line T1, and T1 performs the rollback operation to cancel the modification. The row read by T2;
• Non-repeated read: In the same transaction, the same data is read twice and the obtained content is different;
• For example, transaction T1 reads a row of records, and then transaction T2 modifies the row of records that T1 just read. Then T1 reads the record again and finds that it is different from the result just read. This is called "repeatable" reading, because the row record originally read by T1 has changed;
• Phantom read: in a transaction, the same operation is used to read two times, and the number of records is different;
• For example, transaction T1 reads the result set returned by a specified WHERE clause. Then, transaction T2 inserts a new row of records. This row of records exactly meets the WHERE clause condition in the query conditions used by T1. Then T1 uses the same query to retrieve the table again, but now the new row inserted by transaction T2 is displayed. This new line is called a "phantom", because for T1, this line is like a sudden appearance.

The lower the isolation level, the less the transaction request lock or the shorter the lock holding time. The default isolation level of InnoDB Storage engine is repeatable read. Under this default transaction isolation level, the isolation requirement of transactions can be fully guaranteed, that is, the SERIALIZABLE isolation that meets the SQL standard.

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

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

Note: The default action (without session or global) is to set the isolation level for the next (not started) transaction. If the GLOBAL keyword is used, the statement sets the default transaction level globally for all new connections (except non-existing connections) created from that point. You need the SUPER permission to do this. Use the SESSION keyword to set the default transaction level for future transactions executed on the current connection. Any client can freely change the session isolation level (even in the middle of the 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)

Summary

This article is basically a accumulation of theoretical concepts, and there are basically no practical things. However, none of these are problems. This cannot block this article from becoming a favorite article, right. Now, this article on MySQL transactions is over. If there are new things in the future, we will summarize them.