Is a table lock required to maintain data integrity in the following situation

In MySQL, I have a tree represented by a nested set. Manipulation of the nested set requires modifying multiple rows in the table. For example, adding a node to the tree will require reordering the right and left values...

SELECT @myLeft := lft FROM folders WHERE ID = ?;
UPDATE folders SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE folders SET lft = lft + 2 WHERE lft > @myLeft;

Currently I am wrapping this in a transaction. However, I am wonderi开发者_如何学Cng... Is a transaction enough to ensure data integrity?

I am worried that because there are two separate UPDATE statements, in a high volume environment this might cause problems... Should I also be locking the table, to ensure that nothing changes between those two UPDATE statements?

Thanks in advance for your help

---

You can add FOR UPDATE to the select statement to lock all the rows it comes across.

But it's not enough - you basically need to lock the entire table (because you are going to update the entire table), and transactions will not do it for you. innoDB has row level locking, and only the needed rows are locked, which will fail badly here because you are only selecting one row, but updating everything.

SELECTing the entire table FOR UPDATE in order to lock all rows might lock everything, but it better be the very first SELECT you run. Otherwise you read one row, and freeze your view (consistent read), then another transaction does the same. Now both of you have the same view - but that second transaction actually needs to read the data that is just about to change! (The second transaction will not block - you are reading, and locking, different rows. The system does not know you are planning to update the entire table.) So if you lock in this way (FOR UPDATE), that statement must be the one to "freeze" your view of the table.

After some research I decided that the only possible solution is advisory locking.

(The regular LOCK command in MySQL does not work with transactions.)

Use the GET_LOCK function instead - and run it before opening a transaction, not after.

i.e.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT GET_LOCK('folders_nested_set', <timeout>); /* remember to check to make sure the lock worked */
START TRANSACTION WITH CONSISTENT SNAPSHOT;

do work on folders table here

COMMIT;
DO RELEASE_LOCK('folders_nested_set');

Make sure that all functions that work with the nested set are wrapped in transactions. Not for writing, but to ensure consistent reads across the SQL statements.

i.e.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;

do reads here

COMMIT;

Unless you know you are reading all the data you need in just one SQL statement, then you don't need this.

(You can do SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; right after you connect to the db, instead of by every transaction. Make sure to include the SESSION keyword in that case.)

If I am wrong, or missed something here - or even if there is a better way I would greatly appreciate knowing about it because I am dealing with exactly this same situation.