Loading...  <div class="tip inlineBlock info"> 事务:一组原子性的SQL查询,或者说一个独立工作单元。一个支持事务的存储引擎或关系型数据库,必然是能够满足`ACID`测试的。其中MySQL或MariaDB中有一个`事务日志`,就是用于崩溃后回滚。 </div> ## ACID测试 * A:atomicity,原子性;整个事务中所有操作要么全部成功执行,要么全部失败后回滚; * C:consistency,一致性;数据库总是从一个一致性状态转换为另一个一致性状态; * I:Isolation,隔离性;一个事务所作出的操作在提交之前,是不能为其他所见;隔离有多种隔离级别; * D:durability,持久性;一旦事务提交,其所做的修改悔永久保存于数据库中; <div class="tip inlineBlock warning"> 隔离性中,隔离界越高,数据安全性越高;但是并发能力越低。 </div> ## 事务 ``` 启动事务:START TRANSACTION ... ... 结束事务: 1. COMMIT: 提交 -> 持久固化 2. ROLLBACK: 回滚 => 回到事务开始之前的状态 ``` <div class="tip inlineBlock warning"> 只有事务型存储引擎方能支持此类操作; </div> ``` # 有一个全局参数 autocommit ,意为自动提交 MariaDB [test]> show global variables like '%auto%'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_stats_auto_update | 1 | | sql_auto_is_null | OFF | +-----------------------------+-------+ 8 rows in set (0.00 sec) # 建议:显式请求和提交事务,而不要使用“自动提交”功能,因为每次提交都会产生磁盘IO # 关闭自动提交 MariaDB [test]> set global autocommit=OFF # 永久关闭,在配置文件新增 # autocommit = OFF 或 autocommit = 0 # 发现一个问题,就是查询也需要commit才能查询到最新数据 ``` ## savepoint(保存点) ``` MariaDB [(none)]> HELP SAVEPOINT Name: 'SAVEPOINT' Description: Syntax: SAVEPOINT identifier # 保存点 ROLLBACK [WORK] TO [SAVEPOINT] identifier # 回滚到某个点 RELEASE SAVEPOINT identifier # 删除点 ``` **实例** ``` # 开始事务 MariaDB [test]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) # 插入第一条数据 MariaDB [test]> INSERT students (Name, Age, Gender) VALUES ('小红', 16, 0); Query OK, 1 row affected (0.00 sec) # 保存点 MariaDB [test]> SAVEPOINT S1; Query OK, 0 rows affected (0.00 sec) # 插入第二条数据 MariaDB [test]> INSERT students (Name, Age, Gender) VALUES ('张三', 20, 1); Query OK, 1 row affected (0.00 sec) # 发现第二条数据是不想要的,于是回滚到第二条数据插入操作之前 MariaDB [test]> ROLLBACK TO S1; Query OK, 0 rows affected (0.00 sec) # 提交 MariaDB [test]> COMMIT; Query OK, 0 rows affected (0.00 sec) # 查看表 可见第二条数据没有被保存下来 MariaDB [test]> SELECT * FROM students; +----+--------+------+--------+ | ID | Name | Age | Gender | +----+--------+------+--------+ | 3 | 小红 | 16 | 0 | +----+--------+------+--------+ 1 row in set (0.00 sec) MariaDB [test]> ``` ## 事务隔离级别 1. READ UNCOMMITTED(读未提交,存在下面的前三种问题) 2. READ COMMITTED(读提交,存在不可重复读、幻读问题) 3. REPEATED READ(可重读, 存在幻读问题) 4. SERIALIZABLIE(可串行化, 存在加锁读问题) <div class="tip inlineBlock warning"> 可能存在的问题: * 脏读(读到别人没有提交的数据,然后对方又回滚了) * 不可重复读 * 幻读 * 加锁读 </div> **查看全局事务隔离级别** ``` MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%isola%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.00 sec) # tx_isolation 服务器变量 # 默认是 REPEATABLE-READ 可重读的 # 可以在SESSION级别修改 ``` **脏读演示** ``` # 在会话一和会话二中设置隔离级别为'READ-UNCOMMITTED' MariaDB [(test)]> SET SESSION tx_isolation='READ-UNCOMMITTED' ``` <div class="tab-container post_tab box-shadow-wrap-lg"> <ul class="nav no-padder b-b scroll-hide" role="tablist"> <li class='nav-item active' role="presentation"><a class='nav-link active' style="" data-toggle="tab" aria-controls='tabs-4427849c2695383b17f1d48e7c563dd180' role="tab" data-target='#tabs-4427849c2695383b17f1d48e7c563dd180'>会话一</a></li><li class='nav-item ' role="presentation"><a class='nav-link ' style="" data-toggle="tab" aria-controls='tabs-7f0b13ef452fababbae8ef8bc2bc83ab461' role="tab" data-target='#tabs-7f0b13ef452fababbae8ef8bc2bc83ab461'>会话二</a></li> </ul> <div class="tab-content no-border"> <div role="tabpanel" id='tabs-4427849c2695383b17f1d48e7c563dd180' class="tab-pane fade active in"> ```mysql # 开始事务 MariaDB [(test)]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) # 查询全部数据 MariaDB [test]> SELECT * FROM students; +----+--------+------+--------+ | ID | Name | Age | Gender | +----+--------+------+--------+ | 3 | 小红 | 16 | 0 | +----+--------+------+--------+ 1 row in set (0.00 sec) # 更新小红ID为4 MariaDB [test]> UPDATE students SET ID=4 WHERE ID=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 可以查看到ID已经更新了,这个时候我们到会话二中查询一下 MariaDB [test]> SELECT * FROM students; +----+--------+------+--------+ | ID | Name | Age | Gender | +----+--------+------+--------+ | 4 | 小红 | 16 | 0 | +----+--------+------+--------+ 1 row in set (0.00 sec) # 回滚 MariaDB [test]> ROLLBACK; Query OK, 0 rows affected (0.00 sec) ``` </div><div role="tabpanel" id='tabs-7f0b13ef452fababbae8ef8bc2bc83ab461' class="tab-pane fade "> ``` MariaDB [(none)]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) # 此时我们查询到ID确实变为了4 MariaDB [(none)]> SELECT * FROM test.students; +----+--------+------+--------+ | ID | Name | Age | Gender | +----+--------+------+--------+ | 4 | 小红 | 16 | 0 | +----+--------+------+--------+ 1 row in set (0.00 sec) # 当会话一回滚操作后,ID又变为了3;这就是脏读 MariaDB [(none)]> SELECT * FROM test.students; +----+--------+------+--------+ | ID | Name | Age | Gender | +----+--------+------+--------+ | 3 | 小红 | 16 | 0 | +----+--------+------+--------+ 1 row in set (0.00 sec) ``` </div> </div> </div> 最后修改:2021 年 02 月 28 日 © 允许规范转载 打赏 赞赏作者 支付宝微信 赞 0 如果觉得我的文章对你有用,请随意赞赏