9.1 Transaction processing

9.1.1 Concept of transactions

For example, to understand what a transaction is ：

The transfer can be done in two parts ： into and Transfer out
Only when these two parts are completed can the transfer be considered successful .

A Account to B Account transfer 100 element , that ：
A The accounts are down 100 element
B Account increase 100 element

In the database , This transfer process uses two SQL Statement to complete , as follows ：

# A The accounts are down 100 element 
UPDATE account SET money = money - 100 WHERE name = 'A';
# B Account increase 100 element 
UPDATE account SET money = money + 100 WHERE name = 'B';

Under normal circumstances , If these two SQL If all statements can be executed normally , Then the deal is done , If any one of the statements appears abnormal No implementation , The amounts of the two accounts will not be synchronized , Cause mistakes .

To prevent this from happening , You need to use MySQL Medium Business （Transaction）

Business Is a set of operations for the database , It can be made up of one or more SQL Sentence composition .

9.1.2 The basic operation of a transaction

By default , User executed Every one of them SQL sentence Will be treated as a separate business Automatically Submit .

If you want to put a group SQL Statement as a transaction , You need to execute the following statement to explicitly start a transaction .

START TRANSACTION;

here , Every one of them SQL sentence No longer automatically submit , Users need to manually submit , The operation will take effect , The following command is submitted manually ：

COMMIT;

If you don't want to commit the current transaction , You can cancel the transaction （ That is, rollback ）：

ROLLBACK;

Transaction case demonstration ：
First create a data table sh_user, There are two fields name and money：

CREATE TABLE sh_user(
NAME CHAR(20),
money INT)

Then insert two pieces of data ：

INSERT INTO sh_user VALUES('a',1000),('b',1000)

Check it out. ：

# View user data 
SELECT name, money FROM sh_user;
+------+---------+
| name | money |
+------+---------+
| a | 1000.00 |
| b | 1000.00 |
+------+---------+
2 rows in set (0.00 sec)

take a User 100 Yuan transferred to b user

# ① Open transaction 
mysql> START TRANSACTION;
# ② a Reduce 100 element 
mysql> UPDATE sh_user SET money = money - 100 WHERE name = 'a';
# ③ b increase 100 element 
mysql> UPDATE sh_user SET money = money + 100 WHERE name = 'b';
# ④ Commit transaction 
mysql> COMMIT;

Inquire about Alex and Bill Amount of :

mysql> SELECT name, money FROM sh_user;
+------+---------+
| name | money |
+------+---------+
| a | 900.00 |
| b | 1100.00 |
+------+---------+
2 rows in set (0.00 sec)

Rollback of transaction

# ① Open transaction 
mysql> START TRANSACTION;
# ② b deduction 100 element 
mysql> UPDATE sh_user SET money = money - 100 WHERE name = 'b';
# ③ Inquire about b Amount of 
mysql> SELECT name, money FROM sh_user WHERE name = 'b';
+------+---------+
| name | money |
+------+---------+
| b | 1000.00 |
+------+---------+
1 row in set (0.00 sec)

Find out b My money comes from 1100, Reduce 100 And then become 1000 element , Then we start rolling back

# ① Roll back the transaction 
mysql> ROLLBACK;
# ② see Bill Amount of 
mysql> SELECT name, money FROM sh_user WHERE name = 'b';
+------+---------+
| name | money |
+------+---------+
| b | 1100.00 |
+------+---------+
1 row in set (0.00 sec)

After rollback , The money is back 1100 element （ The state before the transaction ）

The execution of the transaction is either successful , Or return to the state before the transaction , This ensures the synchronization of the same transaction operation and the integrity of data .

MySQL Transactions in must meet A、C、I、D this 4 Basic characteristics .

• Atomicity： Atomicity
• Consistency： Uniformity
• Isolation： Isolation,
• Durability： persistence

Atomicity （Atomicity）

• A transaction must be treated as an indivisible minimum unit of work , Only all database operations in the transaction are successful , The whole transaction is successful .
• If there is any one in the transaction SQL Statement execution failed , Has been implemented successfully SQL The statement must also undo , The state of the database returns to the state before the transaction is executed .

Uniformity （Consistency）

• Consistency means that when a transaction is processed , Whether it's successful or not , Ensure that the database system is in a consistent state , Ensure that the database system never returns to an unprocessed transaction .
• MySQL Consistency in is mainly realized by logging mechanism , Log all changes to the database , Provides trace records for transaction recovery .

Isolation, （Isolation）

• Isolation refers to when a transaction is executing , Will not be affected by other matters . It ensures that all operations of unfinished transactions are isolated from the database system , Until the transaction is completed , To see the execution result of the transaction .
• Technologies related to isolation include concurrency control 、 Serializable 、 Locks, etc. . When multiple users access the database concurrently , A transaction opened by a database for each user , Can't be disturbed by the operation data of other transactions , Multiple concurrent transactions should be isolated from each other .

persistence （Durability）

• Persistence is when a transaction is committed , Its changes to the database are permanent .
• Transaction persistence cannot be 100% persistent , Permanence can only be guaranteed from the perspective of the transaction itself , And some external reasons lead to database failure , If the hard disk is damaged , Then all submitted data may be lost .

attention:

• MySQL Nested transactions are not allowed in , If it's executing START TRANSACTION The previous transaction before the statement has not been committed , Meeting Perform the commit operation implicitly .
• Transaction processing is mainly for the processing of data in the data table , Does not include creating or deleting databases 、 Data sheet , Modify the table structure and other operations , and When such operations are performed, the transaction is implicitly committed .
  for example ： In the use of START TRANSACTION Execute after starting the transaction ALTER TABLE Modify table structure , Implicitly commit transactions .
• When executed COMMIT or ROLLBACK after , The current transaction will end automatically .
  ROLLBACK Only for Not submitted Transaction rollback for , Has been submitted The business of Can't roll back .
• MySQL 5.7 The default storage engine is InnoDB, The storage engine supports transactions , Another common storage engine MyISAM Unsupported transaction .
  about MyISAM Data table of storage engine , Whether the transaction is committed or not , All operations on the data will take effect immediately , Cannot be rolled back .

Automatic commit of transaction
MySQL The default is auto submit mode .
If the transaction is not explicitly opened （START TRANSACTION）, Every one of them SQL Statements are submitted automatically （COMMIT）.

Check whether the transaction is automatically committed

mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)

1： Turn on auto submit
0： Turn off auto submit

Turn off auto submit ：

SET AUTOCOMMIT = 0;

After closing ： The user needs to perform the submission manually （COMMIT） operation .
If terminated directly MySQL conversation ,MySQL It will automatically roll back .

9.1.3 Save point of transaction

When rolling back a transaction , If you want to undo only part of , You can use savepoints to achieve .

SAVEPOINT Save roll call ;

After setting the savepoint , The transaction can be rolled back to the specified savepoint .

ROLLBACK TO SAVEPOINT Save roll call ;

If you no longer need a savepoint , Use the following statement to delete ：

RELEASE SAVEPOINT Save roll call ;

Examples demonstrate ：

mysql> SELECT name, money FROM sh_user WHERE name = 'a';
+------+--------+
| name | money |
+------+--------+
| a | 900.00 |
+------+--------+
1 row in set (0.00 sec)
# ① Open transaction 
mysql> START TRANSACTION;
# ② a deduction 100 element 
mysql> UPDATE sh_user SET money = money - 100 WHERE name = 'a';
# ③ Create a savepoint s1
mysql> SAVEPOINT s1;
# ④ a Then deduct 50 element 
mysql> UPDATE sh_user SET money = money - 50 WHERE name = 'a';

Save it s1 When a Of money by 800 element
After another update ,a Of money by 750 element

# ① Roll back to savepoint s1
mysql> ROLLBACK TO SAVEPOINT s1;
# ② Inquire about a Amount of 
mysql> SELECT name, money FROM sh_user WHERE name = 'a';
+------+--------+
| name | money |
+------+--------+
| a | 800.00 |
+------+--------+
1 row in set (0.00 sec)

At this time, it returns to 800 element , But the transaction has not yet been committed

After rollback , Will return to the original 900 element

# ① Roll back the transaction 
mysql> ROLLBACK;
# ② see a Amount of 
mysql> SELECT name, money FROM sh_user WHERE name = 'a';
+------+--------+
| name | money |
+------+--------+
| a | 900.00 |
+------+--------+
1 row in set (0.00 sec)

Multiple savepoints can be created in one transaction , After committing the transaction , The savepoint in the transaction will be deleted .


After rolling back to a savepoint , The savepoint created after the savepoint will also disappear .

9.2 Transaction isolation level

9.2.1 Check the isolation level

The meaning of transaction isolation level ：

Database is a multi-user shared resource ,MySQL Allow concurrent access by multiple threads , Users can execute different transactions through different threads .

In order to ensure that these transactions are not affected , It is necessary to set the isolation level for transactions .

# ① View the global isolation level 
SELECT @@global.transaction_isolation;
# ② View the isolation level in the current session 
SELECT @@session.transaction_isolation;
# ③ View the isolation level of the next transaction 
SELECT @@transaction_isolation;

Global isolation level ： Affect all connections MySQL user .
Current session isolation level ： It only affects the current login MySQL Users of the server .( Will not affect other users ）
The isolation level of the next transaction ： It only affects the next transaction operation of the current user .

By default 3 The result of both methods is REPEATABLE READ

mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)

MySQL Isolation level of transactions in ：

• REPEATABLE READ： Repeatable
• READ UNCOMMITTED： Read uncommitted
• READ COMMITTED： Read commit
• SERIALIZABLE： Serializable

9.2.2 Change the isolation level

Set the isolation level of the transaction ：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL Parameter values

SESSION： Current session
GLOBAL： overall situation
Omit directly ： The isolation level of the next transaction
TRANSACTION： Business
ISOLATION： Isolation
LEVEL： Level

Using the demonstration ： It is amended as follows READ UNCOMMITTED

# ① Modify the transaction isolation level 
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)
# ② Check whether the modification is successful 
mysql> SELECT @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| READ-UNCOMMITTED |
+---------------------------------+
1 row in set (0.00 sec)

The default access mode for transactions is READ WRITE（ read / Write mode ）,
Transactions can be executed ： read （ Inquire about ） or Write （ change 、 Insert 、 Delete etc. ） operation .

If development needs , You can set the access mode of the transaction to READ ONLY（ read only mode ）, Prohibit changes to the table .

# ① Set read-only transaction 
SET [SESSION | GLOBAL] TRANSACTION READ ONLY
# ② Revert to read / write transactions 
SET [SESSION | GLOBAL] TRANSACTION READ WRITE

9.2.3 MySQL Of 4 Medium isolation level

READ UNCOMMITTED（ Read uncommitted ）:
The lowest level in a transaction , Uncommitted data in other transactions can be read .
Also known as dirty reading （Dirty Read）： One transaction reads uncommitted data from another transaction .

 Scenario ：a to b Transfer accounts 100 Yuan to buy goods .
a Transfer after opening transaction , But don't commit the transaction , notice b To query ,
If b The isolation level of is low , It will read a Uncommitted data in the transaction ,
Find out a I did transfer it to myself 100 element , Just give it to a deliver goods .
etc. b After the delivery is successful ,a Roll back the transaction ,b You're going to lose .

Dirty reading effect demonstration :

Set up the client B Isolation level , Dirty reading allowed

# client B
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

On the client side B Query in b Current amount

# client B
mysql> SELECT name, money FROM sh_user WHERE name = 'b';
+------+---------+
| name | money |
+------+---------+
| b | 1100.00 |
+------+---------+
1 rows in set (0.00 sec)

client A Open transaction and transfer

# client A
mysql> START TRANSACTION;
mysql> UPDATE sh_user SET money = money - 100 WHERE name = 'a';
mysql> UPDATE sh_user SET money = money + 100 WHERE name = 'b';

client A Transaction not committed , client B Inquiry amount

# client B
mysql> SELECT name, money FROM sh_user WHERE name = 'Bill';
+------+---------+
| name | money |
+------+---------+
| Bill | 1200.00 |
+------+---------+
1 row in set (0.00 sec)

here ,b Uncommitted data read , Show that you are 1200 element , In fact, or 1100 element , because a Transfer it to him 100 Yuan hasn't been submitted yet

Raise the isolation level , Solve the dirty reading problem

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
mysql> SELECT name, money FROM sh_user WHERE name = 'Bill';
+------+---------+
| name | money |
+------+---------+
| Bill | 1100.00 |
+------+---------+
1 row in set (0.00 sec)

End of experiment , Rollback client A The business of

# client A
mysql> ROLLBACK;

READ COMMITTED（ Read commit ）

majority DBMS（ Such as SQL Server、Oracle） Default isolation level , But does not include MySQL.
Only data that has been committed by other transactions can be read , Avoid dirty reading problems .

But there will be non repeatable （NON-REPEATABLE READ） problem .

It can't be read repeatedly ： The results of multiple queries in a transaction are inconsistent , The reason is that the data has changed during the query .

Scenario ： Count the total amount of all users in the background of the website .
The first 1 Queries a Yes 900 element ,
The first 2 Queries a Yes 800 element .

problem ： In the same transaction , The results of the same two queries are different ,
reason ： The first 2 Before query a Take out 100 element .

Set isolation level , Inquire about a Amount of

# client B
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
mysql> START TRANSACTION;
mysql> SELECT name, money FROM sh_user WHERE name = 'a';
+------+--------+
| name | money |
+------+--------+
| a | 900.00 |
+------+--------+
1 row in set (0.00 sec)

a Take out 100 element , Query again a amount of money

# client A
mysql> UPDATE sh_user SET money = money - 100 WHERE name = 'a';
# client B
mysql> SELECT name, money FROM sh_user WHERE name = 'a';
+------+--------+
| name | money |
+------+--------+
| a | 800.00 |
+------+--------+
1 row in set (0.00 sec)

The results of the two queries are inconsistent

End of experiment , Commit transaction

# client B
mysql> COMMIT;

Change isolation level , Avoid client B Non repeatable

# client B
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
mysql> START TRANSACTION;
mysql> SELECT name, money FROM sh_user WHERE name = 'a';
+------+--------+
| name | money |
+------+--------+
| a | 800.00 |
+------+--------+
1 row in set (0.00 sec)

change Alex Amount of , client B The results of the two queries are consistent

# client A
mysql> UPDATE sh_user SET money = money + 100 WHERE name = 'a';
# client B
mysql> SELECT name, money FROM sh_user WHERE name = 'a';
+------+--------+
| name | money |
+------+--------+
| a | 800.00 |
+------+--------+
1 row in set (0.00 sec)

End of experiment , Commit transaction

# client B
mysql> COMMIT;

REPEATABLE READ（ Repeatable ）

MySQL The default transaction isolation level of , It solves the problems of dirty reading and non repeatable reading ,
It ensures that multiple instances of the same transaction read data concurrently , You'll see the same result .

In theory, unreal reading will occur at this level （PHANTOM READ） problem .
Unreal reading is also called virtual reading , It means that the number of data in two queries in a transaction is inconsistent ,
Such as ： Other transactions have done the operation of inserting records , Resulting in an increase in the number of records .
however ,MySQL Of InnoDB The storage engine has solved the unreal reading problem .

Scenario ： When calculating the total amount of all users in the background of the website ,
There are currently only two users , The total amount is 2000 element ,
Add a new user , And put in 1000 element .

If you count again, you will find that the total amount becomes 3000 element , Caused unreal reading

adopt READ COMMITTED Demonstrate unreal reading questions

# client B
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
mysql> START TRANSACTION;
mysql> SELECT SUM(money) FROM sh_user;
+------------+
| SUM(money) |
+------------+
| 2000.00 |
+------------+
1 row in set (0.00 sec)

client A Add a new user , client B Will read unreal

# client A
mysql> INSERT INTO sh_user (id, name, money) VALUES
-> (3, 'Tom', 1000);
# client B
mysql> SELECT SUM(money) FROM sh_user;
+------------+
| SUM(money) |
+------------+
| 3000.00 |
+------------+
1 row in set (0.00 sec)

End of experiment , Commit transaction

# client B
mysql> COMMIT;

utilize REPEATABLE READ Avoid unreal reading

# client B
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
mysql> START TRANSACTION;
mysql> SELECT SUM(money) FROM sh_user;
+------------+
| SUM(money) |
+------------+
| 3000.00 |
+------------+
1 row in set (0.00 sec)

client A Add a new user , client B There's no unreal reading

# client A
mysql> INSERT INTO sh_user (id, name, money) VALUES
-> (4, 'd', 1000);
# client B
mysql> SELECT SUM(money) FROM sh_user;
+------------+
| SUM(money) |
+------------+
| 3000.00 |
+------------+
1 row in set (0.00 sec)

End of experiment , Commit transaction

# client B
mysql> COMMIT;

SERIALIZABLE（ Serializable ）
The highest level of isolation , It locks every row of data read , So that there will be no conflict ,
Solved dirty reading 、 The problem of non repetition and unreal reading .
Locking may cause timeout （Timeout） Compete with locks （Lock Contention） The phenomenon ,
Performance is 4 The lowest of the three isolation levels .
Unless for the stability of the data , When you need to force a reduction in concurrency , Will choose this isolation level .

demonstration SERIALIZABLE：

# client B
mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
mysql> START TRANSACTION;

client B Query the data after starting the transaction , The table will be locked

# client B
mysql> SELECT name, money FROM sh_user WHERE name = 'a';
+------+--------+
| name | money |
+------+--------+
| a | 900.00 |
+------+--------+
1 row in set (0.00 sec)

client A Unable to write immediately , Wait for the table to unlock

# client A
mysql> UPDATE sh_user SET money = money + 100 WHERE name = 'Alex';
（ At this time, the cursor keeps flashing , Enter the waiting state ）

client B After committing the transaction , client A Can be written successfully

# client B
mysql> COMMIT;
# client A
Query OK, 1 row affected (3.99 sec)
Rows matched: 1 Changed: 1 Warnings: 0

If the client B The transaction has not been committed , client A An error will be reported after the timeout

# client A
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
transaction

Lock wait timeout defaults to 50 second , It can be changed

mysql> SELECT @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
| 50 |
+----------------------------+
1 row in set (0.00 sec)