事务:一组原子性的SQL查询,或者说一个独立工作单元。一个支持事务的存储引擎或关系型数据库,必然是能够满足
ACID
测试的。其中MySQL或MariaDB中有一个事务日志
,就是用于崩溃后回滚。
ACID测试
- A:atomicity,原子性;整个事务中所有操作要么全部成功执行,要么全部失败后回滚;
- C:consistency,一致性;数据库总是从一个一致性状态转换为另一个一致性状态;
- I:Isolation,隔离性;一个事务所作出的操作在提交之前,是不能为其他所见;隔离有多种隔离级别;
- D:durability,持久性;一旦事务提交,其所做的修改悔永久保存于数据库中;
隔离性中,隔离界越高,数据安全性越高;但是并发能力越低。
事务
启动事务:START TRANSACTION
...
...
结束事务:
1. COMMIT: 提交 -> 持久固化
2. ROLLBACK: 回滚 => 回到事务开始之前的状态
只有事务型存储引擎方能支持此类操作;
# 有一个全局参数 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]>
事务隔离级别
- READ UNCOMMITTED(读未提交,存在下面的前三种问题)
- READ COMMITTED(读提交,存在不可重复读、幻读问题)
- REPEATED READ(可重读, 存在幻读问题)
- SERIALIZABLIE(可串行化, 存在加锁读问题)
可能存在的问题:
- 脏读(读到别人没有提交的数据,然后对方又回滚了)
- 不可重复读
- 幻读
- 加锁读
查看全局事务隔离级别
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'
# 开始事务
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)
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)