SQL Server 竞争条件问题 [英] SQL Server Race Condition Question

问题描述

(注意:这是针对 MS SQL Server)

(Note: this is for MS SQL Server)

假设您有一个表 ABC，其中包含一个主键标识列和一个 CODE 列.我们希望这里的每一行都有一个唯一的、按顺序生成的代码(基于一些典型的校验位公式).

Say you have a table ABC with a primary key identity column, and a CODE column. We want every row in here to have a unique, sequentially-generated code (based on some typical check-digit formula).

假设您有另一个只有一行的表 DEF，用于存储下一个可用的 CODE(想象一个简单的自动编号).

Say you have another table DEF with only one row, which stores the next available CODE (imagine a simple autonumber).

我知道像下面这样的逻辑会出现竞争条件，其中两个用户可能会得到相同的代码:

I know logic like below would present a race condition, in which two users could end up with the same CODE:

1) Run a select query to grab next available code from DEF
2) Insert said code into table ABC
3) Increment the value in DEF so it's not re-used.

我知道，两个用户可能会卡在第 1) 步，最终可能会在 ABC 表中得到相同的 CODE.

I know that, two users could get stuck at Step 1), and could end up with same CODE in the ABC table.

处理这种情况的最佳方法是什么?我以为我可以围绕这个逻辑包装一个begin tran"/commit tran"，但我认为这行不通.我有一个这样的存储过程来测试，但是当我在 MS 中从两个不同的窗口运行时，我没有避免竞争条件:

What is the best way to deal with this situation? I thought I could just wrap a "begin tran" / "commit tran" around this logic, but I don't think that worked. I had a stored procedure like this to test, but I didn't avoid the race condition when I ran from two different windows in MS:

begin tran

declare @x int

select   @x= nextcode FROM  def

waitfor delay '00:00:15'

update def set nextcode = nextcode + 1

select @x

commit tran

有人可以对此有所了解吗?我认为该交易会阻止其他用户在第一个交易完成之前访问我的 NextCodeTable，但我想我对交易的理解是有缺陷的.

Can someone shed some light on this? I thought the transaction would prevent another user from being able to access my NextCodeTable until the first transaction completed, but I guess my understanding of transactions is flawed.

我尝试将等待移到更新"语句之后，我得到了两个不同的代码......但我怀疑.我在那里有 waitfor 语句来模拟延迟，因此可以很容易地看到竞争条件.我认为关键问题是我对交易运作方式的错误认识.

I tried moving the wait to after the "update" statement, and I got two different codes... but I suspected that. I have the waitfor statement there to simulate a delay so the race condition can be easily seen. I think the key problem is my incorrect perception of how transactions work.

推荐答案

将事务隔离级别设置为可序列化.
在较低的隔离级别，其他事务可以读取该事务中已读取(但尚未修改)的行中的数据.所以两个事务确实可以读取相同的值.在非常低的隔离(读取未提交)下，其他事务甚至可以在修改后(但在提交之前)读取数据......

Set the Transaction Isolation Level to Serializable.
At lower isolation levels, other transactions can read the data in a row that is read, (but not yet modified) in this transaction. So two transactions can indeed read the same value. At very low isolation (Read Uncommitted) other transactions can even read data after it's been modified (but before committed)...

查看有关 SQL Server 隔离级别的详细信息此处

Review details about SQL Server Isolation Levels here

所以最重要的是，隔离级别在这里至关重要，它可以控制其他事务进入该级别的访问级别.

So bottom line is that the Isolation level is crtitical piece here to control what level of access other transactions get into this one.

注意.从链接，关于Serializable
语句无法读取其他事务已修改但尚未提交的数据.
这是因为锁是在修改行时放置的，而不是在 Begin Trans 发生时放置的，所以您所做的可能仍然允许另一个事务读取旧值，直到您修改它为止.所以我会改变逻辑，在你阅读它的同一个语句中修改它，从而同时锁定它.

NOTE. From the link, about Serializable
Statements cannot read data that has been modified but not yet committed by other transactions.
This is because the locks are placed when the row is modified, not when the Begin Trans occurs, So what you have done may still allow another transaction to read the old value until the point where you modify it. So I would change the logic to modify it in the same statement as you read it, thereby putting the lock on it at the same time.

begin tran
declare @x int
update def set @x= nextcode, nextcode += 1
waitfor delay '00:00:15'
select @x
commit tran

这篇关于SQL Server 竞争条件问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！