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]>
服务器变量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]>
[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]>
三.事务相关操作
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; +--------------+ | @@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]> 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]>
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]>
[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]>
[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]>
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]>
[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)]>
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]>
[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)]>
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]>
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>.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)]>
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]>
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]>
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]>
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]>
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)]>
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]>
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]>
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)]>
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]>
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]>
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]>
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>.死锁案例模拟
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 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]> 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]>
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]>
3>.死锁的解决方案
让多个事务按照顺序来修改表，不要让多个事务交叉来修改表。当然，运维人员对数据库死锁问题不用太大关心，直接抛给开发人员就好。