PHP Bulk Insert with Rollback Using Prepared Statements

When you write scraped or imported data into a database, you usually want it to land all or nothing. If you're inserting 500 product rows and record 237 violates a constraint or arrives malformed, you rarely want the first 236 committed and the rest dropped — that leaves a half-populated table you now have to reconcile. The clean solution is a PHP bulk insert wrapped in a transaction: run the whole batch, and if any single record fails, roll the entire thing back so the table is exactly as it was before.

This guide shows two solid approaches — a prepared statement executed per row inside a transaction, and a single multi-row INSERT — plus the error handling, batching, and upsert patterns that make it reliable for storing data at volume. Examples use PDO with MySQL, the modern default for web scraping in PHP; the ideas apply to MySQLi and other engines too.

Why a transaction is the point

A database transaction groups statements into one atomic unit: either every change commits together, or none does. Without it, a loop of individual inserts that fails partway leaves orphaned rows. With it, a single rollback() erases every insert since beginTransaction(), and the failure returns cleanly as an error you can log and retry — no faulty or partial data touches the table.

Two prerequisites make this trustworthy in PHP:

1. Set PDO to throw exceptions. Since PHP 8.0 this is the default, but set it explicitly so failures actually raise PDOException instead of returning false that a loop silently ignores.
2. Use prepared statements. They separate SQL from data, which stops SQL injection and lets the database reuse the query plan across every row in the batch.

$pdo = new PDO(
    'mysql:host=localhost;dbname=scraper;charset=utf8mb4',
    $user,
    $pass,
    [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,   // use real server-side prepares
    ]
);

Approach 1: prepare once, execute per row, roll back on failure

This is the pattern most people want: prepare the statement a single time, begin a transaction, execute it for each record, and if any execution throws, roll the whole transaction back. Preparing once and executing many times is efficient — the database parses and plans the query only once.

function bulkInsert(PDO $pdo, array $rows): void
{
    $sql = 'INSERT INTO `data` (`amount`, `currency`, `message`)
            VALUES (:amount, :currency, :message)';
    $stmt = $pdo->prepare($sql);

    $pdo->beginTransaction();
    try {
        foreach ($rows as $item) {
            $stmt->execute([
                ':amount'   => $item['amount'],
                ':currency' => $item['currency'],
                ':message'  => "Invoice {$item['invoice']}",
            ]);
        }
        $pdo->commit();                 // all rows succeeded — make it permanent
    } catch (\PDOException $e) {
        $pdo->rollBack();               // any row failed — undo the entire batch
        throw new \RuntimeException(
            'Bulk insert failed, rolled back: ' . $e->getMessage(),
            (int) $e->getCode(),
            $e
        );
    }
}

A few points worth calling out, including a bug from older versions of this pattern floating around the web:

• Watch the SQL commas. A frequently copied version of this snippet had (amount,currencymessage) — a missing comma between currency and message — which is a syntax error. Small typo, whole batch dead. The column list above is correct.
• One try around the loop. Wrapping the whole loop (not each individual execute) means the first failure jumps straight to rollBack(). You don't want to catch per row and continue, because that defeats the all-or-nothing guarantee.
• Re-throw with context. Roll back, then throw a wrapped exception so the caller knows the batch failed and why. Chaining the original $e preserves the stack trace.
• ATTR_ERRMODE => EXCEPTION is what makes this work. Without it, a failed execute() returns false instead of throwing, the loop keeps going, and commit() runs on a broken batch.

Approach 2: one multi-row INSERT (fastest)

Executing a prepared statement per row is clean, but each execute() is a round trip to the database. For large batches, a single multi-row INSERT — one statement with many value tuples — is dramatically faster because it's one round trip and one parse. You build the placeholders dynamically and bind every value in one call:

function bulkInsertMultiRow(PDO $pdo, array $rows): void
{
    if ($rows === []) {
        return;
    }

    // one "(?, ?, ?)" group per row
    $placeholders = implode(
        ', ',
        array_fill(0, count($rows), '(?, ?, ?)')
    );

    $sql = "INSERT INTO `data` (`amount`, `currency`, `message`)
            VALUES {$placeholders}";
    $stmt = $pdo->prepare($sql);

    // flatten the rows into a single positional-parameter list
    $params = [];
    foreach ($rows as $item) {
        $params[] = $item['amount'];
        $params[] = $item['currency'];
        $params[] = "Invoice {$item['invoice']}";
    }

    $pdo->beginTransaction();
    try {
        $stmt->execute($params);
        $pdo->commit();
    } catch (\PDOException $e) {
        $pdo->rollBack();
        throw new \RuntimeException(
            'Multi-row insert failed, rolled back: ' . $e->getMessage(),
            (int) $e->getCode(),
            $e
        );
    }
}

A single multi-row INSERT is atomic on its own — if one tuple violates a constraint, the whole statement fails and nothing is inserted, even without an explicit transaction. The transaction still earns its place: it lets you combine this insert with other statements (updating a runs table, say) under the same all-or-nothing guarantee, and it makes intent explicit.

One limit to respect: MySQL caps a single packet at max_allowed_packet (a few megabytes by default), and there's a ceiling on placeholders per statement. So you can't put a million rows in one statement — which leads directly to batching.

Batching large datasets

Scrapes produce thousands of rows; one giant statement will hit packet or placeholder limits, and one giant transaction holds locks and burns memory. Chunk the data into batches of a few hundred to a few thousand rows and insert each batch — each as its own transaction, or all under one outer transaction if you truly need the entire import to be atomic:

function bulkInsertChunked(PDO $pdo, array $rows, int $chunkSize = 1000): void
{
    foreach (array_chunk($rows, $chunkSize) as $chunk) {
        bulkInsertMultiRow($pdo, $chunk);   // each chunk commits or rolls back on its own
    }
}

Chunks of 500–2,000 rows are a good starting range; tune to your row width and max_allowed_packet. Per-chunk transactions keep lock duration and memory sane while still guaranteeing each chunk lands cleanly. If a chunk fails, only that chunk rolls back, and you can log and retry it in isolation.

Deduplicating scraped data with upserts

Scrapers re-run and re-encounter the same items, so blind inserts collide with unique keys. Rather than failing (and rolling back a whole batch over a duplicate you don't care about), use MySQL's INSERT ... ON DUPLICATE KEY UPDATE to insert new rows and update existing ones in a single statement — an upsert. It needs a UNIQUE index on whatever identifies a record (a product SKU, a URL hash):

$sql = "INSERT INTO products (sku, title, price, scraped_at)
        VALUES (:sku, :title, :price, NOW())
        ON DUPLICATE KEY UPDATE
            title      = VALUES(title),
            price      = VALUES(price),
            scraped_at = VALUES(scraped_at)";

Now re-scraping refreshes prices instead of erroring on duplicates — exactly what you want for monitoring data that changes over time. Note that with upserts a "failed record" that would trigger a rollback becomes rarer, since the common collision case is handled gracefully rather than as an error.

Error handling that holds up in production

A few habits keep bulk inserts robust when they run unattended on a schedule:

• Validate before you insert. Cast and check types in PHP first. Catching a bad amount in application code gives a clearer error than a database type failure mid-transaction, and lets you skip or quarantine one bad row instead of losing the batch.
• Beware nested transactions. PDO doesn't support true nested transactions; calling beginTransaction() while one is open throws. If a function might run inside a caller's transaction, check $pdo->inTransaction() and use savepoints for partial rollback instead of assuming you own the transaction.
• Log the failure, keep the source. On rollback, log the exception and, ideally, which record triggered it, so you can fix data rather than guess. Don't swallow the exception — a silent failure that rolls back looks identical to success from the outside.
• Make reruns safe. Combined with upserts and per-chunk transactions, a failed job can be re-run from the start without creating duplicates or partial state.

FAQ

How do I roll back a PHP bulk insert if one record fails? Wrap the batch in beginTransaction() / commit(), and in a catch block call rollBack() before re-throwing. With PDO::ERRMODE_EXCEPTION set, any failed execute() throws and jumps to the catch, so the whole batch is undone atomically.

Which is faster: a loop of prepared executes or one multi-row INSERT? The single multi-row INSERT is faster for large batches because it's one round trip and one parse, versus one round trip per row. Prepare-once-execute-many is cleaner for streaming and small sets; multi-row wins on throughput.

Do I still need a transaction if I use one multi-row INSERT? A single INSERT statement is already atomic — if one tuple fails, none are inserted. The explicit transaction still helps when you want that insert to succeed or fail together with other statements, and it documents intent.

How many rows can I insert at once in MySQL? It's bounded by max_allowed_packet (statement size) and a placeholder limit, not a fixed row count. Chunk large imports into batches of a few hundred to a few thousand rows to stay within those limits.

How should I store scraped data to avoid duplicates? Put a UNIQUE index on the identifying column (SKU, URL hash) and use INSERT ... ON DUPLICATE KEY UPDATE so re-scrapes update existing rows instead of erroring. See our notes on storing scraped data in a database.

Wrapping up

Reliable bulk inserts come down to a few disciplined choices: prepared statements with exceptions turned on, a transaction so a batch is all-or-nothing, and a rollBack() in the catch that undoes everything when a single record fails. Use a multi-row INSERT for speed, chunk large datasets to respect packet limits, and reach for ON DUPLICATE KEY UPDATE so re-scrapes deduplicate themselves. Get those right and your storage layer stops being the fragile part of the pipeline. When the pipeline itself — scraping, cleaning, and loading data on a schedule — is more than you want to build and babysit, scraping.pro delivers it as data as a service: clean, deduplicated records landed straight into your database.