数据库事务管理与备份恢复

事务（Transaction） 是数据库管理系统中执行的一个不可分割的工作单元，它包含一组 SQL 操作，这些操作要么全部成功执行，要么全部不执行。

事务的四大特性（ACID）：

• 原子性（Atomicity）：事务中的所有操作要么全部提交成功，要么全部回滚失败
• 一致性（Consistency）：事务执行前后，数据库的完整性约束没有被破坏
• 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不影响其他事务
• 持久性（Durability）：事务一旦提交，其结果永久保存在数据库中

一、事务管理：银行转账示例

1. ACID特性图解

2. 事务控制代码解析

-- 银行转账事务示例
BEGIN TRANSACTION; -- 开启事务-- 账户1扣款
UPDATE accounts 
SET balance = balance - 100 
WHERE id = 1;-- 账户2入账
UPDATE accounts 
SET balance = balance + 100 
WHERE id = 2;-- 检查结果
IF (SELECT balance FROM accounts WHERE id = 1) >= 0COMMIT; -- 提交事务：所有操作永久生效PRINT '转账成功';
ELSEROLLBACK; -- 回滚事务：撤销所有操作PRINT '余额不足，转账失败';
END IF;

3. 隔离级别问题示例

脏读问题：

-- 事务A（未提交）
BEGIN TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE id = 100; -- 库存减1-- 事务B（读取未提交数据）
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT stock FROM products WHERE id = 100; -- 读取到99（未提交的值）-- 事务A回滚
ROLLBACK;-- 结果：事务B读取了不存在的数据

幻读（Phantom Read）：一个事务在查询范围内插入了新数据，导致另一个事务两次查询结果不同

不可重复读问题：

-- 事务A
BEGIN TRANSACTION;
SELECT price FROM products WHERE id = 200; -- 返回100-- 事务B
UPDATE products SET price = 120 WHERE id = 200;
COMMIT;-- 事务A再次查询
SELECT price FROM products WHERE id = 200; -- 返回120
COMMIT;

二、备份与恢复：电商数据库案例

高可用性与灾难恢复

高可用性架构：

灾难恢复策略：

• 异地容灾：在地理位置不同的数据中心建立备份系统
• 定期测试恢复流程：确保备份数据可用
• 备份验证：定期还原备份数据进行验证
• 日志传输：实时将事务日志传输到远程备份中心

1. 备份策略示例（SQL Server）

-- 周日：完全备份
BACKUP DATABASE EcommerceDB 
TO DISK = 'D:\Backup\Ecom_Full.bak'
WITH INIT, NAME = 'Full Backup';-- 周一至周六：差异备份
BACKUP DATABASE EcommerceDB 
TO DISK = 'D:\Backup\Ecom_Diff.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup';-- 每15分钟：事务日志备份
BACKUP LOG EcommerceDB 
TO DISK = 'D:\Backup\Ecom_Log.trn'
WITH NAME = 'Transaction Log Backup';

2. 恢复流程图示

3. 时间点恢复示例

-- 恢复到7月8日14点的状态
RESTORE DATABASE EcommerceDB 
FROM DISK = 'D:\Backup\Ecom_Full.bak'
WITH NORECOVERY; RESTORE LOG EcommerceDB 
FROM DISK = 'D:\Backup\Ecom_Log.trn'
WITH STOPAT = '2025-07-08 14:00:00', RECOVERY;

恢复效果对比：

数据库状态时间线：
7月8日12:00 ─── 14:00（故障点）─── 15:00恢复操作：
完全备份（12:00） + 日志备份（12:00-14:00） = 恢复到14:00状态

5. 高可用技术对比

---

场景：在线商城订单处理

三、数据库并发控制

一、为什么需要并发控制？

当多个用户同时访问数据库时，会出现经典问题：

问题示例：银行账户并发访问

-- 事务A（转账）
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 读到500
UPDATE accounts SET balance = 500 - 100 WHERE id = 1;-- 同时事务B（存款）
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 也读到500
UPDATE accounts SET balance = 500 + 200 WHERE id = 1;
COMMIT;-- 事务A提交后：
-- 预期：500 - 100 + 200 = 600
-- 实际：500 + 200 = 700（丢失了-100的操作）

二、核心并发控制技术

并发控制技术分类

三、锁机制详解

锁的类型

共享锁（Shared Lock, S 锁）：

• 允许其他事务读取同一资源

• 多个事务可同时持有同一资源的共享锁

• 语法示例（MySQL）：

  SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;
  

排他锁（Exclusive Lock, X 锁）：

• 禁止其他事务读取或修改同一资源

• 同一资源上只能有一个排他锁

• 语法示例（MySQL）：

  SELECT * FROM table_name WHERE ... FOR UPDATE;
  

锁的粒度

表级锁：

• 对整张表加锁
• 实现简单，开销小，但并发度低
• 适用于批量操作（如 TRUNCATE、ALTER TABLE）

行级锁：

• 对表中特定行加锁
• 并发度高，但实现复杂，开销大
• 适用于高并发事务处理（如 OLTP 系统）

意向锁：

• 表示事务后续可能对表或行加锁
• 分为意向共享锁（IS）和意向排他锁（IX）
• 用于快速判断表中是否有行被锁定

锁表示例：

-- 事务A（获取排他锁）
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- X锁
UPDATE accounts SET balance = balance - 100 WHERE id = 1;-- 事务B（尝试读取）
SELECT balance FROM accounts WHERE id = 1; -- 被阻塞，等待锁释放

三级锁协议

三级锁协议是并发控制的基础框架，定义了事务在不同阶段如何获取和释放锁：

一级锁协议（基本锁协议）

• 核心规则：写数据前必须加排他锁（X 锁），事务结束才释放

• 解决问题：丢失更新

• 示例：

  -- 事务A
  BEGIN;
  SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- 获取X锁
  -- 此时事务B的更新请求会被阻塞
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  COMMIT; -- 释放锁
  

二级锁协议（扩展锁协议）

• 核心规则：

  • 写数据前加X锁（事务结束释放）
  • 读数据前加共享锁（S 锁）（读完立即释放）

• 解决问题：丢失更新 + 脏读

• 示例：

  -- 事务A
  BEGIN;
  SELECT balance FROM accounts WHERE id = 1 LOCK IN SHARE MODE; -- 获取S锁
  -- 其他事务可以读但不能写
  COMMIT; -- S锁在SELECT后立即释放-- 事务B
  BEGIN;
  SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 获取X锁（阻塞直到A释放S锁）
  

三级锁协议（严格锁协议）

• 核心规则：

  • 写数据前加X锁（事务结束释放）
  • 读数据前加S锁（事务结束释放）

• 解决问题：丢失更新 + 脏读 + 不可重复读

• 示例：

  -- 事务A
  BEGIN;
  SELECT balance FROM accounts WHERE id = 1 LOCK IN SHARE MODE; -- 获取S锁
  -- 此时事务B的更新会被阻塞
  SELECT balance FROM accounts WHERE id = 1; -- 第二次读取（结果相同）
  COMMIT; -- 释放S锁
  

三级锁协议对比

锁协议	防止丢失更新	防止脏读	防止不可重复读	锁释放时机
一级	✅	❌	❌	X 锁在事务结束释放
二级	✅	✅	❌	S 锁在读取后释放
三级	✅	✅	✅	S 锁和 X 锁在事务结束释放

死锁（Deadlock）

定义：

• 两个或多个事务互相等待对方释放锁资源，导致所有事务都无法继续执行的状态。

死锁示例：

死锁产生的四个必要条件：

1. 互斥条件：资源不能被多个事务同时访问
2. 占有并等待：事务持有锁的同时请求其他锁
3. 不可抢占：锁只能由持有它的事务主动释放
4. 循环等待：事务之间形成循环等待锁的链

死锁处理策略：

• 预防：破坏死锁产生的四个必要条件之一
• 检测与恢复：DBMS 定期检测死锁，选择代价最小的事务回滚
• 超时机制：事务等待锁超过一定时间后自动回滚

MySQL 死锁检测与处理：

sql

-- 查看死锁日志
SHOW ENGINE INNODB STATUS;-- 设置死锁超时时间（默认50秒）
SET innodb_lock_wait_timeout = 10;-- 启用死锁检测（默认开启）
SET innodb_deadlock_detect = ON;

活锁（Livelock）

定义：

• 事务因不断收到锁请求被拒绝而无法继续执行的状态。

活锁示例：

• 事务 T1 持有锁 L，事务 T2 和 T3 循环请求锁 L
• DBMS 采用公平锁策略，轮流授予 T2 和 T3 锁，但 T1 始终未释放锁
• 导致 T2 和 T3 始终无法获取锁而陷入活锁

活锁处理策略：

• 先来先服务（FCFS）：按请求锁的顺序授予锁
• 随机后退：请求锁失败的事务随机等待一段时间后重试
• 优先级机制：为事务分配优先级，高优先级事务优先获取锁

死锁与活锁对比

类型	产生原因	状态特征	解决方法
死锁	事务间循环等待锁	所有事务停滞，无法继续执行	死锁检测、超时机制、锁排序
活锁	事务频繁被拒绝获取锁	事务持续运行但无法完成	公平锁策略、随机后退、优先级机制

死锁处理机制

死锁处理策略：

现代并发控制优化

1. 乐观并发控制（OCC）

2. 多粒度锁定

3. 索引并发控制（B+树示例）

         [根节点]/       \
[叶节点A]       [叶节点B]1│3│5           7│9│11插入操作：
1. 对根节点加S锁
2. 找到叶节点A，升级为X锁
3. 插入数据4
4. 释放所有锁