On Thu, Apr 5, 2012 at 10:04 AM, Andreas Wiencke <wien...@flyingdog.de>wrote:
>
> Hello,
>
> I have a question regarding the locking in Sqlite3: Does Sqlite3 lock the
> entire database when there is a lock on only one table?
> In my code I want to read from one table and write the results to another
> table. But if I don't fetch all the results and close the reading
> connection, I cannot write to another table in the same database. Is this
> the intended behavior? And how could I work around that?
> I believe that this worked in In SQlite2. Here is a reduced example in PHP:
>
> <?
> $db="/tmp/testdb";
> //unlink($db); // always use a fresh database
>
> // open database s1
> $s1 = new SQLite3($db);
>
> // create a table
> $s1->query("CREATE TABLE t1(one varchar(10), two smallint);");
>
> // write one entry
> $s1->query("INSERT INTO t1 VALUES('dog',1);");
> $s1->query("INSERT INTO t1 VALUES('cat',2);");
>
> // read the only entry and output it
> $r = $s1->query("SELECT * FROM t1");
> $a = $r->fetchArray(SQLITE3_ASSOC);
> var_dump($a);
>
> // close connection to s1, makes the code work
> //$s1->close();
>
Probably the $r query is not being finalized, and is thus holding the read
lock, preventing the subsequent write from happening. You shouldn't have
to close the connection in order to finalize the query - but I don't know
the PHP syntax for finalizing the query without closing the connection.
A work-around is to use write-ahead log
(WAL)<http://www.sqlite.org/wal.html>mode, where it is possible to
read on one connection while simultaneously
writing on a different connection. You'll still want to figure out why
your query is not finalizing, though, since while the query will not longer
block writes, it will block checkpoints from running to completion, which
can result in excessively large WAL files.
>
> // write to table s2 -> does not work because database is locked
> $s2 = new SQLite3($db);
> $s2->query("CREATE TABLE t2(one varchar(10), two smallint);");
> $s2->query("INSERT INTO t2 VALUES('fish',3);");
> ?>
>
> This code produces a warning "database is locked" and does not insert the
> value "fish".
>
> Any help is appreciated.
>
> Thank you,
>
> Andreas
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
