Transaction (Process ID) was deadlocked

Question

I have an application lock for pessimistic concurrency and generate code in Asp.Net Mvc.

using (var scope = new TransactionScope())
{
      using (var context = new Context())
      {
           var submitedEntity = context.Entities.Add(entity);
           context.SaveChanges();
           context.Entry(submitedEntity).Reload();
           code = submitedEntity.Code;

           scope.Complete();
      }
}

And generate code in SQL Server Trigger

Create  Trigger     [dbo].[OnInsertEntity]
On  [dbo].[TableName]
After   Insert
As
Begin
Begin   Transaction
    Declare @ID     BigInt,
        @LastCode   Int,
        @Length     Int,
        @Code       nChar(5)

Select  @ID     = ID
    From    Inserted

Select  @LastCode   = Max(Convert(Int, Code))
    From    [TableName]
    With    (TABLOCKX, HOLDLOCK)

If  @@RowCount      = 0
    Set @LastCode   = 0

Waitfor Delay '00:00:05'

Select  @Length     = Max_Length
    From    Sys.Columns
    Where   Name        = N'Code'   And
        [Object_ID] = Object_ID('[TableName]')

Select  @LastCode   = @LastCode + 1,
    @Code       = Replicate('0', @Length - Len(@LastCode)) + Convert(nChar, @LastCode)


Update  [TableName]
    Set Code    = @Code
    Where   ID  = @ID
Commit  Transaction
End

Error at:

catch (DbUpdateException ex)

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

What is the problem?

Thanks.

In addition to that you have a serious flaw in your logic, the inserted & deleted tables in SQL Server can (and will) contain more than one row when the insert / update is done for multiple rows — James Z
Waitfor Delay '00:00:05' It's for test pessimistic concurrency — Saeid Mirzaei

James Z James Z · Accepted Answer · 2015-07-21T11:52:37

If I understand your code correctly, it looks like you're just trying to update a running number to each row and it's actually a nchar field that contains leading zeros.

Using triggers with exclusive lock on the whole table etc. sounds like a bad idea. I would rather recommend you to use identity field in the table. If you really need to have a leading zeros, I would just add a computed column to the table that uses the identity field for the numbering.

Transaction (Process ID) was deadlocked

1 Answers