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
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
where X.ApplicationStatusEntityID = 'AlreadyAssigned'
IF (@AlreadyAssigned = 0)
select @CAID=ca.id from
select ca.id from
from <TABLE1> ca
inner join applicationstatus ast on ast.id = ca.statusID and ast.statuscode = 'New'
left outer join
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
IF (@CAID <> 0)
insert into <Table2History>(Columns)
select VALUES FROM from <Table2>
DELETE FROM <TAble2> WHERE customerapplicationentityid = @CAID
insert into <Table2>(Columns)
VALUES FROM <Table1>
COMMIT TRANSACTION [Tran1]
ROLLBACK TRANSACTION [Tran1]
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