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
> 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: 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 >
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
> 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
Re: [sqlite] Multi-threading Common Problem
On 24 May 2011, at 12:43pm, John Deal wrote: > Sorry to be so brain-dead but I am still confused. I have multiple threads, > each with their own DB connection. Read http://www.sqlite.org/threadsafe.html If it's not clear to you please ask specific questions about what's on that page, since your questions will help us to work out how to improve it. > 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). The documentation on how to handle this is a little lacking, but if your app has no principles of its own, just loop until the problem goes away. > 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? You use transactions correctly. Pay especial attention to the differences between BEGIN BEGIN IMMEDIATE BEGIN EXCLUSIVE BEGIN DEFERRED See the following page http://www.sqlite.org/lang_transaction.html However, you may have done something to defeat SQLite's built in mechanism. Have you done any of the following: Have you set any of SQLite's compiler directives in your compilation ? I'm thinking especially of SQLITE_THREADSAFE. I'm not completely familiar with SQLite's model, but it seems to me as if you should be using '-DSQLITE_THREADSAFE=2'. The explanation of this is on the first web page I pointed to above. Have you set any PRAGMAs which are related to threading ? Especially 'PRAGMA temp_store_directory' and 'PRAGMA read_uncommitted' ? If so, try removing your PRAGMAs and see if this fixes your problem. Simon. ___ 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
-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
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] Multi-threading Common Problem
-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
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] 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
> "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
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] Multi-threading Common Problem
-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
