should i use table lock?
Posted: Tue Nov 08, 2005 4:44 pm
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?
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?