On Wed, 2005-09-07 at 20:25 +0100, Brandon, Nicholas wrote:
>
> >take longer than 5 seconds. These reads are being done by PHP using the
> >PDO driver for sqlite3. My understanding of the problem is that the PHP
> >reader is holding a SHARED lock for longer than 5 seconds, so while the
> >C program can acquire a PENDING lock, it can not get the EXCLUSIVE lock
> >inside of 5 seconds and thus times out.
>
> I've also used the PDO driver in the past and it took me a while to realise
> that the SHARED lock was not being released by PDO/SQlite under after it
> tried to retrieve the next row and failed. So in the following example loop:
>
> (Run SQL select statement)
> while (fetchArray())
> {
>
> /* Long time spent processing here */
>
> }
>
> The SHARED lock is held until the end of the while loop instead of releasing
> before the loop (which I was expecting). Obviously if you have a significant
> amount of processing in the loop, its not difficult to get writer
> starvation.
>
> A question to those who know - when (in terms of which C API call) does
> SQLite release the SHARED lock after a read?
>
> And a general survey to everyone... in your applications, what is the
> 'standard' practice to handle a SELECT statement that may return more than a
> few rows? Can temporary tables be used without still holding the
> database-level lock?
>
> Sorry Mark, I've noticed I've diverged from your question a tad.Nick, Thanks for bringing this up. We are doing a bunch of processing on each row of data that comes out. We're likely going to experiment with sucking all of the rows into an array and working on it afterwards to see if that benefits things at all. Mark. > Thanks > Nick
signature.asc
Description: This is a digitally signed message part
