Statement using Apoc Periodic Iterate gets stuck, but works without the iterate

Neo4j Graph Platform Cypher
,
1

I am trying to merge duplicate nodes using this ( A, B ,C values are just for explanation)

CALL apoc.periodic.iterate("match (a1:Person) where a1.registryMouhafaza='A' and a1.registryKadaa='B' and a1.registryTown='C' with a1.duplicateField as dup , collect(a1) as nodes where size(nodes)>1 return nodes","CALL apoc.refactor.mergeNodes(nodes,{ properties:'combine', mergeRels:true}) YIELD node set node.id=randomUUID()",{batchSize:5000})

The data that needs to be worked on is only about 300 nodes. The query gets stuck . In the transactions i see that there 2 are transactions and one is blocking the other

|"CALL apoc.periodic.iterate("match (a1:Person) where a1.registryMouhafaza='A' and a1.registryKadaa='B' and a1.registryTown='C' with a1.duplicateField as dup , collect(a1) as nodes where size(nodes)>1 return nodes","CALL apoc.refactor.mergeNodes(nodes,{ properties:'combine', mergeRels:true}) YIELD node set node.id=randomUUID()",{batchSize:5000})"| "Blocked by: [neo4j-transaction-145]"

where neo4j-transaction-145 is the following
"UNWIND $_batch AS _batch WITH _batch.nodes AS nodes CALL apoc.refactor.mergeNodes(nodes,{ properties:'combine', mergeRels:true}) YIELD node set node.id=randomUUID()"

However if i run it without using iterate it works fine and completes within seconds

match (a1:Person) where a1.registryMouhafaza='A' and a1.registryKadaa='B' and a1.registryTown='C' with a1.duplicateField as dup , collect(a1) as nodes where size(nodes)>1 with nodes CALL apoc.refactor.mergeNodes(nodes,{ properties:'combine', mergeRels:true}) YIELD node set node.id=randomUUID()

I have a range index on registryMouhafaza,registryKadaa,registryTown and on duplicateField

I need to run this query on multiple combinations of registryMouhafaza,registryKadaa,registryTown, where in some combinations, the data would be thousands, so I don't want to have to drop the iterate and risk getting Java Heap issues

2

I think the issue that you're running into might be a race condition, causing a deadlock when trying to modify multiple nodes at once. Could you try to run the following query and see if it helps.

CALL apoc.periodic.iterate(
  "MATCH (a1:Person)
   WHERE a1.registryMouhafaza = 'A' AND a1.registryKadaa = 'B' AND a1.registryTown = 'C'
   WITH a1.duplicateField AS dup, COLLECT(a1) AS nodes
   WHERE SIZE(nodes) > 1
   RETURN nodes",
  "CALL apoc.lock.nodes(nodes)
   CALL apoc.refactor.mergeNodes(nodes,{ properties:'combine', mergeRels:true}) YIELD node
   SET node.id = randomUUID()
   WITH node LIMIT $batchSize
   RETURN COUNT(*)",
  {batchSize: 5000, parallel: false}
);
3

Hey thank you for the reply.
I tried it, I had to replace $batchSize with 5000 in the query because I got an error regarding it.
The same issue is still happening, the transaction with the complete query is being blocked by the this
"UNWIND $_batch AS _batch WITH _batch.nodes AS nodes CALL apoc.lock.nodes(nodes)
CALL apoc.refactor.mergeNodes(nodes,{ properties:'combine', mergeRels:true}) YIELD node
SET node.id = randomUUID()
WITH node LIMIT 5000
RETURN COUNT(*)"

4

I think that is normal behavior. Looking at the source code, the first query is run in a transaction. The results are batched, and each batch is run in a separate transaction. the batch processing is occurring while the first transaction is still open, as the batching is consuming the results.

The code you show for the blocking transaction is from the second query you pass. The first line with the 'unwind' is prepended by the apoc procedure.

It is running the batch query sequentially, since you did not specify parallel as true.

Have you tried 'call in transactions' instead?