Page 1 of 1

should i use table lock?

Posted: Tue Nov 08, 2005 4:44 pm
by newmember
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?

Posted: Tue Nov 08, 2005 5:23 pm
by s.dot
PHP/MySQL is VERY fast, so I doubt such a situation should occur. The updating would take place in microseconds.

Posted: Wed Nov 09, 2005 12:15 am
by AGISB
Well the fact that something is fast will only limit the chance of a problem. If you just have enough users and processes the chance will increase. If the calcualtion is more complex the timeframe will increase a lot.

Posted: Wed Nov 09, 2005 5:09 am
by onion2k
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:

Code: Select all

update table set field = (field*2) where id = 1;
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.

Posted: Fri Nov 11, 2005 10:45 am
by newmember
thanks guys...