SQL事务管理

 事务管理是针对数据库的一组操作。由一条或多条SQL语句组成，这些语句在逻辑上具有强烈的相关性，如果其中一条语句无法执行，那么所有的语句都不会执行。

1 事务管理

表 事务的四个特性

事务有以下的优点：

1. 并发控制，事务可以管理并发访问数据库的能力。保证多个并发执行的事务不会相互干扰或产生不一致的结果。确保每个事务的操作在逻辑上相互隔离，避免了数据竞争和冲突。
2. 错误恢复，如果事务执行过程发生错误，事务可以被回滚，撤销之前的操作，将数据库状态恢复到事务开始之前的状态。
3. 高效性，通过将多个相关操作组合在一个事务中，可以减少与数据库的交互次数，提高效率。

1.1 事务基本用法

START TRANSACTION; -- 开启事务

SAVEPOINT label1; -- 保存回滚节点

ROLLBACK TO label1; -- 回滚到具体的节点

ROLLBACK; -- 回滚整个事务

COMMIT; -- 提交事务

START TRANSACTION;

INSERT teacher(id,name) VALUES(4,"刘老师");

SAVEPOINT label1;

INSERT teacher(id,name) VALUES(5,"张老师");

ROLLBACK TO label1;

COMMIT;  -- 最终，只有第一条信息会被插入id=4

1.2 事务并发引发的问题

1）脏读。一个事务处理过程中读取了另一个未提交的事务中的数据。

当数据库的事务隔离级别为读未提交（RU，READ-UNCOMMITTED）时，会出现这个问题。

-- 事务隔离级别为“读未提交”

START TRANSACTION;

UPDATE teacher SET `name`=CONCAT(`name`,"(未提交事务)") WHERE id = 4;

ROLLBACK -- 先不执行，先在其他事务查询这条数据，发现name="刘老师(未提交事务)"，然后再执行回滚

将数据库隔离级别设置为其他的级别（比如读已提交），则不会有这个问题。

2）不可重复读。在一个事务中，多次读取同一个数据时，读取的数据不一致。（数据被更新了update）。

-- 隔离级别为“读已提交”

START TRANSACTION;

SELECT `name` FROM teacher WHERE id = 4;

SELECT SLEEP(30); -- 休眠30s，在这个时间在另一个事务中执行update操作，来跟新id=4的数据

SELECT `name` FROM teacher WHERE id = 4;

COMMIT;

将隔离级别设置为可重复读或可串行化则不会出现这个问题。

3）幻读。读取某一范围的数据时，在一个事务中多次读，结果不一致。（发生在插入或删除数据时。）

-- 隔离级别为 "读已提交"，注意mysql 的“可重复读”不会出现这个问题。

START TRANSACTION;

SELECT * FROM teacher WHERE name = '刘老师';

SELECT SLEEP(20); -- 休眠20s,在这段时间在另一个事务执行insert操作，来插入新刘老师的数据

SELECT * FROM teacher WHERE name = '刘老师';

COMMIT;

mysql 将隔离级别设置为可重复读或可串行化则不会出现这个问题。

1.3 事务的隔离级别

表 事务的四种隔离级别

表 不同隔离级别下所解决的问题

注意：MySQL 隔离级别为可重复读解决了“幻读”的问题。

SHOW VARIABLES LIKE "transaction_isolation"; -- 查看数据库的隔离级别

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 设置全局隔离级别为“读未提交”。需要重新连接数据库才会生效。

1.4 当前读和快照读

当前读，读取最新提交的数据。（隔离级别为可串行化）。

快照读（一致性读），读取某一时间点的数据。（隔离级别为读提交或可重复读）。

在读提交隔离级别下，每次SELECT都会建立新的快照。

在可重复读隔离级别下，建立快照的时机为：

1. 事务启动后，首次SELECT。
2. 事务启动时选择了with consistent snapshot,则在启动时建立快照。
3. 基于旧数据的修改操作（或insert及delete操作），会重新建立快照。

-- 隔离级别为 "可重复读"

START TRANSACTION;

SELECT * FROM teacher WHERE name LIKE '%刘老师%'; -- 建立快照

SELECT SLEEP(20); -- 休眠20s,在这段时间在另一个事务执行insert操作，来插入新刘老师的数据

INSERT INTO teacher(`name`) VALUES('刘老师'); -- 数据插入或删除会建立新的快照

UPDATE teacher SET `name` = '刘老师(旧数据修改)' WHERE id = 2; -- 旧数据修改，会建立新的快照

SELECT * FROM teacher WHERE name LIKE '%刘老师%';

COMMIT;

注意，上面查询的结果虽然不一致，这是因为在同一个事务中进行修改或插入操作。因此不是幻读。 而如果在休眠的20s中在其他事务中执行了插入操作，依旧不会影响查询结果。因为快照读是基于MVCC(多版本并发控制)实现的。