A large import can fail because of a dropped connection, invalid data, or a process time limit. If all changes depend on a single transaction, the system may hold locks for too long and lose all the work that would otherwise be committed only at the end. If, instead, each row is saved independently without a recovery plan, an interruption can leave the data inconsistent.
Designing batch transactions for PHP imports means choosing units of work that can be committed, recorded, and safely repeated. This is not the same as making the entire import atomic: typically, each batch is atomic on its own, while the overall process may be partially applied. That distinction should be reflected both in the implementation and in the operational experience.
Why a single transaction may be unsuitable
A long-running transaction can hold locks, increase pressure on the transaction log, and compete with application queries. If the import processes millions of rows, it is also difficult to tell how much remains and what to do after a failure. The exact behavior depends on the database engine, isolation level, and write volume, so it is worth measuring in the actual environment.
Committing each row shortens each transaction, but can multiply the cost of commits and leave dependencies between rows only partially applied. A batch offers a middle ground: if an operation in the group fails and the exception propagates, that group is rolled back; previously committed batches remain. The application should communicate this as partial progress, not as overall success or a complete rollback.
Choosing the right transaction boundary
The unit does not always have to be a fixed number of rows. It should represent changes that can be validated and persisted coherently. If each record is independent, a batch of rows may be sufficient. If several records make up a business entity—for example, a header and its line items—keeping the set in the same transaction prevents an incomplete entity from being saved.
To select the batch size, evaluate the duration of each batch, memory usage, contention on shared tables, error frequency, and the cost of repeating work. A large batch reduces the number of commits, but takes longer to process and increases the amount of work that must be repeated after a failure. A small batch limits that cost, although it increases overhead and may hurt performance. There is no universal size: start with a configurable value and adjust it using representative load tests.
In PHP, avoid loading the entire file into memory if it can be read incrementally. Read a bounded group of rows, validate their fields, start the transaction, apply the writes, and commit. If an error occurs, roll back the batch transaction and classify the failure. With PDO, for example, the control flow can use beginTransaction(), commit(), and rollBack(); exception handling must ensure that a failed transaction is not committed.
Recording progress for reliable resumption
A checkpoint indicates which part of the source has been processed and committed. It can be a stable identifier, a position in a file, or a composite key, as long as it allows processing to continue without relying on a changing order. For a paginated query, it is usually safer to advance using a stable key than to use an offset that may change if records are inserted or deleted during processing.
Progress and batch changes must be coordinated. When feasible, save the business writes and the new checkpoint in the same transaction. That way, if the process crashes before committing, no progress is recorded that the database did not apply; if it commits, the cursor advances too. If execution state resides in another system, this atomicity is not automatic: you need to design reconciliation or accept that batches will be repeated and make them idempotent.
Also store the import identifier, its status, the checkpoint, counters, and, where appropriate, a reference to the file version or fingerprint. When resuming, verify that it is the same input and that the run is recoverable. Define explicit states, such as pending, in progress, completed, and failed, and a policy for detecting abandoned runs. Do not mark an import as completed just because the process ended without throwing an exception: verify that it reached the expected end.
Isolating errors and controlling retries
Decide in advance whether an invalid record should stop the entire import, be excluded, or be flagged for review. For predictable format errors, it may be useful to validate before opening the transaction and record the reason alongside a row identifier. For persistence errors, roll back the affected batch. Continuing with subsequent batches is reasonable only if their data is independent and the import can be clearly declared partial.
Distinguish permanent errors—such as a missing required value—from transient failures—such as a recoverable connection interruption. Retrying a permanent error wastes resources and makes diagnosis harder. A transient failure, on the other hand, may be retried a limited number of times with an increasing delay. If attempts are exhausted, preserve enough state and context to investigate and resume without hiding the original cause.
Retries can repeat writes if they are not designed carefully. Use business keys or source identifiers to recognize records that have already been imported, and decide whether a repeat should insert, update, ignore, or flag a conflict. Unique constraints in the database can reinforce that decision, but they do not replace business logic. Do not rely on the checkpoint as the only protection against duplicates: it may become stale or fail to cover changes made through another path.
If a batch also sends emails, publishes messages, or calls an API, the database transaction cannot undo those external effects. Do not run them as if they were part of a nonexistent distributed transaction. One alternative is to record the pending event in a transactional outbox table and process it later with retries and idempotency identifiers. If the external effect fails, the state must allow recovery without duplicating the operation or claiming that the batch is fully complete.
Testing consistency, progress, and recovery
Tests should cover more than the happy path. Inject failures before starting a transaction, during writes, just before commit, and after commit but before updating any external state. Verify which data remains, which checkpoint is retained, and whether resuming produces the expected result. Include duplicate records, invalid rows, empty batches, and persistence errors.
In a representative test, measure duration per batch, memory, error rate, lock time, and progress. Also verify that counters distinguish rows read, accepted, rejected, and committed; “processed” can be ambiguous if it includes rolled-back changes. Metrics and logs should identify the import and batch without dumping sensitive data that is not needed for diagnosis.
Pre-production checklist
- The transaction unit matches a coherent business unit.
- The batch size is configurable and has been evaluated with representative data and load.
- The checkpoint advances only when the corresponding batch is committed.
- The resumption strategy uses a stable key and recognizes the source input.
- Permanent and transient errors have distinct, observable handling.
- Repeated operations are idempotent or explicitly detect conflicts.
- External effects are processed using an independent, recoverable strategy.
- Tests verify failures at different points, not just a complete run.
The goal is not to prevent every failure, but to limit its consequences and make the resulting state explicit. With reasoned transaction boundaries, consistent checkpoints, and safe retries, a PHP import can recover without confusing partial progress with completion or reapplying changes that have already been committed.
