Transaction safe insertion of node in nested set?

I am storing hierarchical data in mysql in the form of a nested set.

myTable
  id,
  title,
  lft,
  rgt

I use the following series of sql statements to insert a new node:

SELECT @myLeft := lft FROM myTable WHERE ID = $id;  
UPDATE myTable SET rgt = rgt + 2 WHERE rgt > @myLeft;       
UPDATE myTable SET lft = lft + 2 WHERE lft > @myLeft;       
INSERT INTO myTable(title, lft, rgt) VALUES($title, @myLeft + 1, @myLeft + 2);

This works, but there are potentially problems if a lot of nodes get added (near) simultaneously.

I am wondering, what is the best way to ensure no data corruption (stored procedures are not an option). Is it ade开发者_如何学JAVAquate simply to enclose this sql in a transaction? Should I use transactions and also table locking?

Thanks

If you're using MyISAM tables, you'll have to lock the table, as MyISAM tables doesn't support transactions.

For InnoDB tables, you can do your entire work in a transaction.

BEGIN; -- or whatever API your framework has for starting a transaction
SELECT @myLeft := lft FROM myTable WHERE ID = $id FOR UPDATE;  
UPDATE myTable SET rgt = rgt + 2 WHERE rgt > @myLeft;       
UPDATE myTable SET lft = lft + 2 WHERE lft > @myLeft;       
INSERT INTO myTable(title, lft, rgt) VALUES($title, @myLeft + 1, @myLeft + 2);
COMMIT; -- or whatever API your framework has for commiting a transaction