linux进阶MySQL事务小结

MySQL事务小结

MySQL 事务主要用于处理操作量大&＃xff0c;复杂度高的数据。
比如说&＃xff1a;在人员管理系统中&＃xff0c;你删除一个人员&＃xff0c;你即需要删除人员的基本资料&＃xff0c;也要删除和该人员相关的信息&＃xff0c;如信箱&＃xff0c;文章等等&＃xff0c;这样&＃xff0c;这些数据库操作语句就构成一个事务&＃xff01;- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。- 事务处理可以用来维护数据库的完整性&＃xff0c;保证成批的 SQL 语句要么全部执行&＃xff0c;要么全部不执行。- 事务用来管理 insert,update,delete 语句


1.事务日志的配置项

innodb_log_files_in_group
innodb_log_group_home_dir
innodb_log_file_size
innodb_mirrored_log_groups


2.ACID测试

一般来说&＃xff0c;事务是必须满足4个条件&＃xff08;ACID测试&＃xff09;&＃xff1a;原子性&＃xff08;Atomicity&＃xff0c;或称不可分割性&＃xff09;#整个事务中的所有操作要么全部成功执行&＃xff0c;要么全部失败后回滚一致性&＃xff08;Consistency&＃xff09;#数据库总是从一个一致性状态转换为另一个一致性状态隔离性&＃xff08;Isolation&＃xff0c;又称独立性&＃xff09; #一个事务所做出的操作在提交之前&＃xff0c;是不能为其它事务所见&＃xff1b;隔离有多种隔
离级别&＃xff0c;实现并发持久性&＃xff08;Durability&＃xff09;#一旦事务提交&＃xff0c;其所做的修改会永久保存于数据库中


Transaction生命周期

• 原子性&＃xff1a;

一个事务&＃xff08;transaction&＃xff09;中的所有操作&＃xff0c;要么全部完成&＃xff0c;要么全部不完成&＃xff0c;不会结束在中间某个环节。
事务在执行过程中发生错误&＃xff0c;会被回滚&＃xff08;Rollback&＃xff09;到事务开始前的状态&＃xff0c;就像这个事务从来没有执行过一样。


• 一致性&＃xff1a;

在事务开始之前和事务结束以后&＃xff0c;数据库的完整性没有被破坏。
这表示写入的资料必须完全符合所有的预设规则&＃xff0c;这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。


• 隔离性&＃xff1a;

数据库允许多个并发事务同时对其数据进行读写和修改的能力&＃xff0c;隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
事务隔离分为不同级别&＃xff0c;包括读未提交&＃xff08;Read uncommitted&＃xff09;读提交&＃xff08;read committed&＃xff09;可重复读&＃xff08;repeatable read&＃xff09;串行化&＃xff08;Serializable&＃xff09;


• 持久性&＃xff1a;

事务处理结束后&＃xff0c;对数据的修改就是永久的&＃xff0c;即便系统故障也不会丢失。


3.事务控制语句

• BEGIN 或 START TRANSACTION 显式地开启一个事务&＃xff1b;

• COMMIT 也可以使用 COMMIT WORK&＃xff0c;不过二者是等价的。COMMIT 会提交事务&＃xff0c;并使已对数据库进行的所有修改成为永久性的&＃xff1b;

• ROLLBACK 也可以使用 ROLLBACK WORK&＃xff0c;不过二者是等价的。回滚会结束用户的事务&＃xff0c;并撤销正在进行的所有未提交的修改&＃xff1b;

• SAVEPOINT identifier&＃xff0c;SAVEPOINT 允许在事务中创建一个保存点&＃xff0c;一个事务中可以有多个 SAVEPOINT&＃xff1b;

• RELEASE SAVEPOINT identifier 删除一个事务的保存点&＃xff0c;当没有指定的保存点时&＃xff0c;执行该语句会抛出一个异常&＃xff1b;

• ROLLBACK TO identifier 把事务回滚到标记点&＃xff1b;

• SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MariaDB [bokebi]> select &＃64;&＃64;autocommit; #查询自动提交事务设置
&＃43;--------------&＃43;
| &＃64;&＃64;autocommit |
&＃43;--------------&＃43;
| 1 |
&＃43;--------------&＃43;
1 row in set (0.00 sec)
---------------------------------------------------------------------------------
在 MySQL 命令行的默认设置下&＃xff0c;事务都是自动提交的&＃xff0c;即执行 SQL 语句后就会马上执行 COMMIT 操作。
因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION&＃xff0c;或者执行命令 SET AUTOCOMMIT&＃61;0&＃xff0c;用来禁止使用当前会话的自动提交。


4.MYSQL 事务处理主要有两种方法

1.用 BEGIN, ROLLBACK, COMMIT来实现

- BEGIN 开始一个事务- ROllBACK 事务回滚- COMMIT 事务确认


2.直接用 SET 来改变 MySQL 的自动提交模式:

- SET AUTOCOMMIT&＃61;0/false 禁止自动提交- SET AUTOCOMMIT&＃61;1/true 开启自动提交
-


3.查看事务

#查看当前的事务
- SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;#查看当前锁定的事务
- SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;#查看当前等锁的事务
- SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;


5.事务隔离

事务隔离级别&＃xff1a;
READ-UNCOMMITTED&＃xff1a;可读取到未提交数据 --> 脏读READ-COMMITTED&＃xff1a;可读取到提交数据&＃xff0c;但未提交数据不可读&＃xff0c;产生不可重复读&＃xff0c;即可读取到多个提交数据&＃xff0c;导致每次
读取数据不一致--> 不可重复读REPEATABLE-READ&＃xff1a;可重复读&＃xff0c;多次读取数据都一致&＃xff0c;产生幻读&＃xff0c;即读取过程中&＃xff0c;即使有其它提交的事务修改数据&＃xff0c;仍
只能读取到未修改前的旧数据。此为MySQL默认设置 --> 幻读&＃xff0c;MySQL的默认级别SERIALIZABLE&＃xff1a;可串行化&＃xff0c;未提交的读事务阻塞修改事务&＃xff08;加读锁&＃xff0c;但不阻塞读事务&＃xff09;&＃xff0c;或者未提交的修改事务阻
塞读事务&＃xff08;加写锁&＃xff0c;其它事务的读&＃xff0c;写都不可以执行&＃xff09;。会导致并发性能差
--------------------------------------------------------------------------------
查看事务隔离级别&＃xff1a;
SELECT &＃64;&＃64;session.tx_isolation;
-----------------------------------------------------------------
查看InnoDB存储引擎的状态信息&＃xff1a;
SHOW ENGINE innodb STATUS;


MVCC和事务的隔离级别&＃xff1a;

MVCC&＃xff08;多版本并发控制机制&＃xff09;只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。
其他两个隔离级别都和MVCC不兼容,因为READUNCOMMITTED总是读取最新的数据行&＃xff0c;而不是符合当前事务版本的数据行。
而SERIALIZABLE则会对所有读取的行都加锁


指定事务隔离级别&＃xff1a;

1. 服务器变量tx_isolation指定&＃xff0c;默认为REPEATABLE-READ&＃xff0c;可在GLOBAL和SESSION级进行设置
SET tx_isolation&＃61;&＃39;&＃39;READ-UNCOMMITTEDREAD-COMMITTEDREPEATABLE-READSERIALIZABLE2. 服务器选项中指定
vim /etc/my.cnf
[mysqld]
transaction-isolation&＃61;SERIALIZABLE死锁&＃xff1a;两个或多个事务在同一资源相互占用&＃xff0c;并请求锁定对方占用的资源的状态


6.事务回滚案例

简单的事务回滚

MariaDB [bokebi]> CREATE TABLE bokebi_test(id int(5)) engine&＃61;innodb;
Query OK, 0 rows affected (0.00 sec)MariaDB [bokebi]> SELECT * FROM bokebi_test;
Empty set (0.00 sec)MariaDB [bokebi]> BEGIN;
Query OK, 0 rows affected (0.00 sec)MariaDB [bokebi]> INSERT INTO bokebi_test VALUE(5);
Query OK, 1 row affected (0.00 sec)MariaDB [bokebi]> INSERT INTO bokebi_test VALUE(6);
Query OK, 1 row affected (0.00 sec)MariaDB [bokebi]> INSERT INTO bokebi_test VALUE(7);
Query OK, 1 row affected (0.00 sec)MariaDB [bokebi]> COMMIT;
Query OK, 0 rows affected (0.00 sec)MariaDB [bokebi]> SELECT * FROM bokebi_test;
&＃43;------&＃43;
| id |
&＃43;------&＃43;
| 5 |
| 6 |
| 7 |
&＃43;------&＃43;
3 rows in set (0.00 sec)MariaDB [bokebi]> BEGIN; #开始事务
Query OK, 0 rows affected (0.00 sec)MariaDB [bokebi]> INSERT INTO bokebi_test VALUE(1);
Query OK, 1 row affected (0.00 sec)MariaDB [bokebi]> INSERT INTO bokebi_test VALUE(2);
Query OK, 1 row affected (0.00 sec)MariaDB [bokebi]> INSERT INTO bokebi_test VALUE(3);
Query OK, 1 row affected (0.00 sec)MariaDB [bokebi]> SELECT * FROM bokebi_test;
&＃43;------&＃43;
| id |
&＃43;------&＃43;
| 5 |
| 6 |
| 7 |
| 1 |
| 2 |
| 3 |
&＃43;------&＃43;
6 rows in set (0.00 sec)MariaDB [bokebi]> ROLLBACK; #进行回滚
Query OK, 0 rows affected (0.00 sec)MariaDB [bokebi]> SELECT * FROM bokebi_test; #回到了我们对表操作前的数据
&＃43;------&＃43;
| id |
&＃43;------&＃43;
| 5 |
| 6 |
| 7 |
&＃43;------&＃43;
3 rows in set (0.00 sec)MariaDB [bokebi]>


在事务中添加回滚点&＃xff0c;从而进行选择性回滚

MariaDB [bokebi]> SELECT * FROM bokebi_test;
&＃43;------&＃43;
| id |
&＃43;------&＃43;
| 5 |
| 6 |
| 7 |
&＃43;------&＃43;
3 rows in set (0.00 sec)MariaDB [bokebi]> BEGIN; #开始事务
Query OK, 0 rows affected (0.00 sec)MariaDB [bokebi]> SAVEPOINT rollback_point1; #添加回滚点1
Query OK, 0 rows affected (0.00 sec)MariaDB [bokebi]> INSERT INTO bokebi_test values(1);
Query OK, 1 row affected (0.00 sec)MariaDB [bokebi]> SAVEPOINT rollback_point2; #添加回滚点2
Query OK, 0 rows affected (0.00 sec)MariaDB [bokebi]> INSERT INTO bokebi_test values(2);
Query OK, 1 row affected (0.00 sec)MariaDB [bokebi]> SAVEPOINT rollback_point3; #添加回滚点3
Query OK, 0 rows affected (0.00 sec)MariaDB [bokebi]> INSERT INTO bokebi_test values(3);
Query OK, 1 row affected (0.00 sec)MariaDB [bokebi]> SELECT * FROM bokebi_test;
&＃43;------&＃43;
| id |
&＃43;------&＃43;
| 5 |
| 6 |
| 7 |
| 1 |
| 2 |
| 3 |
&＃43;------&＃43;
6 rows in set (0.00 sec)MariaDB [bokebi]> ROLLBACK TO rollback_point3; #回滚至回滚点3
Query OK, 0 rows affected (0.00 sec)MariaDB [bokebi]> SELECT * FROM bokebi_test;
&＃43;------&＃43;
| id |
&＃43;------&＃43;
| 5 |
| 6 |
| 7 |
| 1 |
| 2 |
&＃43;------&＃43;
5 rows in set (0.00 sec)MariaDB [bokebi]> ROLLBACK TO rollback_point2; #回滚至回滚点2
Query OK, 0 rows affected (0.00 sec)MariaDB [bokebi]> SELECT * FROM bokebi_test;
&＃43;------&＃43;
| id |
&＃43;------&＃43;
| 5 |
| 6 |
| 7 |
| 1 |
&＃43;------&＃43;
4 rows in set (0.00 sec)MariaDB [bokebi]> ROLLBACK TO rollback_point1; #回滚至回滚点1
Query OK, 0 rows affected (0.00 sec)MariaDB [bokebi]> SELECT * FROM bokebi_test;
&＃43;------&＃43;
| id |
&＃43;------&＃43;
| 5 |
| 6 |
| 7 |
&＃43;------&＃43;
3 rows in set (0.00 sec)MariaDB [bokebi]>