June 14, 2015 at 12:53 pm
All,
I have the following situation:
One database table involved
Several processes performing the same update statement every x seconds.
The updates must not update the same rows.
Several processes inserting rows. They can insert rows at the same time as the updates. The update processes will find the inserted rows the next time they run.
Ideally I don't want the inserts blocked by the updates
My intended query is:
Begin transaction
Update top (1) table set set instance=[program value] where datediff(m,creationtime, getdate())>2 and uploaded is null
commit transaction
I think will stop the update statements updating the same rows?
Without the transaction several processes could update the rows?
Will it stop inserts? If so is there a safe method to avoid this?
Thanks
June 14, 2015 at 4:35 pm
You don't need an explicit transaction for a single statement.
Using a top 1 in an update makes no sense.
Your WHERE clause is not SARGable. A possible better way would be to use the following:
DECLARE @startdate datetime = DATEADD(m, DATEDIFF(m, 0, GETDATE())-2, 0);
UPDATE table SET
instance=[program value]
WHERE creationtime < @startdate
AND uploaded IS NULL;
You seriously want the blocking to occur if you want some consistency on your data. To reduce the contingency, create the appropriate indexes.
June 14, 2015 at 4:51 pm
Hello,
Thank you for your help.
The reason for the update 1 is because the process is selecting an item to work on and I only want it to take one at once. Is there a better way?
Thanks for the advice on the none SARGABLE query, I had forgotten that.
Thanks
Andrew
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply