Hello Pavel, Many thanks on the extensive information. I think I understand what you are saying. I do have a couple of questions.
If on thread #1 using connection #1 does a step on a prepared select statement and then is blocked before the sqlite3_column() statements and tailing reset statement and thread #2 using connection #2 tries to do a step of a different select prepared statement on the same DB, will the second select step return a database lock error? It just seems with what I understand this is a race condition. Of course with multi-core, thread #1 does not even have to block for this to happen. If the above is true, then it seems the solution is to use a single DB connection and let the selects serialize. I choose the separate connection/separate thread model since that was implemented before the separate thread/common connection model assuming the implementation of the former was more established than the later. Again thanks for the information and I apologize for taking up so much list bandwidth. I hope others can benefit. John --- On Thu, 5/12/11, Pavel Ivanov <paiva...@gmail.com> wrote: > From: Pavel Ivanov <paiva...@gmail.com> > Subject: Re: [sqlite] Common Multi-treaded Problem > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Date: Thursday, May 12, 2011, 9:53 PM > > Humm. Resetting each prepared > statement right after use seemed to work. So in review, a > select prepared statement will lock the DB from other > threads (or is it DB connections?) but not the current > thread (or is it DB connection). > > Yes, you are right. Transactions and database locks are > per-connection. So if you work with only one connection > (even sharing > it between different threads) and not resetting your > statements it > means that all your application works in a one huge > transaction, > committing everything at the end (I guess if you hard kill > your > application in the middle you'll see that nothing was > committed to the > database). And if you work with several different > connections (no > matter in different threads or in a single thread) they > will block > each other, i.e. if you execute writing transaction in one > connection > you won't be able to write in a second connection and > sometimes you > won't even able to read in a second connection. > > And answering your question from another email: you can > step through > any number of prepared statements simultaneously as long as > they are > all created from the same connection. They won't block each > other from > executing. You just can't call sqlite3_step() on one > connection > simultaneously in several threads - they will be > serialized. Other > than that you are free to step through any number of select > statements > and execute updates in parallel. But there's one rule of > thumb to > remember: never change data that should be returned in some > active > select statement. You can get very surprising behavior in > this case. > > > Pavel > > > On Thu, May 12, 2011 at 8:33 PM, John Deal <bassd...@yahoo.com> > wrote: > > Hello Pavel, > > > > Humm. Resetting each prepared statement right after > use seemed to work. So in review, a select prepared > statement will lock the DB from other threads (or is it DB > connections?) but not the current thread (or is it DB > connection). > > > > Thanks for the help! > > > > John > > > > --- On Thu, 5/12/11, Pavel Ivanov <paiva...@gmail.com> > wrote: > > > >> From: Pavel Ivanov <paiva...@gmail.com> > >> Subject: Re: [sqlite] Common Multi-treaded > Problem > >> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > >> Date: Thursday, May 12, 2011, 5:58 PM > >> > Interesting is the impression I > >> had with prepared statements was the reset was > only > >> necessary if you wanted to reuse that statement. > Since > >> each each DB connection is in its own instance of > a class > >> (with it own set of prepared statements) I would > not think > >> there would be any dependency on different > physical prepared > >> statements on different threads. I would expect > this with > >> incomplete transactions. > >> > >> There's no dependency between different prepared > >> statements, but there > >> is dependency between transactions as they use the > same > >> database. And > >> transaction cannot be finished (implicitly or > explicitly) > >> until all > >> statements in this transaction are reset or > finalized. > >> > >> > >> Pavel > >> > >> > >> On Thu, May 12, 2011 at 4:01 PM, John Deal <bassd...@yahoo.com> > >> wrote: > >> > Hello Igor, > >> > > >> > That very well maybe it. I am not at home > so can't > >> test for sure but I reset the prepared statements > right > >> before I use them so they are left hanging if > another thread > >> came in. > >> > > >> > Interesting is the impression I had with > prepared > >> statements was the reset was only necessary if you > wanted to > >> reuse that statement. Since each each DB > connection is in > >> its own instance of a class (with it own set of > prepared > >> statements) I would not think there would be any > dependency > >> on different physical prepared statements on > different > >> threads. I would expect this with incomplete > >> transactions. > >> > > >> > Anyway, thanks for the insight. > >> > > >> > John > >> > > >> > --- On Thu, 5/12/11, Igor Tandetnik <itandet...@mvps.org> > >> wrote: > >> > > >> >> From: Igor Tandetnik <itandet...@mvps.org> > >> >> Subject: Re: [sqlite] Common > Multi-treaded > >> Problem > >> >> To: sqlite-users@sqlite.org > >> >> Date: Thursday, May 12, 2011, 12:35 PM > >> >> On 5/12/2011 12:31 PM, John Deal > >> >> wrote: > >> >> > When I allow multiple readers with > each > >> thread using a > >> >> different DB > >> >> > connection (open with the same > flags) and > >> each thread > >> >> having > >> >> > exclusive use of its DB connection > (no > >> sharing of > >> >> connections) and if > >> >> > more than one thread is reading the > DB at the > >> same > >> >> time, the DB > >> >> > becomes locked for writing even when > all the > >> reads are > >> >> finished. > >> >> > >> >> My first inclination would be to look for > places > >> where you > >> >> leak active > >> >> statement handles, by failing to reset > or > >> finalize > >> >> statements. The read > >> >> operation is not really finished until > the > >> statement is > >> >> reset/finalized. > >> >> -- > >> >> Igor Tandetnik > >> >> > >> >> > _______________________________________________ > >> >> sqlite-users mailing list > >> >> sqlite-users@sqlite.org > >> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> >> > >> > > _______________________________________________ > >> > sqlite-users mailing list > >> > sqlite-users@sqlite.org > >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
