May 2, 2002 at 10:27 am
insert into #TempBatchTable
select Top 1 *
from BatchTable
where inuseflag = 'N'
update BatchTable
set Inuseflag = 'Y'
from #TempBatchTable TBT
where TBT.BatchNo = BatchTable.batchno
select @batchno=batchno
from #TempBatchTable
I have the above statements in a stored procedure. When 2 or more clients are executing the stored procedure at the same time, I end up getting the same batch number. It looks like the insert into the temp table is happenning for the 2nd client before the update can be done for the 1st client. How can I avoid this.
Thanks,
Senthil.
May 2, 2002 at 11:12 am
You need to use a lock hint to prevent access see BOL "Locking Hints". Also # temp tables are only visible to the connection that created it. Thsu connection 1 sees a # temp table and connection 2 sees it's own # temp table but not 1s.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 2, 2002 at 3:24 pm
I change the insert to
insert into #TempBatchTable
select Top 1 *
from BatchTable
with (updlock)
where inuseflag = 'N'
now it seems to have a slight performance hit, but it works as desired. Is this the correct method or can I improve the performance by any other way ?
quote:
You need to use a lock hint to prevent access see BOL "Locking Hints". Also # temp tables are only visible to the connection that created it. Thsu connection 1 sees a # temp table and connection 2 sees it's own # temp table but not 1s."Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 2, 2002 at 5:02 pm
You might love this, but after I tested it works you might want to try this instead.
DECLARE @batchno INT --Or whatever datatype you need
UPDATE
BatchTable
SET
Inuseflag = 'Y',
@batchno = batchno
FROM
(
SELECT TOP 1 batchno
FROM BatchTable
WHERE inuseflag = 'N'
) batOne
WHERE
batOne.batchno = BatchTable.batchno
So in otherwords you update the inuseflag for the top 1 item from the batchtable and set you variable all in one query. Then you can dump using the temp table and pull the data from the batchtable and I see no need got the updlock hint.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 05/02/2002 5:03:20 PM
May 3, 2002 at 8:30 am
Thanks a lot. It works fine.
quote:
You might love this, but after I tested it works you might want to try this instead.DECLARE @batchno INT --Or whatever datatype you need
UPDATE
BatchTable
SET
Inuseflag = 'Y',
@batchno = batchno
FROM
(
SELECT TOP 1 batchno
FROM BatchTable
WHERE inuseflag = 'N'
) batOne
WHERE
batOne.batchno = BatchTable.batchno
So in otherwords you update the inuseflag for the top 1 item from the batchtable and set you variable all in one query. Then you can dump using the temp table and pull the data from the batchtable and I see no need got the updlock hint.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 05/02/2002 5:03:20 PM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply