为什么以下SQL Server在事务中运行时会插入死锁？

I'm currently inserting a record into a SQL Server Table and then selecting the auto-increment ID as follows:

我目前正在将记录插入SQL Server表,然后选择自动增量ID,如下所示:

(@p0 int,@p1 nvarchar(8))INSERT INTO [dbo].[Tag]([Some_Int], [Tag])
VALUES (@p0, @p1)

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value] 

(This was generated using Linq-to-SQL). For some reason when I run this code inside a transaction using the TransactionScope object with a Serializable isolation level, SQL Server throws a deadlock error. I analyzed the deadlock graph events and found that the two processes involved were each waiting on the other to perform the convert operation, as I understand the following information:

(这是使用Linq-to-SQL生成的)。出于某种原因,当我使用具有Seri​​alizable隔离级别的TransactionScope对象在事务中运行此代码时,SQL Server会引发死锁错误。我分析了死锁图事件,发现所涉及的两个进程都在等待另一个进行转换操作,因为我了解以下信息:

<resource-list>
   <keylock hobtid="72057594101170176" dbid="5" objectname="foo.dbo.Tag" indexname="PK_Tag_1" id="lockb77cdc0" mode="RangeS-S" associatedObjectId="72057594101170176">
    <owner-list>
     <owner id="processc9be40" mode="RangeS-S"/>
    </owner-list>
    <waiter-list>
     <waiter id="processc9ae38" mode="RangeI-N" requestType="convert"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594101170176" dbid="5" objectname="foo.dbo.Tag" indexname="PK_Tag_1" id="lockb77cdc0" mode="RangeS-S" associatedObjectId="72057594101170176">
    <owner-list>
     <owner id="processc9ae38" mode="RangeS-S"/>
    </owner-list>
    <waiter-list>
     <waiter id="processc9be40" mode="RangeI-N" requestType="convert"/>
    </waiter-list>
   </keylock>
  </resource-list>

My understanding was that the transaction scope would prevent the second process from performing the insert until the first had finished both inserting and selecting the identity. However this doesn't seem to be the case. Could anyone shed some light on the best approach to achieving what I require in a thread-safe way?

我的理解是,事务范围将阻止第二个进程执行插入,直到第一个进程完成插入和选择标识。然而,情况似乎并非如此。任何人都能解释一下以线程安全的方式实现我需要的最佳方法吗?

--Updated--

Just to note; I'm 99% sure that a connection isn't being shared between the two processes as each creates a new DataContext to communicate with the database.

请注意;我99%确定两个进程之间没有共享连接,因为每个进程都创建一个新的DataContext来与数据库通信。

--Updated Again--

Remus Rusanu pointed out that some omitted information was related to the problem, I tried to simplify the scenario based on the deadlock graph report, but I've extended the explanation here. Before I do the insert I perform an exists query on the table in question to determine if the tag already exists. If it does I end the transaction. If not the insert should go ahead and then I perform an update, not shown here, on a table that has Some_Int as the primary key, though the update is purely for a last modified value. It may also be of importance that the Tag table has a clustered index composed of both the auto inc ID and Some_Int. I didn't think this last piece of information was of relevance as I have tried changing the table to only have the auto inc field as the primary key / clustered index to no avail.

Remus Rusanu指出一些省略的信息与问题有关,我试图根据死锁图报告简化场景,但我在这里扩展了解释。在我执行插入操作之前,我对相关表执行存在查询以确定标记是否已存在。如果是的话我结束了交易。如果不是,插入应该继续,然后我在具有Some_Int作为主键的表上执行此处未示出的更新,尽管更新纯粹是针对最后修改的值。 Tag表具有由auto inc ID和Some_Int组成的聚簇索引也很重要。我不认为这最后一条信息具有相关性,因为我尝试将表更改为仅将auto inc字段作为主键/聚簇索引无效。

Thanks.

3 个解决方案

#1

The 'convert' in question is a 'lock convert' from RangeS-S to RangeI-N, not related to the 'CONVERT' function in any way. The fact that you have RangeS-S locks already placed on the PK_Tag_1 index indicates that you're doing something more than just an INSERT. Does your transaction does, by any chance, a check first to see if the the new record 'exists' before attempting the insert?

有问题的'转换'是从RangeS-S到RangeI-N的'锁定转换',与任何方式的'CONVERT'功能无关。您已将RangeS-S锁置于PK_Tag_1索引上的事实表明您正在做的不仅仅是INSERT。在尝试插入之前,您的交易是否确实先检查新记录是否“存在”?

#2

Check the isolationLevel, which is used in your query. Note, that TransactionScope uses Serializable isolation level by default (http://msdn.microsoft.com/en-us/library/ms172152.aspx). Try change the isolation level of your transaction to Read Commited.

检查查询中使用的isolationLevel。请注意,TransactionScope默认使用Serializable隔离级别(http://msdn.microsoft.com/en-us/library/ms172152.aspx)。尝试将事务的隔离级别更改为Read Commited。

#3

You don't need a transaction at all. The scope_identity() function will return the id last created in the same scope, so there is no problem if another insert is executed before you get the id, as that is in a different scope.

您根本不需要交易。 scope_identity()函数将返回最后在同一范围内创建的id,因此如果在获取id之前执行了另一个插入,则没有问题,因为它位于不同的范围内。