June 15, 2019 at 3:10 am
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]
@user-id 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 = @user-id
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
June 16, 2019 at 3:10 am
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply