Mysql transaction refers to the execution of a batch of operations on the database. In the same transaction, these operations will either all succeed or fail, and there will be no partial success; the transaction is an atomic operation and is A minimal execution unit can consist of one or more SQL statements.
The operating environment of this tutorial: Windows 10 system, MySQL version 5.7, Dell G3 computer.
Detailed explanation of transactions
What is a transaction?
A transaction in a database refers to the execution of a batch of operations on the database. In the same transaction, these operations will either all succeed or fail, and there will be no partial success.
A transaction is an atomic operation. Is a minimum execution unit. Can be composed of one or more SQL statements
In the same transaction, when all SQL statements are successfully executed, the entire transaction is successful. If one SQL statement fails to execute, the entire transaction fails to execute.
For example:
For example, user A transfers 100 to user B. The process is as follows:
Deduct 100 from account A
Add 100 to account B
If supported by transactions, there are only two results in the above:
The operation is successful: account A decreases by 100; account B increases 100
Operation failed: neither account A nor B has changed
If there is no transaction support, an error may occur: Account A is reduced by 100, and the system hangs at this time , causing the B account not to add 100, and the A account to lose 100 out of thin air.
Several characteristics of transactions (ACID) - Key points
Atomicity(Atomicity)
The entire process of the transaction is as follows Atomic operations are the same. In the end, either all succeed or all fail. This atomicity is seen from the final result. From the final result, this process is indivisible.
Consistency(Consistency)
A transaction must change the database from one consistency state to another consistency state.
First review the definition of consistency. The so-called consistency means that the data is in a meaningful state, which is semantically rather thangrammatically. The most common example is money transfers. For example, if a sum of money is transferred from account A to account B, if the money in account A decreases but the money in account B does not increase, then we think that the data is in an inconsistent state at this time.
From the understanding of this paragraph, the so-called consistency means that from the actual business logic, the final result is correct and fully consistent with the programmer's expected result.
Isolation
The execution of a transaction cannot be interfered with by other transactions. That is, the operations and data used within a transaction are isolated from other concurrent transactions, and transactions executed concurrently cannot interfere with each other.
Once a transaction Submit, his changes to the data in the database should be permanent. When the transaction is committed, the data will be persisted to the hard disk, and the modification will be permanent.
Transactions in MySQL are implicit transactions by default. When insert, update, and delete operations are performed, the database automatically starts the transaction, commits, or rolls back. affairs. Whether to enable implicit transactions is controlled by the variable autocommit. So transactions are divided into
Transactions are automatically opened, submitted or rolled back, such as insert, update, delete statements. The opening, submission or rollback of transactions are automatically started, submitted or rolled back by mysql. controlling. Check whether the variable autocommit is turned on for automatic submission
mysql> show variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | ON |+---------------+-------+1 row in set, 1 warning (0.00 sec)
Copy after login
If autocommit is ON, it means that automatic submission is turned on.
Transactions need to be manually opened, submitted or rolled back, and are controlled by the developer themselves. 2 ways to manually control transactions:
mysql> show variables like 'transaction_isolation';+-----------------------+----------------+| Variable_name | Value |+-----------------------+----------------+| transaction_isolation | READ-COMMITTED |+-----------------------+----------------+1 row in set, 1 warning (0.00 sec)
C:\Windows\system32>net stop mysql
mysql 服务正在停止..mysql 服务已成功停止。
C:\Windows\system32>net start mysql
mysql 服务正在启动 .mysql 服务已经启动成功。
Copy after login
Copy after login
Copy after login
Copy after login
Copy after login
Copy after login
查看隔离级别：
mysql> show variables like 'transaction_isolation';+-----------------------+----------------+| Variable_name | Value |+-----------------------+----------------+| transaction_isolation | READ-UNCOMMITTED |+-----------------------+----------------+1 row in set, 1 warning (0.00 sec)
Copy after login
先清空test1表数据：
delete from test1;select * from test1;
Copy after login
Copy after login
Copy after login
Copy after login
按时间顺序在2个窗口中执行下面操作：
时间
窗口A
窗口B
T1
start transaction;
T2
select * from test1;
T3
start transaction;
T4
insert into test1 values (1);
T5
select * from test1;
T6
select * from test1;
T7
commit;
T8
commit;
A窗口如下：
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from test1;Empty set (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 |+------+1 row in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)
Copy after login
B窗口如下：
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into test1 values (1);Query OK, 1 row affected (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 |+------+1 row in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)
C:\Windows\system32>net stop mysql
mysql 服务正在停止..mysql 服务已成功停止。
C:\Windows\system32>net start mysql
mysql 服务正在启动 .mysql 服务已经启动成功。
Copy after login
Copy after login
Copy after login
Copy after login
Copy after login
Copy after login
查看隔离级别：
mysql> show variables like 'transaction_isolation';+-----------------------+----------------+| Variable_name | Value |+-----------------------+----------------+| transaction_isolation | READ-COMMITTED |+-----------------------+----------------+1 row in set, 1 warning (0.00 sec)
Copy after login
Copy after login
先清空test1表数据：
delete from test1;select * from test1;
Copy after login
Copy after login
Copy after login
Copy after login
按时间顺序在2个窗口中执行下面操作：
时间
窗口A
窗口B
T1
start transaction;
T2
select * from test1;
T3
start transaction;
T4
insert into test1 values (1);
T5
select * from test1;
T6
select * from test1;
T7
commit;
T8
select * from test1;
T9
commit;
A窗口如下：
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from test1;Empty set (0.00 sec)mysql> select * from test1;Empty set (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 |+------+1 row in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)
Copy after login
B窗口如下：
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into test1 values (1);Query OK, 1 row affected (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 |+------+1 row in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)
C:\Windows\system32>net stop mysql
mysql 服务正在停止..mysql 服务已成功停止。
C:\Windows\system32>net start mysql
mysql 服务正在启动 .mysql 服务已经启动成功。
Copy after login
Copy after login
Copy after login
Copy after login
Copy after login
Copy after login
查看隔离级别：
mysql> show variables like 'transaction_isolation';+-----------------------+----------------+| Variable_name | Value |+-----------------------+----------------+| transaction_isolation | REPEATABLE-READ |+-----------------------+----------------+1 row in set, 1 warning (0.00 sec)
Copy after login
Copy after login
先清空test1表数据：
delete from test1;select * from test1;
Copy after login
Copy after login
Copy after login
Copy after login
按时间顺序在2个窗口中执行下面操作：
时间
窗口A
窗口B
T1
start transaction;
T2
select * from test1;
T3
start transaction;
T4
insert into test1 values (1);
T5
select * from test1;
T6
select * from test1;
T7
commit;
T8
select * from test1;
T9
commit;
T10
select * from test1;
A窗口如下：
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from test1;Empty set (0.00 sec)mysql> select * from test1;Empty set (0.00 sec)mysql> select * from test1;Empty set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 || 1 |+------+2 rows in set (0.00 sec)
Copy after login
B窗口如下：
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into test1 values (1);Query OK, 1 row affected (0.00 sec)mysql> select * from test1;+------+| a |+------+| 1 || 1 |+------+2 rows in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)
C:\Windows\system32>net stop mysql
mysql 服务正在停止..mysql 服务已成功停止。
C:\Windows\system32>net start mysql
mysql 服务正在启动 .mysql 服务已经启动成功。
Copy after login
Copy after login
Copy after login
Copy after login
Copy after login
Copy after login
查看隔离级别：
mysql> show variables like 'transaction_isolation';+-----------------------+----------------+| Variable_name | Value |+-----------------------+----------------+| transaction_isolation | REPEATABLE-READ |+-----------------------+----------------+1 row in set, 1 warning (0.00 sec)
Copy after login
Copy after login
准备数据：
mysql> create table t_user(id int primary key,name varchar(16) unique key);Query OK, 0 rows affected (0.01 sec)mysql> insert into t_user values (1,'路人甲Java'),(2,'路人甲Java');ERROR 1062 (23000): Duplicate entry '路人甲Java' ***\*for\**** key 'name'mysql> select * from t_user;Empty set (0.00 sec)
Copy after login
上面我们创建t_user表，name添加了唯一约束，表示name不能重复，否则报错。
按时间顺序在2个窗口中执行下面操作：
时间
窗口A
窗口B
T1
start transaction;
T2
start transaction;
T3
– 插入路人甲Java insert into t_user values (1,‘路人甲Java’);
T4
select * from t_user;
T5
– 查看路人甲Java是否存在 select * from t_user where name=‘路人甲Java’;
T6
commit;
T7
– 插入路人甲Java insert into t_user values (2,‘路人甲Java’);
T8
– 查看路人甲Java是否存在 select * from t_user where name=‘路人甲Java’;
T9
commit;
A窗口如下：
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select * from t_user where name='路人甲Java';Empty set (0.00 sec)mysql> insert into t_user values (2,'路人甲Java');ERROR 1062 (23000): Duplicate entry '路人甲Java' ***\*for\**** key 'name'mysql> select * from t_user where name='路人甲Java';Empty set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)
Copy after login
B窗口如下：
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into t_user values (1,'路人甲Java');Query OK, 1 row affected (0.00 sec)mysql> select * from t_user;+----+---------------+| id | name |+----+---------------+| 1 | 路人甲Java |+----+---------------+1 row in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)
C:\Windows\system32>net stop mysql
mysql 服务正在停止..mysql 服务已成功停止。
C:\Windows\system32>net start mysql
mysql 服务正在启动 .mysql 服务已经启动成功。
Copy after login
Copy after login
Copy after login
Copy after login
Copy after login
Copy after login
查看隔离级别：
mysql> show variables like 'transaction_isolation';+-----------------------+--------------+| Variable_name | Value |+-----------------------+--------------+| transaction_isolation | SERIALIZABLE |+-----------------------+--------------+1 row in set, 1 warning (0.00 sec)
Copy after login
先清空test1表数据：
delete from test1;select * from test1;
Copy after login
Copy after login
Copy after login
Copy after login
按时间顺序在2个窗口中执行下面操作：
时间
窗口A
窗口B
T1
start transaction;
T2
select * from test1;
T3
start transaction;
T4
insert into test1 values (1);
T5
commit;
T6
commit;
Run the above commands in chronological order and you will find that T4-B will be blocked until T5-A is completed.
The above demonstration is the effect of read-write mutual exclusion. You can write about the effect of write-read and write-write mutual exclusion yourself.
It can be seen that transactions can only be executed serially. There are no problems with dirty reads, non-repeatable reads, and phantom reads in serial situations.
Summary
Read Uncommitted(Read Uncommitted)
Read Uncommitted is the isolation level The lowest transaction level. Under this isolation level, one transaction will read updated but uncommitted data from another transaction. If the other transaction rolls back, the data read by the current transaction will be dirty data. This is called dirty read.
Read Committed
Under the Read Committed isolation level, a transaction may encounter non-repeatable reads (Non Repeatable Read) question. Non-repeatable reading means reading the same data multiple times within a transaction. If another transaction happens to modify this data before the transaction ends, then in the first transaction, the data read twice It may be inconsistent.
Repeatable Read
Under the Repeatable Read isolation level, a transaction may encounter phantom read (Phantom Read) The problem. Phantom reading means that in a transaction, the first time you query a certain record, you find that it does not exist. However, when you try to update this non-existent record, you can actually succeed, and when you read the same record again, it works magically. appeared. Phantom reading is a record that has not been read and is thought not to exist, but in fact it can be updated successfully, and after the update is successful, it will appear when it is read again.
Serializable
Serializable is the strictest isolation level. Under the Serializable isolation level, all transactions are executed in sequence, so dirty reads, non-repeatable reads, and phantom reads will not occur.
Although transactions under the Serializable isolation level have the highest security, because the transactions are executed serially, the efficiency will be greatly reduced and the performance of the application will be drastically reduced. If there is no particularly important situation, the Serializable isolation level is generally not used.
Default isolation level: If no isolation level is specified, the database will use the default isolation level. In MySQL, if you use InnoDB, the default isolation level is Repeatable Read.
About the choice of isolation level
You need to have a good understanding of the phenomena caused by various isolation levels, and then you can make the choice with ease
The higher the isolation level, the lower the concurrency. For example, the highest level SERIALIZABLE will cause things to be executed serially, and concurrent operations will become serial, which will directly reduce system performance.
The specific choice needs to be based on the specific business.
READ-COMMITTED is usually used more often.
Summary
Understand the 4 characteristics of transactions: atomicity, consistency, isolation, and durability
Master Introduction to common commands for transaction operations
set autocommit can set whether to turn on automatic transaction submission
start transaction: turn on the transaction
start transaction read only: turn on the read-only transaction
commit: Commit the transaction
rollback: Roll back the transaction
savepoint: Set the save point
rollback to save point: You can roll back to a save point
Master the 4 isolation levels and understand their characteristics
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
![MySQL query optimization solution [taught by architects from major manufacturers] [Getting Started with MySQL | Tuning | Indexing] Advanced Tutorial](https://img.php.cn/upload/course/000/000/068/6242a7d5be236814.png)
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
