数据库事务总结

MySQL数据库事务总结

一、ACID特性

原子性（Atomicity）

定义：事务操作的不可分割性

原子性确保事务中的所有操作要么全部成功执行，要么全部失败回滚，不存在部分执行的情况。一个事务是一个不可分割的工作单位。

实现基础：回滚日志（Undo Log）

• Undo Log作用：记录数据修改前的状态，用于事务回滚
• 日志类型：
  • TRX_UNDO_INSERT_REC：记录INSERT操作的回滚信息
  • TRX_UNDO_UPD_EXIST_REC：记录UPDATE操作的回滚信息
  • TRX_UNDO_UPD_DEL_REC：记录DELETE操作的回滚信息
• 回滚机制：当事务失败时，根据Undo Log逆向执行操作恢复数据

典型场景：转账交易的完整性保障

START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 'B';
COMMIT;

如果任一操作失败，整个事务回滚，确保资金不会丢失或重复。

一致性（Consistency）

定义：数据从一个有效状态到另一个有效状态的转换

一致性确保事务执行前后，数据库都处于有效状态，不违反任何完整性约束。

约束类型：

• 主键约束：确保主键唯一性和非空性
• 外键约束：维护表间引用完整性
• 业务规则约束：
  • CHECK约束：CHECK (balance >= 0)
  • 触发器约束：复杂业务逻辑验证
  • 应用层约束：业务规则校验

实现方式：数据库约束+应用层校验

-- 数据库层约束
ALTER TABLE accounts ADD CONSTRAINT chk_balance CHECK (balance >= 0);-- 应用层校验
if (transferAmount > fromAccount.getBalance()) {throw new InsufficientFundsException();
}

隔离性（Isolation）

定义：并发事务间的相互隔离程度

隔离性控制并发事务对数据的访问程度，防止事务间的相互干扰。

隔离级别与并发问题对应关系

隔离级别	脏读	不可重复读	幻读
Read Uncommitted	✗	✗	✗
Read Committed	✓	✗	✗
Repeatable Read	✓	✓	✗
Serializable	✓	✓	✓

实现技术：锁机制+MVCC

• 锁机制：通过各种锁控制并发访问
• MVCC：多版本并发控制，提供非阻塞读取

持久性（Durability）

定义：事务提交后数据的永久性

一旦事务提交成功，其对数据库的更改就是永久性的，即使系统崩溃也不会丢失。

实现机制：

重做日志（Redo Log）

• 作用：记录数据页的物理修改，用于崩溃恢复
• 写入时机：事务提交前必须先写入Redo Log
• WAL原则：Write-Ahead Logging，先写日志再写数据
• 配置参数：

  -- 控制Redo Log刷盘策略
  SET GLOBAL innodb_flush_log_at_trx_commit = 1;
  

双写缓冲（Double Write Buffer）

• 作用：防止页面写入过程中的部分写入问题
• 工作流程：
  1. 先将脏页写入Double Write Buffer
  2. 调用fsync()确保写入磁盘
  3. 再将页面写入实际位置

刷盘策略（fsync参数调优）

-- 主要参数配置
SET GLOBAL innodb_flush_log_at_trx_commit = 1;  -- 每次提交刷盘
SET GLOBAL innodb_flush_method = 'O_DIRECT';    -- 绕过OS缓存
SET GLOBAL sync_binlog = 1;                     -- 二进制日志同步写入

崩溃恢复流程

1. 解析Redo Log：从检查点开始扫描日志
2. 重做阶段：应用所有已提交事务的修改
3. 回滚阶段：根据Undo Log回滚未提交事务
4. 清理阶段：清理临时数据和锁信息

二、多版本并发控制（MVCC）

MVCC基础

定义与核心目标

MVCC（Multi-Version Concurrency Control）是一种并发控制机制，通过维护数据的多个版本来实现读写操作的并发执行，核心目标是：

• 读操作不阻塞写操作
• 写操作不阻塞读操作
• 减少锁竞争，提高并发性能

与锁机制的互补关系

• MVCC：主要解决读写冲突，提供快照读
• 锁机制：主要解决写写冲突，控制当前读
• 结合使用：MVCC处理读操作，锁机制处理写操作

适用场景与优势

• 适用场景：读多写少的OLTP场景
• 优势：
  • 高并发读取性能
  • 减少死锁概率
  • 提供一致性读取
  • 支持长时间分析查询

实现原理

版本链结构

每行数据通过指针链接形成版本链，新版本指向旧版本：

最新版本 -> 版本1 -> 版本2 -> ... -> 最旧版本

隐藏列（DB_TRX_ID、DB_ROLL_PTR）

InnoDB为每行记录添加三个隐藏列：

• DB_TRX_ID（6字节）：最后修改该行的事务ID
• DB_ROLL_PTR（7字节）：回滚指针，指向Undo Log中的记录
• DB_ROW_ID（6字节）：行ID，无主键时自动生成

Undo Log版本管理

• 版本存储：Undo Log存储行的历史版本
• 链式结构：通过DB_ROLL_PTR形成版本链
• 空间管理：Purge线程定期清理无用版本

Read View机制

Read View是事务在某个时刻看到的数据库状态快照。

活跃事务列表（m_ids）

记录创建Read View时所有活跃（未提交）事务的ID列表。

低水位（min_trx_id）与高水位（max_trx_id）

• 低水位：当前活跃事务中最小的事务ID
• 高水位：系统中下一个要分配的事务ID

可见性判断规则

对于记录的DB_TRX_ID，判断规则如下：

1. DB_TRX_ID < min_trx_id：可见（已提交的历史事务）
2. DB_TRX_ID >= max_trx_id：不可见（未来事务）
3. min_trx_id <= DB_TRX_ID < max_trx_id：
   • 如果DB_TRX_ID在m_ids中：不可见（活跃事务）
   • 如果DB_TRX_ID不在m_ids中：可见（已提交事务）

快照读与当前读

快照读（普通SELECT）

-- 快照读，使用MVCC
SELECT * FROM users WHERE id = 1;

• 读取数据的快照版本
• 不加锁，高并发
• 可能读到历史数据

当前读（SELECT … FOR UPDATE/SHARE）

-- 当前读，需要加锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;-- DML操作也是当前读
UPDATE users SET name = 'new' WHERE id = 1;
DELETE FROM users WHERE id = 1;

语句级快照与事务级快照

• 语句级快照（RC级别）：每个语句开始时创建Read View
• 事务级快照（RR级别）：事务开始时创建Read View，整个事务使用同一快照

数据库实现差异

MySQL InnoDB MVCC

• 基于Undo Log实现版本链
• 聚簇索引存储完整行数据
• 支持行级MVCC

PostgreSQL MVCC

• 基于元组头信息（xmin/xmax）
• 直接在数据页存储多版本
• 需要VACUUM清理旧版本

Oracle 多版本机制

• 基于Undo表空间
• 使用SCN（System Change Number）
• 支持闪回查询

MVCC优化

Undo Log清理策略（purge线程）

-- 相关参数
SET GLOBAL innodb_purge_threads = 4;              -- purge线程数
SET GLOBAL innodb_max_purge_lag = 1000000;        -- 延迟清理阈值
SET GLOBAL innodb_max_purge_lag_delay = 50000;    -- 延迟时间（微秒）

长事务对MVCC的影响

• 版本积累：长事务阻止旧版本清理
• 性能下降：版本链过长影响查询性能
• 空间浪费：Undo Log空间持续增长

版本链长度控制

• 及时提交事务
• 避免长时间的只读事务
• 监控事务运行时间
• 定期重启长时间运行的连接

三、隔离级别实现

隔离级别定义

SQL标准四大隔离级别

1. Read Uncommitted：读取未提交数据
2. Read Committed：读取已提交数据
3. Repeatable Read：可重复读
4. Serializable：串行化

各数据库支持情况对比

数据库	默认隔离级别	支持级别
MySQL InnoDB	Repeatable Read	全部支持
PostgreSQL	Read Committed	全部支持
Oracle	Read Committed	RC, Serializable
SQL Server	Read Committed	全部支持

读未提交（Read Uncommitted）

实现方式：直接读取未提交数据

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;-- 读取可能包含未提交的修改
SELECT * FROM accounts WHERE account_id = 'A';

并发问题：脏读、不可重复读、幻读

脏读（Dirty Read）

定义：一个事务读取到另一个事务未提交的数据。

场景示例：

-- 时间线 T1: 事务A开始
-- 时间线 T2: 事务B开始
-- 事务A                     事务B
BEGIN;                      BEGIN;UPDATE users SET balance = 1000 WHERE id = 1;
SELECT balance FROM users   -- 读取到1000（脏读）
WHERE id = 1;               ROLLBACK; -- 回滚，balance实际还是原值
COMMIT;

危害：基于脏数据做出错误决策，可能导致业务逻辑错误。

不可重复读（Non-Repeatable Read）

定义：同一事务内，多次读取同一行数据，得到不同的结果。

场景示例：

-- 事务A                     事务B
BEGIN;                      BEGIN;
SELECT balance FROM users   -- 第一次读取：500
WHERE id = 1;               UPDATE users SET balance = 1000 WHERE id = 1;COMMIT;
SELECT balance FROM users   -- 第二次读取：1000（不可重复读）
WHERE id = 1;               
COMMIT;

特点：

• 针对同一行数据的多次读取
• 数据内容发生变化
• 主要由UPDATE操作引起

幻读（Phantom Read）

定义：同一事务内，多次执行相同查询条件，返回的行数不同。

场景示例：

-- 事务A                     事务B
BEGIN;                      BEGIN;
SELECT COUNT(*) FROM users  -- 第一次查询：3条记录
WHERE age BETWEEN 20 AND 30;INSERT INTO users VALUES (4, 'Tom', 25);COMMIT;
SELECT COUNT(*) FROM users  -- 第二次查询：4条记录（幻读）
WHERE age BETWEEN 20 AND 30;
COMMIT;

特点：

• 针对查询结果集的行数变化
• 主要由INSERT/DELETE操作引起
• 新增或删除了满足条件的记录

幻读与不可重复读的区别：

对比项	不可重复读	幻读
影响范围	单行数据	结果集行数
主要原因	UPDATE操作	INSERT/DELETE操作
表现形式	数据内容变化	记录数量变化

读已提交（Read Committed）

实现机制：

语句级快照（MySQL）

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

• 每个SELECT语句开始时创建新的Read View
• 只能读取已提交事务的数据
• 同一事务内可能读取到不同的结果

提交读隔离（PostgreSQL）

• 读取数据时检查元组的xmax
• 确保读取的数据来自已提交事务

解决脏读问题

通过Read View机制确保只读取已提交的数据版本。

仍存在的问题：不可重复读和幻读

不可重复读在RC级别的表现

-- 会话1（事务A）              会话2（事务B）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;                        BEGIN;
SELECT balance FROM accounts  -- 读取：1000
WHERE id = 1;                 UPDATE accounts SET balance = 2000 WHERE id = 1;COMMIT;
SELECT balance FROM accounts  -- 读取：2000（不可重复读）
WHERE id = 1;                 
COMMIT;

原因分析：RC级别下，每个SELECT语句都会创建新的Read View，能看到其他事务已提交的修改。

幻读在RC级别的表现

-- 会话1（事务A）              会话2（事务B）
BEGIN;                        BEGIN;
SELECT COUNT(*) FROM users    -- 返回：5条记录
WHERE age > 25;               INSERT INTO users VALUES (6, 'Alice', 28);COMMIT;
SELECT COUNT(*) FROM users    -- 返回：6条记录（幻读）
WHERE age > 25;               
COMMIT;

可重复读（Repeatable Read）

MySQL InnoDB实现：

事务级快照

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

• 事务开始时创建Read View
• 整个事务期间使用同一快照
• 保证可重复读

Next-Key Lock防幻读

• Gap Lock：锁定索引记录间的间隙
• Record Lock：锁定索引记录本身
• Next-Key Lock：Gap Lock + Record Lock

-- 示例：防止幻读
SELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE;
-- 会锁定age在[20,30]范围内的记录和间隙

解决不可重复读问题

事务级快照的作用

-- 演示可重复读解决不可重复读问题
-- 会话1（事务A）              会话2（事务B）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;                        BEGIN;
SELECT balance FROM accounts  -- 读取：1000（创建Read View）
WHERE id = 1;                 UPDATE accounts SET balance = 2000 WHERE id = 1;COMMIT;
SELECT balance FROM accounts  -- 读取：1000（使用同一Read View，可重复读）
WHERE id = 1;                 
COMMIT;

MySQL InnoDB对幻读的特殊处理

Next-Key Lock机制详解

MySQL InnoDB在RR级别通过Next-Key Lock机制能够防止大部分幻读：

-- 防止幻读的示例
-- 会话1（事务A）              会话2（事务B）
BEGIN;                        BEGIN;
SELECT * FROM users           -- 加Next-Key Lock
WHERE age BETWEEN 20 AND 30   
FOR UPDATE;                   INSERT INTO users VALUES (6, 'Bob', 25);-- 被阻塞，等待锁释放
COMMIT;                       -- 此时事务B才能继续COMMIT;

Next-Key Lock的锁定范围

假设age字段有索引，现有记录age值为：[10, 20, 30, 40]

-- 查询条件：WHERE age = 25 FOR UPDATE
-- 锁定范围：(20, 30]，包括：
-- 1. Gap Lock: (20, 30) 间隙
-- 2. Record Lock: age=30的记录

快照读的幻读现象

即使在RR级别，快照读仍可能出现幻读：

-- 会话1（事务A）              会话2（事务B）
BEGIN;                        BEGIN;
SELECT COUNT(*) FROM users    -- 快照读：返回5条
WHERE age > 25;               INSERT INTO users VALUES (6, 'Charlie', 28);COMMIT;
SELECT COUNT(*) FROM users    -- 快照读：仍返回5条（无幻读）
WHERE age > 25;               
-- 但是如果使用当前读：
SELECT COUNT(*) FROM users    -- 当前读：返回6条（出现幻读）
WHERE age > 25 FOR UPDATE;    
COMMIT;

PostgreSQL实现：

SSI（可序列化快照隔离）前身

PostgreSQL在RR级别使用快照隔离，但不能完全防止所有异常：

-- PostgreSQL RR级别的限制
-- 仍可能出现写偏序异常（Write Skew）
-- 会话1                      会话2
BEGIN ISOLATION LEVEL REPEATABLE READ;BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT sum(balance) FROM      SELECT sum(balance) FROM 
accounts WHERE id IN (1,2);   accounts WHERE id IN (1,2);
-- 假设总和为1000              -- 总和也是1000
UPDATE accounts SET           UPDATE accounts SET
balance = balance - 100       balance = balance - 100
WHERE id = 1;                 WHERE id = 2;
COMMIT;                       COMMIT;
-- 结果：总和变为800，违反了业务约束（总和应>=1000）

串行化（Serializable）

串行化的核心目标

完全消除所有并发异常，包括脏读、不可重复读、幻读和各种异常现象，确保事务的执行效果等同于串行执行。

实现方式：

表级锁强制串行（传统方式）

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;-- 示例：传统串行化实现
-- 会话1                      会话2
BEGIN;                       BEGIN;
SELECT * FROM accounts;      SELECT * FROM accounts;
-- 可能对整个表加锁           -- 等待锁释放
UPDATE accounts SET          
balance = balance + 100      
WHERE id = 1;                
COMMIT;                      -- 会话2继续执行UPDATE accounts SETbalance = balance - 50WHERE id = 2;COMMIT;

缺点：严重影响并发性能，基本退化为单线程执行。

SSI冲突检测（现代实现）

核心思想：通过检测读写依赖关系，识别可能破坏串行化的模式。

SSI的工作原理

1. 依赖图构建：记录事务间的读写依赖关系
2. 环路检测：检测依赖图中是否存在环路
3. 选择回滚：发现环路时选择合适的事务回滚

具体实现示例（PostgreSQL）

-- 写偏序检测示例
-- 会话1                      会话2
BEGIN ISOLATION LEVEL SERIALIZABLE;BEGIN ISOLATION LEVEL SERIALIZABLE;
-- 读取约束检查
SELECT sum(balance) FROM     SELECT sum(balance) FROM
accounts WHERE id IN (1,2);  accounts WHERE id IN (1,2);
-- 返回：1000                -- 返回：1000-- 并发写入
UPDATE accounts SET          UPDATE accounts SET
balance = balance - 200      balance = balance - 200
WHERE id = 1;                WHERE id = 2;COMMIT;                      COMMIT;
-- 第二个事务可能被回滚：ERROR: could not serialize access

SSI的优势

• 高并发：避免不必要的锁等待
• 自动检测：无需程序员手动处理冲突
• 精确控制：只回滚真正冲突的事务

不同数据库的串行化实现

MySQL InnoDB Serializable

-- MySQL的串行化实现
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;-- 所有SELECT自动加共享锁
-- 会话1                      会话2
BEGIN;                       BEGIN;
SELECT * FROM users          -- 自动加S锁
WHERE age > 25;              SELECT * FROM users FOR UPDATE; -- 等待S锁释放
COMMIT;                      -- 现在可以获取X锁COMMIT;

特点：

• 所有SELECT语句自动加共享锁
• 基于锁机制实现，可能导致死锁
• 性能影响较大

PostgreSQL SSI

-- PostgreSQL的SSI实现
BEGIN ISOLATION LEVEL SERIALIZABLE;-- 读写冲突的高级检测
-- 能检测到复杂的异常模式，如：
-- 1. 读写反依赖（rw-antidependency）
-- 2. 写读反依赖（wr-antidependency）  
-- 3. 写写冲突（ww-conflict）SELECT pg_current_xact_id(); -- 获取事务ID
-- 系统自动维护SIREAD锁进行冲突检测

Oracle Serializable

-- Oracle基于回滚段实现
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;-- 使用语句级一致性
-- 如果检测到冲突，抛出ORA-08177错误
SELECT * FROM accounts FOR UPDATE;
-- 可能抛出：ORA-08177: can't serialize access for this transaction

串行化异常处理

序列化失败的处理

// 应用层重试机制示例
public void transferMoney(int fromId, int toId, int amount) {int retryCount = 0;int maxRetries = 3;while (retryCount < maxRetries) {try {// 设置串行化隔离级别connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);connection.setAutoCommit(false);// 执行转账操作debitAccount(fromId, amount);creditAccount(toId, amount);connection.commit();return; // 成功完成} catch (SQLException e) {if (isSerializationFailure(e)) {retryCount++;// 等待随机时间后重试Thread.sleep((long)(Math.random() * 1000));continue;} else {// 其他错误，直接抛出throw e;}}}throw new RuntimeException("转账失败，超过最大重试次数");
}

性能与一致性权衡

一致性保证

• 完全串行化：事务执行效果等同于某种串行顺序
• 无并发异常：消除所有读写冲突
• 强一致性：满足最严格的ACID要求

性能影响

• 吞吐量下降：频繁的冲突检测和回滚
• 延迟增加：事务可能需要多次重试
• 资源消耗：维护依赖图的额外开销

适用场景分析

-- 适用场景：金融交易系统
-- 对一致性要求极高，可以容忍性能损失
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- 复杂的账户余额检查和更新
SELECT sum(balance) FROM accounts WHERE customer_id = ?;
UPDATE accounts SET balance = balance - ? WHERE account_id = ?;
INSERT INTO transactions (from_account, amount, timestamp) VALUES (?, ?, ?);
COMMIT;-- 不适用场景：高并发读取为主的系统
-- 大量SELECT操作，对性能要求高
-- 建议使用READ COMMITTED或REPEATABLE READ

监控和调优

序列化冲突监控

-- PostgreSQL监控序列化冲突
SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables 
WHERE schemaname = 'public';-- 查看序列化冲突统计
SELECT * FROM pg_stat_database 
WHERE datname = 'your_database';

调优策略

1. 减少事务时间：尽快提交或回滚
2. 优化查询顺序：按固定顺序访问资源
3. 合理的重试策略：指数退避算法
4. 监控冲突频率：调整业务逻辑减少冲突

隔离级别配置

MySQL：transaction-isolation参数

-- 全局设置
SET GLOBAL transaction_isolation = 'READ-COMMITTED';-- 会话设置
SET SESSION transaction_isolation = 'REPEATABLE-READ';-- 配置文件
[mysqld]
transaction-isolation = REPEATABLE-READ

PostgreSQL：default_transaction_isolation

-- 设置默认隔离级别
SET default_transaction_isolation = 'read committed';-- 单个事务设置
BEGIN ISOLATION LEVEL SERIALIZABLE;

Oracle：SET TRANSACTION ISOLATION LEVEL

-- Oracle支持的隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

四、锁机制

锁基础

定义与作用

锁是数据库用来控制并发访问的机制，确保数据的一致性和完整性。主要作用：

• 防止并发修改冲突
• 维护数据一致性
• 控制访问顺序
• 实现事务隔离

锁粒度：行级锁、表级锁、页级锁

• 行级锁：锁定单行记录，并发度最高
• 页级锁：锁定数据页，介于行锁和表锁之间
• 表级锁：锁定整个表，并发度最低但开销小

锁模式分类

• 共享锁（S锁）：读锁，允许多个事务同时读取
• 排他锁（X锁）：写锁，独占访问
• 意向锁（I锁）：表示事务想要获取更细粒度的锁

锁类型详解

共享锁（S锁/读锁）

兼容性规则

• S锁与S锁兼容
• S锁与X锁冲突
• 多个事务可同时持有S锁

加锁/释放时机

-- 显式加共享锁
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;-- 自动加锁（某些情况下）
SELECT * FROM users WHERE id = 1;  -- 可能加S锁

排他锁（X锁/写锁）

冲突规则

• X锁与任何锁都冲突
• 一次只能有一个事务持有X锁
• 写操作必须获取X锁

应用场景

-- 显式加排他锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;-- DML操作自动加X锁
UPDATE users SET name = 'new' WHERE id = 1;
INSERT INTO users VALUES (2, 'user2');
DELETE FROM users WHERE id = 1;

意向锁（Intention Lock）

意向共享锁（IS）

• 表示事务想要在表的某些行上获取S锁
• 在获取行级S锁前必须先获取表级IS锁

意向排他锁（IX）

• 表示事务想要在表的某些行上获取X锁
• 在获取行级X锁前必须先获取表级IX锁

锁兼容性矩阵

	IS	IX	S	X
IS	✓	✓	✓	✗
IX	✓	✓	✗	✗
S	✓	✗	✓	✗
X	✗	✗	✗	✗

特殊锁类型

间隙锁（Gap Lock）

-- 锁定索引记录之间的间隙
SELECT * FROM users WHERE id BETWEEN 10 AND 20 FOR UPDATE;
-- 防止在(10,20)范围内插入新记录

Next-Key Lock

-- Record Lock + Gap Lock
SELECT * FROM users WHERE id <= 10 FOR UPDATE;
-- 锁定id<=10的记录以及(10,+∞)的间隙

插入意向锁（Insert Intention Lock）

• 特殊的间隙锁，用于INSERT操作
• 多个事务可以同时持有同一间隙的插入意向锁
• 只有在插入不同位置时才兼容

锁协议

两阶段锁协议（2PL）

加锁阶段

• 事务只能加锁，不能释放锁
• 可以获取新的锁

解锁阶段

• 事务只能释放锁，不能获取新锁
• 开始释放锁后不能再获取锁

严格两阶段锁协议

• 事务持有的所有排他锁在事务提交或回滚时才释放
• 保证避免级联回滚

锁超时与死锁处理

-- 锁等待超时设置
SET GLOBAL innodb_lock_wait_timeout = 50;-- 死锁检测
SHOW ENGINE INNODB STATUS\G
-- 查看LATEST DETECTED DEADLOCK部分

数据库锁实现差异

MySQL InnoDB锁机制

行锁实现（聚簇索引）

• 基于聚簇索引实现行锁
• 无索引时升级为表锁
• 支持Next-Key Lock机制

锁等待超时参数（innodb_lock_wait_timeout）

-- 查看当前设置
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';-- 设置锁等待超时
SET GLOBAL innodb_lock_wait_timeout = 50;

PostgreSQL锁机制

表级锁与行级锁分离

• 表级锁：8种不同的锁模式
• 行级锁：基于元组实现
• 锁升级：自动进行锁升级

锁升级策略

• 当行锁数量超过阈值时升级为表锁
• 减少锁管理开销

Oracle锁机制

行级锁无索引退化问题

• 使用索引时精确行锁
• 无索引时可能锁定大量行
• 基于ROWID实现行锁

锁优化实践

索引对锁粒度的影响

-- 有索引：精确行锁
UPDATE users SET name = 'new' WHERE id = 1;  -- id是主键-- 无索引：可能表锁
UPDATE users SET name = 'new' WHERE email = 'user@example.com';  -- email无索引

避免长事务持有锁

• 缩短事务执行时间
• 及时提交事务
• 避免在事务中进行耗时操作

死锁检测与预防

-- 统一加锁顺序
-- 事务1和事务2都按照id升序加锁
UPDATE table1 SET ... WHERE id = 1;
UPDATE table2 SET ... WHERE id = 2;-- 使用较短的锁超时时间
SET innodb_lock_wait_timeout = 10;

锁竞争监控工具

-- 查看锁等待情况
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;-- 查看事务状态
SELECT * FROM information_schema.INNODB_TRX;-- 监控死锁
SHOW ENGINE INNODB STATUS\G

---

总结

MySQL的事务机制通过ACID特性、MVCC、隔离级别和锁机制的协同工作，为数据库提供了强大的并发控制和数据一致性保障。理解这些机制的实现原理和相互关系，对于数据库性能优化和问题排查具有重要意义。

在实际应用中，需要根据具体的业务场景选择合适的隔离级别，合理设计索引以优化锁粒度，避免长事务和死锁，从而获得最佳的性能表现。