Prev: Toshiba Satellite Pro L305 Series overheating solved
Next: rdesktop segmentation fault
From: Alan Chandler on 10 Apr 2010 07:40
I seem to be stuck (slightly) with converting an some postgres and mysql
based php web applications to sqlite.

There seems to be two possible interfaces to the database SQLite3 and PDO

I have discovered that SQLite3 seems to set the busy timeout to 0 -
meaning that if there is any other activity (from another web thread) on
the database there is a strong chance of a lock failure. There doesn't
seem to be way to change the value.

PDO, on the other hand seems to have this set at 60 seconds, and it is
possible to change it, but doesn't seems to support the "reset" function
to return a cursor to the begining of a rowset during a query.

Is there anyone more expert than me on this who can tell me whether I
can either (a) get round the SQLite3 problem, or (b) get round the PDO
problem.

I am going with PDO at the moment, because the reset problem is minor
but it would be nice to know if I could solve the issue.

(I would also be interested to know if the PDO::beginTransaction and the
equivalent PDO::commit and PDO:rollBack do anything different that just
including a query with the sql commands in them).

--
Alan Chandler
http://www.chandlerfamily.org.uk


--
To UNSUBSCRIBE, email to debian-user-REQUEST(a)lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster(a)lists.debian.org
Archive: http://lists.debian.org/4BC0614F.1030200(a)chandlerfamily.org.uk
From: Alan Chandler on 14 Apr 2010 19:20
Alan Chandler wrote:
> I seem to be stuck (slightly) with converting an some postgres and mysql
> based php web applications to sqlite.
>
> There seems to be two possible interfaces to the database SQLite3 and PDO
>
> I have discovered that SQLite3 seems to set the busy timeout to 0 -
> meaning that if there is any other activity (from another web thread) on
> the database there is a strong chance of a lock failure. There doesn't
> seem to be way to change the value.
>
> PDO, on the other hand seems to have this set at 60 seconds, and it is
> possible to change it, but doesn't seems to support the "reset" function
> to return a cursor to the begining of a rowset during a query.
>
> Is there anyone more expert than me on this who can tell me whether I
> can either (a) get round the SQLite3 problem, or (b) get round the PDO
> problem.
>
> I am going with PDO at the moment, because the reset problem is minor
> but it would be nice to know if I could solve the issue.
>
> (I would also be interested to know if the PDO::beginTransaction and the
> equivalent PDO::commit and PDO:rollBack do anything different that just
> including a query with the sql commands in them).


I know this was a few days ago, and nobody replied, but I just thought I
would share what I have learned since.

Neither PDO or SQLite3 really seamlessly handle parallel transactions.
The underlying engine returns a busy to both PDO and SQLite3 when a lock
contention occurs.

You really need to handle this by rolling back the transaction yourself
and starting it again. That is - except for when the lock contention
comes on the commit, when you just retry the commit until it gets the
lock to enable it to happen.

Having understood how the underlying C API works, it would say that the
SQLite3 is closer to it and am currently writing a class to manage it in
the way most suitable for my application. The core of it is something
like the following (not yet tested) where begin and commit are the names
of preprepared begin and commit statements.

do {

try {
if(!$this->begin->execute()) throw DBCheck("BEGIN");

$this->doWork();

while (!$this->commit->execute()) {
if($this->db->lastErrorCode() != SQLITE_BUSY) {
throw DBError();
}
$this->commit->reset();
usleep(LOCK_WAIT_TIME);
}
break;
} catch(DBCheck $e) {
$this->db->exec("ROLLBACK;");
if($this->db->lastErrorCode() != SQLITE_BUSY) {
throw DBError($e->getMessage());
}
} catch(DBError $e) {
throw new Exception("<p>DATABASE ERROR:".
$this->db->lastErrorMsg());
} catch(DBRollBack $e) {
$this->db->exec("ROLLBACK;");
break;
}
$this->begin->reset();
foreach($this->statements as $statement) {
$statement->reset();
}

} while(true);




--
Alan Chandler
http://www.chandlerfamily.org.uk


--
To UNSUBSCRIBE, email to debian-user-REQUEST(a)lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster(a)lists.debian.org
Archive: http://lists.debian.org/4BC64BAC.7090108(a)chandlerfamily.org.uk
 | 
Pages: 1
Prev: Toshiba Satellite Pro L305 Series overheating solved
Next: rdesktop segmentation fault