表锁可以扩展吗？ / 行锁对于嵌套集会更有效吗？
我正在使用 嵌套集 在 MyISAM 中存储分层数据桌子;该表由每个用户的多个层次结构集组成。每个用户将是唯一写入其各自树的人，但其他用户可以从中读取。节点删除/插入要求同一树中的其他行更新其 lft 和 rgt 值，可能有数百行。
为了做到这一点，我需要获得表写锁，更新树中的其他节点，删除/插入行并解锁表。
我想知道的是——表锁是否可以扩展到数百个并发用户？数千？
在这种情况下InnoDB的行锁会更有效吗？ （锁定几百行大部分仅由用户自己使用）
如果我要使用行锁，我是否需要添加显式逻辑来处理死锁错误？
I'm using nested sets to store hierarchical data in a MyISAM table; the table consists of several hierarchical sets for each user. Each user will be the only one writing to his respective trees, but other users may read from them. Node deletion / Insertion requires that other rows in the same tree have their lft and rgt values updated, potentially hundreds of rows.
In order to do this, I need to get a table write lock, update the other nodes in the tree, delete/insert the row and unlock the table.
What I'm wondering is this -- Do table locks scale to hundreds of concurrent users? thousands?
Would InnoDB's row locks be more efficient in this case? (locking a few hundred rows that will mostly be used only by the user himself)
If I were to use row locks, do I need to add explicit logic to deal with deadlock errors?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
嗯，两个引擎的锁定原理是不同的。
对于 MyISAM，全表锁定的原因是写入通常应该很快。写入只需要两个操作（锁定表，然后将行写入磁盘）。因此，MyISAM 性能实际上受到磁盘速度的限制。
对于 InnoDB，情况会变得稍微复杂一些。由于它完全符合 ACID，因此每次写入都需要 4 个步骤（锁定行、写入事务日志、将行写入磁盘、写入事务日志）。请注意，它会写入磁盘 3 次。因此，这意味着（实际上）InnoDB 写入时间将比 MyISAM 写入时间长 3 倍。这是行级锁定的原因之一（事务是另一个原因）。
但这并不那么容易。对于 MyISAM，表锁需要该表的一个信号量。因此，对内存使用和速度的影响充其量是微不足道的。然而，对于 InnoDB，每行需要一个索引和一个信号量。它需要一个索引来加快“检查”速度，以查看该行是否已经有锁。现在，如果您同时更新 1 行或 10 行，则几乎没有什么区别。但是，当您谈论数百万行时，差异可能是不小的（在内存使用和速度方面，因为它需要横向锁定每行的锁“索引”）。
还有一个额外的权衡。由于 InnoDB 符合 ACID，因此如果发生断电（或其他崩溃），您永远不会处于不一致的状态。数据库中没有未提交的事务数据，也没有已提交的事务已损坏（如果检测到需要修复的内容，它将自动运行事务日志）。使用 MyISAM，写入期间断电（或崩溃）可能会使表处于不一致的状态，而您对此无能为力。如果你关心你的数据，InnoDB 会更好。但是，有了良好的二进制日志和备份系统，您应该能够恢复 MyISAM，但这需要一些手动干预......
现在，话虽如此，您的问题是哪个扩展性更好确实很难。首先，您的大部分写入都是处理一行还是两行？如果是这样，InnoDB 和行级锁定将倾向于更好地扩展。如果您执行大量查询同时更新大量行（数万行或更多），您会注意到 MyISAM 往往会具有更好的性能。
至于你的死锁问题，MySQL将为你找到并处理它们（但它不会执行其中一个查询，因此你可能需要一些异常处理代码来重试查询或其他操作）。内部系统将防止僵局......
现在，另一个说明。既然MySQL在数据库中支持多个引擎，为什么不将数据放入InnoDB，然后制作一张MyISAM连接表来处理嵌套的集合数据呢？将育儿信息存储在数据表中（通过
parent_id机制）。这样，您的所有数据都位于符合 ACID 的数据库中，但是您可以通过使用更快的（用于读取和大量写入）MyISAM 进行嵌套集逻辑来提高速度...Well, the philosophy on locking is different between the two engines.
With MyISAM, the reason for full table locking is that writes should normally be fast. There are only two operation needed for the write (Lock table, then write row to disk). MyISAM performance is really bound by disk speed for this reason.
With InnoDB, it gets a little more complicated. Since it's fully ACID compliant, every write takes 4 steps (Lock row, write to transaction log, write row to dis, write to transaction log). Note that it writes to the disk three times. So that means that (in practice) an InnoDB write will take 3 times longer than a MyISAM write. That's one reason for the row level locking (transactions are another).
But it's not that easy. With MyISAM, the table lock requires one semaphore for that table. So the impact on both memory usage and speed are trivial at best. With InnoDB however, it requires an index and one semaphore per row. It needs an index to speed up the "check" to see if there's already a lock for the row. Now, if you're updating one or 10 rows at the same time, there's little difference. But when you're talking millions of rows the difference can be non-trivial (both in memory usage and speed, since it needs to transverse the lock "index" for each row to be locked).
There is also an additional tradeoff. Since InnoDB is ACID compliant, if there's a power loss (or other crash), you're never left in an inconsistent state. There's no uncommitted transaction's data in the db, and there's no committed transaction corrupted (it will automatically run the transaction log if it detects something to fix it). With MyISAM, a power loss (or crash) during a write can leave the table in an inconsistent state and there's nothing you can do about it. If you care about your data, InnoDB would be better. But, with good Binary logs and a backup system, you should be able to recover MyISAM, but it will require some manual intervention...
Now, with that said, your question of which scales better is really hard. First, are most of your writes dealing with one or two rows? If so, InnoDB and Row level locking will tend to scale better. If you do a lot of queries updating a lot of rows at the same time (tens of thousands and up), you'll notice that MyISAM will tend to have better performance.
As for your question of deadlocks, MySQL will locate and handle them for you (but it won't execute one of the queries, so you may want some exception handling code to either retry the query or something else). The internal system will prevent the deadlock...
Now, another note. Since MySQL supports more than one engine in a db, why not put your data into InnoDB, and then make a MyISAM join table to handle the nested set data? Store parenting info in the data table (via a
parent_idmechanism). That way, all your data is in an ACID compliant db, but you can gain the speed increase by using the faster (for reading and large writes) MyISAM for the nested set logic...