October 7, 2008 at 10:25 pm
Hi Folks,
I’m concerned about the possibility of my application logic breaking down, when concurrent stored procedures are executed at the same time…leading to erroneous data.
In the application I’m developing it is imperative that some resources are locked while a stored procedure is executing, so my question essentially comes down to how MSSQL locks resources and releases them.
When does a resource get locked? And how long does it stay locked?
In my situation: each member that signs up gets a different Card number, I’m generating the Card#'s using a stored procedure which gets that last Card# in my DB and adding 1 to it ( NewCard# = OldCard# +1).
I have to make sure that no two concurrent procedures fetch the last Card# at the same time…. As this would produce doubles of the card#...
To make communication easier this is the procedure I am running:
ALTER PROCEDURE [dbo].[GetNextNum]
AS
BEGIN TRANSACTION
-- Step 1 ( Get Last Number issued in a variable)
DECLARE @LastNum bigint
SELECT @LastNum = LastNumIssued FROM dbo.CardNum
-- Step 2 (Create the Next Number to be issued)
DECLARE @NextNum bigint
SET @NextNum = @LastNum + 1
-- Step 3 (Update the record of Last # issued)
UPDATE dbo.CardNum SET LastNumIssued = @NextNum
-- Step 4 ( Select Last # issued, to send back to VB code to create user)
SELECT LastNumIssued FROM dbo.CardNum
COMMIT
From the above code, the DB is only updated at STEP 3, so can a concurrent transaction start running STEPS 1, 2 before the current procedure reaches STEP 3?
If another concurrent procedures retrieves the @LastNum value before it has a chance to update, then the concurrent procedure will get the old value… and I will end up with two members having the same Card#...no good!
If the resources only lock at the point when the UDATE IS RUN...should I then use WITH (TABLOCK) on STEPS 1 and 4.
...also when is a lock released?
Even if it locks on STEP 1 and STEP 2.
Is the lock released in between the steps?
If the lock is released in between the STEPS that's enough time for a concurent sproc it slip in there...
The bottom line:
Q#1:
How can I make sure that the data is untouched, while this procedure is running?
Q#2:
What happens to the concurent procedure which attempts to use a lock resource, how long is it queued for, and what happens if it dies?
Regards,
- Joel
October 9, 2008 at 7:04 am
Joel, there is a lot of stuff to read in books online about locking. You should also read about transactions and transaction isolation levels. Once you do your homework you can expecte more help in the forums
October 9, 2008 at 9:25 am
Your transaction should be essentially instantaneous so the likely hood of an error is minimal unless you are doing thousands of transactions per second. To specifically answer your questions:
1. You can change the isolation level (Set Isolation Level) in the stored procedure or supply a locking hint to your select statement.
2. This would depend on the lock timeout setting (Set Lock_Timeout) and connection settings. By default the Lock Timeout setting is -1 or indefinite. In .NET you can set the CommandTimeout property of your command which will cancel the command after that time has been exceeded.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 9, 2008 at 10:09 am
Wow, why not use an identity here? You're reinventing a wheel you don't need to.
The other thing you can do is raise a semaphore. The first line in the proc can check for a "1" in a column, and if so, loop.
If it gets past that, set a "1" in that column, then do your calculation, and then set a "0" in the column. Every second person will "pause", just be sure your application doesn't time out.
October 9, 2008 at 6:11 pm
Hi,
Thank you all for the replies, lots of food for though.
I decided to implement a whole new approach to the problem, and I want to run it by you, to make sure if it's safe from, data corruption
Instead of generating the NextCard# within the procedure mathematically I created, a new Table that holds all the cards numbers, already generated.
This new table also has other columns:...‘available’ and ‘used’.
Which indicated if this card# is "available" for use, or it it already has been used.
( Some Card # may not be available, so therefore the procedure can access the Card# out of sequence if need be - to skip some card# I can set the 'available' to 'No')
My procedure now, only needs to pick-out the next available card #, and make it 'used'.
This is the procedure:
ALTER PROCEDURE [dbo].[NextCardOut]
@MyCard bigint OUTPUT
AS
BEGIN
-- STEP 1
-- Note the XLOCK on the row
SELECT top (1) @MyCard = CardNum FROM CardTable WITH(ROWLOCK,XLOCK)
WHERE Available = 'Yes' AND Used ='No'
-- What happens in between the steps? Does the above lock release?
-- STEP 2
UPDATE CardTable
SET Used = 'Yes'
WHERE CardNum = @MyCard
END
Now, my concern is having a concurrent procedure, read the CardNum, in between
the STEPS 1 & 2.
The only way to be sure of that I think is to set the transaction isolation level in the procedure…
So that way my row is locked for the ENTIRE PROCEDURE... not just for the STEPS...
However I can’t seem to get the syntax right, to set the isolation level, because of the output parameter…
Q#1.
How should I write this procedure, to set transaction isolation level, and using a output parameter at the same time?
Q#2
Which transaction isolation level should I use, I’ve read up on the transaction isolation levels, and I’m not sure which one creates an exclusive lock on the accessed data…most documentation mention locks, but are not very clear as to whether it’s a shared(read allowed) lock or exclusive lock (no read or write).
Q#3.
What happens, when the concurent procedure will encounter a locked row...will it sit there and wait until the row unlocks, or can it skip over that row and continiue scanning the table for the condition: WHERE Available = 'Yes' AND Used ='No'
I'd rather it could skip over the currently lock row...
Thank you very much for your insight,
- Joel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply