PHP Bulk Insert with Rollback Using Prepared Statements

A nightly job writes 20,000 scraped product rows. Row 14,932 carries a currency code nine characters long, because the source page changed its markup on Tuesday. What happens next has almost nothing to do with your try/catch and almost everything to do with three settings you wrote once and never looked at again: the storage engine of the target table, the server's sql_mode, and whether PDO throws. Get any of them wrong and the batch does not fail. It commits 20,000 rows, one of which is silently truncated, and nothing anywhere logs a word about it.

The pattern that fixes this is old and short: wrap the batch in a transaction, and roll the whole thing back when a single record fails. The pattern is not the hard part. What follows is everything around it, re-measured on 10 August 2026 against PHP 8.4.21 and MariaDB 10.11.14, and re-read against MySQL's own manual for the current 9.7 series. Four numbers in the previous version of this article turned out to be wrong, and they are corrected below by name.

Examples use PDO with MySQL, the usual pairing for web scraping in PHP. Most of it carries over to MySQLi and other engines; the places where it does not are called out.

What the transaction is actually for

A transaction groups statements into one unit: every change commits together, or none does. Without one, a loop of individual inserts that fails at row 237 leaves 236 orphaned rows and a table you now have to reconcile by hand. With one, a single rollback() erases everything since beginTransaction(), and the failure arrives as an exception you can log and retry.

That is the correctness argument, and it is the reason to do it. There is a second reason nobody puts in the headline, and it outweighs every other optimisation here.

Inserting 20,000 rows one prepared execute() at a time, in autocommit mode, took 6.079 seconds. The identical loop wrapped in one transaction took 0.397 seconds. That is 15 times faster for one beginTransaction() and one commit(), and the reason is in MySQL's own bulk-loading guidance: autocommit "performs a log flush to disk for every insert". Twenty thousand rows means twenty thousand fsyncs.

So the transaction is not a safety tax you pay for atomicity. It is the single biggest speed lever on the list, and atomicity comes free with it.

Setting PDO up so failures are visible

Two prerequisites make any of this trustworthy.

PDO has to throw. Since PHP 8.0 it does by default: the migration notes record that "the default error handling mode has been changed from 'silent' to 'exceptions'". Set it explicitly anyway. Connection options travel with the code and survive a move to an older host, and a false return that a loop ignores is exactly the failure mode this whole article exists to prevent.

Use real prepared statements. They separate SQL from data, which is what stops SQL injection, and they let the server reuse one query plan across the batch. Turning emulation off has a second consequence that matters later: it moves you under a hard limit on placeholder count.

$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,   // real server-side prepares
    ]
);

On PHP 8.4 and later there is a better constructor. PDO::connect() returns a driver-specific subclass instead of a bare PDO, and on MySQL that subclass carries one method this article needs:

$pdo = PDO::connect($dsn, $user, $pass, $options);
// PHP 8.4.21 returns Pdo\Mysql here, not PDO.
// $pdo->getWarningCount() now works — see the INSERT IGNORE section below.

Pdo\Mysql::getWarningCount() came in through the driver-specific subclasses RFC, which passed 23 to 0 and shipped in 8.4. It returns the warning count for the last statement without a SHOW WARNINGS round trip. That is the cheapest instrument you can point at a batch that succeeded when it should not have.

Strict mode is part of the setup, not a server detail. We inserted a nine-character string into a CHAR(3) column twice. With sql_mode empty, PDO threw nothing, the batch committed, and the column held 'USD'. With STRICT_TRANS_TABLES on, the same insert raised error 1406, "Data too long for column 'currency' at row 1", and the rollback fired. A rollback can only undo failures the server agrees are failures. On a non-strict server your carefully written catch block never runs.

Approach 1: prepare once, execute per row

Prepare the statement once, begin a transaction, execute per record, and let the first throw take you to rollBack().

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) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();           // any row failed, undo the entire batch
        }
        throw new \RuntimeException(
            'Bulk insert failed, rolled back: ' . $e->getMessage(),
            (int) $e->getCode(),
            $e
        );
    }
}

Four things in that function are load-bearing.

• One try around the whole loop, not around each execute(). Catching per row and continuing is a different program with a different guarantee. It is a reasonable program, but it is not all-or-nothing, and the two get confused constantly.
• The inTransaction() guard is not decoration. rollBack() throws PDOException with the message "There is no active transaction" when the transaction is already gone, and there are at least two ordinary ways for it to be gone before your catch block runs. Both are in the breakage section below. An unguarded rollBack() inside a catch replaces your real exception with a misleading one, and the original stack trace goes with it.
• Re-throw with the original chained. The caller needs to know the batch failed and why. Passing $e as the previous exception keeps the trace.
• Watch the SQL commas. A version of this snippet that circulated widely had (`amount`,`currencymessage`), missing the comma between two column names. It is a syntax error, and it kills the whole batch rather than one row. Small typo, entire night's data.

Approach 2: one multi-row INSERT

Each execute() is a round trip. A single statement with many value tuples is one round trip and one parse, so it should win, and it does. Build the placeholder groups, flatten the values, bind once.

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) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw new \RuntimeException(
            'Multi-row insert failed, rolled back: ' . $e->getMessage(),
            (int) $e->getCode(),
            $e
        );
    }
}

A single multi-row INSERT is atomic by itself on InnoDB. We inserted four tuples where the third duplicated a unique key: the statement failed with error 1062 and the table was left with zero rows, no transaction opened. The same four tuples against a MyISAM copy of the table failed with the same 1062 and left two rows behind. The previous version of this article said the statement is atomic on its own and stopped there. It is atomic on a transactional engine, which is not the same claim.

The explicit transaction still earns its place. It lets you put the insert and other statements under one guarantee, such as marking a runs row complete only if the rows landed, and it makes the intent readable.

The two ceilings, and their real numbers

Placeholders. With emulation off, PDO sends your parameters through the binary protocol, and the COM_STMT_PREPARE response carries num_params as int<2>. Two bytes, so 65,535. We walked up to the boundary: 65,535 placeholders prepared and executed fine, 65,538 failed with error 1390, "Prepared statement contains too many placeholders". Three columns means 21,845 rows per statement and not one more.

Packet size. MySQL closes the connection on any packet over max_allowed_packet. The previous version of this article called the default "a few megabytes." That was true of MySQL 5.7 and has been wrong for years. MySQL's own packet-too-large page puts the server default at 64MB, the mysql client at 16MB, and the protocol ceiling at 1GB. The MariaDB box we measured on defaults to 16MB, and at 90,000 wide rows the driver returned SQLSTATE 08S01, error 1153, "Got a packet bigger than 'max_allowed_packet' bytes", and dropped the connection.

Which ceiling you hit first depends on row width, and for scraped data it is usually the placeholder one. A tuple like ('149.99','USD','Invoice INV-00001234') is 39 bytes. A 16MB packet holds roughly 430,000 of those; native prepares stop you at 21,845.

Emulation moves the wall. With PDO::ATTR_EMULATE_PREPARES => true, PDO interpolates the values in PHP and sends one plain string, so the placeholder limit disappears. We executed 200,001 placeholders that way without complaint. You are then bounded only by the packet, and you have given up server-side prepares to get there.

Where the time actually goes

Everything above is arithmetic about round trips. Here are the round trips, timed.

Measured on 10 August 2026 on a single 2-vCPU container: PHP 8.4.21 CLI, MariaDB 10.11.14, InnoDB, innodb_flush_log_at_trx_commit = 1, connection over TCP loopback. 20,000 rows of three columns, table truncated between runs, median of five runs each. Your absolute numbers will differ by an order of magnitude on real hardware. The ordering will not.

Three readings, in descending order of how much they should change your code.

The transaction is worth 15x. The multi-row rewrite is worth another 3.4x. If you only ever do one thing to a slow importer, wrap it in beginTransaction(). The rewrite from a loop into tuple groups is the more interesting engineering and the smaller prize.

Chunk size stops mattering almost immediately. Everything from 100 rows per statement to all 20,000 in a single statement landed within the run-to-run noise of each other. The previous version of this article recommended chunks of 500 to 2,000 rows as a starting range, implying there is a tuning curve. On this workload there is not. Pick a number that keeps you clear of both ceilings and spend your attention elsewhere.

LOAD DATA is fast, but not by the multiplier people quote. MySQL's optimisation page says LOAD DATA is "usually 20 times faster than using INSERT statements", and that number gets repeated as though it applies to whatever you are doing now. It compares against naive single-row inserts. Against our autocommit loop the real multiplier was 72x. Against a batched multi-row insert inside a transaction it was 1.4x. That is not nothing, and it is not a reason to rewrite a working loader.

Chunking, and why it is not about speed

The table above says chunking buys you no throughput past a hundred rows. Chunk anyway, for three reasons that have nothing to do with the clock. You stay under both ceilings without arithmetic. You hold row locks for milliseconds instead of for the length of the import, which matters the moment anything else touches the table. And a failure costs you one chunk instead of the run.

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 alone
    }
}

Note what that function gives up. Each chunk is its own transaction, so a failure at chunk 40 leaves chunks 1 to 39 committed. If the whole import must be all-or-nothing, open one transaction around the loop and use savepoints per chunk. We tested that path: SAVEPOINT chunk_2, then ROLLBACK TO SAVEPOINT chunk_2, then COMMIT, and the row written before the savepoint survived while the row after it did not. Savepoints are also how Doctrine DBAL emulates nesting: "transaction nesting is emulated by resorting to SQL savepoints," and its docs warn that calling PDO::beginTransaction() directly underneath DBAL "can therefore corrupt the nesting level."

Plain PDO has no such emulation. beginTransaction() inside an open transaction throws, and it throws even when ATTR_ERRMODE is not set to exceptions. A function that might be called from inside somebody else's transaction has to check inTransaction() and use savepoints, not assume it owns the boundary.

Upserts: making a re-scrape idempotent

Scrapers re-run and re-find the same items, so blind inserts collide with unique keys. Rolling back an entire batch over a duplicate you do not care about is the wrong response. INSERT ... ON DUPLICATE KEY UPDATE inserts new rows and refreshes existing ones in one statement, given a UNIQUE index on whatever identifies the record.

Here the syntax has moved, and most PHP tutorials have not moved with it. The VALUES() function was deprecated in MySQL 8.0.20, whose release notes say plainly that it "is now deprecated, and is subject to removal in a future MySQL release." The replacement, row and column aliases, arrived one release earlier in 8.0.19. The current 9.7 manual still documents VALUES() and still marks it deprecated.

-- MySQL 8.0.19+, the current form
INSERT INTO products (sku, title, price, scraped_at)
     VALUES (:sku, :title, :price, NOW()) AS new
        ON DUPLICATE KEY UPDATE
           title      = new.title,
           price      = new.price,
           scraped_at = new.scraped_at;

-- deprecated since 8.0.20, and the only form MariaDB accepts
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);

Those two are not interchangeable, and the split is worth knowing before you deploy. MariaDB documents only the VALUES() form, and on 10.11.14 the alias version failed outright with error 1064, a plain syntax error. Code written against current MySQL does not run on MariaDB, and code written against MariaDB is running on a deprecation notice in MySQL. If you ship to both, keep the statement in one place and pick the form at connect time.

Now the parts that surprise people once the upsert works.

rowCount() does not count rows. After an ON DUPLICATE KEY UPDATE we measured 1 for a fresh insert, 2 for a row whose values changed, and 0 for a row re-sent identically. A three-row batch of one new, one changed and one unchanged reported three affected rows, which reads exactly like "three rows inserted" and means nothing of the sort. If you need to know how many products are new tonight, count them, do not infer them.

lastInsertId() returns the first id, not the last. We inserted three rows in one statement, they took ids 1, 2 and 3, and lastInsertId() returned 1. This is documented behaviour: MySQL's manual states that with a multiple-row insert, LAST_INSERT_ID() "returns the value generated for the first inserted row only". Code that treats it as the last id writes its child rows against the wrong parent.

Upserts burn auto-increment values. We inserted one product, ran five no-op upserts against the same SKU, then inserted a genuinely new product. It got id 7. Each duplicate-key upsert allocated an id and threw it away. On a nightly re-scrape of a large catalogue, an INT primary key is being consumed at the rate of rows seen, not rows kept.

A batch may contain the same key twice. MySQL and MariaDB accept it and apply the tuples in order, so the last one wins. PostgreSQL does not: its manual calls ON CONFLICT DO UPDATE a "deterministic" statement and says a cardinality violation error is raised when one existing row would be touched twice. Deduplicate the batch in PHP before it reaches the database and the question stops being dialect-specific.

If you are inside a framework, check what it does before hand-rolling this. Laravel's upsert() in 13.x compiles to exactly this statement, with a caveat in its own docs: "the MariaDB and MySQL database drivers ignore the second argument of the upsert method and always use the 'primary' and 'unique' indexes of the table to detect existing records." Naming the wrong column there does nothing at all on MySQL, and something quite different on PostgreSQL.

With an upsert in place, re-scraping refreshes prices instead of erroring, which is what you want for monitoring data that changes over time. It also means genuine rollbacks become rare, because the common collision is no longer an error.

Where the all-or-nothing guarantee stops holding

This is the section the pattern needs and rarely gets. Every item below was reproduced on the test box, and in every one of them rollBack() fails to do what the surrounding code assumes.

The table is not transactional. We opened a transaction on a MyISAM table, inserted two rows, and called rollBack(). It returned normally. No exception, no warning, and both rows still in the table afterwards. Nothing in PHP tells you the guarantee was never there. Check the engine, not the code.

A DDL statement ran inside the transaction. MySQL issues an implicit commit for CREATE TABLE, ALTER, TRUNCATE and friends, which php.net states directly: "The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary." We inserted a row, created a table, inserted a second row, then rolled back. The rollBack() call itself threw "There is no active transaction," and both rows survived. A catch block that calls rollBack() unguarded turns a data error into a confusing PDO error and loses the original.

A deadlock already rolled you back. InnoDB resolves a deadlock by rolling back one transaction whole. We forced one across two PHP processes. The victim got error 1213, $pdo->inTransaction() still returned true, and the row it had written earlier in the batch was gone. PDO cannot see a rollback it did not issue. The guarded rollBack() then succeeded as a no-op, which is fine; code that reads inTransaction() as "my writes are still pending" is reading a stale answer.

A lock wait timeout rolled back only one statement. This is the asymmetry that catches people. MySQL's error handling page is explicit: a deadlock "causes InnoDB to roll back the entire transaction," while a lock wait timeout "causes InnoDB to roll back the current statement" unless the server runs with innodb_rollback_on_timeout enabled, which is not the default. We reproduced it: error 1205, transaction still open, earlier rows still pending, everything committable. Catch that, log it, and continue the loop, and you commit a half-written batch.

INSERT IGNORE ignores far more than duplicates. It downgrades errors to warnings across the board. Under STRICT_TRANS_TABLES we sent a nine-character value into CHAR(3) with INSERT IGNORE, and the row was inserted with the value truncated to 'USD'. On a two-row batch where one row duplicated a key, one row landed, and getWarningCount() returned 1 with warning 1062. That method is the cheap fix: assert zero warnings after each batch and INSERT IGNORE stops being a silent data shredder.

The value never reached the database as what you think. PDO binds parameters as strings by default. A PHP false arrives as an empty string, and against an INT column that is error 1366, "Incorrect integer value: ''". A string like '12abc' is error 1265, "Data truncated." Both are legitimate failures that trigger a legitimate rollback, and both are far cheaper to catch in PHP. Validate and cast before the batch is built, and a bad row gets quarantined instead of killing 19,999 good ones.

Six ways out of one guarantee. Two are completely silent, and the other four throw an exception that points somewhere other than the cause.

Retrying instead of failing

Once you know a deadlock rolls back the whole transaction, the correct response follows from MySQL's own advice, which is unusually blunt: "Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again."

The important part is distinguishing what to retry from what to give up on. A deadlock and a lock wait timeout are transient. A duplicate key or a truncation is your data, and retrying it produces the same failure at the same speed forever.

const TRANSIENT = [1213, 1205];   // deadlock, lock wait timeout

function insertChunkWithRetry(PDO $pdo, array $chunk, int $attempts = 3): void
{
    for ($try = 1; ; $try++) {
        try {
            bulkInsertMultiRow($pdo, $chunk);
            return;
        } catch (\RuntimeException $e) {
            $prev = $e->getPrevious();
            $errno = $prev instanceof \PDOException ? (int) ($prev->errorInfo[1] ?? 0) : 0;

            if (!in_array($errno, TRANSIENT, true) || $try >= $attempts) {
                throw $e;               // permanent, or we are out of tries
            }
            usleep((int) (50_000 * (2 ** ($try - 1)) * (1 + mt_rand() / mt_getrandmax())));
        }
    }
}

The jitter in that sleep is not decoration either. Two workers that back off by exactly 50ms and 100ms collide again on the same schedule.

Log the chunk identity alongside the exception, not just the message. "Bulk insert failed, rolled back: Duplicate entry" tells you nothing you can act on at 3 a.m. The same line carrying the chunk index and its first source URL lets you re-run 1,000 rows instead of the night.

What ten thousand pages a night does to this

A script that runs once and a loader that runs every night are different programs, and the differences show up in places the single run never reaches.

Auto-increment gaps become permanent. We inserted 500 rows, rolled the transaction back, then inserted one row. It got id 501. InnoDB does not return allocated values, and a job that rolls back nightly burns ids at the rate of rows attempted. Combined with the upsert behaviour above, a 32-bit signed INT key on a catalogue of 200,000 products re-scraped daily has a finite life. Use BIGINT, or key on the natural identifier and skip the surrogate.

Long transactions are not free even when they are correct. One transaction around 200,000 rows holds locks for its whole duration, grows the undo log, and makes every reader of that table work harder for a consistent view. Per-chunk transactions with a resumable checkpoint beat one heroic transaction in every dimension except literal atomicity.

Connections die between batches. A loader that pauses to fetch the next page can idle past wait_timeout, and the next execute() returns error 2006, "MySQL server has gone away." We hit the same 2006 from the other direction, on the connection MySQL closed after the oversized packet. PDO does not reconnect for you and no attribute makes it. Detect 2006, rebuild the connection, and restart the chunk from its source data, which you still have because you chunked.

The parameter array is real memory. Twenty thousand rows of three columns is 60,000 array entries built and held before a single byte goes out. Generate each chunk lazily and let it go after the execute().

One wasted retry per chunk across a nightly run adds up. At 1,000-row chunks a 10-million-row load is 10,000 chunks. Half a second of unnecessary backoff on each is 83 minutes.

When PHP is the wrong place to do the insert

At the top of our table sits LOAD DATA LOCAL INFILE, at 237,946 rows per second. It is a genuine option for a bulk backfill, and it is transactional on InnoDB like any other DML, so it rolls back with everything else.

It also has a security gate that trips most first attempts. As of MySQL 8.0 the server variable is off: "By default, local_infile is disabled. (This is a change from previous versions of MySQL.)" The client has to opt in as well, through PDO::MYSQL_ATTR_LOCAL_INFILE, and if either side refuses you get error 3950, "Loading local data is disabled; this must be enabled on both the client and server side."

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_LOCAL_INFILE => true,
]);

$pdo->beginTransaction();
$pdo->exec("LOAD DATA LOCAL INFILE '/tmp/scrape.csv'
            INTO TABLE `data`
            FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
            (amount, currency, message)");
$pdo->commit();

Enabling it means a compromised server can ask your client for local files. On a scraper writing to a database you control, that is a manageable trade. On shared infrastructure it is a conversation, and 1.4x over a batched insert may not be worth having it.

The portability picture is worth knowing before you commit to any of this. PostgreSQL 18 spells the upsert ON CONFLICT ... DO UPDATE with an excluded pseudo-table, and rejects a batch that conflicts with itself. SQLite has had the same shape since 3.24.0, released 2018-06-04. SQL Server takes a different road entirely, covered separately in our notes on storing scraped data in a database. The transaction and the rollback are the portable part. Everything about duplicate handling is dialect.

Version housekeeping, current on 10 August 2026: PHP 8.4 and 8.5 are in active support, 8.2 and 8.3 in security fixes only. MySQL 8.0 moved to Oracle Sustaining Support on 21 April 2026, the same day 9.7.0 shipped, with 9.7.2 following on 28 July. If your importer still targets 8.0, a deprecated VALUES() is not the most pressing thing on that server.

What to keep

Turn exceptions on, turn emulation off, and confirm the server runs in strict mode, because a rollback can only undo a failure the server admits to. Wrap the batch in a transaction: that is the correctness guarantee and, at 15x on our measurements, the largest speed win available. Move to a multi-row INSERT for the next 3.4x, and keep chunks well under 21,845 rows for three columns. Guard rollBack() with inTransaction(), retry 1213 and 1205 with jittered backoff, and give up on 1062. Then go and read your production sql_mode, because that one setting decides whether any of the rest of it ever fires.

The storage layer fails quietly rather than loudly, which is why it is usually the last part of a pipeline anyone instruments. If you would rather consume finished records than own the loader and its retry policy, that is what data as a service means in practice: deduplicated rows arrive in your database, and the 3 a.m. deadlock is somebody else's.