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?
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
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
@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
Just use the highest isolation level and it shouldn’t be a problem
you mean 
set transaction isolation level snapshot ?
Set transaction isolation level SERIALIZABLE
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 #UpdatedIdsIt'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.