Graph Data Modeling

Using CALL IN TRANSACTIONS for batched writes

You have a write that touches far too many rows to fit in one transaction — a bulk MERGE over a few million records, a property backfill across an entire label, a DETACH DELETE of a stale subgraph. Run it as a single statement and Neo4j buffers every change in heap until the final commit, which on a large enough write ends in an OutOfMemoryError and a full rollback with nothing landed. Neo4j 5.x solves this with CALL { … } IN TRANSACTIONS, which runs the inner work in committed sub-transactions of a fixed size so memory is released between batches and progress is durable as it goes. This is the direct replacement for the removed USING PERIODIC COMMIT clause, and it works over any input stream — UNWIND, LOAD CSV, or an APOC yield. This page shows the clause, its error-handling modes, and the one thing people get wrong: how to invoke it from the Python driver. It is part of Data Loading Tools and Bulk Ingestion.

Prerequisites

Why one giant transaction fails

A Neo4j transaction accumulates all its uncommitted changes — created nodes, set properties, relationship records — in memory until it commits. A write over ten million rows therefore holds ten million rows’ worth of state at once, and when that exceeds the configured heap the transaction dies with OutOfMemoryError, rolling back everything. Even short of OOM, one enormous transaction holds locks for its entire duration and bloats the transaction log. CALL { … } IN TRANSACTIONS OF n ROWS breaks the work into independent sub-transactions of n input rows each: batch one commits and frees its memory before batch two begins, locks are released per batch, and a failure loses only the current batch instead of the whole load.

One giant transaction versus batched sub-transactions Top row: a single wide transaction box spanning all ten million rows with one commit at the far right and a note that any failure rolls back everything, risking out-of-memory. Bottom row: the same stream divided into four small sub-transaction boxes, each with its own commit tick, so each batch is durable independently and memory is freed between batches. One transaction MERGE over all 10,000,000 rows every change buffered in heap until the end 1 commit any failure rolls back the whole load buffered in heap · OutOfMemory risk CALL {} IN TRANSACTIONS OF 10000 ROWS 10k rows commit ✓ 10k rows commit ✓ 10k rows commit ✓ 10k rows commit ✓ each batch durable on its own · heap freed between batches
Splitting the write into committed sub-transactions keeps memory bounded and every landed batch durable.

Core implementation

The clause wraps the per-row work in a subquery and appends IN TRANSACTIONS OF n ROWS. The variable produced by the driving read (UNWIND, LOAD CSV, or a procedure) is passed into the subquery with WITH. Everything inside the braces runs and commits in batches of n input rows.

cypher
// The 5.x pattern. $rows is a list parameter; the driving UNWIND streams it,
// and the inner subquery commits every 10,000 rows. On a huge $rows this keeps
// heap bounded and makes every committed batch durable as it lands.
UNWIND $rows AS row
CALL {
  WITH row                                     // pipe the row into the batch scope
  MERGE (c:Customer {customer_id: row.customer_id})
  MERGE (t:Transaction {tx_id: row.tx_id})
    SET t.amount = row.amount,
        t.processed_at = datetime(row.processed_at)
  MERGE (c)-[:INITIATED]->(t)
} IN TRANSACTIONS OF 10000 ROWS;

The number after OF is input rows per sub-transaction, not nodes written — one input row may create several nodes and edges. Size it against heap the way you would any batch-processing chunk: 5,000–10,000 rows balances commit overhead against per-batch memory.

Error-handling modes

By default, a failure in any batch aborts the whole statement and the batches already committed stay committed — the write is not atomic across batches. Three ON ERROR modes let you choose how a failing batch is handled, and REPORT STATUS AS surfaces per-batch outcomes:

cypher
// ON ERROR CONTINUE: skip a failing batch and keep going; collect the errors.
UNWIND $rows AS row
CALL {
  WITH row
  MERGE (t:Transaction {tx_id: row.tx_id}) SET t.amount = row.amount
} IN TRANSACTIONS OF 10000 ROWS
  ON ERROR CONTINUE
  REPORT STATUS AS status
RETURN status.committed AS committed, status.errorMessage AS error;
  • ON ERROR CONTINUE — a failing batch is rolled back and skipped; remaining batches still run. Pair with REPORT STATUS to capture which batches failed and dead-letter them.
  • ON ERROR BREAK — stop at the first failing batch; batches already committed remain. This is the default behaviour made explicit.
  • ON ERROR RETRY — re-attempt a failed batch (optionally FOR <duration>) before falling through to BREAK or CONTINUE; useful for transient lock contention.

Use ON ERROR CONTINUE with REPORT STATUS when you want a best-effort load that quarantines bad batches; use ON ERROR RETRY … THEN CONTINUE when failures are mostly transient. Deterministic failures such as constraint violations should still be routed to quarantine per Error Handling & Rollback Mechanisms, not retried blindly.

Invoking it from the Python driver

This is the step that trips people up. CALL {} IN TRANSACTIONS is an auto-commit clause — it manages its own sub-transaction boundaries — so it cannot run inside an explicit or a managed transaction. That rules out session.execute_write, whose whole job is to open one transaction around your function. It must be issued through session.run, which sends an auto-commit query.

python
from neo4j import GraphDatabase

BATCHED = """
UNWIND $rows AS row
CALL {
  WITH row
  MERGE (c:Customer {customer_id: row.customer_id})
  MERGE (t:Transaction {tx_id: row.tx_id}) SET t.amount = row.amount
  MERGE (c)-[:INITIATED]->(t)
} IN TRANSACTIONS OF 10000 ROWS
"""

def load(uri, auth, rows: list[dict]) -> None:
    with GraphDatabase.driver(uri, auth=auth) as driver:
        with driver.session(database="neo4j") as session:
            # session.run is auto-commit — REQUIRED here. Wrapping this in
            # session.execute_write raises: the clause cannot nest inside a
            # managed transaction, because it commits its own batches.
            result = session.run(BATCHED, rows=rows)
            result.consume()   # drive the query to completion

Because the driver is not managing retries for this call, transient-error recovery moves into the Cypher itself via ON ERROR RETRY, or into an outer application loop that re-runs the whole auto-commit statement — which is safe precisely because the inner MERGE is idempotent.

Validation & verification

Confirm the clause actually batched rather than falling back to one transaction, and that every input row landed. First, use EXPLAIN and check the plan contains a TransactionForeach operator — its presence is the signature of IN TRANSACTIONS:

cypher
EXPLAIN
UNWIND $rows AS row
CALL { WITH row MERGE (t:Transaction {tx_id: row.tx_id}) }
IN TRANSACTIONS OF 10000 ROWS;   // plan must show TransactionForeach

Then reconcile the committed count. With REPORT STATUS, sum the per-batch committed flags; without it, compare the node count to the input size:

cypher
MATCH (t:Transaction)
RETURN count(t) AS loaded,
       count(DISTINCT t.tx_id) AS distinct_ids;   // equal == no duplicates

Remember that a partial failure under the default ON ERROR BREAK leaves earlier batches committed — so a re-run must be idempotent to converge, which is why every write here is a MERGE. Full source-to-graph reconciliation belongs to Data Validation & Integrity Checks.

Edge cases & gotchas

1. Nesting it inside execute_write. The most common error. A managed transaction cannot contain an auto-commit batching clause, and the driver raises rather than silently running unbatched. Always use session.run:

python
# WRONG — raises: CALL {} IN TRANSACTIONS cannot run in a managed transaction
session.execute_write(lambda tx: tx.run(BATCHED, rows=rows))
# RIGHT — auto-commit
session.run(BATCHED, rows=rows).consume()

2. Referencing an outer variable without WITH. The subquery has its own scope; a variable from the driving UNWIND or LOAD CSV must be imported with WITH as the first line inside the braces, or Cypher reports an unknown variable.

cypher
// WRONG — 'row' is not in scope inside the subquery
UNWIND $rows AS row CALL { MERGE (t:Transaction {tx_id: row.tx_id}) } IN TRANSACTIONS;
// RIGHT — pipe it in
UNWIND $rows AS row CALL { WITH row MERGE (t:Transaction {tx_id: row.tx_id}) } IN TRANSACTIONS OF 10000 ROWS;

3. Expecting all-or-nothing atomicity. Under the default mode, a mid-load failure leaves committed batches in place — the operation is not atomic across batches. If you need the whole load to appear atomically, that is a checkpoint-and-resume concern, handled by the ledger pattern in Error Handling & Rollback Mechanisms, not by this clause.

Parent context

Batched sub-transactions are the mechanism that makes the online loaders in Data Loading Tools and Bulk Ingestion safe at scale — both the LOAD CSV path and large driver writes lean on this clause to stay within heap.

Related pages