any scalable solution?

hi,
I have a table which stores some user details like email, name and Flag.
Now currently we have a stored procedure which runs on the table and selects
all the records from this table where flag=0 processes these records and
sets the flag to 1.
This process is working fine.
But now we want to deploy this service on multiple servers meaning this
procedure should be called on say two different servers. The procedures will
now be modified to say select first top 100 records and process them and
then update the flag to 1. simultaneously if the same proc is called from
another server then the 100 records selected by the first server should not
be selected by the proc called by the second server.
I am sorry if i m not clear.
any pointers will be helpful..
thanks for the replies.
jyo.Maybe one solution is to use a three valued logic.
Thats when you fetch rows for processing update them to a differenct status
code.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"harshal mistry" <harshal_in@.hotmail.com> wrote in message
news:uktaSMlGFHA.2732@.TK2MSFTNGP15.phx.gbl...
> hi,
> I have a table which stores some user details like email, name and Flag.
> Now currently we have a stored procedure which runs on the table and
> selects
> all the records from this table where flag=0 processes these records and
> sets the flag to 1.
> This process is working fine.
> But now we want to deploy this service on multiple servers meaning this
> procedure should be called on say two different servers. The procedures
> will
> now be modified to say select first top 100 records and process them and
> then update the flag to 1. simultaneously if the same proc is called from
> another server then the 100 records selected by the first server should
> not
> be selected by the proc called by the second server.
> I am sorry if i m not clear.
> any pointers will be helpful..
> thanks for the replies.
> jyo.
>|||right this is what i m planning to do ... but in the time interval between
the select and update
in the first procedure call, if the second procedure call is made and the
same records may get selected.. and if locking is done then one of them one
of the call is selected as the deadlock victim...
I am looking for something like a rowlock... which even would not allow to
read the locked rows.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:e9krnQlGFHA.3472@.TK2MSFTNGP09.phx.gbl...
> Maybe one solution is to use a three valued logic.
> Thats when you fetch rows for processing update them to a differenct
status
> code.
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "harshal mistry" <harshal_in@.hotmail.com> wrote in message
> news:uktaSMlGFHA.2732@.TK2MSFTNGP15.phx.gbl...
and
from
>|||You can select using UpdLock table hint. This means you are trying to select
with the intent to update it later. So it will try to acquire IX (intent
exclusive) lock and later it will update it to X (exclusive) as and when u
you do the update. This is the way you can avoid deadlocks.
hth,
--
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"harshal mistry" <harshal_in@.hotmail.com> wrote in message
news:em71SflGFHA.2748@.tk2msftngp13.phx.gbl...
> right this is what i m planning to do ... but in the time interval between
> the select and update
> in the first procedure call, if the second procedure call is made and the
> same records may get selected.. and if locking is done then one of them
one
> of the call is selected as the deadlock victim...
> I am looking for something like a rowlock... which even would not allow to
> read the locked rows.
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:e9krnQlGFHA.3472@.TK2MSFTNGP09.phx.gbl...
> status
Flag.
> and
this
procedures
and
> from
should
>|||even after using updlock table hint it deadlocks the other process ...
harshal.
"avnrao" <avn@.newsgroups.com> wrote in message
news:uAQv6mlGFHA.3964@.TK2MSFTNGP14.phx.gbl...
> You can select using UpdLock table hint. This means you are trying to
select
> with the intent to update it later. So it will try to acquire IX (intent
> exclusive) lock and later it will update it to X (exclusive) as and when u
> you do the update. This is the way you can avoid deadlocks.
> hth,
> --
> Av.
> http://dotnetjunkies.com/WebLog/avnrao
> http://www28.brinkster.com/avdotnet
>
> "harshal mistry" <harshal_in@.hotmail.com> wrote in message
> news:em71SflGFHA.2748@.tk2msftngp13.phx.gbl...
between
the
> one
to
> Flag.
records
> this
> procedures
> and
> should
>|||can you check syslockinfo and sysprocesses table ifnormation..and find out
which one is causing the deadlock.
AFAIK, updlock should avoid the deadlock.
can you post your code..
if you want to resolve deadlock..you can use PSSDiag tool
http://www.microsoft.com/downloads/...&displaylang=en
hth,
--
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"harshal mistry" <harshal_in@.hotmail.com> wrote in message
news:uyUX75lGFHA.1528@.TK2MSFTNGP09.phx.gbl...
> even after using updlock table hint it deadlocks the other process ...
> harshal.
>
> "avnrao" <avn@.newsgroups.com> wrote in message
> news:uAQv6mlGFHA.3964@.TK2MSFTNGP14.phx.gbl...
> select
u
> between
> the
them
allow
> to
and
> records
them
called
>|||Try reading the rows, with a lock ( like updlock or xlock), but also use
readpast, which means to NOT wait on locked rows ( by the other process...
DO this in a transaction which includes the updating of the rows...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"harshal mistry" <harshal_in@.hotmail.com> wrote in message
news:uktaSMlGFHA.2732@.TK2MSFTNGP15.phx.gbl...
> hi,
> I have a table which stores some user details like email, name and Flag.
> Now currently we have a stored procedure which runs on the table and
> selects
> all the records from this table where flag=0 processes these records and
> sets the flag to 1.
> This process is working fine.
> But now we want to deploy this service on multiple servers meaning this
> procedure should be called on say two different servers. The procedures
> will
> now be modified to say select first top 100 records and process them and
> then update the flag to 1. simultaneously if the same proc is called from
> another server then the 100 records selected by the first server should
> not
> be selected by the proc called by the second server.
> I am sorry if i m not clear.
> any pointers will be helpful..
> thanks for the replies.
> jyo.
>|||"harshal mistry" <harshal_in@.hotmail.com> wrote in message
news:uktaSMlGFHA.2732@.TK2MSFTNGP15.phx.gbl...
> hi,
> I have a table which stores some user details like email, name and Flag.
> Now currently we have a stored procedure which runs on the table and
selects
> all the records from this table where flag=0 processes these records and
> sets the flag to 1.
> This process is working fine.
> But now we want to deploy this service on multiple servers meaning this
> procedure should be called on say two different servers. The procedures
will
> now be modified to say select first top 100 records and process them and
> then update the flag to 1. simultaneously if the same proc is called from
> another server then the 100 records selected by the first server should
not
> be selected by the proc called by the second server.
> I am sorry if i m not clear.
> any pointers will be helpful..
> thanks for the replies.
> jyo.
Maybe something like:
Step 1: Update Flag to an in use value depending on the server (Say flag
= -1 for Server 1, flag = -2 for server 2, etc.)
Step 2: Do the processing on records where flag = (-1) for Server 1
Step 3: Update Flag to 1
The key is to mark the records you want first.
In Step 1 only select from the pool where flag = 0.
The way you describe the problem sounds to me as if you may be using a
cursor. If so, you may want to rethink your solution to a something more
set based. If you aren't using a cursor, ignore this.
Good Luck,
Jim.