SQL Server-从TRANSACTION复制，SELECT / INSERT存在“唯一”值

I am really new to SQL Transaction management, and I have been reading up a lot on transaction management, specifically concurrency issues and isolation levels.

我是SQL事务管理的新手,我一直在阅读很多关于事务管理的内容,特别是并发问题和隔离级别。

I have a rather 'simple' problem that I can't seem to solve-

我有一个相当“简单”的问题,我似乎无法解决 -

The database has a table of consumers with a primary key, ConsumerID, and an indexed (but not unique) field that should be unique, SSN. We have a stored procedure to handle incoming loan requests that essentially has this block of code to maintain the Consumer table:

数据库有一个消费者表,其中包含主键,ConsumerID和应该唯一的索引(但不是唯一)字段SSN。我们有一个存储过程来处理传入的贷款请求,这些请求本质上有这个代码块来维护Consumer表:

    SELECT @ConsumerID = ConsumerID FROM Consumer WHERE SSN = @SSN
    IF @ConsumerID IS NULL
    BEGIN
        INSERT INTO Consumer (SSN, ...) VALUES (@SSN, ...)
        SELECT @ConsumerID = SCOPE_IDENTITY()
    END

We have been getting instances where two requests to the stored procedure come in simultaneously, and (of course) we are getting duplicate SSN records in the Consumer table. Making the SSN index UNIQUE is on the table, but not something we can do right now. The solution was to use a TRANSACTION:

我们一直在获得对存储过程的两个请求同时进入的实例,并且(当然)我们在Consumer表中获得了重复的SSN记录。制作SSN索引UNIQUE就在桌面上,但我们现在无法做到。解决方案是使用TRANSACTION:

    BEGIN TRANSACTION
    BEGIN TRY
        SELECT @ConsumerID = ConsumerID FROM Consumer WHERE SSN = @SSN
        IF @ConsumerID IS NULL
        BEGIN
            INSERT INTO Consumer (SSN, ...) VALUES (@SSN, ...)
            SELECT @ConsumerID = @SCOPE_IDENTITY
            COMMIT TRANSACTION
        END
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
    END CATCH

Now, this certainly prevents duplicate SSNs, because of the TRANSACTION. However, I did a concurrency test by calling the stored procedure asynchronously many times from two C# processes running at the same time. The performance is affected by the TRANSACTION on the order of several seconds. This is not acceptable since we rely on speed as the primary factor of our system responses.

现在,由于TRANSACTION,这肯定会阻止重复的SSN。但是,我通过从同时运行的两个C#进程多次异步调用存储过程来进行并发测试。性能受到TRANSACTION的影响,大约为几秒钟。这是不可接受的,因为我们依赖速度作为我们系统响应的主要因素。

How can I tweak this TRANSACTION to improve response time? I have a hunch it lies somewhere in optimistic concurrency (we use the default for SQL Server), but I don't understand how it works in relation to such a pair of SELECT - INSERT instructions. Remember, making the SSN index UNIQUE is a possibility, but one we may not wish to pursue right now.

如何调整此TRANSACTION以缩短响应时间?我有预感它位于乐观并发中(我们使用SQL Server的默认值),但我不明白它是如何与这样一对SELECT - INSERT指令相关的。请记住,使SSN索引UNIQUE是可能的,但我们可能不希望立即采用。

2 个解决方案

#1

You can make use of exclusive locks to make sure other users cannot insert duplicates while you are querying and inserting values by doing something like......

您可以使用独占锁来确保其他用户在查询和插入值时不能插入重复项......

BEGIN TRY
  BEGIN TRANSACTION;

    IF NOT EXISTS(SELECT 1 FROM Consumer WITH (UPDLOCK,HOLDLOCK) WHERE SSN = @SSN)
    BEGIN
        INSERT INTO Consumer (SSN, ...) VALUES (@SSN, ...)
        SELECT @ConsumerID = SCOPE_IDENTITY();
    END
  COMMIT TRANSACTION;
END TRY

BEGIN CATCH
 IF (@@TRANCOUNT <> 0)
     ROLLBACK TRANSACTION;
END CATCH

#2

1. You can test with SQL Insert Trigger for the table and rollback the transaction that will cause the duplicate values in the table. Compare the time taken for it.

   您可以使用SQL Insert Trigger测试表并回滚将导致表中重复值的事务。比较它所用的时间。

2. In case the above option is not possible. Minimize the work inside inside the transaction, if that is not possible then optimize the query for SELECT & INSERT or any other query inside the transaction.

   如果上述选项不可行。最小化事务内部的工作,如果不可能,则优化查询SELECT&INSERT或事务内的任何其他查询。

The query execution time will depends upon the number of index exists on the tables, number of records in the table. INSERT are faster with no or minimum Indexes, while SELECT may benefit well with it. Take a look at the Query execution plan.

查询执行时间将取决于表中存在的索引数,表中的记录数。 INSERT在没有索引或索引最小的情况下更快,而SELECT可能会受益匪浅。看一下Query执行计划。

create table test(id int)
Go

Create TRIGGER test_insert_one  
ON test  
AFTER INSERT
AS 
Declare @Count int
select @Count = count(id) from test where id = (select id from inserted)
IF(@Count > 1)
    Rollback;
GO  

insert into test
select 1
GO
insert into test
select 1
GO

select * from test
GO