MySQL数据库 - 事务

小蜜蜂~嗡嗡嗡~

已于 2024-03-22 16:38:11 修改

阅读量798

点赞数 25

分类专栏：运维文章标签：数据库 mysql linux

于 2024-03-22 16:33:53 首次发布

本文链接：https://blog.csdn.net/qq_57093716/article/details/136916707

版权

运维专栏收录该内容

73 篇文章 0 订阅

订阅专栏

1. 事务的概念

事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，要删除一个人员，即需要删除人员的基本资料，又需要删除和该人员相关的信息，如信箱，文章等等。这样，这些数据库操作语句就构成一个事务！

事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。
事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。
事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。
事务是通过事务的整体性以保证数据的一致性。

所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

2. 事务的ACID特点

ACID，是指在可靠数据库管理系统（DBMS）中，事务(transaction)应该具有的四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。这是可靠数据库所应具备的几个特性。

原子性：
- 指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。
  - 事务是一个完整的操作，事务的各元素是不可分的。
  - 事务中的所有元素必须作为一个整体提交或回滚。
  - 如果事务中的任何元素失败，则整个事务将失败。
一致性：
- 指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
  - 当事务完成时，数据必须处于一致状态。
  - 在事务开始前，数据库中存储的数据处于一致状态。
  - 在正在进行的事务中，数据可能处于不一致的状态。
  - 当事务成功完成时，数据必须再次回到已知的一致状态。
隔离性：
- 指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。
  - 对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
  - 修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。
持久性：
- 在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
  - 指不管系统是否发生故障，事务处理的结果都是永久的。
  - 一旦事务被提交，事务的效果会被永久地保留在数据库中

当多个用户进行同时操作，如果不存在隔离性的事务，就会出现以下状况：

① 脏读(读取未提交数据)：

脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读

案例：

假设在银行转账的场景中，有两个并发运行的事务：

事务A：从账户A向账户B转账100元。这个过程包括两个步骤：
- 减少账户A的余额（UPDATE Account SET balance = balance - 100 WHERE account_id = A）。
- 增加账户B的余额（UPDATE Account SET balance = balance + 100 WHERE account_id = B）。
事务B：在事务A的第一步执行完毕，第二步还未执行时，事务B开始读取账户A和账户B的余额。

如果此时数据库系统的事务隔离级别设置为读未提交（Read Uncommitted），那么事务B可以看到事务A尚未提交的中间状态，即账户A的余额已经减少，但账户B的余额尚未增加。假设事务A因为某种原因回滚，那么事务B读到的就是“脏”数据，因为它反映了从未真正发生的交易。

实际情况应避免脏读的发生，大多数数据库系统通过设置恰当的事务隔离级别（如读已提交、可重复读、序列化等）来防止这种情况。在上述案例中，如果采用读已提交或更高级别的隔离策略，事务B在读取数据时，只会看到事务A已经提交后的结果，从而避免了脏读。

② 不可重复读(前后多次读取，数据内容不一致)：

一个事务内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。

案列：

假设在图书馆管理系统中有两个并发的事务

事务A：管理员正在统计某个时间段内借阅次数最多的书籍。事务A开始并执行查询：

SELECT COUNT(*) AS borrow_count, book_id 
FROM BorrowRecord 
WHERE borrow_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY book_id
ORDER BY borrow_count DESC;

得到了一份统计报表。

事务B：与此同时，另一名读者前来归还一本书籍，并且这本书恰好在事务A查询的范围内。事务B执行：

UPDATE BorrowRecord 
SET return_date = NOW() 
WHERE book_id = 'book_X' AND borrow_date <= '2023-12-31';

并提交了事务。

事务A：管理员再次执行相同的查询以验证数据准确性，但这次查询的结果与第一次不同，因为事务B已经在其间归还了一本书，导致该书的借阅次数减少。

在这个案例中，事务A在两次查询之间遭遇了不可重复读问题，尽管查询条件不变，但因为其他事务的修改，导致事务A前后两次读取的数据不一致。

为了避免不可重复读，数据库系统通常会提供不同级别的事务隔离，如可重复读（Repeatable Read）隔离级别，可以确保在一个事务内部多次读取同一数据时，除非该事务自己修改了数据，否则总是能看到相同的结果。在MySQL InnoDB存储引擎中，默认的事务隔离级别是可重复读，通过MVCC（多版本并发控制）机制来避免不可重复读问题。

③ 幻读(前后多次读取，数据总量不一致)：

一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表中还有没有修改的数据行，就好象发生了幻觉一样。

案列：

假设一个电商网站上有两个并发的事务：

事务A：买家正在进行商品筛选，想要购买价格低于100元的库存充足的某个类别的商品。事务A首先执行查询：

SELECT * FROM Products 
WHERE category = 'Electronics' AND price < 100 AND stock > 0;

此时事务A获取到了50条符合条件的商品记录。

事务B：同时，另一位卖家在后台上传了一批新的电子产品，并设定价格低于100元，这批商品立即生效，事务B执行并提交了以下插入操作：

INSERT INTO Products (category, price, stock)
VALUES ('Electronics', 99, 10),
       ('Electronics', 85, 20),
       ...;

事务A：买家随后准备下单，再次执行了同样的查询：

SELECT * FROM Products 
WHERE category = 'Electronics' AND price < 100 AND stock > 0;

这次查询的结果包含了事务B刚刚插入的记录，所以结果集中商品数量大于50条，对于事务A而言，就好像出现了“幻影”般的记录。

在标准的可重复读（Repeatable Read）隔离级别下，MySQL的InnoDB存储引擎通过间隙锁（Gap Locks）和Next-Key Locks可以一定程度上防止幻读问题的发生，但如果查询条件不包含唯一索引或主键，仍有可能发生幻读。要完全避免幻读，需要将事务隔离级别提升到序列化（Serializable）级别。在序列化级别下，数据库会采取更强的锁策略，以确保事务间的严格串行化执行，但这会牺牲一定的并发性能。

④ 丢失更新：

两个事务同时读取同一条记录，A先修改记录，B也修改记录（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。

案列：

假设在一个银行账户转账系统中，有两个并发的事务：

事务A：Alice想要从她的账户转账100元给Bob，事务A首先读取Alice的账户余额为1000元。

SELECT balance FROM Accounts WHERE account_id = Alice;
// 返回结果：balance = 1000

事务B：几乎同时，Bob的父亲Charlie也想转账100元给Bob，事务B也读取了Alice的账户余额为1000元。

SELECT balance FROM Accounts WHERE account_id = Alice;
// 返回结果：balance = 1000

事务A：事务A开始执行转账操作，减少Alice账户100元，并更新数据库。

UPDATE Accounts SET balance = balance - 100 WHERE account_id = Alice;

事务B：事务B也开始执行转账操作，也减少Alice账户100元，并更新数据库。

UPDATE Accounts SET balance = balance - 100 WHERE account_id = Alice;

事务提交：事务A和事务B都成功提交了自己的更新操作。

现在的问题是，Alice实际上应该只转出了100元，但是由于两个事务并发且没有采取适当的并发控制机制，Alice的账户减少了200元，导致Alice的100元更新丢失。

要避免丢失更新的问题，数据库系统可以采用乐观锁（如使用version字段或时间戳字段进行比较）或悲观锁（如在事务开始时对要更新的数据行加排他锁）等机制。在SQL Server中，可以使用UPDLOCK提示在SELECT语句中获取更新锁来防止丢失更新。而在MySQL的InnoDB存储引擎中，通过使用适当的事务隔离级别（如可重复读及以上）以及在更新时添加FOR UPDATE或LOCK IN SHARE MODE子句，也可以避免这类问题。

3. 事务的隔离级别

事务的隔离级别是数据库管理系统（DBMS）用来控制并发事务之间相互影响的程度，以确保事务的执行不会彼此干扰，维持数据的完整性和一致性。标准SQL规范定义了四种事务隔离级别，它们分别是：

读未提交（Read Uncommitted）
- 在这个级别，一个事务可以读取到另一个事务尚未提交（commit）的数据变更，这可能导致“脏读”（Dirty Read）。这是最低的隔离级别，允许最大的并发，但并发问题最严重。
- 安全性最差但性能最好（不使用）
读已提交（Read Committed）
- 在这个级别，一个事务只能读取到已经提交的数据，即事务开始读取数据时，其他事务对数据的修改只有在它们提交后才能被这个事务看到。这解决了“脏读”问题，但可能导致“不可重复读”（Non-repeatable Read）和“幻读”（Phantom Read）现象。
- 安全性较差性能较好（oracle 数据库默认就是这种）
可重复读（Repeatable Read）
- 在这个级别，一个事务在自身持续时间内多次读取同一数据，总会得到相同的结果，即使有其他事务提交了对该数据的更新。MySQL的InnoDB存储引擎默认使用这个级别，并通过多版本并发控制（MVCC）机制防止了“脏读”和“不可重复读”。但在这个级别，“幻读”现象依然可能存在。
- 安全性较高，性能较差（mysql 默认的就是这种）
串行化（Serializable）
- 可以解决脏读不可重复读和幻读—相当于锁表，完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。这是最高的隔离级别，提供完全的事务隔离。在这种情况下，事务按照完全串行化的顺序执行，好像被安排成依次执行一样。通过使用范围锁或更严格的锁机制，该级别可以防止“脏读”、“不可重复读”和“幻读”等问题，但可能导致更高的并发开销和更低的性能，因为它会限制事务并发执行的能力。
- 安全性高性能差不使用

mysql默认的事务处理级别是 repeatable read ，而Oracle和SQL Server是 read committed 。

事务隔离级别的作用范围分为两种:
全局级: 对所有的会话有效
会话级: 只对当前的会话有效

4. 事务级别的查看与设置

variables 指变量， global variables 指全局变量

4.1 查询全局事务隔离级别

show global variables like '%isolation%';

SELECT @@global.tx_isolation;

4.2 查询会话事务隔离级别

show session variables like '%isolation%';
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;

4.3 设置全局事务隔离级别

set global transaction isolation level read committed;
#将当前数据库服务器中所有新启动的事务的默认隔离级别设置为“读已提交”

4.4 设置会话事务隔离级别

set session transaction isolation level read committed;

5. 事务控制语句

begin 或  start transaction;  
#显示的开启一个服务

commit  或  commit work;
#提交事务，并使已对数据库进行的所有修改变为永久性的。

rollback  或  rollback work;
#回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。

savepoint s1
#使用 SAVEPOINT 允许在事务中创建一个回滚点，一个事务中可以有多个 
'S1'代表回滚点名称。

rollback to [savepoint] s1; 把事务回滚到标记点。

5.1 使用 set 设置控制事务

SET AUTOCOMMIT=0;						#禁止自动提交
SET AUTOCOMMIT=1;						#开启自动提交，Mysql默认为1
SHOW VARIABLES LIKE 'AUTOCOMMIT';		#查看Mysql中的AUTOCOMMIT值

如果没有开启自动提交，当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback 或者 commit;当前事务才算结束。当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。
如果开起了自动提交，mysql会把每个sql语句当成一个事务，然后自动的commit。
当然无论开启与否，begin; commit|rollback; 都是独立的事务。

5.2 测试提交事务

另一台连此数据库

这里，我们提交事务

再去查看

5.3 测试回滚事务

5.4 测试多点回滚

多点回滚：

在数据库中，一旦一个事务被回滚（rollback），事务中所做的所有更改都将被撤销，回到事务开始前的状态。理论上，一旦事务被回滚，那些被撤销的更改就无法再恢复，因为回滚操作是不可逆的。

然而，如果你希望回到事务回滚后的某个状态，通常有两种方法：

重新执行事务：如果你知道事务中做了哪些更改，你可以重新执行相同的事务操作，前提是其他并发事务没有对相同数据进行更改。
依赖于数据库的特定功能：某些数据库系统提供了诸如闪回（Flashback）等高级功能，允许你在一定条件下恢复到过去某一时间点的数据状态。例如，在Oracle数据库中，可以使用FLASHBACK或FLASHBACK DATABASE命令来恢复到之前的时间点。

但是，大部分数据库系统并没有内置的直接手段去恢复一个已被回滚的事务。因此，最佳实践是谨慎对待事务的提交和回滚操作，并且定期备份数据库以防数据丢失。在开发阶段，可以使用版本控制系统来追踪代码更改，以便在必要时恢复代码的某个版本。在生产环境，做好数据库备份和恢复策略是非常关键的。

总结：

事务的ACID特性：
原子性(Atomicity)
一致性(Consistency )
隔离性(Isolation)
持久性(Durability)
这是可靠数据库所应具备的几个特性。

事务之间的相互影响
当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：

脏读
不可重复读
幻读
丢失更新
事务的隔离级别：
（1）未提交读（Read Uncommitted（RU））：允许脏读。

（2）提交读（Read Committed （RC）) ：防止脏读。

（3）可重复读（Repeatable Read（RR））：—mysql默认的隔离级别，防止脏读和不可重复读。

（4）串行读（serializable）：—相当于锁表，可以防止脏读、不可重复读和幻读，（事务串行化）会降低数据库的执行效率。

查询全局事务隔离级别：
show global variables like '%isolation%'；
select @@global.tx_isolation;
查询会话事务隔离级别：
show session variables like '%isolation%';
select @@session.tx_isolation;
select @@tx_isolation;
设置全局事务隔离级别：
set global transaction isolation level 隔离级别； #永久生效
示例： set global transaction isolation level read committed;
设置会话事务隔离级别：
set session transaction isolation level 隔离级别； #退出连接后失效
示例：set session transaction isolation level read committed;
事务控制语句：
BEGIN 或 START TRANSACTION：显式地开启一个事务。
COMMIT 或 COMMITWORK：提交事务，并使已对数据库进行的所有修改变为永久性的。
ROLLBACK 或 ROLLBACK WORK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
SAVEPOINT S1：使用SAVEPOINT 允许在事务中创建一个回滚点，一个事务中可以有多个SAVEPOINT；“S1”代表回滚点名称。
ROLLBACK TO [SAVEPOINT] S1：把事务回滚到标记点。
使用 set 设置控制事务：
set autocommit=0； #禁止自动提交（仅针对当前会话）