asynchronous select statements

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
>

Assigning variables with SELECT statements

Hi,
The syntax
SELECT @.varname = colname FROM table WHERE ...
is valid in SQL server, but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ...
which would be useful if (for example) getting the most recent index number
from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
I can use a workaround such as
SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
As I have a workaround that works well, I'm not too concerned about this -
just wondering if I'm missing something with the syntax that causes my
second example to fail.
John.
Try:
SELECT TOP 1 @.varname = colname FROM table WHERE ...
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
Hi,
The syntax
SELECT @.varname = colname FROM table WHERE ...
is valid in SQL server, but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ...
which would be useful if (for example) getting the most recent index number
from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
I can use a workaround such as
SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
As I have a workaround that works well, I'm not too concerned about this -
just wondering if I'm missing something with the syntax that causes my
second example to fail.
John.
|||John,
You were so close...
SELECT TOP 1 @.varname = colname FROM table WHERE ...
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The syntax
> SELECT @.varname = colname FROM table WHERE ...
> is valid in SQL server, but I am unable to use the syntax
> SELECT @.varname = TOP 1 colname FROM table WHERE ...
> which would be useful if (for example) getting the most recent index
number
> from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
> I can use a workaround such as
> SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
> As I have a workaround that works well, I'm not too concerned about this -
> just wondering if I'm missing something with the syntax that causes my
> second example to fail.
> John.
>
|||>> ...but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ... <<
The variable should be immediately before the column name like:
SELECT TOP 1 @.varname = colname FROM table ...
Anith
|||How about this method:
SELECT TOP 1 @.varname = colname FROM table WHERE ...
Keith
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The syntax
> SELECT @.varname = colname FROM table WHERE ...
> is valid in SQL server, but I am unable to use the syntax
> SELECT @.varname = TOP 1 colname FROM table WHERE ...
> which would be useful if (for example) getting the most recent index
number
> from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
> I can use a workaround such as
> SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
> As I have a workaround that works well, I'm not too concerned about this -
> just wondering if I'm missing something with the syntax that causes my
> second example to fail.
> John.
>
|||Keith Kratochvil wrote:
> How about this method:
> SELECT TOP 1 @.varname = colname FROM table WHERE ...
Thanks all - much appreciated.
John.

Assigning variables with SELECT statements

Hi,
The syntax
SELECT @.varname = colname FROM table WHERE ...
is valid in SQL server, but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ...
which would be useful if (for example) getting the most recent index number
from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
I can use a workaround such as
SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
As I have a workaround that works well, I'm not too concerned about this -
just wondering if I'm missing something with the syntax that causes my
second example to fail.
John.Try:
SELECT TOP 1 @.varname = colname FROM table WHERE ...
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
Hi,
The syntax
SELECT @.varname = colname FROM table WHERE ...
is valid in SQL server, but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ...
which would be useful if (for example) getting the most recent index number
from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
I can use a workaround such as
SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
As I have a workaround that works well, I'm not too concerned about this -
just wondering if I'm missing something with the syntax that causes my
second example to fail.
John.|||>> ...but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ... <<
The variable should be immediately before the column name like:
SELECT TOP 1 @.varname = colname FROM table ...
--
Anith|||John,
You were so close...
SELECT TOP 1 @.varname = colname FROM table WHERE ...
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The syntax
> SELECT @.varname = colname FROM table WHERE ...
> is valid in SQL server, but I am unable to use the syntax
> SELECT @.varname = TOP 1 colname FROM table WHERE ...
> which would be useful if (for example) getting the most recent index
number
> from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
> I can use a workaround such as
> SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
> As I have a workaround that works well, I'm not too concerned about this -
> just wondering if I'm missing something with the syntax that causes my
> second example to fail.
> John.
>|||How about this method:
SELECT TOP 1 @.varname = colname FROM table WHERE ...
--
Keith
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The syntax
> SELECT @.varname = colname FROM table WHERE ...
> is valid in SQL server, but I am unable to use the syntax
> SELECT @.varname = TOP 1 colname FROM table WHERE ...
> which would be useful if (for example) getting the most recent index
number
> from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
> I can use a workaround such as
> SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
> As I have a workaround that works well, I'm not too concerned about this -
> just wondering if I'm missing something with the syntax that causes my
> second example to fail.
> John.
>|||Keith Kratochvil wrote:
> How about this method:
> SELECT TOP 1 @.varname = colname FROM table WHERE ...
Thanks all - much appreciated.
John.

Assertion Errors:

Hi All,

I have started getting Assertion Errors in SQL.
It appears when I process a cube (Most of the time)
Other SQL statements, usually with a join or 6 do the same thing.

Whaving a scratch around google, I noticed the most people who get
these errors are using SATA drives. Either RAID or not.
Surprise, I am using SATA in RAID 1.

Is this a common thing with SATA? I can't go to the pwers that be and
say I need a couple large SCSI drives because I _think_ it's the
SATA's.

Another very odd thing that happened thismorning was I copied the mdf
and ldf files off my machine (About 70GB) and onto the server. attached
them and SQL was happy.
Select Count(*) from aview gave me the count I was expecting.
Select * From aview returned no rows. most of the time.

I thought I was going mad. F5 works, then it doesn't then it does then,
you get the point.
Backup and restore seemed better until the errors below started...

HELP!!!!

Thanks
Cheers,
Crispin

17066 :
SQL Server Assertion: File:
<q:\SPHINX\NTDBMS\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.<crispin.proctor@.gmail.com> wrote in message
news:1105982769.678231.261080@.f14g2000cwb.googlegr oups.com...
> Hi All,
> I have started getting Assertion Errors in SQL.
> It appears when I process a cube (Most of the time)
> Other SQL statements, usually with a join or 6 do the same thing.
> Whaving a scratch around google, I noticed the most people who get
> these errors are using SATA drives. Either RAID or not.
> Surprise, I am using SATA in RAID 1.
> Is this a common thing with SATA? I can't go to the pwers that be and
> say I need a couple large SCSI drives because I _think_ it's the
> SATA's.
> Another very odd thing that happened thismorning was I copied the mdf
> and ldf files off my machine (About 70GB) and onto the server. attached
> them and SQL was happy.
> Select Count(*) from aview gave me the count I was expecting.
> Select * From aview returned no rows. most of the time.
> I thought I was going mad. F5 works, then it doesn't then it does then,
> you get the point.
> Backup and restore seemed better until the errors below started...
>
> HELP!!!!
> Thanks
> Cheers,
> Crispin
> 17066 :
> SQL Server Assertion: File:
> <q:\SPHINX\NTDBMS\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.

This KB article seems to describe what you're seeing:

http://support.microsoft.com/kb/828337

Simon|||Thanks Simon,

Problem is the checkDB does the same thing.
We have replaced the drives, drive controller etc.
Even replaced the mother board.
Only thing I have not replaced on the machine is the mouse :)

My concern is we have shipped some servers to client with a simlar
config. (SATA drives running RAID 1)
Now if this is a limitation of SATA I am in a world of sh1t.
We use SATA drives as a cheaper alternative to SCSI for some clients.
I reasoned they would be faster than IDE and if Dell, HP etc have SANs
running on SATA drives, they can't be all that bad.

arrrrggggggg. This is killing me. It's actually gettiing worse. Run the
diagnostics on the drives and no error's reported.

Cheers,
Crispin

Simon Hayes wrote:
> <crispin.proctor@.gmail.com> wrote in message
> news:1105982769.678231.261080@.f14g2000cwb.googlegr oups.com...
> > Hi All,
> > I have started getting Assertion Errors in SQL.
> > It appears when I process a cube (Most of the time)
> > Other SQL statements, usually with a join or 6 do the same thing.
> > Whaving a scratch around google, I noticed the most people who get
> > these errors are using SATA drives. Either RAID or not.
> > Surprise, I am using SATA in RAID 1.
> > Is this a common thing with SATA? I can't go to the pwers that be
and
> > say I need a couple large SCSI drives because I _think_ it's the
> > SATA's.
> > Another very odd thing that happened thismorning was I copied the
mdf
> > and ldf files off my machine (About 70GB) and onto the server.
attached
> > them and SQL was happy.
> > Select Count(*) from aview gave me the count I was expecting.
> > Select * From aview returned no rows. most of the time.
> > I thought I was going mad. F5 works, then it doesn't then it does
then,
> > you get the point.
> > Backup and restore seemed better until the errors below started...
> > HELP!!!!
> > Thanks
> > Cheers,
> > Crispin
> > 17066 :
> > SQL Server Assertion: File:
> > <q:\SPHINX\NTDBMS\storeng\drs\include\record.inl>, line=1447
> > Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
> This KB article seems to describe what you're seeing:
> http://support.microsoft.com/kb/828337
> Simon|||(crispin.proctor@.gmail.com) writes:
> Problem is the checkDB does the same thing.
> We have replaced the drives, drive controller etc.
> Even replaced the mother board.
> Only thing I have not replaced on the machine is the mouse :)
> My concern is we have shipped some servers to client with a simlar
> config. (SATA drives running RAID 1)
> Now if this is a limitation of SATA I am in a world of sh1t.
> We use SATA drives as a cheaper alternative to SCSI for some clients.
> I reasoned they would be faster than IDE and if Dell, HP etc have SANs
> running on SATA drives, they can't be all that bad.

The KB article that Simon referred you to, suggested that you should
open a case with Microsoft, and I would encourage you do that.

Assertion errors are always bugs in SQL Server in the sense that they
should not occur. But it could be that instead you should have gotten
a better error message. In your case, it appears that your database is
corrupt. (Which could be due to a previous hardware error.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp