MySQL的事务和隔离级别

事务

当我们要进行的操作非常多时，比如要依次删除很多个表的数据，我们就需要执行大量的SQL语句来完成，这些数据库操作语句就可以构成一个事务！只有Innodb引擎支持事务，我们可以这样来查看支持的引擎：

SHOW ENGINES;

MySQL默认采用的是Innodb引擎，我们也可以去修改为其他的引擎。

事务具有以下特性：

• **原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• **一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• **隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• **持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

我们通过以下例子来探究以下事务：

begin;   #开始事务
...
rollback;  #回滚事务
savepoint 回滚点;  #添加回滚点
rollback to 回滚点; #回滚到指定回滚点
...
commit; #提交事务
-- 一旦提交，就无法再进行回滚了！

举个例子

假设我们有一个Accounts表，用于记录银行账户的余额：

Accounts 表格

AccountID	Balance
A	1000
B	500

现在，我们要执行一个操作，从账户A转移200到账户B。这个操作需要在一个事务中完成，以确保转账的完整性。过程中，我们还会设置一个回滚点，以便在出现问题时回滚到特定的状态。

事务操作示例

BEGIN;   -- 开始事务

-- 假设当前账户A和账户B的余额分别是1000和500

-- 第1步：从账户A扣除200
UPDATE Accounts SET Balance = Balance - 200 WHERE AccountID = 'A';

-- 设置回滚点
SAVEPOINT transfer_step1;

-- 第2步：向账户B添加200
UPDATE Accounts SET Balance = Balance + 200 WHERE AccountID = 'B';

-- 假设在此时发现了一个问题（比如账户B是一个被冻结的账户）

-- 回滚到刚扣除账户A的200之后的状态
ROLLBACK TO transfer_step1;

-- 重新检查并修正问题，比如改为转账到另一个账户C

-- 最后，如果一切正常，则提交事务
COMMIT;

在这个例子中：

1. 我们开始了一个事务。
2. 从账户A中扣除了200。
3. 在扣款之后设置了一个回滚点transfer_step1。
4. 接着尝试向账户B转账200。
5. 假设发现了问题（例如账户B被冻结），我们就可以使用ROLLBACK TO transfer_step1命令回滚到回滚点，即回到扣除账户A金额后的状态。
6. 解决问题后，我们可以继续操作，或者决定放弃并回滚整个事务。
7. 最后，如果所有步骤都顺利完成，我们提交事务，使所有更改都生效。

这个例子展示了如何在数据库事务中使用回滚点来管理复杂的操作序列，确保数据的一致性和完整性。

四种隔离级别

让我们用一个简单的银行转账例子来解释MySQL的四种隔离级别。假设我们有一个简单的Accounts表，其中包含两个账户的余额：

Accounts 表格

AccountID	Balance
A	100
B	200

1. 读未提交（Read Uncommitted）

• 场景: 假设事务1从账户A转移50到账户B，但尚未提交。事务2同时读取账户B的余额。
• 发生情况: 在“读未提交”级别，事务2可以看到未提交的变更。如果事务1在转移后回滚，事务2读到的数据将是不准确的。
• 示例:
  • 事务1: 转移50从账户A到账户B，未提交。
  • 事务2: 此时读取账户B的余额，看到250（尽管事务1尚未提交）。

2. 读已提交（Read Committed）

• 场景: 事务1读取账户A的余额，然后事务2从账户A转移50到账户B并提交。事务1再次读取账户A的余额。
• 发生情况: 在“读已提交”级别，事务1在第二次读取时会看到新的余额（50被转走了）。
• 示例:
  • 事务1: 读取账户A的余额，看到100。
  • 事务2: 转移50从账户A到账户B，然后提交。
  • 事务1: 再次读取账户A的余额，现在看到50。

3. 可重复读（Repeatable Read）

• 场景: 事务1读取账户A和B的余额，然后事务2从账户A转移50到账户B并提交。事务1再次读取账户A和B的余额。
• 发生情况: 在“可重复读”级别，事务1在整个事务期间将看到相同的余额，不受事务2的影响。
• 示例:
  • 事务1: 读取账户A和B的余额，分别看到100和200。
  • 事务2: 转移50从账户A到账户B，然后提交。
  • 事务1: 再次读取账户A和B的余额，仍然看到100和200。

4. 串行化（Serializable）

• 场景: 事务1尝试读取所有账户的总余额，而事务2同时尝试从账户A转移50到账户B。
• 发生情况: 在“串行化”级别，事务1和事务2不能同时执行。如果事务1首先开始，则事务2必须等待事务1完成后才能开始。
• 示例:
  • 事务1: 开始读取账户总余额。
  • 事务2: 尝试转移50从账户A到账户B，但必须等待，直到事务1完成。
  • 事务1: 完成读取，总余额为300。
  • 事务2: 现在执行转移，更新账户A和B的余额。

这些示例简单地说明了不同隔离级别下，相同的操作可能如何导致不同的结果和并发行为。