MySQL间隙锁定 [英] MySQL gap locking
问题描述
我有一个名为 tree_nodes 的表，在其中存储了节点的...树表示.我的主题树是在持有者的上下文中创建的.因此，有一列称为 holder_id .
I have a table called tree_nodes, in there I store a ... tree representation of my nodes. My topic tree is created in the context of a holder. So, there is a column called holder_id.
我有一个事务处理方法，该方法正在对此表和其他表进行多次读取，以确定应该从树中添加/删除哪些节点.
I have a transactional method that is doing several reads from this and other tables in order to determine what nodes should be added/removed from the tree.
我的应用程序是集群的并且高度并发，因此，应用程序内代码互斥锁将无法工作.
My application is clustered and is highly concurrent, so, the in-app code mutexes not gonna work.
我的关系数据库是MySQL.
My relational DB is MySQL.
这是我想要做的，我想放置一个间隙锁定查询以防止对该表进行并发修改.从我看来，大多数查询看起来都是范围查询或完全匹配查询.例如:
Here is what I wanted to do, I want to place a gap locking query to prevent concurrent modifications into that table. From what I see most of those queries look like range queries or exact match queries. For instance:
SELECT * FROM tree_nodes where tree_node_id > some_value FOR UPDATE
问题是，在我的情况下，查询将如下所示:
The thing is that in my case the query will look like this:
SELECT * FROM tree_nodes where holder_id=? FOR UPDATE
但这可能会导致大量数据集无缘无故地从db传输到我的应用程序中，因为我不打算使用所有这些节点.
But this might result in a large dataset being transferred from db into my app for no reason as I do not plan to work with all of those nodes.
我可以代之以放置下面的查询并期望锁定能够按预期工作吗?
Can I instead place the follwing query and expect locking to work as expected?
SELECT count(*) FROM tree_nodes WHERE holder_id=? FOR UPDATE
=======更新======
=======UPDATE=======
这是我所经历的行为，我看到所有线程都停止了，因为其中一个线程获得了锁，然后看起来像一个闩锁被移除，其余线程却在没有获得锁的情况下开始命中，我在第一个线程上持有锁时，已在下面附加了有关事务的信息.
Here is the behavior that I experience, I see that all threads stop as one of them acquires the lock, and then after it finishes it seems like a latch being removed and the rest of them start hitting without acquiring a lock, I have appended the information below about the transactions when the lock is being held on the first thread.
---TRANSACTION 7492, ACTIVE 261 sec
1 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 10, OS thread handle 123145553260544, query id 1290 localhost 127.0.0.1 imochurad cleaning up
Trx read view will not see trx with id >= 7485, sees < 7485
---TRANSACTION 7491, ACTIVE 261 sec
1 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 11, OS thread handle 123145553575936, query id 1289 localhost 127.0.0.1 imochurad cleaning up
Trx read view will not see trx with id >= 7485, sees < 7485
---TRANSACTION 7490, ACTIVE 261 sec
1 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 17, OS thread handle 123145555468288, query id 1287 localhost 127.0.0.1 imochurad cleaning up
Trx read view will not see trx with id >= 7485, sees < 7485
---TRANSACTION 7489, ACTIVE 261 sec
1 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 15, OS thread handle 123145554837504, query id 1286 localhost 127.0.0.1 imochurad cleaning up
Trx read view will not see trx with id >= 7485, sees < 7485
---TRANSACTION 7488, ACTIVE 261 sec
1 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 18, OS thread handle 123145555783680, query id 1285 localhost 127.0.0.1 imochurad cleaning up
Trx read view will not see trx with id >= 7485, sees < 7485
---TRANSACTION 7487, ACTIVE 261 sec
1 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 12, OS thread handle 123145553891328, query id 1284 localhost 127.0.0.1 imochurad cleaning up
Trx read view will not see trx with id >= 7485, sees < 7485
---TRANSACTION 7486, ACTIVE 261 sec
1 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 16, OS thread handle 123145555152896, query id 1282 localhost 127.0.0.1 imochurad cleaning up
Trx read view will not see trx with id >= 7485, sees < 7485
---TRANSACTION 7485, ACTIVE 261 sec
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 9, OS thread handle 123145552945152, query id 1283 localhost 127.0.0.1 imochurad cleaning up
Trx read view will not see trx with id >= 7485, sees < 7485
欢迎任何帮助.
推荐答案
这是我无法实现我想要的原因的原因: https://bugs.mysql.com/bug.php?id=95230
This the reason I wasn't able to achieve what I wanted: https://bugs.mysql.com/bug.php?id=95230
这篇关于MySQL间隙锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
