On Sat, 15 Apr 2006, Tito Ciuro wrote:
>Hello,
>
>I was wondering whether it is safe to read or write a table while
>being indexed. Here's a scenario: for batch imports, it's sometimes
>better to DROP the indexes, do the INSERTs and then recreate the
>relevant indexes. Indexing may take a little bit of time, so I was
>wondering if:
>
>- Other processes/threads could read or write the data (even though
>the indexes are not yet available)
>
>- Is it safe, or even possible?
SQLite will make possible whatever is safe. Just be prepared to handle
SQLITE_BUSY and SQLite will take care of the rest.
>
>- Does SQLite acquire an EXCLUSIVE lock when indexing? If not, should
>I wrap the CREATE INDEX statements within a BEGIN EXCLUSIVE
>transaction, to be safe?
>
>- If I'm not mistaken, an EXCLUSIVE lock does not stop other readers
>from accessing the database. What would happen if a process/thread
>reads data being indexed?
If the indexing process uses a large SQLite cache (using the cache_size
PRAGMA) then the indexer can perform some of the indexing without
promoting the lock to EXCLUSIVE, and allow concurrent access with readers.
This is using a RESERVED lock. However, the EXCLUSIVE lock will be
required once the cache is filled with dirty pages as pages will be
required to be spilled to the database file (and hence the rollback
journal etc will be created.) This will occur once all current readers
have finished. When an EXCLUSIVE lock is required, no new readers are
allowed.
>
>Thanks in advance,
>
>-- Tito
>
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
