On 15.03.2011 15:34, Philip Martin wrote:
> 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.
There's a trick we could use if that's a problem, namely: instead of
simply creating temporary tables by using the connection's default temp
database; create another temp *database* per query. It's anonymous, so
only the code that holds its handle knows about it, and there's no way
for the callback to access it.
This would require a bit more code, however, the concept is a reusable
pattern (and the code for the temp database handling would be reusable,
too.)
-- Brane
Received on 2011-03-15 18:28:21 CET