Re: [sqlite] c-api
Hello Baruch, You may want to look at sqlite3_exec() (http://www.sqlite.org/c3ref/exec.html). John --- On Wed, 7/27/11, Baruch Burstein wrote: > From: Baruch Burstein > Subject: [sqlite] c-api > To: "General Discussion of SQLite Database" > Date: Wednesday, July 27, 2011, 9:22 AM > Is there an easier way to get a > single value (for instance "select > last_insert_rowid();" ) then prepare -> step -> > column -> finalize? > ___ > 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
Re: [sqlite] year, month & day problem
Hello, I am new and have received much information from this list so I hope I am not wasting bandwidth. I don't know if it is my misunderstanding or typos but should your sqlite3_column_int() use indexes 0,1 and 2 instead of 1, 12 and 13? If this is the case, according to the docs on sqlite3_column_int() "...if the column index is out of range, the result is undefined." I hope I did not misunderstand the issue. --- On Sun, 7/17/11, marco bianchini wrote: > From: marco bianchini > Subject: [sqlite] year, month & day problem > To: sqlite-users@sqlite.org > Date: Sunday, July 17, 2011, 6:05 AM > Hi all, > call me stupid but after some days of try and a lot of > Googleing, im still > wondering how to solve my problem: > i need to execute a query that updates 3 integer fields > (AA, MM, GG) of a > table, containing respectively today year, today month and > today day: > > update settings set AA=strftime('%Y', 'now'), > MM=strftime('%m', 'now'), > GG=strftime('%d', 'now') > > looks working well, but later, when i read that values: > > const char *sql="select AA,MM,GG from > settings"; > sqlite3_stmt *statmentS; > if (sqlite3_prepare_v2(database, sql > ,-1,&statmentS, NULL)==SQLITE_OK) > { > if > (sqlite3_step(statmentS)==SQLITE_ROW) { > UserSettings *k = > [UserSettings sharedUserSettings]; > > k.AA=sqlite3_column_int(statmentS, 1); > > k.MM=sqlite3_column_int(statmentS, 12); > > k.GG=sqlite3_column_int(statmentS, 13); > ... > > > i obtain correct values, but in inverse order: > > AA (year) contains the day number > MM (month) is correct > GG (day) contains the year > > using SQLIte Manager addons for Firefox, this query: > select strftime('%Y', 'now'), strftime("%m", "now"), > strftime("%d", "now") > from settings > returns correct values, running or loading value into XCode > simulator looks > not working and i dont know why. :'( > > > does anyone can suggest me what to fix or check? > thx in advance, marco > ___ > 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
Re: [sqlite] Multi-threading Common Problem
Hello Pavel, Thanks. This is the conclusion I was arriving at. I do use the Sqlite3 utility but only for looking at test results when the server is in an inactive state. Thanks, John --- On Tue, 5/24/11, Pavel Ivanov wrote: > From: Pavel Ivanov > Subject: Re: [sqlite] Multi-threading Common Problem > To: "General Discussion of SQLite Database" > Date: Tuesday, May 24, 2011, 10:32 AM > > I don't want per-say to remove > my pthread_rwlock() but the main point of this discussion is > I should not have to use pthread_rwlock(). > > I'd say pthread_rwlock and SQLite-specific mechanisms work > completely > differently and you should choose depending on what you > want to do. As > you saw to use SQLite's mechanisms you should write > additional code > waiting when write lock is released. As you said it's not > effective > and prone to starvation. But it works across process > boundaries when > pthread_rwlock works only inside your process although it > does all > waiting very effectively on a kernel level. > So if you will ever want to connect to your database with > sqlite3 > command line tool for example while your application is > running, and > you will do some manipulations with the database, then > your > pthread_rwlock won't work and you will still get > SQLITE_BUSY. And now > you decide what mechanism is better for you. > > > Pavel > > > On Tue, May 24, 2011 at 10:11 AM, John Deal > wrote: > > Hello Pavel, > > > > I don't want per-say to remove my pthread_rwlock() but > the main point of this discussion is I should not have to > use pthread_rwlock(). Others have mentioned I should be > using SQLite-specific mechanisms to achieve the same > results. I am just trying to understand how to do that. > Pthread_rwlock() works fine. > > > > Thanks, > > > > John > > > > --- On Tue, 5/24/11, Pavel Ivanov > wrote: > > > >> From: Pavel Ivanov > >> Subject: Re: [sqlite] Multi-threading Common > Problem > >> To: "General Discussion of SQLite Database" > >> Date: Tuesday, May 24, 2011, 9:51 AM > >> > I have all writes in > >> transactions. If I deactivate my > pthread_rwlock() that > >> enforce the above, several writes fail with a > "database > >> locked" error (I assume it is returning > SQLITE_BUSY). > >> > > >> > So how do I implement the equivalent of a > >> pthread_rwlock() using SQLite mechinisms? > >> > >> When SQLITE_BUSY in a reader transaction is > returned just > >> wait a > >> little bit and try again. Also you can benefit > from > >> sqlite3_busy_timeout (http://www.sqlite.org/c3ref/busy_timeout.html). > >> > >> Another question is why do you want to get read > of > >> pthread_rwlock if > >> it works for you? > >> > >> > >> Pavel > >> > >> > >> On Tue, May 24, 2011 at 7:43 AM, John Deal > >> wrote: > >> > Hello Roger, > >> > > >> > Sorry to be so brain-dead but I am still > confused. I > >> have multiple threads, each with their own DB > connection. > >> I want to allow multiple readers accessing the > DB at the > >> same time since nothing is changing. However, if > a writes > >> is to take place, I want all readers to finish > their reads > >> and give the writer exclusive access. Once the > writer is > >> done, the readers can come back in. > >> > > >> > I have all writes in transactions. If I > deactivate > >> my pthread_rwlock() that enforce the above, > several writes > >> fail with a "database locked" error (I assume it > is > >> returning SQLITE_BUSY). With my > pthread_rwlock(), I have > >> multiple threads reading the DB and my writes get > the > >> exclusive access they need. Now I could loop on > the write > >> until it gets in but that seems very wasteful. > >> > > >> > So how do I implement the equivalent of a > >> pthread_rwlock() using SQLite mechinisms? > >> > > >> > Thanks, > >> > > >> > John > >> > > >> > --- On Tue, 5/24/11, Roger Binns > >> wrote: > >> > > >> >> From: Roger Binns > >> >> Subject: Re: [sqlite] Multi-threading > Common > >> Problem > >> >> To: "General Discussion of SQLite > Database" > >> >> Date:
Re: [sqlite] Multi-threading Common Problem
Hello Pavel, I don't want per-say to remove my pthread_rwlock() but the main point of this discussion is I should not have to use pthread_rwlock(). Others have mentioned I should be using SQLite-specific mechanisms to achieve the same results. I am just trying to understand how to do that. Pthread_rwlock() works fine. Thanks, John --- On Tue, 5/24/11, Pavel Ivanov wrote: > From: Pavel Ivanov > Subject: Re: [sqlite] Multi-threading Common Problem > To: "General Discussion of SQLite Database" > Date: Tuesday, May 24, 2011, 9:51 AM > > I have all writes in > transactions. If I deactivate my pthread_rwlock() that > enforce the above, several writes fail with a "database > locked" error (I assume it is returning SQLITE_BUSY). > > > > So how do I implement the equivalent of a > pthread_rwlock() using SQLite mechinisms? > > When SQLITE_BUSY in a reader transaction is returned just > wait a > little bit and try again. Also you can benefit from > sqlite3_busy_timeout (http://www.sqlite.org/c3ref/busy_timeout.html). > > Another question is why do you want to get read of > pthread_rwlock if > it works for you? > > > Pavel > > > On Tue, May 24, 2011 at 7:43 AM, John Deal > wrote: > > Hello Roger, > > > > Sorry to be so brain-dead but I am still confused. I > have multiple threads, each with their own DB connection. > I want to allow multiple readers accessing the DB at the > same time since nothing is changing. However, if a writes > is to take place, I want all readers to finish their reads > and give the writer exclusive access. Once the writer is > done, the readers can come back in. > > > > I have all writes in transactions. If I deactivate > my pthread_rwlock() that enforce the above, several writes > fail with a "database locked" error (I assume it is > returning SQLITE_BUSY). With my pthread_rwlock(), I have > multiple threads reading the DB and my writes get the > exclusive access they need. Now I could loop on the write > until it gets in but that seems very wasteful. > > > > So how do I implement the equivalent of a > pthread_rwlock() using SQLite mechinisms? > > > > Thanks, > > > > John > > > > --- On Tue, 5/24/11, Roger Binns > wrote: > > > >> From: Roger Binns > >> Subject: Re: [sqlite] Multi-threading Common > Problem > >> To: "General Discussion of SQLite Database" > >> Date: Tuesday, May 24, 2011, 3:10 AM > >> -BEGIN PGP SIGNED MESSAGE- > >> Hash: SHA1 > >> > >> On 05/23/2011 09:12 PM, John Deal wrote: > >> > I guess I am lost on how to obtain a many > reader or > >> one writer mutex in SQLite. > >> > >> You are confusing locks on the database and locks > in the > >> library on a > >> sqlite3 pointer. The latter is what the mutex > alloc > >> function you reference > >> is about and there there is no reader/writer > >> mechanism. Access has to be > >> serialized. > >> > >> > You are correct in the locking article > referenced I > >> want a mutex that can have the lock states of > shared, > >> pending, and exclusive. > >> > >> Those are locks on the database which you get > through > >> regular operations and > >> transactions. > >> > >> Roger > >> -BEGIN PGP SIGNATURE- > >> Version: GnuPG v1.4.11 (GNU/Linux) > >> > >> > iEYEARECAAYFAk3bWeIACgkQmOOfHg372QQf8QCgjlawQMJWJ1I3/6OqMkczXswk > >> VWQAmgLzGifXbh9UJpuEdUTTZl8e8xYp > >> =rXCY > >> -END PGP SIGNATURE- > >> ___ > >> 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
Re: [sqlite] Multi-threading Common Problem
Hello Roger, Sorry to be so brain-dead but I am still confused. I have multiple threads, each with their own DB connection. I want to allow multiple readers accessing the DB at the same time since nothing is changing. However, if a writes is to take place, I want all readers to finish their reads and give the writer exclusive access. Once the writer is done, the readers can come back in. I have all writes in transactions. If I deactivate my pthread_rwlock() that enforce the above, several writes fail with a "database locked" error (I assume it is returning SQLITE_BUSY). With my pthread_rwlock(), I have multiple threads reading the DB and my writes get the exclusive access they need. Now I could loop on the write until it gets in but that seems very wasteful. So how do I implement the equivalent of a pthread_rwlock() using SQLite mechinisms? Thanks, John --- On Tue, 5/24/11, Roger Binns wrote: > From: Roger Binns > Subject: Re: [sqlite] Multi-threading Common Problem > To: "General Discussion of SQLite Database" > Date: Tuesday, May 24, 2011, 3:10 AM > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 05/23/2011 09:12 PM, John Deal wrote: > > I guess I am lost on how to obtain a many reader or > one writer mutex in SQLite. > > You are confusing locks on the database and locks in the > library on a > sqlite3 pointer. The latter is what the mutex alloc > function you reference > is about and there there is no reader/writer > mechanism. Access has to be > serialized. > > > You are correct in the locking article referenced I > want a mutex that can have the lock states of shared, > pending, and exclusive. > > Those are locks on the database which you get through > regular operations and > transactions. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.11 (GNU/Linux) > > iEYEARECAAYFAk3bWeIACgkQmOOfHg372QQf8QCgjlawQMJWJ1I3/6OqMkczXswk > VWQAmgLzGifXbh9UJpuEdUTTZl8e8xYp > =rXCY > -END PGP SIGNATURE- > ___ > 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
Re: [sqlite] Multi-threading Common Problem
Hello Roger, Sorry for the long delay. I did not see this in the swamp of recent email. I guess I am lost on how to obtain a many reader or one writer mutex in SQLite. I interpret the mutex returned by sqlite3_mutex_alloc() as being an exclusive mutex since the documentation states "The sqlite3_mutex_enter() and sqlite3_mutex_try() routines attempt to enter a mutex. If another thread is already within the mutex, sqlite3_mutex_enter() will block and sqlite3_mutex_try() will return SQLITE_BUSY." If I interpret this correctly, this is to ensure serial access to the DB in a multi-connection/multi-thread environment. It seems if a recursive mutex is requested, the same thread can enter the mutex multiple times if it also frees the mutex the same amount of time. I don't understand how to implement a multi-reader or one writer mutex with sqlite3_mutex_alloc(). Should I be looking at something else? You are correct in the locking article referenced I want a mutex that can have the lock states of shared, pending, and exclusive. In other words, the same functionality as a pthread_rwlock() OS call. How do I get this? Sorry if I am missing something obvious. Thanks, John --- On Fri, 5/13/11, Roger Binns wrote: > From: Roger Binns > Subject: Re: [sqlite] Multi-threading Common Problem > To: sqlite-users@sqlite.org > Date: Friday, May 13, 2011, 2:00 AM > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 05/12/2011 01:26 PM, John Deal wrote: > > Good question. Very possible my understanding is > not complete. > > This document has the full details: > > http://www.sqlite.org/lockingv3.html > > >I have basically read and write transactions, each > potentially with several accesses to the DB. > >I want to ensure that if a write transaction is > happening, no read > transactions are in progress > > You do know you can use transactions for > reads? Or use multiple database > connections to get isolation. If you worry about the > efficiency of the > latter then don't - ie get your code correct and then worry > about > performance. I recommend against the use of shared > cache mode on general > purpose computers (as opposed to embedded devices with > trivial amounts of > memory) because it changes some API behaviour (especially > busy handling) and > the amount of memory "wasted" is a rounding error. > > In any event it looks like I am not understanding some > deeper aspect of what > you are doing. My underlying point remains - there is > absolutely no need to > remove or workaround SQLite's builtin mutexes. They > ensure that threaded > code does not screw things up and are thoroughly > tested/developed. > > Any question that starts with "so I > removed/changed/subverted SQLite's > existing mutexes" will be followed with a response where > you'll need to > prove that doing so isn't the cause. > > It is ok to augment them with your own locking but even > that should not be > necessary. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.11 (GNU/Linux) > > iEYEARECAAYFAk3MyPkACgkQmOOfHg372QTTXgCcCa2bDbYH9WKQ2J2fPYhKLHPX > DBgAoLoj+uRJ3GDIHWGU7TfgNXxDAuAH > =exlM > -END PGP SIGNATURE- > ___ > 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
Re: [sqlite] Common Multi-treaded Problem
Hello Simon, Yes I am following that sequence. However I do my sqlite3_column(...) calls to extract data from selects between the step() and reset() calls. My concern is the time between the step() and reset() calls if another select step() is attempted from another thread with a separate DB connection. I am afraid that the second step() call will fail with a "database locked" error since the first step() call is complete but the reset() has not taken place yet (because I am doing column() calls to extract the retrieved data before resetting the prepared statement). Thanks, John --- On Fri, 5/13/11, Simon Slavin wrote: > From: Simon Slavin > Subject: Re: [sqlite] Common Multi-treaded Problem > To: "General Discussion of SQLite Database" > Date: Friday, May 13, 2011, 9:05 PM > > On 14 May 2011, at 12:29am, John Deal wrote: > > > I actually never finalize prepared statements until > the DB is closed and not being used anymore. I reset > the prepared statements. For writes (inserts, updates, > etc.) I use explicit transactions with commits (or rollbacks > if errors). I reuse the prepared statements by > rebinding parameters (always changing anyway). My > understanding is you finalize a prepared statement when you > know you will not use it anymore to free up resources. > My application uses the prepared statements until the DB is > shutdown. Is that not proper? Please enlighten > me if this is not what I should be doing. > > You may be okay, as long as you conform to the order in > > <http://www.sqlite.org/c3ref/stmt.html> > > "The life of a statement object goes something like this: > > • Create the object using > sqlite3_prepare_v2() or a related function. > • Bind values to host parameters using > the sqlite3_bind_*() interfaces. > • Run the SQL by calling > sqlite3_step() one or more times. > • Reset the statement using > sqlite3_reset() then go back to step 2. Do this zero or more > times. > • Destroy the object using > sqlite3_finalize()." > > So if you do a _reset() after the last _step() for each > search, which is what you say you do, you should be > okay. Then do a _finalize() before closing the > database handle. > > I still think it should be possible to find out where your > leak is, though I'm not sure how. I would recommend > that you check the result code for every call to a sqlite3_ > function, though, if you're not already doing that. > > Simon. > ___ > 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
Re: [sqlite] Common Multi-treaded Problem
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 wrote: > From: Pavel Ivanov > Subject: Re: [sqlite] Common Multi-treaded Problem > To: "General Discussion of SQLite Database" > 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 > 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 > wrote: > > > >> From: Pavel Ivanov > >> Subject: Re: [sqlite] Common Multi-treaded > Problem > >> To: "General Discussion of SQLite Database" > >> 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 > >> wrote: > >>
Re: [sqlite] Common Multi-treaded Problem
Hello Simon, I actually never finalize prepared statements until the DB is closed and not being used anymore. I reset the prepared statements. For writes (inserts, updates, etc.) I use explicit transactions with commits (or rollbacks if errors). I reuse the prepared statements by rebinding parameters (always changing anyway). My understanding is you finalize a prepared statement when you know you will not use it anymore to free up resources. My application uses the prepared statements until the DB is shutdown. Is that not proper? Please enlighten me if this is not what I should be doing. Thanks, John --- On Thu, 5/12/11, Simon Slavin wrote: > From: Simon Slavin > Subject: Re: [sqlite] Common Multi-treaded Problem > To: "General Discussion of SQLite Database" > Date: Thursday, May 12, 2011, 9:28 PM > > On 13 May 2011, at 1:33am, John Deal wrote: > > > 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). > > I don't think it's meant to work like that. Are you > getting errors when you call _finalize() ? > > <http://www.sqlite.org/c3ref/finalize.html> > > Simon. > ___ > 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
Re: [sqlite] Multi-threading Common Problem
Hello Roger, OK I see your point now. I could most likely remove the OS mutexes. Thanks, John --- On Thu, 5/12/11, Roger Binns wrote: > From: Roger Binns > Subject: Re: [sqlite] Multi-threading Common Problem > To: sqlite-users@sqlite.org > Date: Thursday, May 12, 2011, 4:01 PM > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 05/12/2011 09:38 AM, John Deal wrote: > > I have been working for weeks on this and I feel there > must be something simple I am overlooking. > > Why are you discarding SQLite's builtin and tested mutexes > and then > effectively reimplementing your own to get the same > effect? > > Or bigger picture question what is it you are trying to > achieve in the first > place? > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.11 (GNU/Linux) > > iEYEARECAAYFAk3MPIkACgkQmOOfHg372QQzjgCg3106pWiiUMuOQay+2ONv3G0c > ZvQAnAvBFXI+A8ae8tV9yXRmz7IZgid6 > =jehy > -END PGP SIGNATURE- > ___ > 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
Re: [sqlite] Common Multi-treaded Problem
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 wrote: > From: Pavel Ivanov > Subject: Re: [sqlite] Common Multi-treaded Problem > To: "General Discussion of SQLite Database" > 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 > 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 > wrote: > > > >> From: Igor Tandetnik > >> 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
Re: [sqlite] Common Multi-treaded Problem
Hello again Pavel, OK but I am a bit confused. What I have works as a single instance with one DB connection running under different threads as long as only one is using the DB connection at a time. This is with prepared statements "hanging" (not reset) with different threads using different prepared statements (resetting the ones they use before using them). With what you state this should not work (in this case both reads and writes work). If I understand you correctly, on a single thread using one prepared statement (say a select) then using another prepared statement (say another select) should not work since the 1st prepared statement was not reset. Also what about the time between the statement is executed (sqlite3_step()) and reading the return code and reading the retrieved data? It is my understanding you can't reset the statement until after you have performed these activities. That would imply if what I interpret your statements correctly, that the database is locked until the reset is issued. I appreacate your help. I am just trying to understand what you are trying to tell me. Thanks, John --- On Thu, 5/12/11, Pavel Ivanov wrote: > From: Pavel Ivanov > Subject: Re: [sqlite] Common Multi-treaded Problem > To: "General Discussion of SQLite Database" > 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 > 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 > wrote: > > > >> From: Igor Tandetnik > >> 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
Re: [sqlite] Multi-threading Common Problem
Hello Pavel, This makes sense but I have shared cache on. Thanks. --- On Thu, 5/12/11, Pavel Ivanov wrote: > From: Pavel Ivanov > Subject: Re: [sqlite] Multi-threading Common Problem > To: "General Discussion of SQLite Database" > Date: Thursday, May 12, 2011, 5:55 PM > > "After a BEGIN EXCLUSIVE, no > other database connection except for read_uncommitted > connections will be able to read the database and no other > connection without exception will be able to write the > database until the transaction is complete." > > > > This tells me that reads outside of a transaction > would be permitted while an exclusive transaction is taking > place. > > This works only when shared cache mode is turned on and > only within > the same process. Nothing uncommitted can be read between > processes or > between different connections in the same process when > shared cache is > turned off. > > > Pavel > > > On Thu, May 12, 2011 at 4:26 PM, John Deal > wrote: > > Good question. Very possible my understanding is not > complete. > > > > I have basically read and write transactions, each > potentially with several accesses to the DB. I want to > ensure that if a write transaction is happening, no read > transactions are in progress since it would be possible to > have obtain incomplete data (mixture of some reads being > valid but other no longer valid because the write > transaction changed them). In other words, a read > "transaction" (I do not use a transaction for the reads) > consists of multiple pieces of data that makeup a set that I > want to ensure is valid as a set. > > > > It is my understanding that a transaction (which I do > use for the write transaction which is also a set) locks the > DB for writes but not reads. If a transaction does lock the > DB for exclusive access then you are correct, I do not need > the OS mutexes. Maybe I do not understand the following: > > > > "After a BEGIN EXCLUSIVE, no other database connection > except for read_uncommitted connections will be able to read > the database and no other connection without exception will > be able to write the database until the transaction is > complete." > > > > This tells me that reads outside of a transaction > would be permitted while an exclusive transaction is taking > place. > > > > If a write transaction is not taking place, I want to > allow multiple reads which the OS rwlock allows. > > > > Any enlightenment would be welcomed. > > > > Thanks. > > > > --- On Thu, 5/12/11, Roger Binns > wrote: > > > >> From: Roger Binns > >> Subject: Re: [sqlite] Multi-threading Common > Problem > >> To: sqlite-users@sqlite.org > >> Date: Thursday, May 12, 2011, 4:01 PM > >> -BEGIN PGP SIGNED MESSAGE- > >> Hash: SHA1 > >> > >> On 05/12/2011 09:38 AM, John Deal wrote: > >> > I have been working for weeks on this and I > feel there > >> must be something simple I am overlooking. > >> > >> Why are you discarding SQLite's builtin and tested > mutexes > >> and then > >> effectively reimplementing your own to get the > same > >> effect? > >> > >> Or bigger picture question what is it you are > trying to > >> achieve in the first > >> place? > >> > >> Roger > >> -BEGIN PGP SIGNATURE- > >> Version: GnuPG v1.4.11 (GNU/Linux) > >> > >> > iEYEARECAAYFAk3MPIkACgkQmOOfHg372QQzjgCg3106pWiiUMuOQay+2ONv3G0c > >> ZvQAnAvBFXI+A8ae8tV9yXRmz7IZgid6 > >> =jehy > >> -END PGP SIGNATURE- > >> ___ > >> 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
Re: [sqlite] Multi-threading Common Problem
Good question. Very possible my understanding is not complete. I have basically read and write transactions, each potentially with several accesses to the DB. I want to ensure that if a write transaction is happening, no read transactions are in progress since it would be possible to have obtain incomplete data (mixture of some reads being valid but other no longer valid because the write transaction changed them). In other words, a read "transaction" (I do not use a transaction for the reads) consists of multiple pieces of data that makeup a set that I want to ensure is valid as a set. It is my understanding that a transaction (which I do use for the write transaction which is also a set) locks the DB for writes but not reads. If a transaction does lock the DB for exclusive access then you are correct, I do not need the OS mutexes. Maybe I do not understand the following: "After a BEGIN EXCLUSIVE, no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete." This tells me that reads outside of a transaction would be permitted while an exclusive transaction is taking place. If a write transaction is not taking place, I want to allow multiple reads which the OS rwlock allows. Any enlightenment would be welcomed. Thanks. --- On Thu, 5/12/11, Roger Binns wrote: > From: Roger Binns > Subject: Re: [sqlite] Multi-threading Common Problem > To: sqlite-users@sqlite.org > Date: Thursday, May 12, 2011, 4:01 PM > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 05/12/2011 09:38 AM, John Deal wrote: > > I have been working for weeks on this and I feel there > must be something simple I am overlooking. > > Why are you discarding SQLite's builtin and tested mutexes > and then > effectively reimplementing your own to get the same > effect? > > Or bigger picture question what is it you are trying to > achieve in the first > place? > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.11 (GNU/Linux) > > iEYEARECAAYFAk3MPIkACgkQmOOfHg372QQzjgCg3106pWiiUMuOQay+2ONv3G0c > ZvQAnAvBFXI+A8ae8tV9yXRmz7IZgid6 > =jehy > -END PGP SIGNATURE- > ___ > 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
Re: [sqlite] Common Multi-treaded Problem
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 wrote: > From: Igor Tandetnik > 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] Multi-threading Common Problem
Hello All, I have been using SQLite for a couple of years but have never posted to this list before. I am sure my problem is common and am looking for ideas to solve it. I have used SQLite extensively single-threaded with no problems (other than my own!). I am currently working on another project adding SQLite functionality to a multi-threaded environment. Here is my situation. Ubuntu 10.04 64-bit. Have used the SQLite3 library and compiled from source directly into the application. Same results. Multi-threaded compile flag configuration kept as the default and forced with sqlite3_config() to SQLITE_CONFIG_MULTITHREAD with no error reported. Open is via SQLITE_OPEN_READWRITE | SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE flags. I have tried this with and without shared cache. When I use the above configuration multi-threaded but using only one DB connection in which only one thread is accessing the DB at a time, it works fine for both read and write. I use OS read/write mutex in which all threads must obtain a write lock to get at the DB effectively forcing only one thread accessing the DB at one time. 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. The DB is locked, not the OS mutex. There are no DB writes. How can the DB be locked for writes in this situation? I test this with the sqlite3 program and opening the database while the application is running and try to do an insert. I have been working for weeks on this and I feel there must be something simple I am overlooking. Thanks for any help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Hello All, I have been using SQLite for a couple of years but have never posted to this list before. I am sure my problem is common and am looking for ideas to solve it. I have used SQLite extensively single-threaded with no problems (other than my own!). I am currently working on another project adding SQLite functionality to a multi-threaded environment. Here is my situation. Ubuntu 10.04 64-bit. Have used the SQLite3 library and compiled from source directly into the application. Same results. Multi-threaded compile flag configuration kept as the default and forced with sqlite3_config() to SQLITE_CONFIG_MULTITHREAD with no error reported. Open is via SQLITE_OPEN_READWRITE | SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE flags. I have tried this with and without shared cache. When I use the above configuration multi-threaded but using only one DB connection in which only one thread is accessing the DB at a time, it works fine for both read and write. I use OS read/write mutex in which all threads must obtain a write lock to get at the DB effectively forcing only one thread accessing the DB at one time. 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. The DB is locked, not the OS mutex. There are no DB writes. How can the DB be locked for writes in this situation? I test this with the sqlite3 program and opening the database while the application is running and try to do an insert. I have been working for weeks on this and I feel there must be something simple I am overlooking. Thanks for any help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Common Multi-treaded Problem
Hello All, I have been using SQLite for a couple of years but have never posted to this list before. I am sure my problem is common and am looking for ideas to solve it. I have basically the same situation as this thread: --- On Wed, Jan 26, 2011 at 10:56 AM, Ian Hardingham wrote: > Hey guys. > > I am under the impression that there is no concurrent access to a single > SQLite DB. Ie if thread A is performing a query, and thread B trys to > query, it will block until thread A is finished, no matter the query. > > 1. Is this correct? > It is true if A and B are attempting to share the same database connection. Access to the database connection is protected by a mutex. > > 2. Are there any fairly general workarounds of any kind? > Use a separate database connection for each thread. Or better: Use processes instead of threads, as threads are evil. > > Thanks, > Ian I have used SQLite extensively single-threaded with no problems (other than my own!). I am currently working on another project adding SQLite functionality to a multi-threaded environment. Here is my situation. Ubuntu 10.04 64-bit. Have used the SQLite3 library and compiled from source directly into the applicaition. Same results. Multi-threaded compile flag configuration kept as the default and forced with sqlite3_config() to SQLITE_CONFIG_MULTITHREAD with no error reported. Open is via SQLITE_OPEN_READWRITE | SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE flags. I have tried this with and without shared cache. When I use the above configuration multi-threaded but using only one DB connection in which only one thread is accessing the DB at a time, it works fine for both read and write. I use OS read/write mutex in which all threads must obtain a write lock to get at the DB effectively forcing only one thread accessing the DB at one time. 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. The DB is locked, not the OS mutex. There are no DB writes. How can the DB be locked for writes in this situation? I test this with the sqlite3 program and opening the database while the application is running and try to do an insert. I have been working for weeks on this and I feel there must be something simple I am overlooking. Thanks for any help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users