Transaction deadlock on inserting into a table

Giganews Newsgroups
Subject: Transaction deadlock on inserting into a table
Posted by:  fniles (fiefie.nil…@gmail.com)
Date: Wed, 15 Dec 2010

I am using SQL Server 2005 with VB.NET 2008.
I have a table myTbl with an identity column ID (this is NOT a primary
key and I do NOT have an index on this column) and a primary key
column called [Order].
I also have a stored procedure INSERT_INTO_myTbl that inserts a record
into myTbl, and set the [Order] column to be the value of Account
column plus '-'  plus the value of the identity column ID like so:
SET [Order] = @ACCOUNT + '-' + convert(varchar(50),@ID)
For example:
@Order = "11111"
@Account = "11111"
After INSERT_INTO_myTbl, [order] = "11111-123456"
In the VB.NET program I have hundreds of different thread that calls
the sp INSERT_INTO_myTbl.
If I call INSERT_INTO_myTbl at the same time (say 100 times from 1
thread and 100 times from another thread), the insertion is very slow,
and sometimes I will still get error 1205 (transaction deadlock) even
after the stored procedure retry 3 times. I also will get the
transaction deadlock when I try to query the table during this time.
When I increase the retry to 10 times, sometimes I will get the error
"The timeout period elapsed prior to completion of the operation or
the server is not responding".
1. Why if I call sp INSERT_INTO_myTbl 100 times from 1 thread and 100
times from another thread at the same time, it is very slow ?
2. Is there any way to avoid the deadlock ?

Thank you

CREATE PROCEDURE INSERT_INTO_myTbl
@Order varchar(50) = NULL,
@ACCOUNT varchar(10)  = NULL
AS
DECLARE @Success int, @TryNr int, @ErrMsg varchar(500), @ErrNumber
int;
SET @Success = 0;
SET @TryNr = 1;
SET @ErrMsg = ''
WHILE @Success = 0 AND @TryNr <= 3 and @ErrMsg = ''
BEGIN;
  BEGIN TRY;
    BEGIN TRANSACTION;
                insert into myTbl ([Order],ACCOUNT) values
(@Order,@ACCOUNT)
        select @ID = SCOPE_IDENTITY()
        UPDATE HistTradesOrig
        SET [Order] = @ACCOUNT + '-' + convert(varchar(50),@ID)
        WHERE ID = @ID
        SET @Success = 1
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH;
    ROLLBACK TRANSACTION;
        SELECT @ErrNumber = ERROR_NUMBER()
    IF @ErrNumber = 1205
    BEGIN;
      SET @TryNr = @TryNr + 1;
      IF @TryNr > 3
        RAISERROR ('Giving up after three consecutive deadlocks', 16,
1);
    END;
    ELSE
    BEGIN;
      SET @ErrMsg = ERROR_MESSAGE();
      RAISERROR (@ErrMsg, 16, 1);
    END;
  END CATCH;
END;

Replies