Hi
i'm using mysql 4.1 + transactions.
i have queries where i pull some fields values from table, then there is a little php calculation with these values and in the end i update the table based on the calculated result...
i'm thinking about what would happen if suppose there are two scripts trying to update the table...
so it can happen that first script got the values and doing calculation while second script updates the fields to another values.... so when first script updates the fields it actually messes the structure/data...
(to be more specific,
i wrote a simple script that maintains hierarchy and it is based on "(left,right)" approach.
link
so for example when i insert new node:
first i get a 'right' and 'left' values of parent record.
then i do some calculation with this their values and update accordingly other records to maintain proper child/parent relations.
so while first script does some calculation with left and right, another script could already modify the original values and as a result of this the first script updates the records to wrong values.
)
i'm guessing transactions won't help me with this issue so what how do i deal with such a situation?
table locks?
should i use table lock?
Moderator: General Moderators
PHP/MySQL is VERY fast, so I doubt such a situation should occur. The updating would take place in microseconds.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
It's very unlikely .. but like AGISB says .. thats not the same as impossible. If a clash is going to mess things up then I would use a lock.
Better yet though, couldn't you do the entire calculation in SQL? Eg:
Obviously that depends on the complexity of the calculation you're doing. Most things are possible in pure SQL though. And it's always faster to do things that way.
Better yet though, couldn't you do the entire calculation in SQL? Eg:
Code: Select all
update table set field = (field*2) where id = 1;