Hi,
We’ve been using Sqlite though Perl for some time now and have started
to get more adventurous.
Our SQLite database is around 32GB in size, is created and manipulated
by a single Perl process and is working well.
What we now want to do is mine the database using a very long running
query to generate analytics.We have the SQL written to query the
database, we have requested help on this list from last year and we are
happy with the SQL query itself. It takes a long time (between 5 and 60
mins) as its a complex query and collates an awful lot of data. Whilst
we would love to have the query execute in 10 secs, thats not going to
happen due to the size of the data and the queries we need to execute.
What we have now found is that when we are running the analytics query
in one Perl process, we can no longer UPDATE the main database through
another Perl process. We are getting “database is locked” errors.
We don’t need any help with our SQL but we are trying to understand
how the locking works (at a high level) in SQL. Reading the docs
(https://www.sqlite.org/lockingv3.html) for locking seems to indicate
the problem
```
SHARED The database may be read but not written. Any number of processes
can hold SHARED locks at the same time, hence there can be many
simultaneous readers. But no other thread or process is allowed to write
to the database file while one or more SHARED locks are active.
```
Our understanding of this is that many processes can READ the database
at the same time but NO process can INSERT/UPDATE if another is reading.
We had thought that one process can write and multiple processes can
read. Our reading (no pun intended) now of this paragraph from the
manual is that you cannot write if one or more processes is reading.
Have we understood this correctly? If so is there an easy way to get
around this?
Further down the page we find
```
5.1 Writer starvation
In SQLite version 2, if many processes are reading from the database, it
might be the case that there is never a time when there are no active
readers. And if there is always at least one read lock on the database,
no process would ever be able to make changes to the database because it
would be impossible to acquire a write lock. This situation is called
writer starvation.
SQLite version 3 seeks to avoid writer starvation through the use of the
PENDING lock. The PENDING lock allows existing readers to continue but
prevents new readers from connecting to the database. So when a process
wants to write a busy database, it can set a PENDING lock which will
prevent new readers from coming in. Assuming existing readers do
eventually complete, all SHARED locks will eventually clear and the
writer will be given a chance to make its changes.
```
We cannot do this as we cannot set the PENDING lock as we do not know
when we need to do a write.
If we have a single reader and therefore no writers we do have a Plan B
(and a plan C) so whilst there is some work for us (1-2 days), we can
put a workflow into the system to get around it.
Thanks,
Rob
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
