I work on a database that is frequently changed by external processes. I cannot control when or how those other processes work & they often temporarily delete rows or update values to zero and rewrite them. As a result my queries are sometimes wrong as a result of querying at the wrong time.
My workaround is to query criteria about the table in a loop & if all of the key criteria are consistent for one minute, then the data is safe to export.
My question is, are there side effects from the performance aspect? I assume that the query is only blocking insofar as it's individual count & as it is waiting to query again, the table is unblocked. Is that so? Would you do this differently?
DECLARE @count INT;
DECLARE @previous_total INT;
SET @count = 1;
SET @previous_total = 0;
WHILE @count<= 6
IF( SELECT SUM( [MyCol] ) FROM [MyDb].[dbo].[MyTable] WHERE 1=1 AND 2=2 AND 3=3 ) = @previous_total
SET @count = 999
SET @previous_total = ( SELECT SUM( [MyCol] ) FROM [MyDb].[dbo].[MyTable] WHERE 1=1 AND 2=2 AND 3=3 )
SET @count = @count + 1;
WAITFOR DELAY '00:00:10'
You're only waiting for 20 secs and you're waiting for 10 secs after you confirm that the values are the same ... couldn't they change in that 10 secs you are waiting??
Waiting for any time period 10 seconds 10 hours does not make the data value correct at the end of the wait period. If at all possible there ahould be some sort of boolean value that is tripped to indicate whether this data row is being modified and is dirty hence don't use until that column indicates all is well and ready.
Sounds like a tough place to work
My select query would run after this code is executed. Though you are right that this logic is off. The moment the old values are the same as the new (once) the query would stop waiting.
That said, assuming it weren't incorrect, is there a potential issue with this locking data on
[MyDb].[dbo].[MyTable] as a result of the count statements spread out across 1 minute? Or is the table unlocked after each count is returned?
I see your point but am not able to change the table structure. I need to maximize my odds of having a meaningful query within that construct.