So I was looking at row locking for innoDB and it seems like
SELECT...FOR UPDATE is the go to query for locking. In my file I have three queries. Two updates and then an insertion. Is there a way to make sure that for each connection the rows are locked and then these three queries can run before someone else can change the rows?
Here are the queries:
"UPDATE table SET r=r+2 WHERE r > '$l' AND home='$home'"; "UPDATE table SET l=l+2 WHERE l > '$l' AND home='$home'"; "INSERT INTO table (value, home, l, r, value2) VALUES ('$value', '$home', '$l'+1, '$l'+2, value2) ";
To clarify: I want to lock all the rows where home=$home and then free them after the three queries. I want to make sure the three queries are executed before any other connections can have write abilities to those rows.
UPDATE- a_horse_with_no_name 2012-04-03 20:03
LOCK UNTIL FREEand then after the queries
FREE- qitch 2012-04-03 20:07
Use transactions, your changes are not visible to others until you commit them explicitly.
Further informations: msql docs on transactions