none
Deadlock Occured while muliple users using one form at a time.

    Question

  • Hi All,

                I have one web application in which when multiple users working on same form at that time deadlock is occurred.Actual Exception is given below.

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

    I already used a begin tran and commit tran in my dataaccess class and rollback tran if exception occurred.i also read some issue in msdn also at that place i read that update statistics.so i execute this stored procedure sp_updatestats but still there is no result.so please anyone tell me what should i do for this deadlock?


    Nikunj Nandaniya
    Wednesday, March 16, 2011 5:31 AM

Answers

  • Hi,
             I do Given solution which is given below.if you have any suggestion regarding this please let me know.
    Reference Link:-http://www.codeproject.com/KB/database/SQLServer_deadlock.aspx
    RETRY: -- Label RETRY
    BEGIN TRANSACTION
    BEGIN TRY
    UPDATE Orders SET ShippingId = 12 Where OrderId = 221
    WAITFOR DELAY '00:00:05' -- Wait for 5 ms
    UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111
    COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
    PRINT 'Rollback Transaction'
    ROLLBACK TRANSACTION
    IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
    BEGIN
    WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
    GOTO RETRY -- Go to Label RETRY
    END
    END CATCH

    Nikunj Nandaniya
    Tuesday, April 05, 2011 9:09 AM

All replies

  • Hello Nikunj,

    Please have a look at MSDN Deadlocking to see why deadlocks occur; mostly bad programming. See also Analyzing Deadlocks with SQL Server Profiler and Detecting and Ending Deadlocks


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Wednesday, March 16, 2011 6:15 AM
  • Hi Olaf,

              Thanks for Reply i Read this documentation.but i also know the theory.but how can i practically implement it.i think bound connections is better idea for this or can you please suggest me any other better way.can you please tell me how to use sp_bindsession stored procedure.is there any code available that i write in stored procedure so it checks that if any transaction is using this table then it waits until or unless another transaction rollback or commit.


    Nikunj Nandaniya
    Wednesday, March 16, 2011 9:02 AM
  • Hiii,

           I read all things.I already Use Begin Tran,Commit tran, Rollback tran.then why deadlock occurs.can u please help me little for practically implementation.


    Nikunj Nandaniya
    Wednesday, March 23, 2011 5:21 AM
  • Hi,
             I do Given solution which is given below.if you have any suggestion regarding this please let me know.
    Reference Link:-http://www.codeproject.com/KB/database/SQLServer_deadlock.aspx
    RETRY: -- Label RETRY
    BEGIN TRANSACTION
    BEGIN TRY
    UPDATE Orders SET ShippingId = 12 Where OrderId = 221
    WAITFOR DELAY '00:00:05' -- Wait for 5 ms
    UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111
    COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
    PRINT 'Rollback Transaction'
    ROLLBACK TRANSACTION
    IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
    BEGIN
    WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
    GOTO RETRY -- Go to Label RETRY
    END
    END CATCH

    Nikunj Nandaniya
    Tuesday, April 05, 2011 9:09 AM