Resuming a failed ingestion

An ingestion run can fail partway through due to a deadlock, a lock-acquisition timeout, or a bad row. Restarting from the beginning wastes time and risks re-triggering constraint violations on rows already committed. This page covers how to resume from the point of failure instead, using the status reporting of CALL { …​ } IN TRANSACTIONS to find exactly where to pick back up.

Diagnosing a failed import

people.csv has a missing id value on Carol’s row:

people.csv
id,name,age
1,Alice,30
2,Bob,25
,Carol,35
4,Dave,40

Consider the following example. id is the property MERGE (p:Person {id: line.id}) identifies each node by. MERGE requires every property in its pattern to have a real value, so a blank id causes it to fail outright for that row.

Running a failing import
LOAD CSV WITH HEADERS FROM 'file:///people.csv' AS line
CALL (line)
    { MERGE (p:Person {id: toInteger(line.id)})
      SET p.name = line.name,
          p.age = toInteger(line.age)
} IN TRANSACTIONS OF 2 ROWS

This ingestion fails partway through with the following error message:

Table 1. GQLSTATUS error chain

22N31: data exception - invalid properties in merge pattern. The node property id in '(:Person {id: null})' is invalid. 'MERGE' cannot be used with a graph element property value that is null.

22G03: 22G03: data exception - invalid value type

But this error message does not say which row caused it, or how much of the file was already committed before the failure. Without that information, there is no way to resume safely, restarting from the top risks re-processing rows already written, and restarting from the wrong point risks skipping rows that never actually ran.

Finding out where an import failed

Three things are required in order to discover where an import failed:

  1. Each row needs an identifiable position or identity, so that after a failure, the point at which processing succeeded can be determined precisely.

  2. Batches need to commit independently, rather than the whole import running as one transaction, otherwise there is nothing to resume from.

  3. The failure needs to report which row or batch it stopped at, and why.

In practical terms, this translates to four separate Cypher® building blocks:

Cypher building block What it does

linenumber() function + a $startFromLine cursor

A resume cursor. linenumber() returns the physical line number of the row currently being read from the CSV file. Every row is tagged with its physical line in the file. Rows before the cursor are skipped on a re-run.

CALL (line) { …​ } IN TRANSACTIONS OF $batchSize ROWS

Independent, atomically-committed batches. A committed batch survives a later failure, a failing batch rolls back as a whole.

ON ERROR RETRY …​ THEN BREAK

Auto-recovers from transient errors, then stops cleanly at the first batch that still fails, so committed batches form a gap-free prefix.

REPORT STATUS AS status

Per-row visibility into what committed, which lets the query compute the resume line and the failure reason.

LOAD CSV

The linenumber() function provides row position automatically, since each row corresponds to a physical line in the file. Combined with CALL { …​ } IN TRANSACTIONS for independent batch commits and REPORT STATUS for per-batch failure reporting, the following query identifies the point of failure.

More specifically, it tags each row with its line number using a parameter, runs the writes in small independently-committed batches, and captures a status report per batch. It then filters that report down to any batches that failed to commit, and returns the earliest failing line along with the reason, so the import can be resumed from exactly that point.

Set start cursor
{
  "startFromLine": 0
}
Finding what row failed using LOAD CSV
LOAD CSV WITH HEADERS FROM 'file:///people.csv' AS line
WITH line, linenumber() AS lineNum
WHERE lineNum >= $startFromLine
CALL (line) {
  MERGE (p:Person {id: toInteger(line.id)})
  SET p.name = line.name,
      p.age = toInteger(line.age)
} IN TRANSACTIONS OF 2 ROWS
  ON ERROR RETRY THEN BREAK
  REPORT STATUS AS status
WITH status, lineNum
WHERE status.committed = false
WITH min(lineNum) AS firstNonCommittedLine, collect(DISTINCT status.errorMessage) AS errorReasons
WHERE firstNonCommittedLine IS NOT NULL
RETURN firstNonCommittedLine, errorReasons
firstNonCommittedLine errorReasons

4

["Cannot merge the following node because of null property value for 'id': (:Person {id: null})"]

Rows: 1

Trusting firstNonCommittedLine as the resume point relies on ON ERROR BREAK stopping at the first batch that still fails: every batch before it already committed, and nothing after it ever ran, so committed batches always form a gap-free prefix of the file.

UNWIND

The pattern established for LOAD CSV relies on linenumber() to tag each row’s position automatically. For UNWIND, this needs to be adapted: no equivalent exists for an in-memory list, so each row’s position must be assigned explicitly, as a rowIndex field, before the data is sent and the resume cursor $startFromLine becomes $startFromIndex, checked against that field instead. The logic of the query does not change, however, if you are using UNWIND.

Parameters
{
  "rows": [
    {"rowIndex": 1, "id": "1", "name": "Alice", "age": "30"},
    {"rowIndex": 2, "id": "2", "name": "Bob", "age": "25"},
    {"rowIndex": 3, "id": null, "name": "Carol", "age": "35"},
    {"rowIndex": 4, "id": "4", "name": "Dave", "age": "40"}
  ],
  "startFromIndex": 1
}
Finding what row failed using UNWIND
UNWIND $rows AS row
WITH row, row.rowIndex AS idx
WHERE idx >= $startFromIndex
CALL (row) {
  MERGE (p:Person {id: toInteger(row.id)})
  SET p.name = row.name,
      p.age = toInteger(row.age)
} IN TRANSACTIONS OF 2 ROWS
  ON ERROR RETRY THEN BREAK
  REPORT STATUS AS status
WITH status, idx
WHERE status.committed = false
WITH min(idx) AS firstNonCommittedIndex, collect(DISTINCT status.errorMessage) AS errorReasons
WHERE firstNonCommittedIndex IS NOT NULL
RETURN firstNonCommittedIndex, errorReasons
firstNonCommittedIndex errorReasons

3

["Cannot merge the following node because of null property value for 'id': (:Person {id: null})"]

Rows: 1

firstNonCommittedIndex: 3 here corresponds to the same row as firstNonCommittedLine: 4 in the LOAD CSV example. The numbers differ only because linenumber() counts the CSV header as line 1, while rowIndex here starts at 1 with no header to account for.

Resuming failed imports

Once the data is fixed, or if the error had been transient (a deadlock, a lock-acquisition timeout), rerun the same query with the resume cursor set to the line or index it stopped at. Rows before the resume point are skipped; everything from that point onward is reprocessed. MERGE, not CREATE, is what makes this safe even if the resume point is not perfectly precise: MERGE is idempotent, replaying an already-committed row finds the existing node and makes no change, rather than creating a duplicate.

fixed_people.csv
id,name,age
1,Alice,30
2,Bob,25
3,Carol,35
4,Dave,40
Set resume cursor to point first import failed at
{
  "startFromLine": 4
}
Resumed LOAD CSV ingestion
LOAD CSV WITH HEADERS FROM 'file:///fixed_people.csv' AS line
WITH line, linenumber() AS lineNum
WHERE lineNum >= $startFromLine
CALL (line) {
  MERGE (p:Person {id: toInteger(line.id)})
  SET p.name = line.name,
      p.age = toInteger(line.age)
} IN TRANSACTIONS OF 2 ROWS
  ON ERROR RETRY THEN BREAK
  REPORT STATUS AS status
WITH status, lineNum
WHERE status.committed = false
WITH min(lineNum) AS firstNonCommittedLine, collect(DISTINCT status.errorMessage) AS errorReasons
WHERE firstNonCommittedLine IS NOT NULL
RETURN firstNonCommittedLine, errorReasons
Confirm data has been ingested
MATCH (p:Person)
RETURN p.id AS id,
       p.name AS name,
       p.age AS age
id name age

1

"Alice"

30

2

"Bob"

25

3

"Carol"

35

4

"Dave"

40

Rows: 4

Retryable vs. non-retryable errors

Not every failure is worth resuming. Check the error before you restart the ingestion.

Category Example errors / codes Resume can succeed? Notes

Deadlock

Neo.TransientError.Transaction.DeadlockDetected

Yes

Transient; a re-attempt on a different transaction interleaving usually commits.

Lock-acquisition timeout

Neo.TransientError.Transaction.LockAcquisitionTimeout

Yes

Transient; contention may have cleared by the time you resume.

General transient / unavailable

Neo.TransientError.*

Yes

Transient by definition; safe to retry.

Leader change (cluster only)

Neo.ClientError.Cluster.NotALeader

Yes (cluster)

Does not occur on a standalone instance. Only relevant when running against a cluster; the new leader can accept the re-attempt.

Constraint violation

Neo.ClientError.Schema.ConstraintValidationFailed

No

The data conflicts with a schema constraint. Resuming replays the same rows and fails identically. Fix the data or the constraint first.

Type / format / conversion error

For example, a non-numeric value passed to toInteger(), or a malformed date

No

The offending CSV values are unchanged on re-read. Fix the source data or the parsing logic in the query.

Cypher syntax error

Neo.ClientError.Statement.SyntaxError

No

The query itself is wrong. Resuming re-submits the same broken statement. Fix the query.

Automatic resume loop

The following Python script automates the resume loop described above, checking each failure for whether it is retryable before deciding whether to continue.

def is_retryable(reasons):
    non_retryable_prefixes = (
        "Neo.ClientError.Schema.ConstraintValidationFailed",
        "Neo.ClientError.Statement.SyntaxError",
    )
    if not reasons:
        return True
    return not any(reason.startswith(p) for p in non_retryable_prefixes for reason in reasons)

QUERY = """
LOAD CSV WITH HEADERS FROM $csv_filename AS line
WITH line, linenumber() AS lineNum
WHERE lineNum >= $startFromLine
CALL (line) {
  MERGE (p:Person {id: toInteger(line.id)})
  SET p.name = line.name,
      p.age = toInteger(line.age)
} IN TRANSACTIONS OF $batchSize ROWS
  ON ERROR RETRY THEN BREAK
  REPORT STATUS AS status
WITH status, lineNum
WHERE status.committed = false
WITH min(lineNum) AS firstNonCommittedLine, collect(DISTINCT status.errorMessage) AS errorReasons
WHERE firstNonCommittedLine IS NOT NULL
RETURN firstNonCommittedLine, errorReasons
"""

start_from_line, prev_line, stuck_count = 0, None, 0

with driver.session() as session:
    while True:
        record = session.run(
            QUERY,
            csv_filename=CSV_FILENAME,
            startFromLine=start_from_line,
            batchSize=BATCH_SIZE,
        ).single()

        if record is None:
            print("Import complete.")
            break

        line, reasons = record["firstNonCommittedLine"], record["errorReasons"]
        stuck_count = stuck_count + 1 if line == prev_line else 1
        prev_line = line

        if stuck_count >= 3:
            raise RuntimeError(f"Stuck at line {line} after 3 attempts: {reasons}")
        if not is_retryable(reasons):
            raise RuntimeError(f"Non-retryable error at line {line}: {reasons}")

        print(f"Resuming from line {line} after: {reasons}")
        start_from_line = line

General recommendations

  • LOAD CSV always re-scans the file from the top and discards rows before the cursor; it doesn’t seek. On a very large file with many resumes, this adds up. Mitigate by splitting large files and keeping $batchSize tuned so failures are rare.

  • Only batch failures caught by ON ERROR BREAK are recovered this way. A whole-statement crash (server restart, dropped connection) returns no status at all. Cover that case with idempotent MERGE plus a full rerun, or external checkpointing.

  • REPORT STATUS requires ON ERROR BREAK (or RETRY THEN BREAK); it’s not allowed with the default ON ERROR FAIL.

  • Choose $batchSize carefully: too small wastes commit overhead and lengthens rescans, too large increases lock contention and the amount of work lost if a batch fails.

  • Abort if the resume point hasn’t advanced after 3 consecutive attempts; a stuck import shouldn’t retry forever.

  • Always check errorReasons before resuming, and stop on non-retryable errors. Resume only after fixing the root cause.

  • Split very large files to bound rescan cost and limit how much work is lost to any one failure.