On Mon, Nov 23, 2009 at 8:10 AM, Owen Williams <[email protected]> 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 ------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ Mixxx-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/mixxx-devel
