mysql transaction
In MySQL, transaction is actually the smallest and indivisible unit of work. Transactions can ensure the integrity of a business.
Such as bank transfer

   a-> -100
     update user set money = money-100 where name'a';
   b-> +100
      updata user set money=money +100 where name='b';

In the actual program. If only one program is executed successfully and the other is not executed successfully?
Inconsistent data.

Multiple sql statements may be required to complete at the same time or fail at the same time.

How to control transactions in mysql?
mysql enables transactions by default (auto commit).

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

What is the function of default transaction startup?
When we execute an sql statement, the effect will be reflected immediately and cannot be rolled back.

CREATE DATABASE bank;
USE bank;
CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    money INT
);
insert into user values(1,'a',1000);

Transaction rollback: cancels the execution effect of sql statements

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec

No effect!!

Set mysql auto submit to false

mysql>    set autocommit=0;  Turn off auto submit
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)
mysql> insert into user values(2,'b',1000); Insert a new piece of data
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> rollback;    -- Since the data has not been actually committed, rollback can be used             RollBACK 
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;      Test rollback
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec)

Open transaction
How to submit the virtual data to the database? Using COMMIT:

mysql> insert into user values(2,'b',1000);
Query OK, 1 row affected (0.00 sec)
mysql> commit;    Manual submission
Query OK, 0 rows affected (0.00 sec)
mysql> rollback;  Re revocation is irrevocable (persistent)
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)

summary

Auto submit
    To view Auto submit status: SELECT @@AUTOCOMMIT ；
    Set auto submit status: SET AUTOCOMMIT = 0 . 
Manual submission
@@AUTOCOMMIT = 0 When using COMMIT Command commit transaction.
Transaction rollback
@@AUTOCOMMIT = 0 When using ROLLBACK The command rolls back the transaction.

Transfer operation

UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';
mysql> UPDATE user set money = money - 100 WHERE name = 'a';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE user set money = money + 100 WHERE name = 'b';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)

Business provides us with an opportunity to return;
Assuming that the data is not unexpected, you can manually submit the data to the data table: COMMIT.

begin or start transaction;
Can help us start a transaction manually
After the default commit of a transaction is enabled (@ @ AUTOCOMMIT = 1), transaction rollback cannot be used at this time. However, we can also manually start a transaction event so that it can be rolled back:

– START a transaction manually using BEGIN or START TRANSACTION

TRANSACTION;BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';
-- Since the manually started transaction does not enable auto commit,-- At this time, the changed data is still saved in a temporary table. SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+
-- Test rollback ROLLBACK;
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
Still use COMMIT The data is committed, and the rollback of this transaction cannot occur after the commit.
BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+
-- Submit data COMMIT;
-- Test rollback (invalid because the data of the table has been committed) ROLLBACK;

Four characteristics of transaction

A: atomicity: a transaction is the smallest unit and cannot be subdivided. C: consistency: transaction requirements. sql statements in the same transaction must be successful or invalid at the same time;
1: I solation: transaction 1 and transaction 2 are isolated; D: Persistence:
Once a transaction is commit ted, it cannot be returned Once returned, it cannot be submitted again

Transaction on:
Modify default commit set autocommit=0
begin
start transaction; Transaction manual commit
commit transaction rollback
rollback

Transaction isolation:

1.read uncommitted; Read uncommitted
If there are multiple transactions, any transaction can see the uncommitted data of other transactions.
2.read committed; Read submitted
Only committed data of other transactions can be read.
3.repeatable read; Can be read repeatedly
If multiple connections have transactions open, data records cannot be shared between transactions, otherwise only submitted records can be shared.
4.serializable;
All transactions will be executed in a fixed order. After one transaction is executed, the write operation of the next transaction will continue. Serial

To view the default isolation level of the current database:

-- MySQL 8.x, GLOBAL Indicates the system level and does not indicate the session level.
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
SELECT @@TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                | -- MySQL The default isolation level, which can be read repeatedly.+--------------------------------+
-- MySQL 5.xSELECT @@GLOBAL.TX_ISOLATION;
SELECT @@TX_ISOLATION;

Modify isolation level:

-- Set the system isolation level, LEVEL The following indicates the isolation level to be set (READ UNCOMMITTED). 
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Query the system isolation level and find that it has been modified.
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+

Dirty reading

test READ UNCOMMITTED ( Read uncommitted ) Isolation of:
INSERT INTO user VALUES (3, 'Xiao Ming', 1000);
INSERT INTO user VALUES (4, 'Taobao store', 1000);
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | Xiao Ming      |  1000 |
|  4 | Taobao store    |  1000 |
+----+-----------+-------+
-- Open a transaction operation data-- Suppose Xiao Ming bought a pair of 800 yuan shoes at Taobao store: START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = 'Xiao Ming';
UPDATE user SET money = money + 800 WHERE name = 'Taobao store';
-- Then Taobao shop inquired the results on the other side and found that the money had arrived. SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | Xiao Ming      |   200 |
|  4 | Taobao store    |  1800 |
+----+-----------+-------+

Since Xiaoming's transfer is operated on a newly opened transaction, and the result of the operation can be seen by other transactions (Taobao store of the other party), the query result of Taobao store is correct and Taobao store confirms the receipt. But at this time, what happens if Xiao Ming executes the ROLLBACK command on the transaction it is in?

-- Xiao Ming's affairs
ROLLBACK;
-- No matter who the other party is, if you query the result again, you will find:
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | Xiao Ming      |  1000 |
|  4 | Taobao store    |  1000 |
+----+-----------+-------+

This is called dirty reading. One transaction reads uncommitted data from another transaction. This is not allowed in actual development.

Read committed
Set the isolation level to READ COMMITTED:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-COMMITTED                 |
+--------------------------------+

In this way, when new transactions are connected, they can only query the committed transaction data. However, for the current transaction, they still see uncommitted data, such as:

-- Operating data transaction (current transaction) START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = 'Xiao Ming';
UPDATE user SET money = money + 800 WHERE name = 'Taobao store';
-- Although the isolation level is set to READ COMMITTED，But in the current transaction,-- It still sees temporary changes in the data table, not the data actually submitted. SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | Xiao Ming      |   200 |
|  4 | Taobao store    |  1800 |
+----+-----------+-------+


-- Suppose a new transaction is opened remotely and connected to the database.
$ mysql -u root -p12345612
-- At this time, the data queried by the remote connection can only be submitted SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | Xiao Ming      |  1000 |
|  4 | Taobao store    |  1000 |
+----+-----------+-------+


However, there is still a problem, that is, when a transaction is operating data, other transactions interfere with the data of the transaction. For example:
-- When Xiao Zhang inquired the data, he found that: SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | Xiao Ming      |   200 |
|  4 | Taobao store    |  1800 |
+----+-----------+-------+
-- In Xiao Zhang's table money Before the average value, Xiao Wang did an operation: START TRANSACTION;
INSERT INTO user VALUES (5, 'c', 100);
COMMIT;
-- The real data of the table is: SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | Xiao Ming      |  1000 |
|  4 | Taobao store    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+
-- At this time, when Xiao Zhang calculates the average value again, the calculation will be inconsistent: SELECT AVG(money) FROM user;
+------------+
| AVG(money) |
+------------+
|  820.0000  |
+------------+
money The average becomes less  

although READ COMMITTED Let's only read the committed data of other transactions, but there will still be a problem, that is, inconsistencies may occur when reading the data of the same table. This is called non repeatable reading ( READ COMMITTED ) . 

Unreal reading

Set isolation level to REPEATABLE READ ( Can be read repeatedly ) :
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+

test REPEATABLE READ ，Suppose you execute on two different connections START TRANSACTION :

-- Xiao Zhang - Chengdu START TRANSACTION;
INSERT INTO user VALUES (6, 'd', 1000);
-- Xiao Wang - Beijing START TRANSACTION;
-- Xiao Zhang - Chengdu COMMIT;
After the pre transaction is started, it cannot be queried before it is submitted. It can be queried after submission. However, if other transactions are started before committing, the connection with the current operation transaction will not be queried on this transaction line. It is equivalent to opening up a separate thread.
Whether Xiao Zhang has performed it or not COMMIT ，On Xiao Wang's side, you will not query Xiao Zhang's transaction records, but only the records of your own transactions:
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | Xiao Ming      |  1000 |
|  4 | Taobao store    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+
This is because Xiao Wang started a new business before that ( START TRANSACTION ) ，Then, on this new transaction line, it has no connection with other transactions, that is, if other transactions are operating data at this time, it does not know.
However, the fact is that Xiao Zhang has inserted a piece of data in the real data table. But Xiao Wang didn't know at this time. He also inserted the same data. What would happen?
INSERT INTO user VALUES (6, 'd', 1000);
-- ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'
An error is reported. The operator is informed that a field with primary key 6 already exists. This phenomenon is also called Unreal reading. The data submitted by one transaction cannot be read by other transactions

Serialization
As the name suggests, all transaction writes are serialized. what do you mean? Change the isolation level to serial:

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+

Take Xiao Zhang and Xiao Wang as examples:
-- Xiao Zhang - Chengdu START TRANSACTION;
-- Xiao Wang - Beijing START TRANSACTION;
-- Before starting a transaction, query the table to prepare operation data. SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | Xiao Ming      |  1000 |
|  4 | Taobao store    |  1000 |
|  5 | c         |   100 |
|  6 | d         |  1000 |
+----+-----------+-------+
-- It was found that there was no Wang Xiaohua No. 7, so a piece of data was inserted: INSERT INTO user VALUES (7, 'Wang Xiaohua', 1000);

What happens at this point? Since the current isolation level is SERIALIZABLE, serialization means that if all transactions are placed in a serial queue, all transactions will be executed in a fixed order. After one transaction is executed, the write operation of the next transaction will continue (which means that only one transaction can be executed in the queue at the same time).
According to this explanation, when Xiao Wang inserts data, he will be in a waiting state until Xiao Zhang executes a COMMIT to end his transaction, or a waiting timeout occurs.

summary
The higher the isolation level, the worse the performance
The default isolation level of mysql is repeatable read