Data Corruption due to concurent request

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Data Corruption due to concurent request

Author

Topic

langoo
Starting Member

1 Post

Posted - 2008-10-08 : 00:43:20

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 UPDATE 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

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-09 : 16:58:12

Q1: You can ensure table not touch by selecting the next number with table lock while updating (See code below)
Q2: You will be creating deadlocks using with(tablock) in your current Stored procedure.

Try this one:


create PROCEDURE [dbo].[GetNextNum]

AS

BEGIN TRANSACTION

UPDATE dbo.CardNum SET LastNumIssued = (select LastNumIssued + 1 from dbo.cardnum with (tablock))

COMMIT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources