问题描述
我有 100 个线程,每个线程都调用如下定义的存储过程.
I've got 100 threads that are each calling the stored procedure as defined below.
如何防止脏读?
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF GO ALTER procedure GetNextCerealIdentity (@NextKey int output, @TableID int) AS declare @RowCount int, @Err int set nocount on select @NextKey = 0 begin transaction Again: /*Update CfgCerealNumber Table */ UPDATE CfgCerealNumber SET CerealNumber = CerealNumber + 1 WHERE CerealNumberID = @TableID SELECT @RowCount = @@RowCount, @Err = @@Error /*Obtain updated Cereal number previously incremented*/ IF @Err <> 0 /* If Error gets here then exit */ BEGIN RAISERROR ('GetNextCerealIDSeries Failed with Error: %d TableID: %d ', 16, 1, @Err, @TableID) ROLLBACK TRANSACTION set nocount off return 1 END IF @RowCount = 0 /* No Record then assume table is not */ /* been initialized for TableID Supplied*/ BEGIN RAISERROR('No Table Record Exists in CfgCerealNumber for ID:%d ', 16, 1, @TableID) set nocount off Rollback Transaction return 1 END /*Obtain updated Cereal number previously incremented*/ SELECT @NextKey = CerealNumber FROM CfgCerealNumber WHERE CerealNumberID = @TableID SELECT @Err = @@Error /*Obtain updated Cereal number previously incremented*/ IF @Err <> 0 /* If Error gets here then exit */ BEGIN RAISERROR('GetNextCerealIDSeries Failed with Error: %d TableID: %d ', 16, 1, @Err, @TableID) Rollback Transaction set nocount off return 1 END commit transaction set nocount off return 0 GO
看起来这部分存储过程在并行运行时大约有 0.01% 的时间返回相同的值:
It looks like this part of the stored procedure is returning the same value around 0.01% of the time when run in parallel:
SELECT @NextKey = CerealNumber FROM CfgCerealNumber WHERE CerealNumberID = @TableID
我通过将更新包装在事务中来以防止脏读的方式构建我的代码.
I've structured my code in such a way to prevent dirty reads by wrapping the update in a transaction.
如何防止脏读?
推荐答案
如果您需要更新并返回您更新的内容,那么我会使用 OUTPUT 子句:
If you need to update and return what you updated, then I would just use the OUTPUT clause:
UPDATE CfgCerealNumber SET CerealNumber = CerealNumber + 1 OUTPUT INSERTED.CerealNumber WHERE CerealNumberID = @TableID;
如果你需要额外的检查,你可以在从存储过程返回结果集之前输出到一个声明的表变量中.
If you need additional checking, you can OUTPUT into a declared table variable before returning the result set from the stored procedure.
另一种选择是先在表上创建一个阻塞锁,然后更新:
Another alternative would be to create a blocking lock on the table first, and then update:
SELECT @CerealNumber = CerealNumber + 1 FROM CfgCerealNumber WITH (HOLDLOCK, UPDLOCK) WHERE CerealNumberID = @TableID; UPDATE CfgCerealNumber SET CerealNumber = @CerealNumber WHERE CerealNumberID = @TableID;
但我会放下钱,因为我已经看到这仍然会导致问题.我不太相信它.
But I would put money down that I've seen this still cause problems. I trust it much less.