SQLTeam.com | Weblogs | Forums

Random update on multiple row


#1

i have table
Item | Process | Number | Saved
A | 1 | 11,20 | Yes
A | 2 | 11,20 | Yes
A | 3 | 11,20 | Yes
A | 1 | 1,2 | No
A | 2 | 1,2 | No
A | 3 | 1,2 | No
B | 1 | 5,7,8 | No
B | 2 | 5,7,8 | No

if i give input item & number, then it will update the number base on the item on all process if saved field is "no".
so if my item input was A & the number is 4 then the result on field number i want is 1,2,4.

my code logic is like this.

number filtering to prevent multiple input on the same number.
return valid number only
select for process table (to get all the process)
looping for each row
update number into on table where item=A, Proccess=, and saved='no'
end loop

the problem when it execute in many device, like
Device 1 send item A & number 4
Device 2 send item A & Number 8

There is a time when it getting mixed up each other end up random update. like
A | 1 | 1,2,4,8 | No
A | 2 | 1,2,8 | No
A | 3 | 1,2,8 | No

my suspicious is on query queue like :
update to 1,2,4 on process 1 (device 1)
update to 1,2,4,8 on process 1 (device 2)
update to 1,2,4 on process 2 (device 1)
update to 1,2,8 on process 2 (device 2) ***

*** upon filtering, the lines havent been updated so it still check for 1,2 & adding 8 as a valid input.
then the update occure from device 1 become 1,2,4
and then it overwrite by update into 1,2,8

hope my explainantion is not confusing.

is there a way to prevent this?
my idea currently is to move filtering on each process select row but my concern is the time it takes to do the same/similar query multiple times.
or can update statement used with select at once like insert select? and does sql process it as 1 queue or each row on select split into different queue.

thanks.


#2

Please post your SQL


#3

My application is running on web browser using php and my SQL code is kinda simple insert, update query executed from my php code.
update

set = where
so if i just put my SQL code here, i dont think it will be any help at all.
But if i were to put all my code here, is it really ok as it might end up like PHP problem not the query itself, while my question is how SQL server handle queue when many device request for SQL query execution & the data that the query use is the same.

#4

What do you mean here? SQL Server does not "queue" requests. If 1000 processes connect to SQL to do updates, it is as if all 1000 run simultaneously. Of course, to preserve ACID, each of those 1000 processes run as if they are the only one running. The final result will depend on the sequence of updates, which you cannot control and which may be different every time.