循环内执行SQLite Insert查询报错：Database is locked

Hey there, let's tackle that "Database is locked" error you're hitting on line 904 in your try block's for loop. This is a super common gotcha with SQLite—especially when doing batch inserts like you are with your product list. Here's what's likely causing it and how to fix it step by step:

1. You're probably mismanaging database connections #

SQLite is a file-based database, so it gets grumpy if connections are left open, or if you're spawning new connections inside your loop (which creates conflicting locks). If you're opening a new connection every time you run an insert in the loop, that's almost certainly the issue.

Fix: #

• Open your database connection once before the loop starts, not inside it.
• Always close the connection when you're done (use a finally block to guarantee this, even if an error happens).
• Reuse the same prepared statement for every insert in the loop (way faster and avoids lock issues too).

2. You're not using transactions properly #

If you're running individual inserts without wrapping them in a transaction, SQLite locks the database for each single write—this adds up fast in a loop and causes lock conflicts. Even worse, if a previous insert started a transaction that never got committed/rolled back, the lock stays stuck.

Fix: #

Wrap your entire loop in a single transaction. This batches all your inserts into one atomic operation, which is way faster and eliminates lock contention. Here's a modified version of your code structure to show this:

try {
    // Open connection ONCE before the loop
    $db = new PDO('sqlite:your_database_file.db');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Start a transaction
    $db->beginTransaction();

    // Prepare your insert statement ONCE
    $insertStmt = $db->prepare("INSERT INTO your_table_name (product, cname, total, paid, bill_id, customer_id) 
                               VALUES (?, ?, ?, ?, ?, ?)");

    // Loop through products and execute the prepared statement
    foreach ($_REQUEST['Products'] as $product) {
        // Bind your values (adjust based on your actual columns!)
        $insertStmt->bindValue(1, $product);
        $insertStmt->bindValue(2, $_REQUEST['cname']);
        $insertStmt->bindValue(3, $_REQUEST['totalAmt']);
        $insertStmt->bindValue(4, $_REQUEST['paidAmt']);
        $insertStmt->bindValue(5, $_REQUEST['billId']);
        $insertStmt->bindValue(6, $_REQUEST['customer_id']);

        $insertStmt->execute(); // This runs the insert without re-locking every time
    }

    // Commit all inserts at once
    $db->commit();
} catch (PDOException $e) {
    // Rollback if anything goes wrong
    if (isset($db)) $db->rollBack();
    echo "Error: " . $e->getMessage();
} finally {
    // Close the connection no matter what
    $db = null;
}

3. Check for external locks #

Sometimes the lock isn't even from your script! If you have a database viewer (like DB Browser for SQLite) open, or another instance of your script running in the background, it can hold the lock on the SQLite file.

Fix: #

• Close any other programs accessing the SQLite database file.
• If your script crashed recently, look for leftover temporary files (like your_db.db-journal) in the same folder as your database—delete these if you're sure no other process is using the DB (they're supposed to be cleaned up automatically, but sometimes get stuck).

4. Stop using multiple database handles #

If you're creating multiple PDO or SQLite3 instances pointing to the same database file in your script, that can cause lock conflicts, especially if they're writing at the same time.

Fix: #

Reuse a single database handle throughout your entire script instead of creating new ones in functions or loops.

Quick check for line 904 #

Double-check that line 904 isn't opening a new database connection or failing to commit a pending transaction. That's the spot where the lock is being triggered, so it's likely related to how you're handling the connection/transaction there.

内容的提问来源于stack exchange，提问作者Waeez