> 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?
No, two select statements never block each other. Pavel On Fri, May 13, 2011 at 8:14 PM, John Deal <bassd...@yahoo.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
