我有一张桌子叫tree_nodes
,在那里我存储了节点的...树表示。我的主题树是在持有者的上下文中创建的。所以,有一个专栏叫做holder_id
.
我有一个事务方法,它会从此表和其他表中进行多次读取,以确定应从树中添加/删除哪些节点。
我的应用程序是集群式的并且是高度并发的,因此应用程序内的代码互斥体不起作用。
我的关系数据库是MySQL。
这就是我想要做的,我想放置一个间隙锁定查询以防止对该表进行并发修改。
据我所知,大多数查询看起来都像范围查询或精确匹配查询。例如:
SELECT * FROM tree_nodes where tree_node_id > some_value FOR UPDATE
问题是,就我而言,查询将如下所示:
SELECT * FROM tree_nodes where holder_id=? FOR UPDATE
但这可能会导致大型数据集无缘无故地从数据库传输到我的应用程序中,因为我不打算使用所有这些节点。
我可以改为放置以下查询并期望锁定按预期工作吗?
SELECT count(*) FROM tree_nodes WHERE holder_id=? FOR UPDATE
=======更新=======
这是我经历的行为,我看到所有线程都在其中一个线程获取锁时停止,然后在它完成后,似乎闩锁被删除,其余线程开始命中而不获取锁,我已附加下面是有关第一个线程持有锁时的事务的信息。
---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
欢迎任何帮助。