> 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
