事务
MySQL 事务属性
事务是由一组 SQL 语句组成的逻辑处理单元,事务具有 ACID 属性:
- 原子性(Atomicity):事务是一个原子操作单元。在当时原子是不可分割的最小元素,其对数据的修改,要么全部成功,要么全部都不成功。
- 一致性(Consistent):事务开始到结束的时间段内,数据都必须保持一致状态。
- 隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的"独立"环境执行。
- 持久性(Durable):事务完成后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
事务常见问题
①更新丢失(Lost Update)
原因:当多个事务选择同一行操作,并且都是基于最初选定的值,由于每个事务都不知道其他事务的存在,就会发生更新覆盖的问题。类比 Github 提交冲突。
②脏读(Dirty Reads)
原因:事务 A 读取了事务 B 已经修改但尚未提交的数据。若事务 B 回滚数据,事务 A 的数据存在不一致性的问题。
③不可重复读(Non-Repeatable Reads)
原因:事务 A 第一次读取最初数据,第二次读取事务 B 已经提交的修改或删除数据。导致两次读取数据不一致。不符合事务的隔离性。
④幻读(Phantom Reads)
原因:事务 A 根据相同条件第二次查询到事务 B 提交的新增数据,两次数据结果集不一致。不符合事务的隔离性。
幻读和脏读有点类似,脏读是事务 B 里面修改了数据,幻读是事务 B 里面新增了数据。
事务的隔离级别
数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大。这是因为事务隔离实质上是将事务在一定程度上"串行"进行,这显然与"并发"是矛盾的。
根据自己的业务逻辑,权衡能接受的最大副作用。从而平衡了"隔离" 和 "并发"的问题。MySQL 默认隔离级别是可重复读。
脏读,不可重复读,幻读,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决:
隔离级别 | 读数据一致性 | 脏读 | 不可重复 读 | 幻读 |
---|---|---|---|---|
未提交读(Read uncommitted) | 最低级别 | 是 | 是 | 是 |
已提交读(Read committed) | 语句级 | 否 | 是 | 是 |
可重复读(Repeatable read) | 事务级 | 否 | 否 | 是 |
可序列化(Serializable) | 最高级别,事务级 | 否 | 否 | 否 |
查看当前数据库的事务隔离级别:show variables like ‘tx_isolation’。
mysql> show variables like 'tx_isolation';
Variable_name | Value |
---|---|
tx_isolation | REPEATABLE-READ |
事务级别的设置
#1. 未提交读(READ UNCOMMITED) 解决的障碍:无; 引入的问题:脏读
set SESSION TRANSACTION ISOLATION LEVEL READUNCOMMITTED;
#2. 已提交读 (READ COMMITED) 解决的障碍:脏读; 引入的问题:不可重复读
set SESSION TRANSACTION ISOLATION LEVEL readcommitted;
#3. 可重复读(REPEATABLE READ)解决的障碍:不可重复读; 引入的问题:
set SESSION TRANSACTION ISOLATION LEVEL repeatableread;
#4. 可串行化(SERIALIZABLE)解决的障碍:可重复读; 引入的问题:锁全表,性能低下
set SESSION TRANSACTION ISOLATION LEVEL serializable;
**总结:**事务隔离级别为可重复读时,如果有索引(包括主键索引)的时候,以索引列为条件更新数据,会存在间隙锁间、行锁、页锁的问题,从而锁住一些行;如果没有索引,更新数据时会锁住整张表。
事务隔离级别为串行化时,读写数据都会锁住整张表,隔离级别越高,越能保证数据的完整性和一致性,但是对并发性能的影响也越大。
对于多数应用程序,可以优先考虑把数据库系统的隔离级别设为 Read Committed,它能够避免脏读取,而且具有较好的并发性能。
事务保存点,实现部分回滚
定义保存点,以及回滚到指定保存点前状态的语法如下:
- 定义保存点:
SAVEPOINT 保存点名
。 - 回滚到指定保存点:
ROLLBACK TO SAVEPOINT 保存点名
。
-
查看user表中的数据
mysql>
select * from user;
±----±---------±----±-----+
| mid |name | scx |word |
±----±---------±----±-----+
|1| zhangsan |0| NULL |
| 2 |wangwu | 1 |NULL |
±----±---------±----±-----+
2 rows inset (0.05 sec)
-
mysql事务开始
mysql>
BEGIN;
– 或者start transaction;
Query OK, 0 rows affected (0.00 sec)
-
向表user中插入2条数据
mysql> INSERT INTO user VALUES ('3','one','0','');
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO user VALUES ('4,'two','0','');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
±----±---------±----±-----+
|mid | name |scx | word |
±----±---------±----±-----+
| 1 |zhangsan | 0 |NULL |
|2| wangwu |1| NULL |
| 3 |one | 0 ||
|4| two |0| |
±----±---------±----±-----+
4rows inset ( 0. 00sec)
- 指定保存点,保存点名为test
mysql> SAVEPOINT test;
Query OK, 0rows affected ( 0. 00sec)
- 向表user中插入第 3条数据
mysql> INSERT INTO user VALUES ( '5', 'three', '0', '');
Query OK, 1row affected ( 0. 00sec)
mysql> select * from user;
±----±---------±----±-----+
| mid |name | scx |word |
±----±---------±----±-----+
|1| zhangsan |0| NULL |
| 2 |wangwu | 1 |NULL |
|3| one |0| |
| 4 |two | 0 ||
|5| three |0| |
±----±---------±----±-----+
5rows inset ( 0. 02sec)
- 回滚到保存点test
mysql> ROLLBACK TO SAVEPOINT test;
Query OK, 0rows affected ( 0. 31sec)
mysql> select * from user;
±----±---------±----±-----+
| mid |name | scx |word |
±----±---------±----±-----+
|1| zhangsan |0| NULL |
| 2 |wangwu | 1 |NULL |
|3| one |0| |
| 4 |two | 0 ||
±----±---------±----±-----+
4 rows inset (0.00 sec)
我们可以看到保存点 test 以后插入的记录没有显示了,即成功回滚到了定义保存点 test 前的状态。利用保存点可以实现只提交事务中部分处理的功能。
事务控制语句
BEGIN
或 START TRANSACTION;
显式地开启一个事务;
COMMIT;
也可以使用 COMMITWORK
,不过二者是等价的。 COMMIT会提交事务,并使已对数据库进行的所有修改成为永久性的;
ROLLBACK;
有可以使用 ROLLBACKWORK,不过二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
SAVEPOINT identifier;
SAVEPOINT允许在事务中创建一个保存点,一个事务中可以有多个 SAVEPOINT;
RELEASE SAVEPOINT identifier;
删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;
ROLLBACK TO identifier;
把事务回滚到标记点;
SET TRANSACTION;
用来设置事务的隔离级别。 InnoDB存储引擎提供事务的隔离级别有 READUNCOMMITTED、 READCOMMITTED、REPEATABLE READ和 SERIALIZABLE。
用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN开始一个事务
ROLLBACK事务回滚
COMMIT事务确认
直接用 SET来改变 MySQL 的自动提交模式:
SETAUTOCOMMIT= 0或者 off禁止自动提交
SETAUTOCOMMIT= 1或者 on开启自动提交