should i use table lock?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
newmember
Forum Contributor
Posts: 252
Joined: Fri Apr 02, 2004 12:36 pm

should i use table lock?

Post 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?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

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.
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
User avatar
newmember
Forum Contributor
Posts: 252
Joined: Fri Apr 02, 2004 12:36 pm

Post by newmember »

thanks guys...
Post Reply