March 11, 2015 at 12:36 am
Hi,
I am using following queries in a stored procedure.This stored procedure is executed through a dot net application.
DECLARE @DEPTNBR BIGINT
SELECT @DEPTNBR = DEPTNBR
FROM DEPARTMENT_DETAILS WITH (UPDLOCK,READPAST)
WHERE STATUS= 1
UPDATE DEPARTMENT_DETAILS SET STATUS= 0 WHERE DEPTNBR = @DEPTNBR
SELECT DEPTNBR,DEPTNAME,DEPTLOC FROM DEPARTMENT_DETAILS WHERE DEPTNBR = @DEPTNBR
From my queries,I am providing a available department information.Each user needs to get unique available department information.
But when more number of users using the application concurrently, multiple users getting same department information.How to solve my problem?I always wants to get unique department information even though multiple users using the application concurrently.
Please somebody help me in resolving my issue.
March 11, 2015 at 1:29 am
Can u elaborate your requirement , It seems your query not sync with requirement , What bases you want unique department to each user,
Based on ur query , always returns last record from that department details table(with status= 1).
March 11, 2015 at 2:16 am
Hi,
I need to retrieve the active department uniquely to each user (i.e. departments whose status is 1).After retrieval department number (DEPTNBR) in the select query, i am doing status update of retrieved department immediately. (STATUS= 0). so that this department is not going to retrieved by another user.
March 11, 2015 at 2:38 am
There is nothing at all in your code which ensures that multiple people won't get the same department number. Yes, you have an updlock, but since there's no transaction, it's only held until the end of that statement. Once the select finishes, the locks are released and anyone else can read the same row.
Also, if there are multiple rows in DEPARTMENT_DETAILS, your code will get one of them. No guarantees which one.
I suggest you write that as a single update with an OUTPUT clause, then at least it'll all be atomic and one statement. Otherwise you're going to need to add in transactions, error handling and probably a different isolation level.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2015 at 3:27 am
Hi,
Thanks for giving reply.I just changed like below.
BEGIN TRAN
DECLARE @DEPTNBR BIGINT
SELECT @DEPTNBR = DEPTNBR
FROM DEPARTMENT_DETAILS WITH (UPDLOCK,READPAST)
WHERE STATUS= 1
UPDATE DEPARTMENT_DETAILS SET STATUS= 0 WHERE DEPTNBR = @DEPTNBR
SELECT DEPTNBR,DEPTNAME,DEPTLOC FROM DEPARTMENT_DETAILS WHERE DEPTNBR = @DEPTNBR
IF @@ERROR = 0
COMMIT
ELSE
ROLLBACK
Is it ok now?
March 11, 2015 at 3:44 am
No.
Main problem is that it's pretty unlikely for the last select to be the one that throws an error, so the error handling (which is pre-SQL 2005 style) is mostly worthless. The READPAST may also be a problem if the update takes anything other than a rowlock or escalates to anything other than a rowlock
Seriously, I would recommend you change the original code to be a single update with output. It's simpler, less prone to locking peculiarities and keeps everything in a single statement and you won't need locking hints
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2015 at 9:58 am
Edit: Bolded mods to original code.
BEGIN TRANSACTION
DECLARE @DEPTNBR BIGINT
SELECT TOP (1) @DEPTNBR = DEPTNBR
FROM DEPARTMENT_DETAILS WITH (UPDLOCK,READPAST)
WHERE STATUS = 1
UPDATE DEPARTMENT_DETAILS
SET STATUS = 0
WHERE DEPTNBR = @DEPTNBR
SELECT DEPTNBR,DEPTNAME,DEPTLOC
FROM DEPARTMENT_DETAILS
WHERE DEPTNBR = @DEPTNBR
COMMIT TRANSACTION
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply