Doubt At Update - 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 2000 Forums

SQL Server Development (2000)

Doubt At Update

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2011-07-25 : 23:45:25

HI My table structure is :

Create table testtable
(
ID int identity primary key,
Name varchar(200),
age int,
Status int
)

I have a sp like following :

create procedure usp_getrecords
as
declare
@seqno int
begin
select @seqno = min(id) from testtable where status = 1

update status = 2 where id = @seqno and status = 1

if(@@rowcount > 0)

begin

select * from testtable where seqno = @seqno

end

end

My requirement is, each row in the table need to be processed at only time irrespective of number of more than one thread/process access it same time..

For example thread1 and thread2 access this sp same time..so

Step 1:

after executing following query :
select @seqno = min(id) from testtable where status = 1
both thread will get same id

Step 2:(what will happen at this stage?)
update status = 2 where id = @seqno and status = 1

Both threads will execute update statement at same time?
or sqlserver will issue rowlevel lock for one thread first and makes second thread to wait for some time?

If yes, then first thread will update status to 2 hence second thread cannot update status again as filter is for status is 1..so the records affected be 0 and it cannot execute second select statement also(i need this functionality)

My exact requirement each row in table need to be executed only once irrespective of the number of threads accessing it....

Please suggest me on this.....

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-26 : 02:42:33

Sorry but I can see no threads... there is one SELECT and one UPDATE (with wrong syntax by the way).

And why not:
update table set status=2 where id = (select min(id) from table where status=1)

No, you're never too old to Yak'n'Roll if you're too young to die.

rammohan
Posting Yak Master

212 Posts

Posted - 2011-07-26 : 04:51:09

Hi webfred,

Thanks for the reply......

"update table set status=2 where id = (select min(id) from table where status=1)"

Actually what i am looking to know is, what will happen if two or more processes/ threads trying to access the above update statement if it is in a stored procedure at same time i mean in case of parallel processing?

1. All the processes/threads execute the above statement at same time?
or
2. Sql server block's second processes until the first process execute the above update statement?

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-26 : 04:53:53

2. : Yes, SQL Server will handle that for you.

No, you're never too old to Yak'n'Roll if you're too young to die.

rammohan
Posting Yak Master

212 Posts

Posted - 2011-07-26 : 05:01:18

Thanks a lot webfred....

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources