SQL Server中的交易隔离水平100％可靠吗？
我正在对SQL Server中的隔离级别进行一些测试。 首先，我创建一个名为“测试”的表：
然后我在同一的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, 1);
commit transaction;
set @count = @count+1;
end;
第二个线程只需更改线程编号：
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;
在读取订单模式下，当另一个线程不提交时，代码应等待读取最大序列数，这意味着它们不会生成相同的sequence_no。 但这通常给我一个错误：
违反主密钥约束“ pk_test”。无法在对象“ dbo.test-db”中插入重复键。重复的键值为（AAA，2402）。
我再次以可重复的读取模式进行测试，并且相同。
有人可以解释为什么交易之间的阅读查询会崩溃吗？
这是否意味着隔离水平不是100％可靠的？
I'm doing some test about the isolation level in SQL Server.
First I create a table called test with this structure:
Then I run the test code with 2 threads at the same time:
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;
The second thread just change the thread number:
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;
Under read committed mode, the code should wait for read the max number of sequence when the other thread is not commit, which means they won't generate a same sequence_no.
But it often give me a error:
Violation of PRIMARY KEY constraint 'PK_test'. Cannot insert duplicate key in object 'dbo.test-db'. The duplicate key value is (AAA, 2402).
I test it in repeatable read mode again, and it's the same.
Can someone explain why the reading query between transaction will get crash?
Does it means that isolation level isn't 100% reliable?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您需要使用可序列化的隔离，并且还需要一个updlock来避免僵局：
这对于并发很糟糕，因此您应该真正使用身份或序列。
You need to use SERIALIZABLE isolation and an UPDLOCK is also required to avoid deadlocks:
This is awful for concurrency so you should really use an identity or a sequence.