March 3, 2010 at 12:01 pm
When two transactions are running the same process concurrently i need to make sure that the other transaction waits till the first one finishes.
Following is an example,
BEGIN Tran
SELECT LastUsedIndex from SeqNumbers
DECLARE @num
SET @num = @num + 1
UPDATE SeqNumbers SET LastUsedIndex = @num
Commit Tran
when two users run the same stored procedure, the second transaction needs wait till the first transaction commits the changes. Otherwise if both transactions run the select at the same time, they both will end up with the same LastUsedIndex. How can we make sure that only one transaction runs at a time or is there another way to resolve this issue?
March 3, 2010 at 12:09 pm
preetshari (3/3/2010)
When two transactions are running the same process concurrently i need to make sure that the other transaction waits till the first one finishes.Following is an example,
BEGIN Tran
SELECT LastUsedIndex from SeqNumbers
DECLARE @num
SET @num = @num + 1
UPDATE SeqNumbers SET LastUsedIndex = @num
Commit Tran
when two users run the same stored procedure, the second transaction needs wait till the first transaction commits the changes. Otherwise if both transactions run the select at the same time, they both will end up with the same LastUsedIndex. How can we make sure that only one transaction runs at a time or is there another way to resolve this issue?
Does this accomplish what you require?
DECLARE @num int -- or what ever type it is
BEGIN TRAN
UPDATE SeqNumbers
@num = LastUsedIndex = LastUsedIndex + 1;
COMMIT TRAN
March 3, 2010 at 12:39 pm
Yes but i need to return the updated LastUsedIndex.
Does this accomplish what you require?
DECLARE @num int -- or what ever type it is
BEGIN TRAN
UPDATE SeqNumbers
@num = LastUsedIndex = LastUsedIndex + 1;
COMMIT TRAN
[/quote]
March 3, 2010 at 12:45 pm
preetshari (3/3/2010)
Yes but i need to return the updated LastUsedIndex.
Lynn's code does that. The @num variable will containt the updated value. Try it.
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 3, 2010 at 12:45 pm
preetshari (3/3/2010)
Yes but i need to return the updated LastUsedIndex.Does this accomplish what you require?
DECLARE @num int -- or what ever type it is
BEGIN TRAN
UPDATE SeqNumbers
@num = LastUsedIndex = LastUsedIndex + 1;
COMMIT TRAN
[/quote]
It does, add select @num after the commit. It holds the value you need.
March 3, 2010 at 1:34 pm
i get a syntax error when i run this. "Incorrect syntax near '@num'."
Lynn Pettis (3/3/2010)
preetshari (3/3/2010)
Yes but i need to return the updated LastUsedIndex.Does this accomplish what you require?
DECLARE @num int -- or what ever type it is
BEGIN TRAN
UPDATE SeqNumbers
@num = LastUsedIndex = LastUsedIndex + 1;
COMMIT TRAN
It does, add select @num after the commit. It holds the value you need.
[/quote]
March 3, 2010 at 1:51 pm
preetshari (3/3/2010)
i get a syntax error when i run this. "Incorrect syntax near '@num'."Lynn Pettis (3/3/2010)
preetshari (3/3/2010)
Yes but i need to return the updated LastUsedIndex.Does this accomplish what you require?
DECLARE @num int -- or what ever type it is
BEGIN TRAN
UPDATE SeqNumbers
@num = LastUsedIndex = LastUsedIndex + 1;
COMMIT TRAN
It does, add select @num after the commit. It holds the value you need.
[/quote]
Post your code and the entire error message.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply