On Mon, Nov 23, 2009 at 8:10 AM, Owen Williams <owen-bugs@...> wrote:
> On Fri, 2009-11-20 at 13:45 -0800, Albert Santoni wrote:
>> Apparently I didn't read the fine print that says SQLite doesn't
>> support concurrent multiple writer connections, and so the new library
>> code is semi-broken at the moment. To the best of my understanding, we
>> have two options for solving this:
>>
>> 1) Try to write some sort of wrapper around part of QtSql that queues
>> all SQL interactions into a single thread.
>
>>
>> 2) Reorganize the code that uses the database so that the queries all
>> get executed in the GUI thread.
>
>>
>> So the question is, can we refactor the DB access in the above code to
>> emit a signal that runs these queries in the GUI thread?
>
> Having written a large sqlite-based program (in python), I'm a little
> confused by the problem. Sqlite does allow multiple connections via
> threads, as long as all calls to sqlite are made from the same thread
> that created the sqlite connection. You do mention at the top that
> sqlite doesn't support concurrent *writer* connections, but then your
> solutions involve moving *all* sqlite interaction to one thread.
>
> So, firstly, if some threads only need to read from the DB, then
> multiple connections are ok.
Right now, all of the threads that access the DB need to write to it as well.
>
> But secondly, I don't accept the statement that sqlite doesn't support
> multiple writer connections. As of sqlite3, although it technically
> still doesn't support concurrent writes, the locking is all taken care
> of by sqlite itself (http://www.sqlite.org/lockingv3.html). My software
> has multiple writing db connections, all in different threads, and I've
> never had a race condition along these lines in 3 or 4 years of using
> this software every day (it's an RSS reader). I've had to debug a lot
> of race conditions in the past for other reasons, and I've already run
> into my share of SQLite issues, and this has never been one of them.
>
> So, is the issue the problem of sqlite calls blocking some important
> thread? Or is there something else I'm missing? Based on my own
> experience, I don't understand what the major problem is.
>
I think I don't really understand what the major problem still is either.
<sat here for 10 minutes thinking about what to write next>
<huge epiphany occurs thanks to what you wrote in your email and all
of this reading I did about SQLite's locking mechanisms and
QSqlTableModel... things finally start to click>
<runs off and tries experiment>
Ok, I think I may have just figured this out. It appears that SQLite
keeps the DB locked as long as a result set is active.
If I add this to the end of the LibraryTableModel constructor, all my
threading problems magically go away:
while (canFetchMore())
fetchMore();
I think the QSqlTableModel (that LibraryTableModel inherits from) is
only grabbing the first 1000 rows on startup, and because that's only
_part_ of the result set, the result set isn't deleted. Since the
result set is still active, the whole DB is locked, and that stops all
my other threads from being able to do anything.
I was seeing "database is locked" errors coming from my other threads
before, but only when Mixxx was run for a second time. If I ran Mixxx
with no DB present, the library scanner would run fine, and that makes
sense because since there were no tracks in the Library table, the
result set for the QSqlTableModel would have been nil, ergo no
locking.
Does this make sense?
Thanks a ton to everyone that replied, I've been deep in thought about
what you all wrote for a week, and it definitely helped me think
through this. :)
Albert
|