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.
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
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
Any help is welcomed.
