事务安全插入嵌套集中的节点? [英] Transaction safe insertion of node in nested set?

问题描述

我正在以嵌套集的形式在mysql中存储分层数据.

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

myTable
  id,
  title,
  lft,
  rgt

我使用以下一系列sql语句插入一个新节点:

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.

我想知道，确保没有数据损坏(存储过程不是一种选择)的最佳方法是什么.只需将此sql包含在事务中就足够了吗?我应该使用事务还是表锁定?

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?

谢谢

推荐答案

如果使用的是MyISAM表，则必须锁定该表，因为MyISAM表不支持事务.

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

对于InnoDB表，您可以在事务中完成全部工作.

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

这篇关于事务安全插入嵌套集中的节点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！