We have an application deployed on two seperate machines both reading from
the same database. The applications poll the database every few seconds and
find the first record in a table that has a bit field set to 0x0. The
application then updates the field to a value of 0x1 and then do some
processing based on the contents of the record. The problem we have is that
it is currently possible for each application to get the same record because
application 2 might select the record just before application 2 updates the
bit flag. we currently use two sql calls (in stored procs) such as:
select top 1 * from table1 where processed = 0x0
update table1 set processed = 0x1 where recid = @.ID
How can we avoid both apps getting the same recordHave the app call a single SP.
This SP updates the record, and then returns it to the client application.
This is in one transaction, so the other application can not get to the same
record.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeremy Chapman" <NoSpam@.Please.com> wrote in message
news:#GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
> We have an application deployed on two seperate machines both reading from
> the same database. The applications poll the database every few seconds
and
> find the first record in a table that has a bit field set to 0x0. The
> application then updates the field to a value of 0x1 and then do some
> processing based on the contents of the record. The problem we have is
that
> it is currently possible for each application to get the same record
because
> application 2 might select the record just before application 2 updates
the
> bit flag. we currently use two sql calls (in stored procs) such as:
> select top 1 * from table1 where processed = 0x0
> update table1 set processed = 0x1 where recid = @.ID
>
> How can we avoid both apps getting the same record
>|||Jeremy,
This is how you would do it; put the desired row into lock until the update
is done. For rowlock to be effective, you need a primary key on the table!
ReadPast hint will allow you to bypass the locked row and process the next
available one. Without it, your #2 connection will have to wait until #1 is
done. You can try both scenarios out to gain some deeper insight.
e.g.
/*
--sample tb
create table t1(i int primary key, b bit)
insert t1 values(1,0)
insert t1 values(2,0)
insert t1 values(3,0)
*/
-- drop table t1
go
--on connection #1
--this will lock i=1
declare @.i int
begin tran
select top 1 @.i=i
from t1 with (rowlock,readpast)
where b=0
update t1
set b=1
where i=@.i
select @.i as [i]
-- commit
-- rollback
go
--on connnection #2
--this will lock i=2
declare @.i int
begin tran
select top 1 @.i=i
from t1 with (rowlock,readpast)
where b=0
update t1
set b=1
where i=@.i
select @.i as [i]
-- commit
-- rollback
go
-oj
"Jeremy Chapman" <NoSpam@.Please.com> wrote in message
news:%23GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
> We have an application deployed on two seperate machines both reading from
> the same database. The applications poll the database every few seconds
> and
> find the first record in a table that has a bit field set to 0x0. The
> application then updates the field to a value of 0x1 and then do some
> processing based on the contents of the record. The problem we have is
> that
> it is currently possible for each application to get the same record
> because
> application 2 might select the record just before application 2 updates
> the
> bit flag. we currently use two sql calls (in stored procs) such as:
> select top 1 * from table1 where processed = 0x0
> update table1 set processed = 0x1 where recid = @.ID
>
> How can we avoid both apps getting the same record
>|||Magnificent! Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:e$coyd7EFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Jeremy,
> This is how you would do it; put the desired row into lock until the
update
> is done. For rowlock to be effective, you need a primary key on the table!
> ReadPast hint will allow you to bypass the locked row and process the next
> available one. Without it, your #2 connection will have to wait until #1
is
> done. You can try both scenarios out to gain some deeper insight.
> e.g.
> /*
> --sample tb
> create table t1(i int primary key, b bit)
> insert t1 values(1,0)
> insert t1 values(2,0)
> insert t1 values(3,0)
> */
> -- drop table t1
> go
> --on connection #1
> --this will lock i=1
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
> --on connnection #2
> --this will lock i=2
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
>
> --
> -oj
>
> "Jeremy Chapman" <NoSpam@.Please.com> wrote in message
> news:%23GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
from
>|||Actually, testing discovered that this might not work, because if the sql
gets run at the same time, the select statements could select the same
record, because nothing is locked at that point.
"oj" <nospam_ojngo@.home.com> wrote in message
news:e$coyd7EFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Jeremy,
> This is how you would do it; put the desired row into lock until the
update
> is done. For rowlock to be effective, you need a primary key on the table!
> ReadPast hint will allow you to bypass the locked row and process the next
> available one. Without it, your #2 connection will have to wait until #1
is
> done. You can try both scenarios out to gain some deeper insight.
> e.g.
> /*
> --sample tb
> create table t1(i int primary key, b bit)
> insert t1 values(1,0)
> insert t1 values(2,0)
> insert t1 values(3,0)
> */
> -- drop table t1
> go
> --on connection #1
> --this will lock i=1
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
> --on connnection #2
> --this will lock i=2
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
>
> --
> -oj
>
> "Jeremy Chapman" <NoSpam@.Please.com> wrote in message
> news:%23GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
from
>|||You could add another hint to the select to exclusively hold the lock. As
soon as the row is read, it's locked until you invoke commit/rollback.
e.g.
select *
from tb with (rowlock,xlock,readpast)
where pkid=@.para
-oj
"Jeremy Chapman" <NoSpam@.Please.com> wrote in message
news:uLpwUeIFFHA.464@.TK2MSFTNGP09.phx.gbl...
> Actually, testing discovered that this might not work, because if the sql
> gets run at the same time, the select statements could select the same
> record, because nothing is locked at that point.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e$coyd7EFHA.3664@.TK2MSFTNGP15.phx.gbl...
> update
> is
> from
>
