Is there an easy way to handle the 'DATABASE IS LOCKED' situation when accessing SQLite from PHP?

Question

PHP

At www.php.net/manual/en/pdo.begintransaction.php in the comments they recommend this way of processing transactions when accessing the SQLite database:

<?php<br>
$conn = new PDO('sqlite:C:\path\to\file.sqlite');<br>
$stmt = $conn->prepare('INSERT INTO my_table(my_id, my_value) VALUES(?, ?)');<br>
$waiting = true; // Set a loop condition to test for<br>
while($waiting) {<br>
    try {<br>
        $conn->beginTransaction();<br>
        for($i=0; $i < 10; $i++) {<br>
            $stmt->bindValue(1, $i, PDO::PARAM_INT);<br>
            $stmt->bindValue(2, 'TEST', PDO::PARAM_STR);<br>
            $stmt->execute();<br>
            sleep(1);<br>
        }<br>
        $conn->commit();<br>
        $waiting = false;<br>
    } catch(PDOException $e) {<br>
        if(stripos($e->getMessage(), 'DATABASE IS LOCKED') !== false) {<br>
            // This should be specific to SQLite, sleep for 0.25 seconds<br>
            // and try again.  We do have to commit the open transaction first though<br>
            $conn->commit();<br>
            usleep(250000);<br>
        } else {<br>
            $conn->rollBack();<br>
            throw $e;<br>
        }<br>
    }<br>
}<br>

The question is: is there an even simpler and more elegant way to handle the 'DATABASE IS LOCKED' situation ?
Is it possible, for example, to force the PDO module in PHP to wait on its own for a database to be unlocked for some given time?
(But not that, you yourself understand, if at each call such an if is heaped up, then the visibility of the code and readability fall.)

Answer 1

You can write a small wrapper.

<?php

function myQuery(\PDO $conn, \closure $function)
    {
    $waiting = true; // Set a loop condition to test for
    while ($waiting)
        {
        try
            {
            $conn->beginTransaction();
            $function($pdo);
            $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;
                }
            }
        } }

$conn = new PDO('sqlite:C:\path\to\file.sqlite');
$stmt = $conn->prepare('INSERT INTO my_table(my_id, my_value) VALUES(?, ?)');

myQuery($conn, function(\PDO $conn)use($stmt)
        {
        for ($i = 0; $i < 10; $i++)
            {
            $stmt->bindValue(1, $i, PDO::PARAM_INT);
            $stmt->bindValue(2, 'TEST', PDO::PARAM_STR);
            $stmt->execute();
            sleep(1);
            }
        });

It is possible not as a function, but to inherit from PDO and make your own method.

Answer 2

I so understand that the error happens at the multithreaded access to base. For example, an attempt to insert data into a table while the second thread performs a selection on the same table. I read somewhere in the documentation that SQLite can't do that.
Therefore, when I solved such a problem in a multi-threaded application in C #, I created a wrapper with monitor sections for accessing the database.

Answer 3

A standard situation, for a multi-threaded application, I don’t know how much this would solve your problem, since I never used semaphores in PHP, but: www.php.net/manual/en/ref.sem.php . Or write your own interprocess lock