SQLTeam.com | Weblogs | Forums

Few application work on same table and values

tsql
#1

i have SP, which take data from few tables.
my main column are status :
0 -not updated
1- in progress
2- finished
and SourceType (can be 1 to 100
when i put the data i do
select top 500.....
where status=0 and SourceType=@SourceType
which return up to 500 rows.
and update the status to 1
i have applications, which call this SP, how can i prevent applications that
call the same SP at the same time to get the same rows?

#2

Hard to understand what you are saying
Please supply sample data we can use and the results you want to see

Sample like this
Create table #sample(status int, name varchar(50)

Insert into #sample

#3

Use the Update statement with the OUTPUT clause.
This way you setting the status to something other than zero whiles also returning the data you want.
As the status is no longer zero, others will not pick up the returned rows.

Update top (100) TableName
set status = 1
OUTPUT columnsnameshere
where status=0 and SourceType=@SourceType

Example can be found


Perhaps also read up on isolation levels
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017

#4

@Lewie this will insure me that if few app try to call this procedure at the same time,
no one of the, will get the same rows?
because i think i need here something like atomic action that will isloate the
query and update .

@yosiasz
Create table #sample(status int,sourceType int, name varchar(50),data varchar(100))

Insert into #sample
select 0,5,'fff','bla bla'
select 0,5,'fffffff','bfffffla bla'

i want to get all rows that
status=0 and sourceType=5.
the problem is that few application call this at the same time,
which means they can ge the same rows

#5

Just use the highest isolation level and it shouldn’t be a problem

#6

you mean :slight_smile:
set transaction isolation level snapshot ?

#7

Set transaction isolation level SERIALIZABLE

#8

TABLOCK should be enough.

CREATE TABLE #UpdatedIds ( id int NOT NULL );
CREATE TABLE #SourceTypes ( sourcetype varchar(10) NOT NULL );
DECLARE @BatchSize int

SET @BatchSize = 500 /*or whatever*/
INSERT INTO #SourceTypes VALUES('A'), ('C'); /*or whatever*/

UPDATE TOP (@BatchSize) dbo.your_table_name WITH (TABLOCK)
SET status = 1
OUTPUT inserted.id INTO #UpdatedIds ( id ) /*...*/
WHERE status = 0 AND SourceType IN (SELECT sourcetype FROM #SourceTypes);

SELECT * FROM #UpdatedIds
#9

@ScottPletcher the question if using the 100% correct,
or to be on the safe side to use isolation ?

#10

It's safe to prevent others from getting the same row(s) from the table that your are UPDATEing at the time. The TABLOCK will prevent any other rows from that table by being updated by anyone else at the same time.

Serial isolation will work too, but it effects every recoverable resource in the batch. You could end up seeing some deadlocks if you use isolation.