Why does the following SQL Server insert deadlock when run within a transaction?

Question

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

(@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:

<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.

--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.

Thanks.

Answer 1

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.

Answer 2

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?

Answer 3

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.