Insert Multiple

Executing Bulk & Batch Inserts with Transactions

Bulk inserts add multiple rows in a single SQL operation. Using database Transactions (beginTransaction(), commit(), rollBack()) guarantees atomic execution, ensuring either all rows are inserted or none are.

Database Transaction Lifecycle

flowchart TD
    A["$pdo->beginTransaction()"] --> B["Execute Bulk Inserts inside Loop"]
    B --> C{"Any Error Encountered?"}
    C -- Yes --> D["$pdo->rollBack() (Revert All Changes)"]
    C -- No --> E["$pdo->commit() (Persist Changes to DB)"]

Practical Code Example

<?php
declare(strict_types=1);

try {
    $pdo = new PDO("mysql:host=127.0.0.1;dbname=ecommerce_db;charset=utf8mb4", 'root', 'secret_password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);

    $newUsers = [
        ['username' => 'alice_w', 'email' => '[email protected]'],
        ['username' => 'bob_builder', 'email' => '[email protected]'],
        ['username' => 'charlie_dev', 'email' => '[email protected]']
    ];

    // 1. Begin Atomic Transaction
    $pdo->beginTransaction();

    // 2. Prepare Single Insert Statement
    $stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (:username, :email)");

    // 3. Loop and execute prepared statement for each item
    foreach ($newUsers as $user) {
        $stmt->execute($user);
    }

    // 4. Commit all inserts at once!
    $pdo->commit();
    echo "Batch Inserted " . count($newUsers) . " Users Atomically!
";

} catch (PDOException $e) {
    // Revert all changes if any single insert failed!
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    die("Transaction Failed & Rolled Back: " . $e->getMessage());
}

Best Practices

• Wrap Batch Inserts in Transactions: Executing 1000 inserts inside a single transaction is 100x faster than 1000 individual auto-committing queries!
• Always Catch Exceptions and Roll Back: Call $pdo->rollBack() inside the catch block to keep the database consistent.
• Check $pdo->inTransaction(): Verify transaction state before calling rollBack().

Self-Check Challenge

What method on the PDO instance rolls back an active transaction when an error occurs? ($pdo->rollBack())