SQL Server事务隔离级别是否100%可靠？并发插入主键重复问题

测试场景 #

测试前首先创建名为test的业务表，表结构如下：

测试时同时启动2个线程，各循环5000次执行事务逻辑：读取表中sequence_no字段的最大值，将值+1后作为新记录的sequence_no插入表中，两个线程插入的记录仅thread字段值分别为1、2做区分。

线程1测试代码 #

use test-db;
go
declare @count int = 0;
while @count<5000
begin
  set transaction isolation level read committed;
  begin transaction;

  declare @max int;
  select @max = coalesce(max(sequence_no),0) from test;
  print @max;
  insert into test (prefix, sequence_no, thread) values ('AAA', @max+1, 1);

  commit transaction;
  set @count = @count+1;
end;

线程2测试代码 #

use test-db;
go
declare @count int = 0;
while @count<5000
begin
  set transaction isolation level read committed;
  begin transaction;

  declare @max int;
  select @max = coalesce(max(sequence_no),0) from test;
  print @max;
  insert into test (prefix, sequence_no, thread) values ('AAA', @max+1, 2);


  commit transaction;
  set @count = @count+1;
end;

异常现象 #

测试前预期：在READ COMMITTED（读已提交）隔离级别下，其他线程未提交事务时，当前线程读取sequence_no最大值的操作会阻塞等待，不会生成重复的sequence_no。但实际测试频繁触发主键冲突报错：

Violation of PRIMARY KEY constraint 'PK_test'. Cannot insert duplicate key in object 'dbo.test-db'. The duplicate key value is (AAA, 2402).

将事务隔离级别切换为REPEATABLE READ（可重复读）后重新测试，仍然出现相同的主键冲突问题。

这个问题和事务隔离级别是否可靠无关，核心是对不同隔离级别的锁机制、兼容逻辑存在认知偏差：

• 首先明确锁兼容规则：读操作默认加的共享锁（S锁）是互相兼容的，多个事务可以同时持有同一资源的共享锁，不会互相阻塞；只有当资源上存在排他锁（X锁，写操作持有）时，其他事务的读/写请求才会被阻塞。
• READ COMMITTED级别下，读操作的共享锁在读取完成后会立刻释放，不会持有到事务结束。两个线程完全可以先后执行读查询，都顺利拿到共享锁读到同一个最大sequence_no值，等双方都执行插入时，就会生成重复键触发主键冲突。
• REPEATABLE READ级别下，虽然共享锁会持有到事务提交，但依然无法解决问题：一是共享锁之间本身兼容，两个线程还是能同时读到同一个最大值；二是该级别仅会锁定查询命中的已有数据行，不会锁定不存在的插入间隙，无法阻止两个事务基于同一个读取结果生成插入值。

你预期的「读操作阻塞等待」只有在其他事务已经持有对应资源的排他锁时才会触发，而两个线程第一步都是读操作，加的共享锁互不冲突，自然不会出现阻塞。

正确实现方案 #

要实现并发场景下不重复的自增序列，不要依赖默认隔离级别的隐式锁，可选方案如下：

• 读查询时显式加更新锁和持有锁提示，将查询语句改为select @max = coalesce(max(sequence_no),0) from test with (UPDLOCK, HOLDLOCK);。更新锁（U锁）同一时间仅允许一个事务持有，配合HOLDLOCK让锁持有到事务提交，就能保证同一时间只有一个事务能读取到最大值，从根源避免重复值。
• 优先使用SQL Server引擎内置的并发安全序列能力：比如标识列（IDENTITY）、独立序列对象（SEQUENCE），这类能力在引擎层面做了并发优化，性能远高于手动加锁实现的序列逻辑，不会出现重复值问题。
• 不建议在业务表中通过max()函数生成序列，高并发下性能很差，单独维护序列配置表存当前序列值，更新时通过锁行拿新值的方案效率更高。

事务隔离级别本身的设计目标是解决不同级别的数据读一致性问题：READ COMMITTED保证不会读到未提交的脏数据，REPEATABLE READ保证事务内多次读取同一已存在行的结果一致，它本身不负责解决业务层面「先读后写」的并发逻辑冲突，这类冲突需要显式加锁或者使用内置的并发安全组件处理，不存在「隔离级别不可靠」的问题。

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