Very Computer

by **Tim** » Sat, 21 Sep 2002 20:34:50

Hi,
trying to workout what transaction isolation level to use for the
following extract of code from a Stored procedure

-- do other stuff
update Locations set used = 1 where location = location

If i use repeatable read then other users will not be able to update the
same record if i have not yet updated but they may still get the same
location(from the select) as i have not set the flag yet. I can replace the
code with the following however i was wondering if changing the default
transaction isolation level could resolve this issue

backin:

-- do other stuff
update Locations set used = 1 where location = location and used = 0

Thanks in advance

Tim

by **Andrew Joh** » Sat, 21 Sep 2002 22:06:35

Tim,

This may work:

BEGIN

-- Do other stuff

ELSE
BEGIN
-- Error handling
END
ELSE
BEGIN
-- Error Handling
END

Which is I think equivalent to the SERIALIZABLE isolation level.

Hope this helps

AJ

> Hi,
> trying to workout what transaction isolation level to use for the
> following extract of code from a Stored procedure

> -- do other stuff
> update Locations set used = 1 where location = location

> If i use repeatable read then other users will not be able to update the
> same record if i have not yet updated but they may still get the same
> location(from the select) as i have not set the flag yet. I can replace the
> code with the following however i was wondering if changing the default
> transaction isolation level could resolve this issue

> backin:

> -- do other stuff
> update Locations set used = 1 where location = location and used = 0

> Thanks in advance

> Tim

by **Andrew Joh** » Sat, 21 Sep 2002 22:09:28

Tim,

This may work:

BEGIN

-- Do other stuff

ELSE
BEGIN
-- Error handling
END
ELSE
BEGIN
-- Error Handling
END

Which is I think equivalent to the SERIALIZABLE isolation level.

Hope this helps

AJ

> Hi,
> trying to workout what transaction isolation level to use for the
> following extract of code from a Stored procedure

> -- do other stuff
> update Locations set used = 1 where location = location

> If i use repeatable read then other users will not be able to update the
> same record if i have not yet updated but they may still get the same
> location(from the select) as i have not set the flag yet. I can replace the
> code with the following however i was wondering if changing the default
> transaction isolation level could resolve this issue

> backin:

> -- do other stuff
> update Locations set used = 1 where location = location and used = 0

> Thanks in advance

> Tim

by **Tim** » Sat, 21 Sep 2002 23:17:54

Hi Andrew,
if i put a WAITFOR DELAY '000:00:10' in the stored
procedure between the select and the update, and then using two query panes
run the store procedure, they both get the same result. I have tried
setting the default to serializable but the second query gets chosen as a
dead lock victim same occurs with repeatable read.

Any ideas?

Tim B

> Tim,

> This may work:

where unused = 0 order by Location)

> BEGIN

> -- Do other stuff

> ELSE
> BEGIN
> -- Error handling
> END
> ELSE
> BEGIN
> -- Error Handling
> END

> Which is I think equivalent to the SERIALIZABLE isolation level.

> Hope this helps

> AJ

- Show quoted text -

> > Hi,
> > trying to workout what transaction isolation level to use for the
> > following extract of code from a Stored procedure

> > -- do other stuff
> > update Locations set used = 1 where location = location

> > If i use repeatable read then other users will not be able to update the
> > same record if i have not yet updated but they may still get the same
> > location(from the select) as i have not set the flag yet. I can replace
the
> > code with the following however i was wondering if changing the default
> > transaction isolation level could resolve this issue

> > backin:

> > -- do other stuff
> > update Locations set used = 1 where location = location and used = 0

> > Thanks in advance

> > Tim

by **Andrew Joh** » Sat, 21 Sep 2002 23:38:12

Tim,

Works fine for me. Here is the test code I used
( normal newsgroup practise is for YOU to supply this, in order to provide
us with less typing ! )

Data:

create table Locations
(
Location int identity ( 1, 1 ),
unused int,
SomeOtherCol varchar ( 30)
)
insert into Locations ( unused, SomeOtherCol ) values ( 0, 'First' )
insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Second' )
insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Third' )
insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Fourth' )
insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Fifth' )
insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Sixth' )
insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Seventh' )
insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Eighth' )
insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Nineth' )
insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Tenth' )

Query 1:

from Locations with (READPAST)
where unused = 0
order by Location

BEGIN

BEGIN

-- Do other stuff
waitfor delay '000:00:20'

END
ELSE
BEGIN
-- Error handling
print 'No Update'
END
END
ELSE
BEGIN
-- Error Handling
print 'No free row'
END

Query 2: ( started a couple of seconds after Query 1 - only difference is Tran2 vs Tran1 )

from Locations with (READPAST)
where unused = 0
order by Location

BEGIN

BEGIN

-- Do other stuff
waitfor delay '000:00:20'

END
ELSE
BEGIN
-- Error handling
print 'No Update'
END
END
ELSE
BEGIN
-- Error Handling
print 'No free row'
END

Result:

select * from Locations

Location unused SomeOtherCol
----------- ----------- ------------------------------
1 1 FirstTran1
2 1 SecondTran2
3 0 Third
4 0 Fourth
5 0 Fifth
6 0 Sixth
7 0 Seventh
8 0 Eighth
9 0 Nineth
10 0 Tenth

(10 row(s) affected)

Regards
AJ

> Hi Andrew,
> if i put a WAITFOR DELAY '000:00:10' in the stored
> procedure between the select and the update, and then using two query panes
> run the store procedure, they both get the same result. I have tried
> setting the default to serializable but the second query gets chosen as a
> dead lock victim same occurs with repeatable read.

> Any ideas?

> Tim B

> > Tim,

> > This may work:

> where unused = 0 order by Location)

> > BEGIN
> > update Locations with (holdlock) set used = 1 where location =

> > -- Do other stuff

> > ELSE
> > BEGIN
> > -- Error handling
> > END
> > ELSE
> > BEGIN
> > -- Error Handling
> > END

> > Which is I think equivalent to the SERIALIZABLE isolation level.

> > Hope this helps

> > AJ

> > > Hi,
> > > trying to workout what transaction isolation level to use for the
> > > following extract of code from a Stored procedure

> > > -- do other stuff
> > > update Locations set used = 1 where location = location

> > > If i use repeatable read then other users will not be able to update the
> > > same record if i have not yet updated but they may still get the same
> > > location(from the select) as i have not set the flag yet. I can replace
> the
> > > code with the following however i was wondering if changing the default
> > > transaction isolation level could resolve this issue

> > > backin:

> > > -- do other stuff
> > > update Locations set used = 1 where location = location and used = 0

> > > Thanks in advance

> > > Tim

by **Tim** » Sun, 22 Sep 2002 00:28:32

Hi Andrew,
many thanks for the quick reply. Please try the same test as
you used below however with the waitfor "BETWEEN the SELECT and the UPDATE".
Your sample has probably executed the first update before the second
transaction starts running, This update is then locking the record, which
then causes the second transaction to readpast the locked record and suggest
the next available record. However as the work is being done between the
select and the update, if the select doesnt lock the record from being seen
from a different transaction both transactions could have the same Location
before they perform the update.

Thanks greatly in advance
Tim

> Tim,

> Works fine for me. Here is the test code I used
> ( normal newsgroup practise is for YOU to supply this, in order to provide
> us with less typing ! )

> Data:

> create table Locations
> (
> Location int identity ( 1, 1 ),
> unused int,
> SomeOtherCol varchar ( 30)
> )
> insert into Locations ( unused, SomeOtherCol ) values ( 0, 'First' )
> insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Second' )
> insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Third' )
> insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Fourth' )
> insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Fifth' )
> insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Sixth' )
> insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Seventh' )
> insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Eighth' )
> insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Nineth' )
> insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Tenth' )

> Query 1:

> from Locations with (READPAST)
> where unused = 0
> order by Location

> BEGIN

> BEGIN

> -- Do other stuff
> waitfor delay '000:00:20'
> update Locations set SomeOtherCol = SomeOtherCol + 'Tran1' where

- Show quoted text -

> END
> ELSE
> BEGIN
> -- Error handling
> print 'No Update'
> END
> END
> ELSE
> BEGIN
> -- Error Handling
> print 'No free row'
> END

> Query 2: ( started a couple of seconds after Query 1 - only difference is
Tran2 vs Tran1 )

> from Locations with (READPAST)
> where unused = 0
> order by Location

> BEGIN

> BEGIN

> -- Do other stuff
> waitfor delay '000:00:20'
> update Locations set SomeOtherCol = SomeOtherCol + 'Tran2' where

- Show quoted text -

> END
> ELSE
> BEGIN
> -- Error handling
> print 'No Update'
> END
> END
> ELSE
> BEGIN
> -- Error Handling
> print 'No free row'
> END

> Result:

> select * from Locations

> Location unused SomeOtherCol
> ----------- ----------- ------------------------------
> 1 1 FirstTran1
> 2 1 SecondTran2
> 3 0 Third
> 4 0 Fourth
> 5 0 Fifth
> 6 0 Sixth
> 7 0 Seventh
> 8 0 Eighth
> 9 0 Nineth
> 10 0 Tenth

> (10 row(s) affected)

> Regards
> AJ

- Show quoted text -

> > Hi Andrew,
> > if i put a WAITFOR DELAY '000:00:10' in the stored
> > procedure between the select and the update, and then using two query
panes
> > run the store procedure, they both get the same result. I have tried
> > setting the default to serializable but the second query gets chosen as
a
> > dead lock victim same occurs with repeatable read.

> > Any ideas?

> > Tim B

> > > Tim,

> > > This may work:

(READPAST)
> > where unused = 0 order by Location)

> > > BEGIN
> > > update Locations with (holdlock) set used = 1 where location =

> > > -- Do other stuff

> > > ELSE
> > > BEGIN
> > > -- Error handling
> > > END
> > > ELSE
> > > BEGIN
> > > -- Error Handling
> > > END

> > > Which is I think equivalent to the SERIALIZABLE isolation level.

> > > Hope this helps

> > > AJ

> > > > Hi,
> > > > trying to workout what transaction isolation level to use for
the
> > > > following extract of code from a Stored procedure

> > > > -- do other stuff
> > > > update Locations set used = 1 where location = location

> > > > If i use repeatable read then other users will not be able to update
the
> > > > same record if i have not yet updated but they may still get the
same
> > > > location(from the select) as i have not set the flag yet. I can
replace
> > the
> > > > code with the following however i was wondering if changing the
default
> > > > transaction isolation level could resolve this issue

> > > > backin:

> > > > -- do other stuff
> > > > update Locations set used = 1 where location = location and used =
0

> > > > Thanks in advance

> > > > Tim

by **Andrew Joh** » Tue, 24 Sep 2002 08:19:55

Tim,

Yes, in hindsite I should have kept quiet and seen if anyone else was going to respond, as I am aware of this
limitation - It takes 2 updates, one to grab the row, then the 2nd after all the processing results are ready.
What you are really after is something like a SELECT statement, using a combination of READPAST and HOLDLOCK,
but these do not work together in a select. - I have tried extensively, and have both today, and
previously searched in vain for working code posted in this newsgroup, or the internet at large.

This is the best I can come up with:

declare @location int
set @location = NULL

update Locations with (HOLDLOCK)
set @Location = Location,
unused = 1
where Location =
(
select top 1 Location
from Locations with ( READPAST )
where unused = 0
order by Location
)

IF @location is not NULL
BEGIN

-- Do other stuff
waitfor delay '000:00:10'
update Locations
set SomeOtherCol = SomeOtherCol + 'Tran1'
where location = @location

if @@rowcount <> 1
BEGIN
-- Error handling
print 'No Update'
END
END
ELSE
BEGIN
-- Error Handling
print 'No free row'
END

The holdlock is probably not required in this example.
Puting it all in a transaction so you can cancel the 1st update if the processing or 2nd fails is a reasonable
extension. If this approach is not suitable, then I suggest you repost with something like
"How do I select with HOLDLOCK and READPAST, in order to implement a work queue"
and I'll keep my mouth shut this time.

Regards
AJ

"Tim B" <t...@innov8uk.com> wrote in message news:#1I3UoLYCHA.2032@tkmsftngp11...
> Hi Andrew,
> many thanks for the quick reply. Please try the same test as
> you used below however with the waitfor "BETWEEN the SELECT and the UPDATE".
> Your sample has probably executed the first update before the second
> transaction starts running, This update is then locking the record, which
> then causes the second transaction to readpast the locked record and suggest
> the next available record. However as the work is being done between the
> select and the update, if the select doesnt lock the record from being seen
> from a different transaction both transactions could have the same Location
> before they perform the update.

> Thanks greatly in advance
> Tim

> "Andrew John" <a...@NOSPAMmistrose.com> wrote in message
> news:#zVO9MLYCHA.2292@tkmsftngp12...
> > Tim,

> > Works fine for me. Here is the test code I used
> > ( normal newsgroup practise is for YOU to supply this, in order to provide
> > us with less typing ! )

> > Data:

> > create table Locations
> > (
> > Location int identity ( 1, 1 ),
> > unused int,
> > SomeOtherCol varchar ( 30)
> > )
> > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'First' )
> > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Second' )
> > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Third' )
> > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Fourth' )
> > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Fifth' )
> > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Sixth' )
> > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Seventh' )
> > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Eighth' )
> > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Nineth' )
> > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Tenth' )

> > Query 1:

> > declare @location int
> > set @location = NULL
> > select top 1 @location = Location
> > from Locations with (READPAST)
> > where unused = 0
> > order by Location

> > IF @location is not NULL
> > BEGIN
> > update Locations with (holdlock) set unused = 1 where location =
> @location
> > IF @@Rowcount = 1
> > BEGIN

> > -- Do other stuff
> > waitfor delay '000:00:20'
> > update Locations set SomeOtherCol = SomeOtherCol + 'Tran1' where
> location = @location

> > END
> > ELSE
> > BEGIN
> > -- Error handling
> > print 'No Update'
> > END
> > END
> > ELSE
> > BEGIN
> > -- Error Handling
> > print 'No free row'
> > END

> > Query 2: ( started a couple of seconds after Query 1 - only difference is
> Tran2 vs Tran1 )

> > declare @location int
> > set @location = NULL
> > select top 1 @location = Location
> > from Locations with (READPAST)
> > where unused = 0
> > order by Location

> > IF @location is not NULL
> > BEGIN
> > update Locations with (holdlock) set unused = 1 where location =
> @location
> > IF @@Rowcount = 1
> > BEGIN

> > -- Do other stuff
> > waitfor delay '000:00:20'
> > update Locations set SomeOtherCol = SomeOtherCol + 'Tran2' where
> location = @location

> > END
> > ELSE
> > BEGIN
> > -- Error handling
> > print 'No Update'
> > END
> > END
> > ELSE
> > BEGIN
> > -- Error Handling
> > print 'No free row'
> > END

> > Result:

> > select * from Locations

> > Location unused SomeOtherCol
> > ----------- ----------- ------------------------------
> > 1 1 FirstTran1
> > 2 1 SecondTran2
> > 3 0 Third
> > 4 0 Fourth
> > 5 0 Fifth
> > 6 0 Sixth
> > 7 0 Seventh
> > 8 0 Eighth
> > 9 0 Nineth
> > 10 0 Tenth

> > (10 row(s) affected)

> > Regards
> > AJ

> > "Tim B" <t...@innov8uk.com> wrote in message
> news:uFXk2ALYCHA.464@tkmsftngp12...
> > > Hi Andrew,
> > > if i put a WAITFOR DELAY '000:00:10' in the stored
> > > procedure between the select and the update, and then using two query
> panes
> > > run the store procedure, they both get the same result. I have tried
> > > setting the default to serializable but the second query gets chosen as
> a
> > > dead lock victim same occurs with repeatable read.

> > > Any ideas?

> > > Tim B

> > > "Andrew John" <a...@NOSPAMmistrose.com> wrote in message
> > > news:uDDRxZKYCHA.1792@tkmsftngp10...
> > > > Tim,

> > > > This may work:

> > > > set @location = NULL
> > > > select @location = (select top 1 Location from Locations with
> (READPAST)
> > > where unused = 0 order by Location)

> > > > IF @location is not NULL
> > > > BEGIN
> > > > update Locations with (holdlock) set used = 1 where location =
> > > @location
> > > > IF @@Rowcount = 1

> > > > -- Do other stuff

> > > > ELSE
> > > > BEGIN
> > > > -- Error handling
> > > > END
> > > > ELSE
> > > > BEGIN
> > > > -- Error Handling
> > > > END

> > > > Which is I think equivalent to the SERIALIZABLE isolation level.

> > > > Hope this helps

> > > > AJ

> > > > "Tim B" <t...@innov8uk.com> wrote in message
> > > news:#v$6ulJYCHA.1732@tkmsftngp10...
> > > > > Hi,
> > > > > trying to workout what transaction isolation level to use for
> the
> > > > > following extract of code from a Stored procedure

> > > > > select @location = (select Location from Locations where unused = 0)
> > > > > -- do other stuff
> > > > > update Locations set used = 1 where location = location

> > > > > If i use repeatable read then other users will not be able to update
> the
> > > > > same record if i have not yet updated but they may still get the
> same
> > > > > location(from the select) as i have not set the flag yet. I can
> replace
> > > the
> > > > > code with the following however i was wondering if changing the
> default
> > > > > transaction isolation level could resolve this issue

> > > > > backin:
> > > > > select @location = (select Location from Locations where unused = 0)
> > > > > -- do other stuff
> > > > > update Locations set used = 1 where location = location and used =
> 0
> > > > > if @@rowcou nt = 0 then goto backin

> > > > > Thanks in advance

> > > > > Tim

by **Ulric** » Wed, 25 Sep 2002 05:59:24

see sp_getapplock in BOL

"Andrew John" <a...@NOSPAMmistrose.com> wrote in message

news:#idT66oYCHA.2264@tkmsftngp09...

> Tim,

> Yes, in hindsite I should have kept quiet and seen if anyone else was

going to respond, as I am aware of this

> limitation - It takes 2 updates, one to grab the row, then the 2nd after

all the processing results are ready.

> What you are really after is something like a SELECT statement, using a

combination of READPAST and HOLDLOCK,

> but these do not work together in a select. - I have tried extensively,

and have both today, and

> previously searched in vain for working code posted in this newsgroup, or

the internet at large.

> This is the best I can come up with:

> declare @location int
> set @location = NULL

> update Locations with (HOLDLOCK)
> set @Location = Location,
> unused = 1
> where Location =
> (
> select top 1 Location
> from Locations with ( READPAST )
> where unused = 0
> order by Location
> )

> IF @location is not NULL
> BEGIN

> -- Do other stuff
> waitfor delay '000:00:10'
> update Locations
> set SomeOtherCol = SomeOtherCol + 'Tran1'
> where location = @location

> if @@rowcount <> 1
> BEGIN
> -- Error handling
> print 'No Update'
> END
> END
> ELSE
> BEGIN
> -- Error Handling
> print 'No free row'
> END

> The holdlock is probably not required in this example.
> Puting it all in a transaction so you can cancel the 1st update if the

processing or 2nd fails is a reasonable

> extension. If this approach is not suitable, then I suggest you repost
with something like
> "How do I select with HOLDLOCK and READPAST, in order to implement a work
queue"
> and I'll keep my mouth shut this time.

> Regards
> AJ

> "Tim B" <t...@innov8uk.com> wrote in message

news:#1I3UoLYCHA.2032@tkmsftngp11...

- Show quoted text -

> > Hi Andrew,
> > many thanks for the quick reply. Please try the same
test as
> > you used below however with the waitfor "BETWEEN the SELECT and the
UPDATE".
> > Your sample has probably executed the first update before the second
> > transaction starts running, This update is then locking the record,
which
> > then causes the second transaction to readpast the locked record and
suggest
> > the next available record. However as the work is being done between
the
> > select and the update, if the select doesnt lock the record from being
seen
> > from a different transaction both transactions could have the same
Location
> > before they perform the update.

> > Thanks greatly in advance
> > Tim

> > "Andrew John" <a...@NOSPAMmistrose.com> wrote in message
> > news:#zVO9MLYCHA.2292@tkmsftngp12...
> > > Tim,

> > > Works fine for me. Here is the test code I used
> > > ( normal newsgroup practise is for YOU to supply this, in order to
provide
> > > us with less typing ! )

> > > Data:

> > > create table Locations
> > > (
> > > Location int identity ( 1, 1 ),
> > > unused int,
> > > SomeOtherCol varchar ( 30)
> > > )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'First' )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Second' )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Third' )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Fourth' )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Fifth' )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Sixth' )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Seventh' )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Eighth' )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Nineth' )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Tenth' )

> > > Query 1:

> > > declare @location int
> > > set @location = NULL
> > > select top 1 @location = Location
> > > from Locations with (READPAST)
> > > where unused = 0
> > > order by Location

> > > IF @location is not NULL
> > > BEGIN
> > > update Locations with (holdlock) set unused = 1 where location =
> > @location
> > > IF @@Rowcount = 1
> > > BEGIN

> > > -- Do other stuff
> > > waitfor delay '000:00:20'
> > > update Locations set SomeOtherCol = SomeOtherCol + 'Tran1' where
> > location = @location

> > > END
> > > ELSE
> > > BEGIN
> > > -- Error handling
> > > print 'No Update'
> > > END
> > > END
> > > ELSE
> > > BEGIN
> > > -- Error Handling
> > > print 'No free row'
> > > END

> > > Query 2: ( started a couple of seconds after Query 1 - only
difference is
> > Tran2 vs Tran1 )

> > > declare @location int
> > > set @location = NULL
> > > select top 1 @location = Location
> > > from Locations with (READPAST)
> > > where unused = 0
> > > order by Location

> > > IF @location is not NULL
> > > BEGIN
> > > update Locations with (holdlock) set unused = 1 where location =
> > @location
> > > IF @@Rowcount = 1
> > > BEGIN

> > > -- Do other stuff
> > > waitfor delay '000:00:20'
> > > update Locations set SomeOtherCol = SomeOtherCol + 'Tran2' where
> > location = @location

> > > END
> > > ELSE
> > > BEGIN
> > > -- Error handling
> > > print 'No Update'
> > > END
> > > END
> > > ELSE
> > > BEGIN
> > > -- Error Handling
> > > print 'No free row'
> > > END

> > > Result:

> > > select * from Locations

> > > Location unused SomeOtherCol
> > > ----------- ----------- ------------------------------
> > > 1 1 FirstTran1
> > > 2 1 SecondTran2
> > > 3 0 Third
> > > 4 0 Fourth
> > > 5 0 Fifth
> > > 6 0 Sixth
> > > 7 0 Seventh
> > > 8 0 Eighth
> > > 9 0 Nineth
> > > 10 0 Tenth

> > > (10 row(s) affected)

> > > Regards
> > > AJ

> > > "Tim B" <t...@innov8uk.com> wrote in message
> > news:uFXk2ALYCHA.464@tkmsftngp12...
> > > > Hi Andrew,
> > > > if i put a WAITFOR DELAY '000:00:10' in the stored
> > > > procedure between the select and the update, and then using two
query
> > panes
> > > > run the store procedure, they both get the same result. I have
tried
> > > > setting the default to serializable but the second query gets chosen
as
> > a
> > > > dead lock victim same occurs with repeatable read.

> > > > Any ideas?

> > > > Tim B

> > > > "Andrew John" <a...@NOSPAMmistrose.com> wrote in message
> > > > news:uDDRxZKYCHA.1792@tkmsftngp10...
> > > > > Tim,

> > > > > This may work:

> > > > > set @location = NULL
> > > > > select @location = (select top 1 Location from Locations with
> > (READPAST)
> > > > where unused = 0 order by Location)

> > > > > IF @location is not NULL
> > > > > BEGIN
> > > > > update Locations with (holdlock) set used = 1 where location =
> > > > @location
> > > > > IF @@Rowcount = 1

> > > > > -- Do other stuff

> > > > > ELSE
> > > > > BEGIN
> > > > > -- Error handling
> > > > > END
> > > > > ELSE
> > > > > BEGIN
> > > > > -- Error Handling
> > > > > END

> > > > > Which is I think equivalent to the SERIALIZABLE isolation level.

> > > > > Hope this helps

> > > > > AJ

> > > > > "Tim B" <t...@innov8uk.com> wrote in message
> > > > news:#v$6ulJYCHA.1732@tkmsftngp10...
> > > > > > Hi,
> > > > > > trying to workout what transaction isolation level to use
for
> > the
> > > > > > following extract of code from a Stored procedure

> > > > > > select @location = (select Location from Locations where unused
= 0)
> > > > > > -- do other stuff
> > > > > > update Locations set used = 1 where location = location

> > > > > > If i use repeatable read then other users will not be able to
update
> > the
> > > > > > same record if i have not yet updated but they may still get the
> > same
> > > > > > location(from the select) as i have not set the flag yet. I can
> > replace
> > > > the
> > > > > > code with the following however i was wondering if changing the
> > default
> > > > > > transaction isolation level could resolve this issue

> > > > > > backin:
> > > > > > select @location = (select Location from Locations where unused
= 0)
> > > > > > -- do other stuff
> > > > > > update Locations set used = 1 where location = location and
used =
> > 0
> > > > > > if @@rowcou nt = 0 then goto backin

> > > > > > Thanks in advance

> > > > > > Tim

by **Leonid Bensm** » Thu, 26 Sep 2002 01:17:17

"Ulrich" <crowh...@gmx.net> wrote in message <news:evJRVP0YCHA.1152@tkmsftngp10>...
> see sp_getapplock in BOL

This can be the solution:

select @location = location from Locations with (readpast) where used
= 0

sp_getapplock '--on that record--'

-- check if we indeed are the ones that will be processing a record
(both threads concurently may have done a select, but only one at a
time will go through getapplock (i.e. critical section)

select @used = used from Locations where location = @location

if @used = 0
begin
-- do processing
end
else
begin
-- no, someone else got the record, redo search...
end

BUT!!!: How do you find out the resource name of a particular record
(not table or even page, _record_!!) to pass to sp_getapplock??????

I've tried to solve a locking problem in sql server once before, but
had trouble understanding which resource locks belonged to... how do
you convert logical resource to resource code and vice verdsa????

I have a similar problem of implementing a queue: for reference, my
post on very similar issue is:
<news:b91023a9.0209231503.7edad95c@posting.google.com>
Subject: Problem implementing a concurent queue in SQL Server.
Posted on: 2002-09-23

> "Andrew John" <a...@NOSPAMmistrose.com> wrote in message
> news:#idT66oYCHA.2264@tkmsftngp09...
> > Tim,

> > Yes, in hindsite I should have kept quiet and seen if anyone else was
> going to respond, as I am aware of this
> > limitation - It takes 2 updates, one to grab the row, then the 2nd after
> all the processing results are ready.
> > What you are really after is something like a SELECT statement, using a
> combination of READPAST and HOLDLOCK,
> > but these do not work together in a select. - I have tried extensively,
> and have both today, and
> > previously searched in vain for working code posted in this newsgroup, or
> the internet at large.

> > This is the best I can come up with:

> > declare @location int
> > set @location = NULL

> > update Locations with (HOLDLOCK)
> > set @Location = Location,
> > unused = 1
> > where Location =
> > (
> > select top 1 Location
> > from Locations with ( READPAST )
> > where unused = 0
> > order by Location
> > )

> > IF @location is not NULL
> > BEGIN

> > -- Do other stuff
> > waitfor delay '000:00:10'
> > update Locations
> > set SomeOtherCol = SomeOtherCol + 'Tran1'
> > where location = @location

> > if @@rowcount <> 1
> > BEGIN
> > -- Error handling
> > print 'No Update'
> > END
> > END
> > ELSE
> > BEGIN
> > -- Error Handling
> > print 'No free row'
> > END

> > The holdlock is probably not required in this example.
> > Puting it all in a transaction so you can cancel the 1st update if the
> processing or 2nd fails is a reasonable
> > extension. If this approach is not suitable, then I suggest you repost
> with something like
> > "How do I select with HOLDLOCK and READPAST, in order to implement a work
> queue"
> > and I'll keep my mouth shut this time.

> > Regards
> > AJ

> > "Tim B" <t...@innov8uk.com> wrote in message
> news:#1I3UoLYCHA.2032@tkmsftngp11...
> > > Hi Andrew,
> > > many thanks for the quick reply. Please try the same
> test as
> > > you used below however with the waitfor "BETWEEN the SELECT and the
> UPDATE".
> > > Your sample has probably executed the first update before the second
> > > transaction starts running, This update is then locking the record,
> which
> > > then causes the second transaction to readpast the locked record and
> suggest
> > > the next available record. However as the work is being done between
> the
> > > select and the update, if the select doesnt lock the record from being
> seen
> > > from a different transaction both transactions could have the same
> Location
> > > before they perform the update.

> > > Thanks greatly in advance
> > > Tim

> > > "Andrew John" <a...@NOSPAMmistrose.com> wrote in message
> > > news:#zVO9MLYCHA.2292@tkmsftngp12...
> > > > Tim,

> > > > Works fine for me. Here is the test code I used
> > > > ( normal newsgroup practise is for YOU to supply this, in order to
> provide
> > > > us with less typing ! )

> > > > Data:

> > > > create table Locations
> > > > (
> > > > Location int identity ( 1, 1 ),
> > > > unused int,
> > > > SomeOtherCol varchar ( 30)
> > > > )
> > > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'First' )
> > > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Second' )
> > > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Third' )
> > > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Fourth' )
> > > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Fifth' )
> > > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Sixth' )
> > > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Seventh' )
> > > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Eighth' )
> > > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Nineth' )
> > > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Tenth' )

> > > > Query 1:

> > > > declare @location int
> > > > set @location = NULL
> > > > select top 1 @location = Location
> > > > from Locations with (READPAST)
> > > > where unused = 0
> > > > order by Location

> > > > IF @location is not NULL
> > > > BEGIN
> > > > update Locations with (holdlock) set unused = 1 where location =
> @location
> > > > IF @@Rowcount = 1
> > > > BEGIN

> > > > -- Do other stuff
> > > > waitfor delay '000:00:20'
> > > > update Locations set SomeOtherCol = SomeOtherCol + 'Tran1' where
> location = @location

> > > > END
> > > > ELSE
> > > > BEGIN
> > > > -- Error handling
> > > > print 'No Update'
> > > > END
> > > > END
> > > > ELSE
> > > > BEGIN
> > > > -- Error Handling
> > > > print 'No free row'
> > > > END

> > > > Query 2: ( started a couple of seconds after Query 1 - only
> difference is
> Tran2 vs Tran1 )

> > > > declare @location int
> > > > set @location = NULL
> > > > select top 1 @location = Location
> > > > from Locations with (READPAST)
> > > > where unused = 0
> > > > order by Location

> > > > IF @location is not NULL
> > > > BEGIN
> > > > update Locations with (holdlock) set unused = 1 where location =
> @location
> > > > IF @@Rowcount = 1
> > > > BEGIN

> > > > -- Do other stuff
> > > > waitfor delay '000:00:20'
> > > > update Locations set SomeOtherCol = SomeOtherCol + 'Tran2' where
> location = @location

> > > > END
> > > > ELSE
> > > > BEGIN
> > > > -- Error handling
> > > > print 'No Update'
> > > > END
> > > > END
> > > > ELSE
> > > > BEGIN
> > > > -- Error Handling
> > > > print 'No free row'
> > > > END

> > > > Result:

> > > > select * from Locations

> > > > Location unused SomeOtherCol
> > > > ----------- ----------- ------------------------------
> > > > 1 1 FirstTran1
> > > > 2 1 SecondTran2
> > > > 3 0 Third
> > > > 4 0 Fourth
> > > > 5 0 Fifth
> > > > 6 0 Sixth
> > > > 7 0 Seventh
> > > > 8 0 Eighth
> > > > 9 0 Nineth
> > > > 10 0 Tenth

> > > > (10 row(s) affected)

> > > > Regards
> > > > AJ

> > > > "Tim B" <t...@innov8uk.com> wrote in message
> news:uFXk2ALYCHA.464@tkmsftngp12...
> > > > > Hi Andrew,
> > > > > if i put a WAITFOR DELAY '000:00:10' in the stored
> > > > > procedure between the select and the update, and then using two
> query
> panes
> > > > > run the store procedure, they both get the same result. I have
> tried
> > > > > setting the default to serializable but the second query gets chosen
> as
> a
> > > > > dead lock victim same occurs with repeatable read.

> > > > > Any ideas?

> > > > > Tim B

> > > > > "Andrew John" <a...@NOSPAMmistrose.com> wrote in message
> > > > > news:uDDRxZKYCHA.1792@tkmsftngp10...
> > > > > > Tim,

> > > > > > This may work:

> > > > > > set @location = NULL
> > > > > > select @location = (select top 1 Location from Locations with
> (READPAST)
> where unused = 0 order by Location)

> > > > > > IF @location is not NULL
> > > > > > BEGIN
> > > > > > update Locations with (holdlock) set used = 1 where location =
> @location
> > > > > > IF @@Rowcount = 1

> > > > > > -- Do other stuff

> > > > > > ELSE
> > > > > > BEGIN
> > > > > > -- Error handling
> > > > > > END
> > > > > > ELSE
> > > > > > BEGIN
> > > > > > -- Error Handling
> > > > > > END

> > > > > > Which is I think equivalent to the SERIALIZABLE isolation level.

> > > > > > Hope this helps

> > > > > > AJ

> > > > > > "Tim B" <t...@innov8uk.com> wrote in message
> news:#v$6ulJYCHA.1732@tkmsftngp10...
> > > > > > > Hi,
> > > > > > > trying to workout what transaction isolation level to use
> for
> the
> > > > > > > following extract of code from a Stored procedure

> > > > > > > select @location = (select Location from Locations where unused
> = 0)
> > > > > > > -- do other stuff
> > > > > > > update Locations set used = 1 where location = location

> > > > > > > If i use repeatable read then other users will not be able to
> update
> the
> > > > > > > same record if i have not yet updated but they may still get the
> same
> > > > > > > location(from the select) as i have not set the flag yet. I can
> replace
> the

...

read more »

by **Leonid Bensm** » Thu, 26 Sep 2002 01:29:19

I see a possible concurency issue with this one as well. Consider the
following scenario:

Two threads (i.e. two separate connections): #A and #B

It is theoretically possible that #A & #B will simultaneously execute
the sub-select of the first update (select top 1 Location....) -
READPAST hint (if I understand correctly) wouldn't cause to skip the
read, as at this point there is no lock on the record (only shared
lock to prevent dirty reads - this lock is ok with READPAST). So,
both #A and #B can select the same location index, call it Index1.

Now, let's say #A received the lock to do the update - that means that
#B will simply block until lock is released and the resource becomes
available. Once #A releases the record, #B will enter update stage,
and place used=1 no matter if it was 1 or not. #B will continue exact
same execution path as #A, thus causing the concurency problem.

Yes, chances are small, but not nil.

Lenny

"Andrew John" <a...@NOSPAMmistrose.com> wrote in message <news:#idT66oYCHA.2264@tkmsftngp09>...
> Tim,

> Yes, in hindsite I should have kept quiet and seen if anyone else was going to respond, as I am aware of this
> limitation - It takes 2 updates, one to grab the row, then the 2nd after all the processing results are ready.
> What you are really after is something like a SELECT statement, using a combination of READPAST and HOLDLOCK,
> but these do not work together in a select. - I have tried extensively, and have both today, and
> previously searched in vain for working code posted in this newsgroup, or the internet at large.

> This is the best I can come up with:

> declare @location int
> set @location = NULL

> update Locations with (HOLDLOCK)
> set @Location = Location,
> unused = 1
> where Location =
> (
> select top 1 Location
> from Locations with ( READPAST )
> where unused = 0
> order by Location
> )

> IF @location is not NULL
> BEGIN

> -- Do other stuff
> waitfor delay '000:00:10'
> update Locations
> set SomeOtherCol = SomeOtherCol + 'Tran1'
> where location = @location

> if @@rowcount <> 1
> BEGIN
> -- Error handling
> print 'No Update'
> END
> END
> ELSE
> BEGIN
> -- Error Handling
> print 'No free row'
> END

> The holdlock is probably not required in this example.
> Puting it all in a transaction so you can cancel the 1st update if the processing or 2nd fails is a reasonable
> extension. If this approach is not suitable, then I suggest you repost with something like
> "How do I select with HOLDLOCK and READPAST, in order to implement a work queue"
> and I'll keep my mouth shut this time.

> Regards
> AJ

> "Tim B" <t...@innov8uk.com> wrote in message news:#1I3UoLYCHA.2032@tkmsftngp11...
> > Hi Andrew,
> > many thanks for the quick reply. Please try the same test as
> > you used below however with the waitfor "BETWEEN the SELECT and the UPDATE".
> > Your sample has probably executed the first update before the second
> > transaction starts running, This update is then locking the record, which
> > then causes the second transaction to readpast the locked record and suggest
> > the next available record. However as the work is being done between the
> > select and the update, if the select doesnt lock the record from being seen
> > from a different transaction both transactions could have the same Location
> > before they perform the update.

> > Thanks greatly in advance
> > Tim

> > "Andrew John" <a...@NOSPAMmistrose.com> wrote in message
> > news:#zVO9MLYCHA.2292@tkmsftngp12...
> > > Tim,

> > > Works fine for me. Here is the test code I used
> > > ( normal newsgroup practise is for YOU to supply this, in order to provide
> > > us with less typing ! )

> > > Data:

> > > create table Locations
> > > (
> > > Location int identity ( 1, 1 ),
> > > unused int,
> > > SomeOtherCol varchar ( 30)
> > > )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'First' )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Second' )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Third' )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Fourth' )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Fifth' )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Sixth' )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Seventh' )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Eighth' )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Nineth' )
> > > insert into Locations ( unused, SomeOtherCol ) values ( 0, 'Tenth' )

> > > Query 1:

> > > declare @location int
> > > set @location = NULL
> > > select top 1 @location = Location
> > > from Locations with (READPAST)
> > > where unused = 0
> > > order by Location

> > > IF @location is not NULL
> > > BEGIN
> > > update Locations with (holdlock) set unused = 1 where location =
> @location
> > > IF @@Rowcount = 1
> > > BEGIN

> > > -- Do other stuff
> > > waitfor delay '000:00:20'
> > > update Locations set SomeOtherCol = SomeOtherCol + 'Tran1' where
> location = @location

> > > END
> > > ELSE
> > > BEGIN
> > > -- Error handling
> > > print 'No Update'
> > > END
> > > END
> > > ELSE
> > > BEGIN
> > > -- Error Handling
> > > print 'No free row'
> > > END

> > > Query 2: ( started a couple of seconds after Query 1 - only difference is
> Tran2 vs Tran1 )

> > > declare @location int
> > > set @location = NULL
> > > select top 1 @location = Location
> > > from Locations with (READPAST)
> > > where unused = 0
> > > order by Location

> > > IF @location is not NULL
> > > BEGIN
> > > update Locations with (holdlock) set unused = 1 where location =
> @location
> > > IF @@Rowcount = 1
> > > BEGIN

> > > -- Do other stuff
> > > waitfor delay '000:00:20'
> > > update Locations set SomeOtherCol = SomeOtherCol + 'Tran2' where
> location = @location

> > > END
> > > ELSE
> > > BEGIN
> > > -- Error handling
> > > print 'No Update'
> > > END
> > > END
> > > ELSE
> > > BEGIN
> > > -- Error Handling
> > > print 'No free row'
> > > END

> > > Result:

> > > select * from Locations

> > > Location unused SomeOtherCol
> > > ----------- ----------- ------------------------------
> > > 1 1 FirstTran1
> > > 2 1 SecondTran2
> > > 3 0 Third
> > > 4 0 Fourth
> > > 5 0 Fifth
> > > 6 0 Sixth
> > > 7 0 Seventh
> > > 8 0 Eighth
> > > 9 0 Nineth
> > > 10 0 Tenth

> > > (10 row(s) affected)

> > > Regards
> > > AJ

> > > "Tim B" <t...@innov8uk.com> wrote in message
> news:uFXk2ALYCHA.464@tkmsftngp12...
> > > > Hi Andrew,
> > > > if i put a WAITFOR DELAY '000:00:10' in the stored
> > > > procedure between the select and the update, and then using two query
> panes
> > > > run the store procedure, they both get the same result. I have tried
> > > > setting the default to serializable but the second query gets chosen as
> a
> > > > dead lock victim same occurs with repeatable read.

> > > > Any ideas?

> > > > Tim B

> > > > "Andrew John" <a...@NOSPAMmistrose.com> wrote in message
> > > > news:uDDRxZKYCHA.1792@tkmsftngp10...
> > > > > Tim,

> > > > > This may work:

> > > > > set @location = NULL
> > > > > select @location = (select top 1 Location from Locations with
> (READPAST)
> where unused = 0 order by Location)

> > > > > IF @location is not NULL
> > > > > BEGIN
> > > > > update Locations with (holdlock) set used = 1 where location =
> @location
> > > > > IF @@Rowcount = 1

> > > > > -- Do other stuff

> > > > > ELSE
> > > > > BEGIN
> > > > > -- Error handling
> > > > > END
> > > > > ELSE
> > > > > BEGIN
> > > > > -- Error Handling
> > > > > END

> > > > > Which is I think equivalent to the SERIALIZABLE isolation level.

> > > > > Hope this helps

> > > > > AJ

> > > > > "Tim B" <t...@innov8uk.com> wrote in message
> news:#v$6ulJYCHA.1732@tkmsftngp10...
> > > > > > Hi,
> > > > > > trying to workout what transaction isolation level to use for
> the
> > > > > > following extract of code from a Stored procedure

> > > > > > select @location = (select Location from Locations where unused = 0)
> > > > > > -- do other stuff
> > > > > > update Locations set used = 1 where location = location

> > > > > > If i use repeatable read then other users will not be able to update
> the
> > > > > > same record if i have not yet updated but they may still get the
> same
> > > > > > location(from the select) as i have not set the flag yet. I can
> replace
> the
> > > > > > code with the following however i was wondering if changing the
> default
> > > > > > transaction isolation level could resolve this issue

> > > > > > backin:
> > > > > > select @location = (select Location from Locations where unused = 0)
> > > > > > -- do other stuff
> > > > > > update Locations set used = 1 where location = location and used =
> 0
> > > > > > if @@rowcou nt = 0 then goto backin

> > > > > > Thanks in advance

> > > > > > Tim

Very Computer

Transaction isolation level

Transaction isolation level

Transaction isolation level

Transaction isolation level

Transaction isolation level

Transaction isolation level

Transaction isolation level

Transaction isolation level

Transaction isolation level

Transaction isolation level

Transaction isolation level