Introduction
I changed a database backfill after concurrent batch transactions produced a reported Prisma P2034 deadlock during a delete-and-createMany transaction. I set the default BACKFILL_BATCH_SIZE to 1 to serialize batch concurrency and wrapped the transaction in a retry loop. I then re-ran the backfill command, and it completed without deadlock errors.
That is the supported before-and-after result. It doesn't prove a particular locking mechanism. P2034 covers write conflicts or deadlocks; the code alone doesn't identify which rows conflicted or how locks were acquired. Concurrent transactions can acquire conflicting locks, but they don't inevitably deadlock. Serialization and retries were the mitigation used here, not evidence that every other approach would fail.
The Rule That Shipped
The shipped rule has two parts. First, the default BACKFILL_BATCH_SIZE is 1. In this backfill, that serializes batch concurrency: the default execution no longer runs multiple batches concurrently. It removes one source of competing transactions within the script, but it doesn't establish that the database has no other concurrent writers.
Second, a retry loop wraps the delete-and-createMany transaction to recover from P2034 deadlocks. Each retry attempts the transaction containing both operations, rather than retrying delete or createMany independently. The retry scope preserves those two operations as one transactional unit.
I implemented both changes together. The evidence doesn't specify the exact retry count, backoff interval, or maximum-attempt behavior. It also doesn't establish whether BACKFILL_BATCH_SIZE remains configurable or overridable beyond its new default. The rule I can describe is a serial default plus transaction-level retries—not a documented retry schedule, a guarantee of unlimited recovery, or a claim that all failures are retried.
Worked Example
The worked case is the backfill command itself. Before the change, running it with concurrent batch transactions produced the reported P2034 deadlock during the delete-and-createMany transaction. The evidence doesn't include query details or database diagnostics that would identify a specific row collision or lock sequence.
For the changed run, the input was the backfill command after setting BACKFILL_BATCH_SIZE=1 and adding the retry-wrapped delete/createMany transaction. I re-ran the command with both changes in place. The backfill completed successfully without the P2034 deadlock error recurring.
This example establishes that one backfill run completed under the combined mitigation. It doesn't demonstrate the retry mechanism in action: the evidence doesn't show whether a retry occurred internally. It also doesn't establish that serialization alone or retries alone would have produced the same outcome. Table names, row counts, command syntax, and independent data-integrity checks aren't supplied, so they aren't part of this worked case.
Challenges and Tradeoffs
Serial execution gives up batch-level parallelism. It can take longer than a successful concurrent run, although no timing measurements are available here. Its benefit is specific: under the default setting, batches from this execution no longer compete with one another through concurrent batch transactions. Other database activity can still cause contention. Serializing one script doesn't give it exclusive database access.
Transaction retries serve a different purpose: they allow another attempt after a transient conflict instead of immediately ending the backfill. They also raise failure-handling questions. Persistent contention can cause repeated failures, and repeated attempts can add latency. Without the retry limits or backoff policy, I can't say how quickly this implementation stops, how aggressively it retries, or what happens when its recovery policy is exhausted.
A deadlock-aborted transaction normally rolls back its database changes; it shouldn't be described as corrupting partial transactional state. Separately, a backfill that spans multiple transactions may have earlier batches already committed when a later batch fails. That distinction matters when considering reruns, but the evidence doesn't establish that partial job completion or manual cleanup occurred here.
Other remedies could involve query plans, indexing, transaction duration, or lock ordering. Restructuring writes might also change contention, but it would require checking that replacement behavior remains correct. These are technical alternatives, not approaches I can claim to have evaluated or rejected. The documented change kept the delete-and-createMany transaction and added serialization plus retries around its execution.
Verification Process
The verification evidence is one reported rerun after both changes were applied. I observed that the backfill command completed without the P2034 deadlock error recurring. That supports the limited conclusion that the combined implementation worked for that run.
Because both changes were applied together, the rerun doesn't isolate their effects. It doesn't show that reducing concurrency was necessary, that retries were necessary, or that the retry path executed. Successful completion without a surfaced deadlock error could mean the conflict was avoided entirely. The supplied record doesn't distinguish that from recovery through retries.
I have not verified behavior on larger datasets, under higher sustained load, or across repeated runs over time. The exact retry count and backoff interval remain unspecified, and the ability to override BACKFILL_BATCH_SIZE remains unconfirmed.
Further verification would need to answer separate questions. Reviewing the retry configuration would establish attempt limits, delay behavior, and terminal failure handling. Observing attempts during a controlled conflict would test the recovery path. Repeated runs and load testing would assess behavior beyond this example. These are next steps, not completed checks or additional results.
Conclusion
I shipped a serial batch default, BACKFILL_BATCH_SIZE=1, together with a retry loop around the delete-and-createMany transaction. The backfill that had produced a reported P2034 deadlock then completed successfully on the reported rerun without that error recurring.
The result supports the combined mitigation for this case, not a universal deadlock fix. Retry configuration, batch-size overridability, and behavior at greater scale or over repeated runs remain unresolved. Whether retries actually occurred is also unknown. Those limits separate the implemented reliability change from guarantees the available verification can't support.