transaction query - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

transaction query

Author

Topic

ayu
Starting Member

43 Posts

Posted - 2008-05-06 : 22:13:43

CREATE PROCEDURE orders
AS

DECLARE @number int

set nocount on
begin transaction
Select @number = (Select lastNo from Computetab)

Update Computetab
Set Computetab.lastno = (@number+1);

SELECT Computetab.lastno from Computetqb
commit
GO

this is supposed to reutrn a new order id everytime but today for some reason it gave same ord# to two different people trying to save a new order

I thought begin transaction will take care of it any suggestion please.........

thanks a lot!!

sam13
Starting Member

6 Posts

Posted - 2008-05-06 : 22:28:46

Where is Rollback?. You can try using updlock on the table.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-05-07 : 02:24:00

Use the updlock hint and set the transaction isolation level to repeatable read or change to a single statement

Update Computetab
Set Computetab.lastno = (Select lastNo + 1 from Computetab)

Locks on select statements (shared locks) are released as soon as the statement finishes.

--
Gail Shaw

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-07 : 04:21:03

Since we are using SQL Server 2005, try this

UPDATE	ComputeLab
SET	LastNo = LastNo + 1
OUTPUT	inserted.LastNo AS [New LastNo value],
	deleted.LastNo AS [Old LastNo value]

E 12°55'05.25"
N 56°04'39.16"

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-05-07 : 05:36:55

Or you can take advantage of an interesting feature of the update statement (documented)

Update Computetab
Set @NewLastNo = Computetab.lastno = (Select lastNo + 1 from Computetab)

SELECT @NewLastNo as New_LastNo

Many options...

--
Gail Shaw

ayu
Starting Member

43 Posts

Posted - 2008-05-07 : 11:16:55

but what will happen if two people are running this at exact same time?
will they both will get same no?
the idea is they should not get the same no
so I was thinkig to use @@TRANCOUNT
but not sure how
my problem is no matter waht two people should never ge the same no
It is generating new no. fine right now
the only thing is very very rarely which happened only once yesterday
it gave two people trying to save ord gave same ord no

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-05-07 : 13:51:02

quote:

but what will happen if two people are running this at exact same time?
will they both will get same no?

Nope. Both mine and Peso's will work as you want and will not give duplicate numbers.

No matter how many people call that at the same time, only one person can update at a time. SQL's locks ensure that. Since we're both doing the update and select in a single statement, one user at a time can get and update the number

--
Gail Shaw

ayu
Starting Member

43 Posts

Posted - 2008-05-07 : 14:03:53

i tried twice and getting same order nos. but i will try once more and modify in my table and let me see..thanks all of you for ur help and suggetions.

thanks GilaMonster, Pesp, Sam13..thank you so much

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-05-07 : 14:10:39

Post the procedure you have now and the table structure please.

--
Gail Shaw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources