note 90239 added to pdo.begintransaction

If you are using PDO::SQLITE and need to support a high level of concurrency with locking, try preparing your statements prior to calling beginTransaction() and you may also need to call closeCursor() on SELECT statements to prevent the driver from thinking that there are open transactions. Here's an example (Windows, PHP version 5.2.8). We test this by opening 2 browser tabs to this script and running them at the same time. If we put the beginTransaction before the prepare, the second browser tab would hit the catch block and the commit would throw another PDOException indicating that transactions were still open. <?php $conn = new PDO('sqlite:C:\path\to\file.sqlite'); $stmt = $conn->prepare('INSERT INTO my_table(my_id, my_value) VALUES(?, ?)'); $waiting = true; // Set a loop condition to test for while($waiting) { try { $conn->beginTransaction(); for($i=0; $i < 10; $i++) { $stmt->bindValue(1, $i, PDO::PARAM_INT); $stmt->bindValue(2, 'TEST', PDO::PARAM_STR); $stmt->execute(); sleep(1); } $conn->commit(); $waiting = false; } catch(PDOException $e) { if(stripos($e->getMessage(), 'DATABASE IS LOCKED') !== false) { // This should be specific to SQLite, sleep for 0.25 seconds // and try again. We do have to commit the open transaction first though $conn->commit(); usleep(250000); } else { $conn->rollBack(); throw $e; } } } ?> ---- Server IP: 64.71.164.2 Probable Submitter: 75.146.46.106 ---- Manual Page -- http://www.php.net/manual/en/pdo.begintransaction.php Edit -- https://master.php.net/note/edit/90239 Del: integrated -- https://master.php.net/note/delete/90239/integrated Del: useless -- https://master.php.net/note/delete/90239/useless Del: bad code -- https://master.php.net/note/delete/90239/bad+code Del: spam -- https://master.php.net/note/delete/90239/spam Del: non-english -- https://master.php.net/note/delete/90239/non-english Del: in docs -- https://master.php.net/note/delete/90239/in+docs Del: other reasons-- https://master.php.net/note/delete/90239 Reject -- https://master.php.net/note/reject/90239 Search -- https://master.php.net/manage/user-notes.php