I have a service method (C#) that retrieves data from an Azure SQL Server Database, process it and then performs some inserts. When this method is called from different processes, for a large number of data, I sometimes get duplicate records, same data being inserted.
The code is something like this:
<code>begin transaction
if((select count(*) from TableA with (UPDLOCK, HOLDLOCK) WHERE col1= @col1) > @count)
begin
select 0
return;
end
insert into TableA (col1, col2) values (@col1, @col2)
select 1
commit;
</code>
<code>begin transaction
if((select count(*) from TableA with (UPDLOCK, HOLDLOCK) WHERE col1= @col1) > @count)
begin
select 0
return;
end
insert into TableA (col1, col2) values (@col1, @col2)
select 1
commit;
</code>
begin transaction
if((select count(*) from TableA with (UPDLOCK, HOLDLOCK) WHERE col1= @col1) > @count)
begin
select 0
return;
end
insert into TableA (col1, col2) values (@col1, @col2)
select 1
commit;
I do a count after I read the data and before starting to process it to check if new rows were inserted. I assumed using (UPDLOCK, HOLDLOCK) on the table will stop other transactions from reading the data. When I debug it this is what I see but when running for multiple values for col1 I still get duplicates. How can I prevent other transaction from inserting data?
1