Locking rows in in innoDB for multiple queries - 【StackMirror】|php|mysql|sql

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.

2012-04-03 20:00
by qitch

You can't lock a row that isn't there. So there is no way you can lock all "three" rows. You can only lock the two rows that you UPDATE - a_horse_with_no_name 2012-04-03 20:03

@ahorsewithnoname Yeah I guess I kind of made it sound like that. I meant locking all the rows where home=$home and then freeing them after the three queries are done. So if there was something like LOCK UNTIL FREE and then after the queries FREE - qitch 2012-04-03 20:07

Rows that you UPDATE will be locked until you commit. So if the UPDATE is the first thing you do, that's all you need - a_horse_with_no_name 2012-04-03 20:15

Use transactions, your changes are not visible to others until you commit them explicitly.

Further informations: msql docs on transactions

2012-04-03 20:16
by Flo Doe