在嵌套集中事务安全地插入节点？

发布于 2024-09-07 05:02:00 字数 535 浏览 2 评论 0原文

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

myTable
  id,
  title,
  lft,
  rgt

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

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);

这可行，但如果同时（接近）添加大量节点，则可能会出现问题。

我想知道，确保不损坏数据的最佳方法是什么（不能选择存储过程）。简单地将这个 sql 包含在事务中是否足够？我应该使用事务和表锁定吗？

谢谢

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

少钕鈤記 2024-09-14 05:02:01

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

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

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

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

回复收藏 0 原文

~没有更多了~