June 19, 2019 at 4:59 pm
Hello,
I have a web application which assigns a case to a user by clicking on the button "Get Case". On clicking of this button, a stored procedure is called. An existing table containing the CaseIDs is read and one ID from this table is assigned to the user who clicked on "Get Case"
Now the problem is, there are many users accessing the website and clicking on the button simultaneously. This results in the same CaseID getting assigned to multiple users
How can I achieve this so that each user is assigned only one case. I have also used Transaction as shown below
BEGIN TRY
BEGIN TRANSACTION [Tran1]
select top 1 ca.id from Cases ca
where ca.applicationstatusentityID in (1,2,12,15)
COMMIT TRANSACTION [Tran1]
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION [Tran1]
END CATCH
Any help on this will be appreciated
June 19, 2019 at 5:53 pm
You want the SELECT and UPDATE in the same transaction. Otherwise, there is the chance that before you do the assignment, someone else has a chance to perform the select.
If this is a data change in the database, I'd have the stored procedure do something like:
create procedure AssignCase
@userid int
as
begin
with assignmentcte(caseid)
as
( select top 1 ca.id
from cases ca
where ca.applicationstatusentityid in (1,2,12,15)
)
update a
set userid = ac.userid
from assignments a
inner join assignmentcte ac
on a.id = ?
I don't know how you're doing the data change, so can't guess here, but I assume some assignment type table that you update. Combine this into one statement to do the select and assignment. If you need to update the Cases table to remove an id or mark it with a status, do that in a single transaction with the update.
June 19, 2019 at 6:04 pm
I have multiple inserts like inserting the audit_trail table before I insert this caseID into a separate table. So it cannot be a single select insert statement
Can I lock this resource .i.e. lock the stored procedure sp_applock.Is this a correct way to achieve what I'm seeking
June 19, 2019 at 6:51 pm
If you are OK with locking/blocking while the transaction completes, I suggest you look into the possibility of using
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 19, 2019 at 6:51 pm
The way to lock this would be a semphore of sorts. Then your proc needs a check. So you could insert a value into a table when you start, and then your proc would be:
create proc
@userid int
as
while exists(select lockvalue from locktable)
-- waitfor delay 5 sec
insert locktable (lockvalue) select 1
select top 1 caseid
from ...
-- do other work
delete locktable where lockvalue = 1
That's a rough look, but until I see the entire process, it's hard to know. This will caues some blocking and delays, and your clients might timeout if there are issues. I like the idea of semphores, though I'd really rather have some number of retries (maybe count the WHILE loops and then terminate the proc if it never works, getting the client to retry).
Even if this isn't one insert, you could still enclose everything in a transaction as I first suggested. You're not explaining the entire process, so I'm not sure if my advice is good or not. If you have other requirements, I might suggest your idea of locking is a poor one, but hard to know.
June 19, 2019 at 7:00 pm
Session level app lock is what came to my mind. It will prevent other sessions to execute SP until it finishes.
--Vadim R.
June 19, 2019 at 7:06 pm
Sorry if I was not clear earlier.
This is the entire process
BEGIN TRY
BEGIN TRANSACTION [Tran1]
select top 1 @caid=ca.id from Cases ca
where ca.applicationstatusentityID in (1,2,12,15)
Insert into CaseAssigned table the caseId selected above
Delete from the Cases table once a case gets assigned to a user
COMMIT TRANSACTION [Tran1]
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION [Tran1]
END CATCH
June 19, 2019 at 7:14 pm
Try this:
BEGIN TRY
BEGIN TRANSACTION [Tran1]
select top (1) @caid=ca.id from Cases ca WITH (UPDLOCK)
where ca.applicationstatusentityID in (1,2,12,15)
Insert into CaseAssigned table the caseId selected above
Delete from the Cases table once a case gets assigned to a user
COMMIT TRANSACTION [Tran1]
END TRY
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 19, 2019 at 10:43 pm
You can do this all in a single assignment:
Declare @cases Table (id int, ApplicationStatusEntityID int);
Insert Into @cases (id, ApplicationStatusEntityID)
Values (1, 3)
, (2, 1)
, (3, 12)
, (50, 1);
Declare @caid int;
Declare @caseAssignment Table (id int, UserName varchar(30));
Select * From @cases c
Select * From @caseAssignment ca
--==== Example 1 - let SQL Server pick 1
Delete Top (1) @cases
Output deleted.id, 'UserA' Into @caseAssignment
Where ApplicationStatusEntityID In (1, 2, 12, 15);
--==== Example 2 - pick 1 based on id descending
Delete From @cases
Output deleted.id, 'UserB' Into @caseAssignment
Where id In (Select Top 1
c.id
From @cases c
Where ApplicationStatusEntityID In (1, 2, 12, 15)
Order By
c.id desc);
Select * From @cases c
Select * From @caseAssignment ca
This deletes the selected row from 'Cases' and inserts the row into CaseAssignments in a single transaction.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply