Transaction was deadlocked on lock | communication buffer resources with another

  • sridharkannan7

    Mr or Mrs. 500

    Points: 515

    I have a table that contains case numbers. The following stored procedure assigns a case from this table to the user who calls this stored procedure from a web application.

    There are more than 150 users who are using the web application at the same time. This means that at any moment some or the other user will be calling this stored procedure. The stored procedure accepts a parameter called userid.

    The following 2 points covers the entire set of case numbers from which the case can be assigned to the user

    1. The procedure checks for the case numbers in "Table1" with status as "New"".

    2. The case numbers from <TABLE2> with status as "Rectified"

    The case numbers from both the above points now becomes the final set of case numbers from which the user is assigned any one case

    If the case found is Rectified, then the record is inserted into historytable first, deleted from TABLE2 and then inserted into TABLE2

    This is because only record for that case should be present in TABLE2

    CREATE procedure [dbo].[AssignCase]

    @userid INT = NULL

    As

    Begin

    BEGIN TRY

    BEGIN TRANSACTION [Tran1]

    DECLARE @CAID INT

    SET @CAID = 0

    DECLARE @AlreadyAssigned AS INT

    SET @AlreadyAssigned = 0

    /*Checking whether a Case has already been assigned to the user*/

    select @AlreadyAssigned = X.customerapplicationentityid from

    (

    select top 1 nfr.customerapplicationentityid, nfr.ApplicationStatusEntityID

    from <TABLE2> nfr where nfr.UserEntityID = @userid

    order by createdon desc, id

    )X

    where X.ApplicationStatusEntityID = 'AlreadyAssigned'

    --SELECT @AlreadyAssigned

    IF (@AlreadyAssigned = 0)

    BEGIN

    select @CAID=ca.id from

    (

    select ca.id from

    (

    select ca.customerapplicationentityid

    from <TABLE1> ca

    inner join applicationstatus ast on ast.id = ca.statusID and ast.statuscode = 'New'

    left outer join

    (

    select nfr.customerapplicationentityid

    from <TABLE2> nfr with (updlock, holdlock)

    inner join <TABLE1> ca on ca.customerapplicationentityid = nfr.customerapplicationentityid

    inner join applicationstatus ast on ast.id = ca.statusID and ast.statuscode = 'Rectified'

    )X on X.customerapplicationentityid = ca.id

    )ca

    )ca

    IF (@CAID <> 0)

    BEGIN

    insert into <Table2History>(Columns)

    select VALUES FROM from <Table2>

    DELETE FROM <TAble2> WHERE customerapplicationentityid = @CAID

    insert into <Table2>(Columns)

    VALUES FROM <Table1>

    END

    END

    COMMIT TRANSACTION [Tran1]

    END TRY

    BEGIN CATCH

    ROLLBACK TRANSACTION [Tran1]

    END CATCH

    END

     

    The problem with the above stored procedure is that we have started getting Transaction deadlocked error. So if there are 150 users, only 40 to 50 are assigned cases and the others are not getting assigned any case

    Any suggestions on resolving this will be highly appreciated

     

     

  • Site Owners

    SSC Guru

    Points: 80385

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply