MySQL/MariaDB数据库的事务和隔离级别

　　　 　MySQL/MariaDB数据库的事务和隔离级别

　　　　　　　　　　　　　　　　　　　　　　　　　 　　　作者：尹正杰 

版权声明：原创作品，谢绝转载！否则将追究法律责任。

 

 

一.事务概述

1>.事务Transactions

　　一组原子性的SQL语句，或一个独立工作单元。

2>.事务日志

　　由于直接对源表进行修改当服务器突然掉电可能会导致源表数据被损坏,因此可以将修改先记录事务信息，后根据事务日志信息再对源表实现undo,redo等故障恢复功能

　　redo:
　　　　将事务日志中已经commit的事务进行复现操作，即对源表进行修改。

　　undo:
　　　　将事务日志中未commit的事务进行rollback(回滚，撤销)操作，这些操作将不会对源表进行修改。

3>.ACID特性

　　A：
　　　　atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚。

　　C：
　　　　consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态。

　　I：
　　　　Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发。

　　D：
　　　　durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中。

4>.Transaction生命周期

 

二.事务隔离级别概述

1>.事务隔离级别

从上至下更加严格:
　　READ UNCOMMITTED 
　　　　可读取到未提交数据，产生脏读
　　READ COMMITTED 
　　　　可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致。此为ORACLE数据库默认级别。
　　REPEATABLE READ 
　　　　可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
　　SERIALIZABILE 
　　　　可串行化，未提交的读事务阻塞修改事务，或者未提交的修改事务阻塞读事务。导致并发性能差

2>.MVCC

　　多版本并发控制，和事务级别相关。

3>.指定事务隔离级别

MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'tx_isolation';　　　　　　#查看默认的事务隔离级别
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
row in set (0.00 sec)

MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'tx_isolation';　　　　　　#查看默认的事务隔离级别
服务器变量tx_isolation指定，默认为REPEATABLE-READ，可在GLOBAL和SESSION级进行设置
　　SET tx_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE'

服务器选项中指定:(注意，服务器选项和上面的变量名并不同名,详情可参考官官网连接)
vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE

博主推荐阅读:
　　https://mariadb.com/kb/en/library/server-system-variables/#tx_isolation
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SET tx_isolation = 'READ-UNCOMMITTED';　　　　#修改隔离级别为读未提交，可能产生脏读。
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| tx_isolation  | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SET tx_isolation = 'READ-UNCOMMITTED';　　　　#修改隔离级别为读未提交，可能产生脏读。(临时修改)
[root@node105.yinzhengjie.org.cn ~]# vim /mysql/3306/etc/my.cnf
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf　　　　#根据官网的帮助,对实例默认事务隔离级别修改为SERIALIZABLE
[mysqld]
character-set-server    = utf8mb4
default_storage_engine  = InnoDB
transaction-isolation    = SERIALIZABLE
autocommit        = 1
skip_name_resolve    = 1
userstat        = ON
port            = 3306
datadir            = /mysql/3306/data
socket            = /mysql/3306/socket/mysql.sock


[mysqld_safe]
log-error        = /mysql/3306/log/mariadb.log
pid-file        = /mysql/3306/pid/mariadb.pid

[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# /mysql/3306/mysqld restart
Restarting MySQL...
Stoping MySQL...
Starting MySQL...
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# ss -ntl
State       Recv-Q Send-Q               Local Address:Port                              Peer Address:Port              
LISTEN      0      128                              *:22                                           *:*                  
LISTEN      0      80                              :::3306                                        :::*                  
LISTEN      0      128                             :::22                                          :::*                  
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.19-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> use yinzhengjie
Database changed
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| tx_isolation  | SERIALIZABLE |
+---------------+--------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]>
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf　　　#对实例默认事务隔离级别修改为SERIALIZABLE

 

三.事务相关操作

1>.自动提交

set autocommit={1|0} 
　　默认为1，为0时设为非自动提交

建议：
　　显式请求和提交事务，而不要使用“自动提交”功能
MariaDB [yinzhengjie]> SELECT @@AUTOCOMMIT;　　　　#默认开启了自动提交功能，建议显式请求和提交事务，而不要使用自动提交功能。
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT @@AUTOCOMMIT;　　#默认开启了自动提交功能，建议显式请求和提交事务，而不要使用自动提交功能。
MariaDB [yinzhengjie]> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SET AUTOCOMMIT = 0;　　　　　　#为0时设为非自动提交，需要手动显式提交，这种修改只是临时设置，推荐修改配置文件永久配置。
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SET AUTOCOMMIT = 0;　　　　　　#为0时设为非自动提交，需要手动显式提交，这种修改只是临时设置，推荐修改配置文件永久配置。
MariaDB [yinzhengjie]> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> QUIT
Bye
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# /mysql/3306/mysqld stop
Stoping MySQL...
[root@node105.yinzhengjie.org.cn ~]# ss -ntl
State       Recv-Q Send-Q                           Local Address:Port                                          Peer Address:Port              
LISTEN      0      128                                          *:22                                                       *:*                  
LISTEN      0      128                                         :::22                                                      :::*                  
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# vim /mysql/3306/etc/my.cnf
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf　　　　　　#修改配置文件关闭自动提交功能。
[mysqld]
character-set-server    = utf8mb4
default_storage_engine  = InnoDB
autocommit        = 0
skip_name_resolve    = 1
userstat        = ON
port            = 3306
datadir            = /mysql/3306/data
socket            = /mysql/3306/socket/mysql.sock


[mysqld_safe]
log-error        = /mysql/3306/log/mariadb.log
pid-file        = /mysql/3306/pid/mariadb.pid

[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# /mysql/3306/mysqld start
Starting MySQL...
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# ss -ntl
State       Recv-Q Send-Q                           Local Address:Port                                          Peer Address:Port              
LISTEN      0      128                                          *:22                                                       *:*                  
LISTEN      0      80                                          :::3306                                                    :::*                  
LISTEN      0      128                                         :::22                                                      :::*                  
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.19-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> use yinzhengjie
Database changed
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]>
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf　　　　　　#修改配置文件关闭自动提交功能。
MariaDB [yinzhengjie]> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> CREATE TABLE students(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30) NOT NULL,sex ENUM('boy','girl') DEFAULT 'boy',age TINYINT UNSIGNED,mobile CHAR(11),address VARCHAR(50));
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> INSERT INTO students (name,age,mobile,address) VALUES ('Jason Yin',18,10000,'beijing'),('Jay','40',10086,'Taiwan');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name      | sex  | age  | mobile | address |
+----+-----------+------+------+--------+---------+
|  1 | Jason Yin | boy  |   18 | 10000  | beijing |
|  2 | Jay       | boy  |   40 | 10086  | Taiwan  |
+----+-----------+------+------+--------+---------+
2 rows in set (0.00 sec)

MariaDB [yinzhengjie]>
默认事务隔离级别且关闭自动提交的实例一终端执行DML语句
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.2.19-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SELECT * FROM yinzhengjie.students;　　　　　　#在默认的事务隔离级别无法看到未提交的数据。
Empty set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]>
在默认的事务隔离级别无法看到未提交的数据。

2>.启动事务

以下三条命令均可以启动事务:
　　BEGIN
　　BEGIN WORK
　　START TRANSACTION
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name      | sex  | age  | mobile | address |
+----+-----------+------+------+--------+---------+
|  1 | Jason Yin | boy  |   18 | 10000  | beijing |
|  2 | Jay       | boy  |   40 | 10086  | Taiwan  |
+----+-----------+------+------+--------+---------+
2 rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> BEGIN;　　　　　　　　　　　　　　#终端1开启事务进行DML语句操作但不COMMIT,此时发现修改在当前会话生效。
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> INSERT students (age,sex,name,mobile,address) VALUES (28,'girl','Gloria Tang Tsz-Kei',null,'Hong Kong');
Query OK, 1 row affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   18 | 10000  | beijing   |
|  2 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
+----+---------------------+------+------+--------+-----------+
3 rows in set (0.00 sec)

MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> BEGIN;　　　　　#终端1开启事务进行DML语句操作但不COMMIT,此时发现修改在当前会话生效。
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.19-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use yinzhengjie
Database changed
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name      | sex  | age  | mobile | address |
+----+-----------+------+------+--------+---------+
|  1 | Jason Yin | boy  |   18 | 10000  | beijing |
|  2 | Jay       | boy  |   40 | 10086  | Taiwan  |
+----+-----------+------+------+--------+---------+
2 rows in set (0.00 sec)

MariaDB [yinzhengjie]>
终端2默认事务隔离级别看不到终端1未提交的DML语句指令执行的结果。
MariaDB [yinzhengjie]> BEGIN;
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> INSERT students (age,sex,name,mobile,address) VALUES (28,'girl','Gloria Tang Tsz-Kei',null,'Hong Kong');
Query OK, 1 row affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   18 | 10000  | beijing   |
|  2 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
+----+---------------------+------+------+--------+-----------+
3 rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> DELETE FROM students WHERE id = 2;
Query OK, 1 row affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> UPDATE students SET age = 27 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
+----+---------------------+------+------+--------+-----------+
2 rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> COMMIT;　　　　　　#终端1提交事务,所有在改事务中执行的DML语句均会生效.
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> COMMIT;　　　　　　#终端1提交事务,所有在改事务中执行的DML语句均会生效.
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.2.19-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SELECT * FROM yinzhengjie.students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
+----+---------------------+------+------+--------+-----------+
2 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]>
终端2默认事务隔离级别可以看到已经提交的DML语句指令执行的结果。

3>.结束事务

COMMIT：
　　提交

ROLLBACK: 
　　回滚

注意：
　　只有事务型存储引擎中的DML语句方能支持此类操作。
MariaDB [yinzhengjie]> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
Empty set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> INSERT INTO students (name,age,mobile,address) VALUES ('Jason Yin',18,10000,'beijing'),('Jay','40',10086,'Taiwan');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name      | sex  | age  | mobile | address |
+----+-----------+------+------+--------+---------+
|  1 | Jason Yin | boy  |   18 | 10000  | beijing |
|  2 | Jay       | boy  |   40 | 10086  | Taiwan  |
+----+-----------+------+------+--------+---------+
2 rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> ROLLBACK;　　　　　　　　#回滚，从当前语句到上一条COMMIT之后DML语句均被撤销执行。
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
Empty set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> INSERT students (age,sex,name,mobile,address) VALUES (28,'girl','Gloria Tang Tsz-Kei',null,'Hong Kong');
Query OK, 1 row affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
+----+---------------------+------+------+--------+-----------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> COMMIT;　　　　　　　　　　#提交，即从当前语句到上一条COMMIT之后DML语句全部执行。
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]>
在默认事务隔离级别且关闭自动提交功能的MySQL实例一中端执行DML语句操作
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.2.19-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> use yinzhengjie
Database changed
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
+----+---------------------+------+------+--------+-----------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]>
在默认的事务隔离级别只能看到已经提交的数据。

4>.事务支持保存点

SAVEPOINT identifier
　　创建保存点
ROLLBACK [WORK] TO [SAVEPOINT] identifier
　　回滚到指定的保存点
RELEASE SAVEPOINT identifier　　　　　　　　　　
　　释放保存点
MariaDB [yinzhengjie]> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> SELECT *  FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
+----+---------------------+------+------+--------+-----------+
2 rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SAVEPOINT point_yin;　　　　　　#此处我们创建一个名称为point_yin的保存点
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> INSERT INTO students (name,age,mobile,address) VALUES ('Jay','40',10086,'Taiwan');
Query OK, 1 row affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT *  FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  4 | yinzhengjie         | boy  |   27 | NULL   | shanxi    |
|  5 | Jay                 | boy  |   40 | 10086  | Taiwan    |
+----+---------------------+------+------+--------+-----------+
4 rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SAVEPOINT point_jay;　　　　　　　　#在第一个保存点point_yin之后执行了一些DML指令后，创建第二个保存点,名称为point_jay
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> INSERT INTO students (name,age,mobile,address) VALUES ('JangNaRa','38',null,'Seoul');
Query OK, 1 row affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT *  FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  4 | yinzhengjie         | boy  |   27 | NULL   | shanxi    |
|  5 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  6 | JangNaRa            | boy  |   38 | NULL   | Seoul     |
+----+---------------------+------+------+--------+-----------+
5 rows in set (0.00 sec)

MariaDB [yinzhengjie]> SAVEPOINT point_zhang;　　　　　　　　#同理，这是我们创建的第三个保存点
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> DELETE FROM students WHERE id >= 3;
Query OK, 4 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT *  FROM students;
+----+-----------+------+------+--------+---------+
| id | name      | sex  | age  | mobile | address |
+----+-----------+------+------+--------+---------+
|  1 | Jason Yin | boy  |   27 | 10000  | beijing |
+----+-----------+------+------+--------+---------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> ROLLBACK WORK TO SAVEPOINT point_zhang;　　　　#由于我们上面误删除了数据，因此可以回滚到指定的保存点
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT *  FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  4 | yinzhengjie         | boy  |   27 | NULL   | shanxi    |
|  5 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  6 | JangNaRa            | boy  |   38 | NULL   | Seoul     |
+----+---------------------+------+------+--------+-----------+
5 rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> RELEASE SAVEPOINT point_jay;　　　　　　　　　　　　#此时我们释放第二个保存点
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> ROLLBACK TO point_jay;　　　　　　#由于第二个保存点被释放了,因此报错改保存点不存在。
ERROR 1305 (42000): SAVEPOINT point_jay does not exist
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]>
案例展示

 

四.事务隔离级别实战案例

1>.READ-UNCOMMITTED（不推荐使用，因为它能产生脏读，不安全）

[root@node105.yinzhengjie.org.cn ~]# vim /mysql/3306/etc/my.cnf
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf　　　　　　#修改默认的事务隔离级别为"READ-UNCOMMITTED"
[mysqld]
character-set-server    = utf8mb4
default_storage_engine  = InnoDB
transaction-isolation    = READ-UNCOMMITTED
autocommit        = 1
skip_name_resolve    = 1
userstat        = ON
port            = 3306
datadir            = /mysql/3306/data
socket            = /mysql/3306/socket/mysql.sock


[mysqld_safe]
log-error        = /mysql/3306/log/mariadb.log
pid-file        = /mysql/3306/pid/mariadb.pid

[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# /mysql/3306/mysqld restart
Restarting MySQL...
Stoping MySQL...
Starting MySQL...
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.19-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| tx_isolation  | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]>
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf　　   #修改默认的事务隔离级别为"READ-UNCOMMITTED"
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| tx_isolation  | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan    |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> BEGIN;　　　　　　　　　　　　　　#开启一个事务
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> INSERT INTO students SET name='yinzhengjie',age=27,address='shanxi';　　　　#执行一条DML语句
Query OK, 1 row affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;　　　　#虽然当前事务并未提交，但是咱们可以看到修改后的内容
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  8 | yinzhengjie         | boy  |   27 | NULL   | shanxi    |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec)

MariaDB [yinzhengjie]>
终端1事务执行DML语句并未提交
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| tx_isolation  | READ-UNCOMMITTED |
+---------------+------------------+
row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan    |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> BEGIN WORK;
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  8 | yinzhengjie         | boy  |   27 | NULL   | shanxi    |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]>
终端2事务竟可以看到终端1未提交的内容

2>.READ-COMMITTED（Oracle数据库默认使用就是类似该级别）

[root@node105.yinzhengjie.org.cn ~]# vim /mysql/3306/etc/my.cnf
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf          　　#修改默认的事务隔离级别为READ-COMMITTED
[mysqld]
character-set-server    = utf8mb4
default_storage_engine  = InnoDB
transaction-isolation    = READ-COMMITTED
autocommit        = 1
skip_name_resolve    = 1
userstat        = ON
port            = 3306
datadir            = /mysql/3306/data
socket            = /mysql/3306/socket/mysql.sock


[mysqld_safe]
log-error        = /mysql/3306/log/mariadb.log
pid-file        = /mysql/3306/pid/mariadb.pid

[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# /mysql/3306/mysqld restart
Restarting MySQL...
Stoping MySQL...
Starting MySQL...
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# ss -ntl
State       Recv-Q Send-Q               Local Address:Port                              Peer Address:Port              
LISTEN      0      128                              *:22                                           *:*                  
LISTEN      0      80                              :::3306                                        :::*                  
LISTEN      0      128                             :::22                                          :::*                  
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.19-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]>
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf 　　　　　　#修改默认的事务隔离级别为READ-COMMITTED
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan    |
+----+---------------------+------+------+--------+-----------+
3 rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> BEGIN ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> INSERT INTO students SET name='yinzhengjie',age=27,address='shanxi';
Query OK, 1 row affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  8 | yinzhengjie         | boy  |   27 | NULL   | shanxi    |
+----+---------------------+------+------+--------+-----------+
4 rows in set (0.00 sec)

MariaDB [yinzhengjie]>
终端1事务执行DML语句并未提交
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan    |
+----+---------------------+------+------+--------+-----------+
3 rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> BEGIN WORK;
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan    |
+----+---------------------+------+------+--------+-----------+
3 rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]>
终端2事务看不到终端1未提交的内容
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan    |
+----+---------------------+------+------+--------+-----------+
3 rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> BEGIN;
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> INSERT INTO students SET name='yinzhengjie',age=27,address='shanxi';
Query OK, 1 row affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  9 | yinzhengjie         | boy  |   27 | NULL   | shanxi    |
+----+---------------------+------+------+--------+-----------+
4 rows in set (0.00 sec)

MariaDB [yinzhengjie]> COMMIT;
Query OK, 0 rows affected (0.01 sec)

MariaDB [yinzhengjie]>
终端1事务执行DML语句并提交
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan    |
+----+---------------------+------+------+--------+-----------+
3 rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> BEGIN WORK;
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan    |
+----+---------------------+------+------+--------+-----------+
3 rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  9 | yinzhengjie         | boy  |   27 | NULL   | shanxi    |
+----+---------------------+------+------+--------+-----------+
4 rows in set (0.00 sec)

MariaDB [yinzhengjie]>
终端2事务可以看到终端1已提交的内容

3>.REPEATABLE-READ（MySQL默认的事务隔离级别，备份数据默认使用该事务级别）

[root@node105.yinzhengjie.org.cn ~]# vim /mysql/3306/etc/my.cnf
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf       　　#修改默认的事务隔离级别为REPEATABLE-READ
[mysqld]
character-set-server    = utf8mb4
default_storage_engine  = InnoDB
transaction-isolation    = REPEATABLE-READ
autocommit        = 1
skip_name_resolve    = 1
userstat        = ON
port            = 3306
datadir            = /mysql/3306/data
socket            = /mysql/3306/socket/mysql.sock


[mysqld_safe]
log-error        = /mysql/3306/log/mariadb.log
pid-file        = /mysql/3306/pid/mariadb.pid

[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# /mysql/3306/mysqld restart
Restarting MySQL...
Stoping MySQL...
Starting MySQL...
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# ss -ntl
State       Recv-Q Send-Q                           Local Address:Port                                          Peer Address:Port              
LISTEN      0      128                                          *:22                                                       *:*                  
LISTEN      0      80                                          :::3306                                                    :::*                  
LISTEN      0      128                                         :::22                                                      :::*                  
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.19-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]>
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf 　　#修改默认的事务隔离级别为REPEATABLE-READ
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  9 | yinzhengjie         | boy  |   27 | NULL   | shanxi    |
+----+---------------------+------+------+--------+-----------+
4 rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> BEGIN;
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> INSERT students (age,sex,name,mobile,address) VALUES (38,'girl','JangNaRa',null,'Republic of Korea');
Query OK, 1 row affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> COMMIT;
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-------------------+
| id | name                | sex  | age  | mobile | address           |
+----+---------------------+------+------+--------+-------------------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing           |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong         |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan            |
|  9 | yinzhengjie         | boy  |   27 | NULL   | shanxi            |
| 10 | JangNaRa            | girl |   38 | NULL   | Republic of Korea |
+----+---------------------+------+------+--------+-------------------+
5 rows in set (0.00 sec)

MariaDB [yinzhengjie]>
终端1事务执行DML语句并提交
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  9 | yinzhengjie         | boy  |   27 | NULL   | shanxi    |
+----+---------------------+------+------+--------+-----------+
4 rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> BEGIN WORK;
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  9 | yinzhengjie         | boy  |   27 | NULL   | shanxi    |
+----+---------------------+------+------+--------+-----------+
4 rows in set (0.00 sec)

MariaDB [yinzhengjie]>
终端2事务不能看见已提交的内容(因为终端2的事务要比和终端1事务先开启)

4>.SERIALIZABLE（这种事务隔离级别很少用，因为并发性能差，除非数据要求太重要） 

[root@node105.yinzhengjie.org.cn ~]# vim /mysql/3306/etc/my.cnf
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf    　　　　#修改默认的事务隔离级别为SERIALIZABLE 
[mysqld]
character-set-server    = utf8mb4
default_storage_engine  = InnoDB
transaction-isolation    = SERIALIZABLE
autocommit        = 1
skip_name_resolve    = 1
userstat        = ON
port            = 3306
datadir            = /mysql/3306/data
socket            = /mysql/3306/socket/mysql.sock


[mysqld_safe]
log-error        = /mysql/3306/log/mariadb.log
pid-file        = /mysql/3306/pid/mariadb.pid

[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# /mysql/3306/mysqld restart
Restarting MySQL...
Stoping MySQL...
Starting MySQL...
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]# ss -ntl
State       Recv-Q Send-Q                           Local Address:Port                                          Peer Address:Port              
LISTEN      0      128                                          *:22                                                       *:*                  
LISTEN      0      80                                          :::3306                                                    :::*                  
LISTEN      0      128                                         :::22                                                      :::*                  
[root@node105.yinzhengjie.org.cn ~]# 
[root@node105.yinzhengjie.org.cn ~]#mmysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.19-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 
MariaDB [(none)]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| tx_isolation  | SERIALIZABLE |
+---------------+--------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]>
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf 　　　　#修改默认的事务隔离级别为SERIALIZABLE
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| tx_isolation  | SERIALIZABLE |
+---------------+--------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> BEGIN;
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-------------------+
| id | name                | sex  | age  | mobile | address           |
+----+---------------------+------+------+--------+-------------------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing           |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong         |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan            |
|  9 | yinzhengjie         | boy  |   27 | NULL   | shanxi            |
| 10 | JangNaRa            | girl |   38 | NULL   | Republic of Korea |
+----+---------------------+------+------+--------+-------------------+
5 rows in set (0.00 sec)

MariaDB [yinzhengjie]>
终端1事务执行查看语句但并不提交
MariaDB [yinzhengjie]> BEGIN WORK;
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-------------------+
| id | name                | sex  | age  | mobile | address           |
+----+---------------------+------+------+--------+-------------------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing           |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong         |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan            |
|  9 | yinzhengjie         | boy  |   27 | NULL   | shanxi            |
| 10 | JangNaRa            | girl |   38 | NULL   | Republic of Korea |
+----+---------------------+------+------+--------+-------------------+
rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> DELETE FROM students WHERE id = 10;           #由于终端1有查询事务且未提交,因此未提交的读事务会阻塞修改事务
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [yinzhengjie]>
终端2事务也可以执行查看语句，但无法执行删除指令,即未提交的读事务会阻塞修改事务
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| tx_isolation  | SERIALIZABLE |
+---------------+--------------+
1 row in set (0.01 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-------------------+
| id | name                | sex  | age  | mobile | address           |
+----+---------------------+------+------+--------+-------------------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing           |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong         |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan            |
|  9 | yinzhengjie         | boy  |   27 | NULL   | shanxi            |
| 10 | JangNaRa            | girl |   38 | NULL   | Republic of Korea |
+----+---------------------+------+------+--------+-------------------+
5 rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> BEGIN;
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> DELETE FROM students WHERE id = 10;
Query OK, 1 row affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name                | sex  | age  | mobile | address   |
+----+---------------------+------+------+--------+-----------+
|  1 | Jason Yin           | boy  |   27 | 10000  | beijing   |
|  3 | Gloria Tang Tsz-Kei | girl |   28 | NULL   | Hong Kong |
|  7 | Jay                 | boy  |   40 | 10086  | Taiwan    |
|  9 | yinzhengjie         | boy  |   27 | NULL   | shanxi    |
+----+---------------------+------+------+--------+-----------+
4 rows in set (0.00 sec)

MariaDB [yinzhengjie]>
终端1事务执行删除语句并未提交
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| tx_isolation  | SERIALIZABLE |
+---------------+--------------+
1 row in set (0.01 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> BEGIN WORK;
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;    　　　　　　#未提交的修改事务阻塞读事务
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [yinzhengjie]>
若终端1存在修改事务且未提交,终端2事务无法执行查询语句，即未提交的修改事务阻塞读事务

 

五.死锁问题

1>.什么是死锁

　　两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态

2>.死锁案例模拟

MariaDB [yinzhengjie]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)

MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students;
MariaDB [yinzhengjie]> SELECT * FROM teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM teachers;
MariaDB [yinzhengjie]> BEGIN;
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> UPDATE students SET classid = 1 WHERE stuid = 25;     #于此同时可在终端2执行该指令，修改一下classid列的值即可
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |       1 |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)

MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> UPDATE teachers SET age = 25 WHERE tid = 4;　　　　　　#同样,在终端2也执行该语句，观察情况
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [yinzhengjie]> SELECT * FROM teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  25 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

MariaDB [yinzhengjie]>
终端1事务执行UPDATE命令对某一行数据进行修改的同时，Innodb存储引擎默认会加行级锁(我们可以对两张表进程测试)
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> BEGIN WORK;
Query OK, 0 rows affected (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> UPDATE teachers SET age = 30 WHERE tid = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> SELECT * FROM teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  30 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

MariaDB [yinzhengjie]> 
MariaDB [yinzhengjie]> UPDATE students SET classid = 1 WHERE stuid = 25;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
MariaDB [yinzhengjie]>
终端2事务执行和终端1类似的SQL语句对同一行数据修改，这样就会出现死锁(Deadlock)的问题。MySQL会自动选择损失最小的结果(即让某个事务执行SQL失败)

3>.死锁的解决方案

　　让多个事务按照顺序来修改表，不要让多个事务交叉来修改表。当然，运维人员对数据库死锁问题不用太大关心，直接抛给开发人员就好。

 

posted @ 2019-11-02 06:46  尹正杰  阅读(767)  评论(0)  编辑  收藏  举报