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

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?

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?

Indeed it does... – Brehtt Jun 16 '09 at 0:46 I've updated the question to reflect the situation more completely – Brehtt Jun 16 '09 at 1:02 I've changed the isolation level to snapshot, which seems to have alleviated my deadlock woes. Thanks for your help :) – Brehtt Jun 16 '09 at 4:37 Also, I realise that snapshot isolation will not prevent the case where duplicate tags are associated with the same entity. For this I will create a unique key constraint and deal with the potential error gracefully – Brehtt Jun 16 '09 at 4:40.

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

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.

The transaction scope is so I can Roll the changes back if something goes wrong elsewhere, or at the ver least, when the uni test finishes. – Brehtt Jun 15 '09 at 8:13 @downvoter: Why the downvote? If you don't say what you think is wrong, it can't improve the answer in any way.

– Guffa Nov 2 '10 at 10:59.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.