lock question

I need help with next problem:
Exist table my_rows with next columns:
id - int identity
name - varchar
is_locked - bit
I need create store procedure which will return every time diferent
row for every request from different processes.
the pseudo code for the procedure :
1 select statement is :
select @.id = selct top 1 id from my_rows where is_locked =0
2 update my_rows set is_locked = 1 where id=@.id
3 return @.id
In different words : if in same time I call this procedure from 2
different connections, it will return 2 different record.
I also want that first call to procedure will not generate lock error
for second call in same time(just second call will wait for finish
first , it is ok.)
ThanksHi
You select and update statement should be contained within one transaction
and you can use the UPDLOCK hint on the select statement to stop others
returning that value
CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
SET NOCOUNT ON
BEGIN TRANSACTION
SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
-- Error checking
UPDATE my_rows SET is_locked = 1 WHERE id=@.id
-- Error checking
COMMIT TRANSACTION
RETURN
Alternatively you could use
UPDATE my_rows
SET is_locked = 1
WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
but you would not know the ID that was updated.
Using this type of locking can potentially cause a bottleneck and poor
performance.
John
"is_vlb50@.hotmail.com" wrote:
> I need help with next problem:
> Exist table my_rows with next columns:
> id - int identity
> name - varchar
> is_locked - bit
> I need create store procedure which will return every time diferent
> row for every request from different processes.
> the pseudo code for the procedure :
> 1 select statement is :
> select @.id = selct top 1 id from my_rows where is_locked =0
> 2 update my_rows set is_locked = 1 where id=@.id
> 3 return @.id
> In different words : if in same time I call this procedure from 2
> different connections, it will return 2 different record.
> I also want that first call to procedure will not generate lock error
> for second call in same time(just second call will wait for finish
> first , it is ok.)
> Thanks
>|||On Sep 26, 10:12 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> You select and update statement should be contained within one transaction
> and you can use the UPDLOCK hint on the select statement to stop others
> returning that value
> CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
> SET NOCOUNT ON
> BEGIN TRANSACTION
> SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
> -- Error checking
> UPDATE my_rows SET is_locked = 1 WHERE id=@.id
> -- Error checking
> COMMIT TRANSACTION
> RETURN
> Alternatively you could use
> UPDATE my_rows
> SET is_locked = 1
> WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
> but you would not know the ID that was updated.
> Using this type of locking can potentially cause a bottleneck and poor
> performance.
> John
>
> "is_vl...@.hotmail.com" wrote:
> > I need help with next problem:
> > Exist table my_rows with next columns:
> > id - int identity
> > name - varchar
> > is_locked - bit
> > I need create store procedure which will return every time diferent
> > row for every request from different processes.
> > the pseudo code for the procedure :
> > 1 select statement is :
> > select @.id = selct top 1 id from my_rows where is_locked =0
> > 2 update my_rows set is_locked = 1 where id=@.id
> > 3 return @.id
> > In different words : if in same time I call this procedure from 2
> > different connections, it will return 2 different record.
> > I also want that first call to procedure will not generate lock error
> > for second call in same time(just second call will wait for finish
> > first , it is ok.)
> > Thanks- Hide quoted text -
> - Show quoted text -
You wroute:
SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked
=0 )
-- Error checking
Could you explain what is reason for "Error checking " and what I can
do.
If some body in same time call same procedure , I can get error ?
Thanks|||Hi
Read http://www.sommarskog.se/error-handling-I.html and
http://www.sommarskog.se/error-handling-II.html which gives you a very good
idea of what to do. If you follow Erlands advice you would also have checked
for errors when you commit the transaction and have code to rollback.
John
"is_vlb50@.hotmail.com" wrote:
> On Sep 26, 10:12 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> >
> > You select and update statement should be contained within one transaction
> > and you can use the UPDLOCK hint on the select statement to stop others
> > returning that value
> >
> > CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
> > SET NOCOUNT ON
> > BEGIN TRANSACTION
> > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
> > -- Error checking
> > UPDATE my_rows SET is_locked = 1 WHERE id=@.id
> > -- Error checking
> > COMMIT TRANSACTION
> > RETURN
> >
> > Alternatively you could use
> >
> > UPDATE my_rows
> > SET is_locked = 1
> > WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
> >
> > but you would not know the ID that was updated.
> >
> > Using this type of locking can potentially cause a bottleneck and poor
> > performance.
> >
> > John
> >
> >
> >
> > "is_vl...@.hotmail.com" wrote:
> > > I need help with next problem:
> > > Exist table my_rows with next columns:
> > > id - int identity
> > > name - varchar
> > > is_locked - bit
> >
> > > I need create store procedure which will return every time diferent
> > > row for every request from different processes.
> >
> > > the pseudo code for the procedure :
> > > 1 select statement is :
> > > select @.id = selct top 1 id from my_rows where is_locked =0
> > > 2 update my_rows set is_locked = 1 where id=@.id
> > > 3 return @.id
> >
> > > In different words : if in same time I call this procedure from 2
> > > different connections, it will return 2 different record.
> > > I also want that first call to procedure will not generate lock error
> > > for second call in same time(just second call will wait for finish
> > > first , it is ok.)
> > > Thanks- Hide quoted text -
> >
> > - Show quoted text -
> You wroute:
> SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked
> =0 )
> -- Error checking
> Could you explain what is reason for "Error checking " and what I can
> do.
> If some body in same time call same procedure , I can get error ?
> Thanks
>
>
>|||On Sep 26, 6:02 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> Readhttp://www.sommarskog.se/error-handling-I.htmlandhttp://www.sommarskog.se/error-handling-II.htmlwhich gives you a very good
> idea of what to do. If you follow Erlands advice you would also have checked
> for errors when you commit the transaction and have code to rollback.
> John
>
> "is_vl...@.hotmail.com" wrote:
> > On Sep 26, 10:12 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > > Hi
> > > You select and update statement should be contained within one transaction
> > > and you can use the UPDLOCK hint on the select statement to stop others
> > > returning that value
> > > CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
> > > SET NOCOUNT ON
> > > BEGIN TRANSACTION
> > > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
> > > -- Error checking
> > > UPDATE my_rows SET is_locked = 1 WHERE id=@.id
> > > -- Error checking
> > > COMMIT TRANSACTION
> > > RETURN
> > > Alternatively you could use
> > > UPDATE my_rows
> > > SET is_locked = 1
> > > WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
> > > but you would not know the ID that was updated.
> > > Using this type of locking can potentially cause a bottleneck and poor
> > > performance.
> > > John
> > > "is_vl...@.hotmail.com" wrote:
> > > > I need help with next problem:
> > > > Exist table my_rows with next columns:
> > > > id - int identity
> > > > name - varchar
> > > > is_locked - bit
> > > > I need create store procedure which will return every time diferent
> > > > row for every request from different processes.
> > > > the pseudo code for the procedure :
> > > > 1 select statement is :
> > > > select @.id = selct top 1 id from my_rows where is_locked =0
> > > > 2 update my_rows set is_locked = 1 where id=@.id
> > > > 3 return @.id
> > > > In different words : if in same time I call this procedure from 2
> > > > different connections, it will return 2 different record.
> > > > I also want that first call to procedure will not generate lock error
> > > > for second call in same time(just second call will wait for finish
> > > > first , it is ok.)
> > > > Thanks- Hide quoted text -
> > > - Show quoted text -
> > You wroute:
> > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked
> > =0 )
> > -- Error checking
> > Could you explain what is reason for "Error checking " and what I can
> > do.
> > If some body in same time call same procedure , I can get error ?
> > Thanks- Hide quoted text -
> - Show quoted text -
Thanks,
it was very helpful. One last question:
if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
call in store procedure can raise error because first call in first SP
still running (still locks the record) or it just will receive another
row?
Thanks|||> if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
> call in store procedure can raise error because first call in first SP
> still running (still locks the record) or it just will receive another
> row?
TOP 1 can give you *any* row. If the row decided for is locked already by an incompatible lock, then
you will be blocked. If you want some other row, you might wan to check out the READPAST hint.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<is_vlb50@.hotmail.com> wrote in message news:1190831328.803233.199140@.19g2000hsx.googlegroups.com...
> On Sep 26, 6:02 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
>> Hi
>> Readhttp://www.sommarskog.se/error-handling-I.htmlandhttp://www.sommarskog.se/error-handling-II.htmlwhich
>> gives you a very good
>> idea of what to do. If you follow Erlands advice you would also have checked
>> for errors when you commit the transaction and have code to rollback.
>> John
>>
>> "is_vl...@.hotmail.com" wrote:
>> > On Sep 26, 10:12 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
>> > > Hi
>> > > You select and update statement should be contained within one transaction
>> > > and you can use the UPDLOCK hint on the select statement to stop others
>> > > returning that value
>> > > CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
>> > > SET NOCOUNT ON
>> > > BEGIN TRANSACTION
>> > > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
>> > > -- Error checking
>> > > UPDATE my_rows SET is_locked = 1 WHERE id=@.id
>> > > -- Error checking
>> > > COMMIT TRANSACTION
>> > > RETURN
>> > > Alternatively you could use
>> > > UPDATE my_rows
>> > > SET is_locked = 1
>> > > WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
>> > > but you would not know the ID that was updated.
>> > > Using this type of locking can potentially cause a bottleneck and poor
>> > > performance.
>> > > John
>> > > "is_vl...@.hotmail.com" wrote:
>> > > > I need help with next problem:
>> > > > Exist table my_rows with next columns:
>> > > > id - int identity
>> > > > name - varchar
>> > > > is_locked - bit
>> > > > I need create store procedure which will return every time diferent
>> > > > row for every request from different processes.
>> > > > the pseudo code for the procedure :
>> > > > 1 select statement is :
>> > > > select @.id = selct top 1 id from my_rows where is_locked =0
>> > > > 2 update my_rows set is_locked = 1 where id=@.id
>> > > > 3 return @.id
>> > > > In different words : if in same time I call this procedure from 2
>> > > > different connections, it will return 2 different record.
>> > > > I also want that first call to procedure will not generate lock error
>> > > > for second call in same time(just second call will wait for finish
>> > > > first , it is ok.)
>> > > > Thanks- Hide quoted text -
>> > > - Show quoted text -
>> > You wroute:
>> > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked
>> > =0 )
>> > -- Error checking
>> > Could you explain what is reason for "Error checking " and what I can
>> > do.
>> > If some body in same time call same procedure , I can get error ?
>> > Thanks- Hide quoted text -
>> - Show quoted text -
> Thanks,
> it was very helpful. One last question:
> if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
> call in store procedure can raise error because first call in first SP
> still running (still locks the record) or it just will receive another
> row?
> Thanks
>|||On Sep 26, 8:40 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
> > call in store procedure can raise error because first call in first SP
> > still running (still locks the record) or it just will receive another
> > row?
> TOP 1 can give you *any* row. If the row decided for is locked already by an incompatible lock, then
> you will be blocked. If you want some other row, you might wan to check out the READPAST hint.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
> <is_vl...@.hotmail.com> wrote in messagenews:1190831328.803233.199140@.19g2000hsx.googlegroups.com...
> > On Sep 26, 6:02 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> >> Hi
> >> Readhttp://www.sommarskog.se/error-handling-I.htmlandhttp://www.sommarsko...
> >> gives you a very good
> >> idea of what to do. If you follow Erlands advice you would also have checked
> >> for errors when you commit the transaction and have code to rollback.
> >> John
> >> "is_vl...@.hotmail.com" wrote:
> >> > On Sep 26, 10:12 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> >> > > Hi
> >> > > You select and update statement should be contained within one transaction
> >> > > and you can use the UPDLOCK hint on the select statement to stop others
> >> > > returning that value
> >> > > CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
> >> > > SET NOCOUNT ON
> >> > > BEGIN TRANSACTION
> >> > > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
> >> > > -- Error checking
> >> > > UPDATE my_rows SET is_locked = 1 WHERE id=@.id
> >> > > -- Error checking
> >> > > COMMIT TRANSACTION
> >> > > RETURN
> >> > > Alternatively you could use
> >> > > UPDATE my_rows
> >> > > SET is_locked = 1
> >> > > WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
> >> > > but you would not know the ID that was updated.
> >> > > Using this type of locking can potentially cause a bottleneck and poor
> >> > > performance.
> >> > > John
> >> > > "is_vl...@.hotmail.com" wrote:
> >> > > > I need help with next problem:
> >> > > > Exist table my_rows with next columns:
> >> > > > id - int identity
> >> > > > name - varchar
> >> > > > is_locked - bit
> >> > > > I need create store procedure which will return every time diferent
> >> > > > row for every request from different processes.
> >> > > > the pseudo code for the procedure :
> >> > > > 1 select statement is :
> >> > > > select @.id = selct top 1 id from my_rows where is_locked =0
> >> > > > 2 update my_rows set is_locked = 1 where id=@.id
> >> > > > 3 return @.id
> >> > > > In different words : if in same time I call this procedure from 2
> >> > > > different connections, it will return 2 different record.
> >> > > > I also want that first call to procedure will not generate lock error
> >> > > > for second call in same time(just second call will wait for finish
> >> > > > first , it is ok.)
> >> > > > Thanks- Hide quoted text -
> >> > > - Show quoted text -
> >> > You wroute:
> >> > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked
> >> > =0 )
> >> > -- Error checking
> >> > Could you explain what is reason for "Error checking " and what I can
> >> > do.
> >> > If some body in same time call same procedure , I can get error ?
> >> > Thanks- Hide quoted text -
> >> - Show quoted text -
> > Thanks,
> > it was very helpful. One last question:
> > if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
> > call in store procedure can raise error because first call in first SP
> > still running (still locks the record) or it just will receive another
> > row?
> > Thanks- Hide quoted text -
> - Show quoted text -
In this case suggestion of John is not valid, because as I described
at start post,I need solution which will in every call to SP in same
time will return back a different row without any block.
Thanks|||On Wed, 26 Sep 2007 18:50:43 -0000, is_vlb50@.hotmail.com wrote:
>In this case suggestion of John is not valid, because as I described
>at start post,I need solution which will in every call to SP in same
>time will return back a different row without any block.
Hi is_vlb50,
As Tibor already mentioned, the READPAST hint can help you achieve what
you need. You'll find all the details in Books Online.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Sep 27, 12:46 am, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Wed, 26 Sep 2007 18:50:43 -0000, is_vl...@.hotmail.com wrote:
> >In this case suggestion of John is not valid, because as I described
> >at start post,I need solution which will in every call to SP in same
> >time will return back a different row without any block.
> Hi is_vlb50,
> As Tibor already mentioned, the READPAST hint can help you achieve what
> you need. You'll find all the details in Books Online.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
the readpast applied only to update, delete, and writetext records.so
if i use
SELECT @.id = ( SELECT top 1 id FROM my_rows (readpast) WHERE
is_locked
=0 ) it will return same records to both simultanously call.
may be i can use it with UPDLOCK hint?
thanks|||Hi
You would be blocked whilst the second transaction finishes, hence the
possibility of a bottleneck. Why do you need to re-use the ids?
John
"is_vlb50@.hotmail.com" wrote:
> On Sep 26, 8:40 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > > if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
> > > call in store procedure can raise error because first call in first SP
> > > still running (still locks the record) or it just will receive another
> > > row?
> >
> > TOP 1 can give you *any* row. If the row decided for is locked already by an incompatible lock, then
> > you will be blocked. If you want some other row, you might wan to check out the READPAST hint.
> >
> > --
> > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> >
> >
> >
> > <is_vl...@.hotmail.com> wrote in messagenews:1190831328.803233.199140@.19g2000hsx.googlegroups.com...
> > > On Sep 26, 6:02 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > >> Hi
> >
> > >> Readhttp://www.sommarskog.se/error-handling-I.htmlandhttp://www.sommarsko...
> > >> gives you a very good
> > >> idea of what to do. If you follow Erlands advice you would also have checked
> > >> for errors when you commit the transaction and have code to rollback.
> >
> > >> John
> >
> > >> "is_vl...@.hotmail.com" wrote:
> > >> > On Sep 26, 10:12 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > >> > > Hi
> >
> > >> > > You select and update statement should be contained within one transaction
> > >> > > and you can use the UPDLOCK hint on the select statement to stop others
> > >> > > returning that value
> >
> > >> > > CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
> > >> > > SET NOCOUNT ON
> > >> > > BEGIN TRANSACTION
> > >> > > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked =0 )
> > >> > > -- Error checking
> > >> > > UPDATE my_rows SET is_locked = 1 WHERE id=@.id
> > >> > > -- Error checking
> > >> > > COMMIT TRANSACTION
> > >> > > RETURN
> >
> > >> > > Alternatively you could use
> >
> > >> > > UPDATE my_rows
> > >> > > SET is_locked = 1
> > >> > > WHERE id = (SELECT MAX(id) FROM my_rows WHERE is_locked = 0)
> >
> > >> > > but you would not know the ID that was updated.
> >
> > >> > > Using this type of locking can potentially cause a bottleneck and poor
> > >> > > performance.
> >
> > >> > > John
> >
> > >> > > "is_vl...@.hotmail.com" wrote:
> > >> > > > I need help with next problem:
> > >> > > > Exist table my_rows with next columns:
> > >> > > > id - int identity
> > >> > > > name - varchar
> > >> > > > is_locked - bit
> >
> > >> > > > I need create store procedure which will return every time diferent
> > >> > > > row for every request from different processes.
> >
> > >> > > > the pseudo code for the procedure :
> > >> > > > 1 select statement is :
> > >> > > > select @.id = selct top 1 id from my_rows where is_locked =0
> > >> > > > 2 update my_rows set is_locked = 1 where id=@.id
> > >> > > > 3 return @.id
> >
> > >> > > > In different words : if in same time I call this procedure from 2
> > >> > > > different connections, it will return 2 different record.
> > >> > > > I also want that first call to procedure will not generate lock error
> > >> > > > for second call in same time(just second call will wait for finish
> > >> > > > first , it is ok.)
> > >> > > > Thanks- Hide quoted text -
> >
> > >> > > - Show quoted text -
> >
> > >> > You wroute:
> > >> > SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) WHERE is_locked
> > >> > =0 )
> > >> > -- Error checking
> >
> > >> > Could you explain what is reason for "Error checking " and what I can
> > >> > do.
> > >> > If some body in same time call same procedure , I can get error ?
> > >> > Thanks- Hide quoted text -
> >
> > >> - Show quoted text -
> >
> > > Thanks,
> > > it was very helpful. One last question:
> > > if statetement "SELECT top 1 id FROM my_rows (UPDLOCK) ..." in second
> > > call in store procedure can raise error because first call in first SP
> > > still running (still locks the record) or it just will receive another
> > > row?
> > > Thanks- Hide quoted text -
> >
> > - Show quoted text -
> In this case suggestion of John is not valid, because as I described
> at start post,I need solution which will in every call to SP in same
> time will return back a different row without any block.
> Thanks
>|||On Wed, 26 Sep 2007 23:38:57 -0700, is_vlb50@.hotmail.com wrote:
>On Sep 27, 12:46 am, Hugo Kornelis
><h...@.perFact.REMOVETHIS.info.INVALID> wrote:
>> On Wed, 26 Sep 2007 18:50:43 -0000, is_vl...@.hotmail.com wrote:
>> >In this case suggestion of John is not valid, because as I described
>> >at start post,I need solution which will in every call to SP in same
>> >time will return back a different row without any block.
>> Hi is_vlb50,
>> As Tibor already mentioned, the READPAST hint can help you achieve what
>> you need. You'll find all the details in Books Online.
>> --
>> Hugo Kornelis, SQL Server MVP
>> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
>the readpast applied only to update, delete, and writetext records.so
>if i use
>SELECT @.id = ( SELECT top 1 id FROM my_rows (readpast) WHERE
>is_locked
>=0 ) it will return same records to both simultanously call.
>may be i can use it with UPDLOCK hint?
>thanks
>
Hi is_vlb50,
Yes, I thought John Bell already covered that.
First, you do a SELECT with UPDLOCK to make sure an exclusive lock is
acquired right away (to prevent two readers getting the same number),
*and* with READPAST to allow it to skip locked rows.
Then (in the same transaction), you do the update. Since you've already
got an exclusive lock, this will neven block or deadlock.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Sep 28, 10:13 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Wed, 26 Sep 2007 23:38:57 -0700, is_vl...@.hotmail.com wrote:
> >On Sep 27, 12:46 am, Hugo Kornelis
> ><h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> >> On Wed, 26 Sep 2007 18:50:43 -0000, is_vl...@.hotmail.com wrote:
> >> >In this case suggestion of John is not valid, because as I described
> >> >at start post,I need solution which will in every call to SP in same
> >> >time will return back a different row without any block.
> >> Hi is_vlb50,
> >> As Tibor already mentioned, the READPAST hint can help you achieve what
> >> you need. You'll find all the details in Books Online.
> >> --
> >> Hugo Kornelis, SQL Server MVP
> >> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
> >the readpast applied only to update, delete, and writetext records.so
> >if i use
> >SELECT @.id = ( SELECT top 1 id FROM my_rows (readpast) WHERE
> >is_locked
> >=0 ) it will return same records to both simultanously call.
> >may be i can use it with UPDLOCK hint?
> >thanks
> Hi is_vlb50,
> Yes, I thought John Bell already covered that.
> First, you do a SELECT with UPDLOCK to make sure an exclusive lock is
> acquired right away (to prevent two readers getting the same number),
> *and* with READPAST to allow it to skip locked rows.
> Then (in the same transaction), you do the update. Since you've already
> got an exclusive lock, this will neven block or deadlock.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -
> - Show quoted text -
just for confirm final solution:
CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
SET NOCOUNT ON
BEGIN TRANSACTION
SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) (READPAST) WHERE
is_locked =0 )
-- Error checking
UPDATE my_rows SET is_locked = 1 WHERE id=@.id
-- Error checking
COMMIT TRANSACTION
RETURN
Thanks|||Hi
From BOL:
WITH ( < table_hint > [ ,...n ] )
Specifies one or more table hints. For more information about table hints,
see FROM.
John
"is_vlb50@.hotmail.com" wrote:
> On Sep 28, 10:13 pm, Hugo Kornelis
> <h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> > On Wed, 26 Sep 2007 23:38:57 -0700, is_vl...@.hotmail.com wrote:
> > >On Sep 27, 12:46 am, Hugo Kornelis
> > ><h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> > >> On Wed, 26 Sep 2007 18:50:43 -0000, is_vl...@.hotmail.com wrote:
> > >> >In this case suggestion of John is not valid, because as I described
> > >> >at start post,I need solution which will in every call to SP in same
> > >> >time will return back a different row without any block.
> >
> > >> Hi is_vlb50,
> >
> > >> As Tibor already mentioned, the READPAST hint can help you achieve what
> > >> you need. You'll find all the details in Books Online.
> >
> > >> --
> > >> Hugo Kornelis, SQL Server MVP
> > >> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
> > >the readpast applied only to update, delete, and writetext records.so
> > >if i use
> > >SELECT @.id = ( SELECT top 1 id FROM my_rows (readpast) WHERE
> > >is_locked
> > >=0 ) it will return same records to both simultanously call.
> > >may be i can use it with UPDLOCK hint?
> > >thanks
> >
> > Hi is_vlb50,
> >
> > Yes, I thought John Bell already covered that.
> >
> > First, you do a SELECT with UPDLOCK to make sure an exclusive lock is
> > acquired right away (to prevent two readers getting the same number),
> > *and* with READPAST to allow it to skip locked rows.
> >
> > Then (in the same transaction), you do the update. Since you've already
> > got an exclusive lock, this will neven block or deadlock.
> >
> > --
> > Hugo Kornelis, SQL Server MVP
> > My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -
> >
> > - Show quoted text -
> just for confirm final solution:
> CREATE PROCEDURE GetLock ( @.id int OUTPUT ) AS
> SET NOCOUNT ON
> BEGIN TRANSACTION
> SELECT @.id = ( SELECT top 1 id FROM my_rows (UPDLOCK) (READPAST) WHERE
> is_locked =0 )
> -- Error checking
> UPDATE my_rows SET is_locked = 1 WHERE id=@.id
> -- Error checking
> COMMIT TRANSACTION
> RETURN
> Thanks
>

Locate records that meet ALL the requirements using IN or EXIST? Help?

Using SQL Server 2000...
I have a front end that provides the user with a 'search engine' to
pass search parameters. The stored procedure it calls joins multiple
tables/views to return the proper result.
I am running into a problem that I solved in the interface using code
(that takes WAY too long), but I know there must be a way to have SQL
Server do the work using T-SQL... as this seems a very simple issue.
The following is just a snippet of the pertinent information:
Assume I have a main Customer table with a unique CustID field.
I have another table of descriptive Flags with a unique FlagID field.
A third table Customer_Flag_Link has a unique ID field and contains 2
columns, the CustID and the FlagID.
Obvioulsy, the role of this 3rd table is to be able to assign multiple
Flags to each Customer.
Now assume I have the following data in the Customer_Flag_Link table:
UniqueID CustID FlagID
===============================
1 123 333
2 123 444
3 123 222
4 987 444
5 987 222
6 567 111
7 567 222
My issue is that I want to be able to locate Customers who have ALL of
the passed Flags associated with them, I do not know how many Flags
will be passed (and there are other search parameters passed as well -
but this is the piece that is giving me trouble, though I am sure it
is simple!)
For example, I want to return CustID where exists FlagID 222 AND 444.
Based on the above data it should return CustID 123 and 987, but not
567.
Currently, I use the IN operator (ie: FlagID IN (222,444)) but clearly
this does not give me the results I want.
Any ideas would be appreciated!
Thanks,
AK
This might help.
SELECT COUNT(distinct FlagID)
FROM Customer_Flag_Link
WHERE FlagID IN (222,444)
AND Customer = 'Ralph'
HAVING COUNT(distinct FlagID) = 2
The number of items in the IN clause is what is used in the HAVING
test. Of course this could be written as an EXISTS subquery,
correlated on Customer.
Roy Harvey
Beacon Falls, CT
On Thu, 21 Jun 2007 10:50:45 -0700, aklein <abklein@.optonline.net>
wrote:

>Using SQL Server 2000...
>I have a front end that provides the user with a 'search engine' to
>pass search parameters. The stored procedure it calls joins multiple
>tables/views to return the proper result.
>I am running into a problem that I solved in the interface using code
>(that takes WAY too long), but I know there must be a way to have SQL
>Server do the work using T-SQL... as this seems a very simple issue.
>The following is just a snippet of the pertinent information:
>Assume I have a main Customer table with a unique CustID field.
>I have another table of descriptive Flags with a unique FlagID field.
>A third table Customer_Flag_Link has a unique ID field and contains 2
>columns, the CustID and the FlagID.
>Obvioulsy, the role of this 3rd table is to be able to assign multiple
>Flags to each Customer.
>Now assume I have the following data in the Customer_Flag_Link table:
>
>UniqueID CustID FlagID
>===============================
>1 123 333
>2 123 444
>3 123 222
>4 987 444
>5 987 222
>6 567 111
>7 567 222
>My issue is that I want to be able to locate Customers who have ALL of
>the passed Flags associated with them, I do not know how many Flags
>will be passed (and there are other search parameters passed as well -
>but this is the piece that is giving me trouble, though I am sure it
>is simple!)
>For example, I want to return CustID where exists FlagID 222 AND 444.
>Based on the above data it should return CustID 123 and 987, but not
>567.
>Currently, I use the IN operator (ie: FlagID IN (222,444)) but clearly
>this does not give me the results I want.
>Any ideas would be appreciated!
>Thanks,
>AK
|||Hmmmm...
I have to see how to stick that idea into the larger procedure... but
it gives me a starting point.
Thanks for the idea!
AK

Locate records that meet ALL the requirements using IN or EXIST? Help?

Using SQL Server 2000...
I have a front end that provides the user with a 'search engine' to
pass search parameters. The stored procedure it calls joins multiple
tables/views to return the proper result.
I am running into a problem that I solved in the interface using code
(that takes WAY too long), but I know there must be a way to have SQL
Server do the work using T-SQL... as this seems a very simple issue.
The following is just a snippet of the pertinent information:
Assume I have a main Customer table with a unique CustID field.
I have another table of descriptive Flags with a unique FlagID field.
A third table Customer_Flag_Link has a unique ID field and contains 2
columns, the CustID and the FlagID.
Obvioulsy, the role of this 3rd table is to be able to assign multiple
Flags to each Customer.
Now assume I have the following data in the Customer_Flag_Link table:
UniqueID CustID FlagID
=============================== 1 123 333
2 123 444
3 123 222
4 987 444
5 987 222
6 567 111
7 567 222
My issue is that I want to be able to locate Customers who have ALL of
the passed Flags associated with them, I do not know how many Flags
will be passed (and there are other search parameters passed as well -
but this is the piece that is giving me trouble, though I am sure it
is simple!)
For example, I want to return CustID where exists FlagID 222 AND 444.
Based on the above data it should return CustID 123 and 987, but not
567.
Currently, I use the IN operator (ie: FlagID IN (222,444)) but clearly
this does not give me the results I want.
Any ideas would be appreciated!
Thanks,
AKThis might help.
SELECT COUNT(distinct FlagID)
FROM Customer_Flag_Link
WHERE FlagID IN (222,444)
AND Customer = 'Ralph'
HAVING COUNT(distinct FlagID) = 2
The number of items in the IN clause is what is used in the HAVING
test. Of course this could be written as an EXISTS subquery,
correlated on Customer.
Roy Harvey
Beacon Falls, CT
On Thu, 21 Jun 2007 10:50:45 -0700, aklein <abklein@.optonline.net>
wrote:
>Using SQL Server 2000...
>I have a front end that provides the user with a 'search engine' to
>pass search parameters. The stored procedure it calls joins multiple
>tables/views to return the proper result.
>I am running into a problem that I solved in the interface using code
>(that takes WAY too long), but I know there must be a way to have SQL
>Server do the work using T-SQL... as this seems a very simple issue.
>The following is just a snippet of the pertinent information:
>Assume I have a main Customer table with a unique CustID field.
>I have another table of descriptive Flags with a unique FlagID field.
>A third table Customer_Flag_Link has a unique ID field and contains 2
>columns, the CustID and the FlagID.
>Obvioulsy, the role of this 3rd table is to be able to assign multiple
>Flags to each Customer.
>Now assume I have the following data in the Customer_Flag_Link table:
>
>UniqueID CustID FlagID
>===============================>1 123 333
>2 123 444
>3 123 222
>4 987 444
>5 987 222
>6 567 111
>7 567 222
>My issue is that I want to be able to locate Customers who have ALL of
>the passed Flags associated with them, I do not know how many Flags
>will be passed (and there are other search parameters passed as well -
>but this is the piece that is giving me trouble, though I am sure it
>is simple!)
>For example, I want to return CustID where exists FlagID 222 AND 444.
>Based on the above data it should return CustID 123 and 987, but not
>567.
>Currently, I use the IN operator (ie: FlagID IN (222,444)) but clearly
>this does not give me the results I want.
>Any ideas would be appreciated!
>Thanks,
>AK|||Hmmmm...
I have to see how to stick that idea into the larger procedure... but
it gives me a starting point.
Thanks for the idea!
AK

Locate records that meet ALL the requirements using IN or EXIST? Help?

Using SQL Server 2000...
I have a front end that provides the user with a 'search engine' to
pass search parameters. The stored procedure it calls joins multiple
tables/views to return the proper result.
I am running into a problem that I solved in the interface using code
(that takes WAY too long), but I know there must be a way to have SQL
Server do the work using T-SQL... as this seems a very simple issue.
The following is just a snippet of the pertinent information:
Assume I have a main Customer table with a unique CustID field.
I have another table of descriptive Flags with a unique FlagID field.
A third table Customer_Flag_Link has a unique ID field and contains 2
columns, the CustID and the FlagID.
Obvioulsy, the role of this 3rd table is to be able to assign multiple
Flags to each Customer.
Now assume I have the following data in the Customer_Flag_Link table:
UniqueID CustID FlagID
===============================
1 123 333
2 123 444
3 123 222
4 987 444
5 987 222
6 567 111
7 567 222
My issue is that I want to be able to locate Customers who have ALL of
the passed Flags associated with them, I do not know how many Flags
will be passed (and there are other search parameters passed as well -
but this is the piece that is giving me trouble, though I am sure it
is simple!)
For example, I want to return CustID where exists FlagID 222 AND 444.
Based on the above data it should return CustID 123 and 987, but not
567.
Currently, I use the IN operator (ie: FlagID IN (222,444)) but clearly
this does not give me the results I want.
Any ideas would be appreciated!
Thanks,
AKThis might help.
SELECT COUNT(distinct FlagID)
FROM Customer_Flag_Link
WHERE FlagID IN (222,444)
AND Customer = 'Ralph'
HAVING COUNT(distinct FlagID) = 2
The number of items in the IN clause is what is used in the HAVING
test. Of course this could be written as an EXISTS subquery,
correlated on Customer.
Roy Harvey
Beacon Falls, CT
On Thu, 21 Jun 2007 10:50:45 -0700, aklein <abklein@.optonline.net>
wrote:

>Using SQL Server 2000...
>I have a front end that provides the user with a 'search engine' to
>pass search parameters. The stored procedure it calls joins multiple
>tables/views to return the proper result.
>I am running into a problem that I solved in the interface using code
>(that takes WAY too long), but I know there must be a way to have SQL
>Server do the work using T-SQL... as this seems a very simple issue.
>The following is just a snippet of the pertinent information:
>Assume I have a main Customer table with a unique CustID field.
>I have another table of descriptive Flags with a unique FlagID field.
>A third table Customer_Flag_Link has a unique ID field and contains 2
>columns, the CustID and the FlagID.
>Obvioulsy, the role of this 3rd table is to be able to assign multiple
>Flags to each Customer.
>Now assume I have the following data in the Customer_Flag_Link table:
>
>UniqueID CustID FlagID
>===============================
>1 123 333
>2 123 444
>3 123 222
>4 987 444
>5 987 222
>6 567 111
>7 567 222
>My issue is that I want to be able to locate Customers who have ALL of
>the passed Flags associated with them, I do not know how many Flags
>will be passed (and there are other search parameters passed as well -
>but this is the piece that is giving me trouble, though I am sure it
>is simple!)
>For example, I want to return CustID where exists FlagID 222 AND 444.
>Based on the above data it should return CustID 123 and 987, but not
>567.
>Currently, I use the IN operator (ie: FlagID IN (222,444)) but clearly
>this does not give me the results I want.
>Any ideas would be appreciated!
>Thanks,
>AK|||Hmmmm...
I have to see how to stick that idea into the larger procedure... but
it gives me a starting point.
Thanks for the idea!
AK

local app to access sql server box in a different office

Hello,
What different methods exist when trying to set up a sql server box to
be accessible to an app that needs to connect to it through DSN.
The app is running in a different city than the sql server box.
Would I need to do this through a VPN, IIS?
Any suggestions much appreciated.
You'll need to establish some form of network connectivity between your
offices. This could be anything from a VPN connection over the internet, to
dedicated lines (an expensive but potentially more reliable option). You
may want to post this question to microsoft.public.windows.server.networking
group for other ideas and advice.
--Brian
(Please reply to the newsgroups only.)
"Developer in Need of Information"
<DeveloperinNeedofInformation@.discussions.microsof t.com> wrote in message
news:9CAD55C5-2D7F-4CDF-8E46-255D1ACEC8DC@.microsoft.com...
> Hello,
> What different methods exist when trying to set up a sql server box to
> be accessible to an app that needs to connect to it through DSN.
> The app is running in a different city than the sql server box.
> Would I need to do this through a VPN, IIS?
> Any suggestions much appreciated.

local app to access sql server box in a different office

Hello,
What different methods exist when trying to set up a sql server box to
be accessible to an app that needs to connect to it through DSN.
The app is running in a different city than the sql server box.
Would I need to do this through a VPN, IIS?
Any suggestions much appreciated.You'll need to establish some form of network connectivity between your
offices. This could be anything from a VPN connection over the internet, to
dedicated lines (an expensive but potentially more reliable option). You
may want to post this question to microsoft.public.windows.server.networking
group for other ideas and advice.
--Brian
(Please reply to the newsgroups only.)
"Developer in Need of Information"
< DeveloperinNeedofInformation@.discussions
.microsoft.com> wrote in message
news:9CAD55C5-2D7F-4CDF-8E46-255D1ACEC8DC@.microsoft.com...
> Hello,
> What different methods exist when trying to set up a sql server box to
> be accessible to an app that needs to connect to it through DSN.
> The app is running in a different city than the sql server box.
> Would I need to do this through a VPN, IIS?
> Any suggestions much appreciated.