如何解决SQL Server存储过程中的并发插入问题 [英] How to solve concurrency Insert issue in SQL Server stored procedure

问题描述

我有一个存储过程，它以 @id 作为输入参数.表 Student 的主键位于 Id_Column 和 Count_Column 上.如果表中存在给定 id 的任何记录，那么我从表中选择最大 Count_Column ，并通过将max Count_Column 递增值为零的其他1个.

I have a stored procedure which takes @id as input parameter. Table Student has primary key on Id_Column and Count_Column. If any record is present in table for the given id, then I select the max Count_Column from table and insert new row by incrementing max Count_Column by 1 else with zero value.

我正在WCF服务中从ado.net代码中调用此存储过程，而该服务是从asp.net Web应用程序中调用的.

I'm calling this stored procedure from ado.net code in a WCF service and this service is called from an asp.net web application.

该存储过程在正常情况下可以正常工作，但是当多个用户同时调用它时，会发生主键冲突问题，我也通过使应用程序成为多线程来重现了这种情况.我知道这个问题与并发性有关，最初我是在select查询中使用 with(nolock)，但现在已经删除了它.

This stored procedure works fine in normal cases, but when multiple users calls it at same time, primary key violation issue happen, same case I have reproduced by making application multithreaded too. I know that this issue is related to concurrency, initially I was using with(nolock) in select query, but I have removed this now.

我读过一些地方，可以通过设置事务隔离级别来解决它，但是当我尝试时，我遇到了一些回滚事务异常的情况.

Somewhere I have read that by setting transaction isolation level it can be solved but when I tried I am some getting rollback transaction exception.

请让我知道该问题的任何有效解决方案.

Please let me know any efficient solution for this problem.

declare @iCount = 0;

if exists(select 'x' from Student with(nolock) where Id_Column = @iId)
begin
    set @iCount = (select max(Count_Column) 
                   from Student
                   where Id_Column = @iId)
end

insert into Student
values(@id, @iCount + 1);

第二种解决方法:

begin try
set transaction isolation level serializable
begin transaction
    declare @iCount = 0;

    if exists(select 'x' from from Student with(nolock) where Id_Column = @iId)
    begin
        set @iCount = (select max(Count_Column) 
                       from Student
                       where Id_Column = @iId)
    end

    insert into Student
    values(@id, @iCount + 1);

    commit transaction
end try
begin catch
    rollback transaction
end catch

推荐答案

尝试类似的方法……

BEGIN TRY
  BEGIN TRANSACTION;
     DECLARE @iCount INT;

    IF EXISTS(SELECT 1 FROM Student WITH(UPDLOCK,HOLDLOCK) WHERE Id_Column = @iId)
    BEGIN

      select @iCount = ISNULL(max(Count_Column), 0) + 1 
      from Student WITH(UPDLOCK,HOLDLOCK) where Id_Column = @iId

         insert into Student
         values(@id, @iCount);
    END
  COMMIT TRANSACTION;
END TRY

BEGIN CATCH

 IF (@@TRANCOUNT <> 0)
     ROLLBACK TRANSACTION;

END CATCH

重要提示

您实际上应该在此处使用 Identity 列来处理自动增量值.如果您使用的是sql server 2012或更高版本，则还可以选择使用 Sequence Object 还是自动增量.

You should really be using Identity column here to handle the auto-increment value. If you are using sql server 2012 or later you also have another option of using Sequence Object also an auto-increment .

这篇关于如何解决SQL Server存储过程中的并发插入问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！