如何正确设置事务锁？求4种隔离级别的示例及排他事务实现

一、四种隔离级别差异场景示例 #

假设我们有一张表test_table，初始数据为id=1, value=100，通过两个并发事务的操作对比不同隔离级别的表现：

1. READ UNCOMMITTED（未提交读） #

• 事务A：开启事务，更新id=1的value为200，不提交
• 事务B：设置隔离级别为READ UNCOMMITTED，查询id=1的value
• 结果：事务B直接读到事务A未提交的200（脏读）
• 特点：完全不做锁控制，性能最高，但数据一致性最差

2. READ COMMITTED（提交读，SQL Server默认级别） #

• 事务A：开启事务，更新id=1的value为200，不提交
• 事务B：设置隔离级别为READ COMMITTED，查询id=1的value
• 结果：事务B被阻塞，直到事务A提交/回滚；若A提交，B读到200；若A回滚，B读到原100（避免脏读）
• 局限：会出现不可重复读——事务B第一次查询到100，A提交更新后，B再次查询同一行会读到200，两次结果不一致

3. REPEATABLE READ（可重复读） #

• 事务B：设置隔离级别为REPEATABLE READ，开启事务，第一次查询id=1的value为100
• 事务A：尝试更新id=1的value为200，被阻塞直到事务B结束
• 结果：事务B第二次查询同一行，依然读到100（避免不可重复读）
• 局限：会出现幻读——事务B查询所有value<200的行得到1条，A插入id=2, value=150并提交，B再次查询会得到2条新行

4. SERIALIZABLE（可序列化） #

• 事务B：设置隔离级别为SERIALIZABLE，开启事务，查询所有value<200的行得到1条
• 事务A：尝试插入id=2, value=150，被阻塞直到事务B结束
• 结果：事务B再次执行同一查询，依然只读到1条（避免幻读）
• 特点：最高一致性，完全模拟串行执行，但锁范围最大、性能最差

二、你的并发创建场景解决方案 #

需求：多机器运行C++应用，确保仅创建一次foo表、唯一索引，且仅插入一条数据，单连接执行核心逻辑：

BEGIN TRANSACTION
CREATE TABLE IF NOT EXISTS foo (id INT); -- 补充字段定义，原语句语法需调整
CREATE UNIQUE INDEX IF NOT EXISTS foo_x ON foo(id);
INSERT INTO foo VALUES(1);
COMMIT

关键解决步骤： #

1. 设置SERIALIZABLE隔离级别
   在事务开始前执行：

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   

   该级别会对元数据和操作范围加锁，强制事务串行执行，避免多机器并发时的创建冲突。

2. 优化逻辑：先检查再操作
   调整代码，先检查表是否存在，再执行创建和插入，结合SERIALIZABLE的锁机制确保原子性：

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   BEGIN TRANSACTION
   IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'foo')
   BEGIN
       CREATE TABLE foo (id INT);
       CREATE UNIQUE INDEX foo_x ON foo(id);
       INSERT INTO foo VALUES(1);
   END
   COMMIT
   

   多机器同时执行时，只有第一个完成的事务会创建表和插入数据，后续事务检测到表已存在，直接跳过创建逻辑。

3. 额外保障：利用唯一索引的原子性
   若因特殊情况出现并发插入，唯一索引foo_x会抛出约束冲突错误，在C++代码中捕获该错误并忽略即可——这说明已有其他事务完成了插入操作。

4. 语法修正
   SQL Server中CREATE TABLE IF NOT EXIST需改为CREATE TABLE IF NOT EXISTS，且表定义必须包含字段（如示例中的id INT），否则会报错。

内容的提问来源于stack exchange，提问作者Igor