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 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 rog...@rogerbinns.com wrote: From: Roger Binns rog...@rogerbinns.com Subject: Re: [sqlite] Multi-threading Common Problem To: General Discussion of SQLite Database sqlite-users@sqlite.org 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
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
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 bassd...@yahoo.com 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 rog...@rogerbinns.com wrote: From: Roger Binns rog...@rogerbinns.com Subject: Re: [sqlite] Multi-threading Common Problem To: General Discussion of SQLite Database sqlite-users@sqlite.org 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
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 paiva...@gmail.com wrote: From: Pavel Ivanov paiva...@gmail.com Subject: Re: [sqlite] Multi-threading Common Problem To: General Discussion of SQLite Database sqlite-users@sqlite.org 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 bassd...@yahoo.com 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 rog...@rogerbinns.com wrote: From: Roger Binns rog...@rogerbinns.com Subject: Re: [sqlite] Multi-threading Common Problem To: General Discussion of SQLite Database sqlite-users@sqlite.org 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 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 bassd...@yahoo.com 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 paiva...@gmail.com wrote: From: Pavel Ivanov paiva...@gmail.com Subject: Re: [sqlite] Multi-threading Common Problem To: General Discussion of SQLite Database sqlite-users@sqlite.org 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 bassd...@yahoo.com 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 rog...@rogerbinns.com wrote: From: Roger Binns rog...@rogerbinns.com Subject: Re: [sqlite] Multi-threading Common Problem To: General Discussion of SQLite Database sqlite-users@sqlite.org 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 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 paiva...@gmail.com wrote: From: Pavel Ivanov paiva...@gmail.com Subject: Re: [sqlite] Multi-threading Common Problem To: General Discussion of SQLite Database sqlite-users@sqlite.org 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 bassd...@yahoo.com 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 paiva...@gmail.com wrote: From: Pavel Ivanov paiva...@gmail.com Subject: Re: [sqlite] Multi-threading Common Problem To: General Discussion of SQLite Database sqlite-users@sqlite.org 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 bassd...@yahoo.com 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 rog...@rogerbinns.com wrote: From: Roger Binns rog...@rogerbinns.com Subject: Re: [sqlite] Multi-threading Common Problem To: General Discussion of SQLite Database sqlite-users@sqlite.org 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
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 rog...@rogerbinns.com wrote: From: Roger Binns rog...@rogerbinns.com 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
[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
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
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 rog...@rogerbinns.com wrote: From: Roger Binns rog...@rogerbinns.com 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
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 bassd...@yahoo.com 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 rog...@rogerbinns.com wrote: From: Roger Binns rog...@rogerbinns.com 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
Hello Pavel, This makes sense but I have shared cache on. Thanks. --- On Thu, 5/12/11, Pavel Ivanov paiva...@gmail.com wrote: From: Pavel Ivanov paiva...@gmail.com Subject: Re: [sqlite] Multi-threading Common Problem To: General Discussion of SQLite Database sqlite-users@sqlite.org 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 bassd...@yahoo.com 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 rog...@rogerbinns.com wrote: From: Roger Binns rog...@rogerbinns.com 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
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 rog...@rogerbinns.com wrote: From: Roger Binns rog...@rogerbinns.com 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
