Re: wc_db performance

Philip Martin <philip.martin_at_wandisco.com> writes:

> Branko Čibej <brane_at_e-reka.si> writes:
>
>> The temporary table is:
>>
>> * in a different database, so the read lock on it does not affect
>> the main wc-db;
>> * per-connection, so even the same process using a different
>> database connection will not even see that temporary table.
>
> OK, so the process is:
>
> - read lock on main database
> - write lock on temporary database
> - populate temporary table
> - release write lock
> - release read lock
> - read lock on temporary database
> - make callbacks
> - release read lock
>
> without the temporary table it could be:
>
> - read lock on main database
> - make callbacks
> - release lock
>
> What we gain is that the callback can modify the main database, because
> there is no read lock. It still can't modify it using any functions
> that require write access to the temporary database.
>
> What we lose is that the callback cannot call any "read-only" functions
> that use temporary tables because the step that requires a write lock
> will fail.

So with the temporary table approach the callback really has to use a
separate database connection to read/write the database from within the
callback.

However I think that is also the case if we were to avoid the table and
simply lock the main database; if just one connection was reused it
might be attempt to reuse an SQLite statement.

-- 
Philip