Transaction safe insertion of node in nested set?

Question

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 adequate simply to enclose this sql in a transaction? Should I use transactions and also table locking?

Thanks

Answer 1

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