MySQL高级知识点 | 三、事务

事务

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 保存点名。

1. 查看user表中的数据

   mysql> select * from user;

   ±----±---------±----±-----+

   | mid |name | scx |word |

   ±----±---------±----±-----+

   |1| zhangsan |0| NULL |

   | 2 |wangwu | 1 |NULL |

   ±----±---------±----±-----+

   2 rows inset (0.05 sec)

2. mysql事务开始

   mysql> BEGIN; – 或者start transaction；

   Query OK, 0 rows affected (0.00 sec)

3. 向表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)

4. 指定保存点，保存点名为test

mysql> SAVEPOINT test;

Query OK, 0rows affected ( 0. 00sec)

5. 向表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)

6. 回滚到保存点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开启自动提交