Re: [sqlite] SQLite and Threadsafety (again)
I'm a bit curious why it wouldn't work. I use the same approach right here to have a exclusive access to a database table in the same model I told you. Also, I never used other locking mode that exclusive, because if want to write to the database the write lock should be granted exclusively by a single thread (similar to pthreads multiple reader single writer lock scheme). Also, DRH told in other answer in this thread the problem of using only one connection shared with many threads: you cannot guarantee that last_insert_rowid() is correct, because someone other can started a transaction and write a record to any table, modifying the last_insert_rowid() result, which lead you to produce bugs if you depend on this feature. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Shawn Wilsher Sent: quarta-feira, 21 de maio de 2008 14:15 To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite and Threadsafety (again) The problem with the approach you suggest as that that does not work when PRAGMA locking_mode = EXCLUSIVE, which we use in many places to improve performance. Additionally, it's my understanding that when using the shared cache, that transactions are grouped across connection objects (it's possible I misread a past e-mail though). We use the shared cache for every database connection (although, I've been wondering as of late if it's really worthwhile). It is unfortunate that we'll lose the ability to do multiple reads at the same time, however. I'm open to suggestions on a better way to fix this problem. Cheers, Shawn On Wed, May 21, 2008 at 1:05 PM, Virgilio Alexandre Fornazin <[EMAIL PROTECTED]> wrote: > Not to putting flame in question, but why not use any connection per thread > ? At this way you can guarantee: > > - Correct transaction processing; > - Avoid waiting on R/W locks, allowing more than one read to run > concurrently; > > We also use this model with ODBC / ADO database layers. > > You don't need to take care if your database drivers provides thread safety, > handle multiple active result sets > (client-side cursors), last insert row id concurrency, etc. > > We tried to use a single connection per process, but after changed to one > connection per thread model, the > gains we got avoiding synchronization was bigger than we imaginated. > > To get this changes working best, we created a database connection pool, > that we use to get the connections by their > ID´s (yes, it´s a key-value dictionary). The pool also 'recycle' connections > that will not be used by any thread to > avoid resource leaking. > > Is this case, assuming that the unique ID of the database is the file name > (SQLite database file name), you can get > this behaviour to work transparently for your consumers (I assume you´re not > using directly the sqlite3_* calls inside > your program, you have some kind of high-level abstraction to use them). > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Shawn Wilsher > Sent: quarta-feira, 21 de maio de 2008 13:08 > To: General Discussion of SQLite Database > Subject: [sqlite] SQLite and Threadsafety (again) > > Hey all, > > I've come to the sad realization that we need to make our sqlite > wrapper threadsafe so it can be used on multiple threads without > consumers having to worry about threadsafety themselves. So, I wanted > to make sure all my assumptions about sqlite data structures are > correct so I don't introduce issues before undertaking this task. > > First, I know that the sqlite3 object can be accessed on multiple > threads, but it must only be used by one thread of control at a time. > It is also my understanding that this same constraint applies to > sqlite3_stmt objects - they can only be used by one thread of control > at a time but accessed on multiple ones. What I am not so sure about, > however, is if I have to protect the sqlite3 object that "owns" the > statement when I'm calling methods on it such as sqlite3_bind_* > interfaces, sqlite3_step, sqlite3_reset, and sqlite3_finalize. > Conservatively, I'm assuming yes on all of the above, but I hope I'm > wrong for at least some of those. I would, however, expect to have to > protect the sqlite3 object when calling sqlite3_prepare_v2. > > Clarification on this would be greatly appreciated. > > Cheers, > > Shawn Wilsher > Mozilla Developer > ___ > 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/sql
Re: [sqlite] SQLite and Threadsafety (again)
On May 21, 2008, at 12:08 PM, Shawn Wilsher wrote: > Hey all, > > I've come to the sad realization that we need to make our sqlite > wrapper threadsafe so it can be used on multiple threads without > consumers having to worry about threadsafety themselves. So, I wanted > to make sure all my assumptions about sqlite data structures are > correct so I don't introduce issues before undertaking this task. > > First, I know that the sqlite3 object can be accessed on multiple > threads, but it must only be used by one thread of control at a time. Beginning with version 3.5.0, SQLite enforces this itself using its own internal mutexes, so the application is free to (try to) use the same database connection from multiple threads at the same time. > > It is also my understanding that this same constraint applies to > sqlite3_stmt objects - they can only be used by one thread of control > at a time but accessed on multiple ones. What I am not so sure about, > however, is if I have to protect the sqlite3 object that "owns" the > statement when I'm calling methods on it such as sqlite3_bind_* > interfaces, sqlite3_step, sqlite3_reset, and sqlite3_finalize. > Conservatively, I'm assuming yes on all of the above, but I hope I'm > wrong for at least some of those. I would, however, expect to have to > protect the sqlite3 object when calling sqlite3_prepare_v2. > > Clarification on this would be greatly appreciated. SQLite takes care of the serialization for you now. There are a few routines that might not be purely threadsafe. For example, sqlite3_last_insert_rowid(). All this does it return a 64- bit integer out of a structure. But if a 64-bit integer load is not an atomic operation on your platform and a different thread happens to be updating the last_insert_rowid at the same time you are reading it, you might get back a nonsense value. But on the other hand, if you have one thread trying to find the last_insert_rowid while another thread is busy changes the last_insert_rowid, then you already have bigger problems. There are a few similar cases. Of course, all this mutexing comes at a cost in performance. When version 3.6.0 comes out, it might support a "low-mutex" mode as a compile-time option where it is the applications responsibility to serialize access to separate database connections again, just like in 3.4.2. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and Threadsafety (again)
The problem with the approach you suggest as that that does not work when PRAGMA locking_mode = EXCLUSIVE, which we use in many places to improve performance. Additionally, it's my understanding that when using the shared cache, that transactions are grouped across connection objects (it's possible I misread a past e-mail though). We use the shared cache for every database connection (although, I've been wondering as of late if it's really worthwhile). It is unfortunate that we'll lose the ability to do multiple reads at the same time, however. I'm open to suggestions on a better way to fix this problem. Cheers, Shawn On Wed, May 21, 2008 at 1:05 PM, Virgilio Alexandre Fornazin <[EMAIL PROTECTED]> wrote: > Not to putting flame in question, but why not use any connection per thread > ? At this way you can guarantee: > > - Correct transaction processing; > - Avoid waiting on R/W locks, allowing more than one read to run > concurrently; > > We also use this model with ODBC / ADO database layers. > > You don't need to take care if your database drivers provides thread safety, > handle multiple active result sets > (client-side cursors), last insert row id concurrency, etc. > > We tried to use a single connection per process, but after changed to one > connection per thread model, the > gains we got avoiding synchronization was bigger than we imaginated. > > To get this changes working best, we created a database connection pool, > that we use to get the connections by their > ID´s (yes, it´s a key-value dictionary). The pool also 'recycle' connections > that will not be used by any thread to > avoid resource leaking. > > Is this case, assuming that the unique ID of the database is the file name > (SQLite database file name), you can get > this behaviour to work transparently for your consumers (I assume you´re not > using directly the sqlite3_* calls inside > your program, you have some kind of high-level abstraction to use them). > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Shawn Wilsher > Sent: quarta-feira, 21 de maio de 2008 13:08 > To: General Discussion of SQLite Database > Subject: [sqlite] SQLite and Threadsafety (again) > > Hey all, > > I've come to the sad realization that we need to make our sqlite > wrapper threadsafe so it can be used on multiple threads without > consumers having to worry about threadsafety themselves. So, I wanted > to make sure all my assumptions about sqlite data structures are > correct so I don't introduce issues before undertaking this task. > > First, I know that the sqlite3 object can be accessed on multiple > threads, but it must only be used by one thread of control at a time. > It is also my understanding that this same constraint applies to > sqlite3_stmt objects - they can only be used by one thread of control > at a time but accessed on multiple ones. What I am not so sure about, > however, is if I have to protect the sqlite3 object that "owns" the > statement when I'm calling methods on it such as sqlite3_bind_* > interfaces, sqlite3_step, sqlite3_reset, and sqlite3_finalize. > Conservatively, I'm assuming yes on all of the above, but I hope I'm > wrong for at least some of those. I would, however, expect to have to > protect the sqlite3 object when calling sqlite3_prepare_v2. > > Clarification on this would be greatly appreciated. > > Cheers, > > Shawn Wilsher > Mozilla Developer > ___ > 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] SQLite and Threadsafety (again)
Not to putting flame in question, but why not use any connection per thread ? At this way you can guarantee: - Correct transaction processing; - Avoid waiting on R/W locks, allowing more than one read to run concurrently; We also use this model with ODBC / ADO database layers. You don't need to take care if your database drivers provides thread safety, handle multiple active result sets (client-side cursors), last insert row id concurrency, etc. We tried to use a single connection per process, but after changed to one connection per thread model, the gains we got avoiding synchronization was bigger than we imaginated. To get this changes working best, we created a database connection pool, that we use to get the connections by their ID´s (yes, it´s a key-value dictionary). The pool also 'recycle' connections that will not be used by any thread to avoid resource leaking. Is this case, assuming that the unique ID of the database is the file name (SQLite database file name), you can get this behaviour to work transparently for your consumers (I assume you´re not using directly the sqlite3_* calls inside your program, you have some kind of high-level abstraction to use them). -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Shawn Wilsher Sent: quarta-feira, 21 de maio de 2008 13:08 To: General Discussion of SQLite Database Subject: [sqlite] SQLite and Threadsafety (again) Hey all, I've come to the sad realization that we need to make our sqlite wrapper threadsafe so it can be used on multiple threads without consumers having to worry about threadsafety themselves. So, I wanted to make sure all my assumptions about sqlite data structures are correct so I don't introduce issues before undertaking this task. First, I know that the sqlite3 object can be accessed on multiple threads, but it must only be used by one thread of control at a time. It is also my understanding that this same constraint applies to sqlite3_stmt objects - they can only be used by one thread of control at a time but accessed on multiple ones. What I am not so sure about, however, is if I have to protect the sqlite3 object that "owns" the statement when I'm calling methods on it such as sqlite3_bind_* interfaces, sqlite3_step, sqlite3_reset, and sqlite3_finalize. Conservatively, I'm assuming yes on all of the above, but I hope I'm wrong for at least some of those. I would, however, expect to have to protect the sqlite3 object when calling sqlite3_prepare_v2. Clarification on this would be greatly appreciated. Cheers, Shawn Wilsher Mozilla Developer ___ 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] SQLite and Threadsafety (again)
Hey all, I've come to the sad realization that we need to make our sqlite wrapper threadsafe so it can be used on multiple threads without consumers having to worry about threadsafety themselves. So, I wanted to make sure all my assumptions about sqlite data structures are correct so I don't introduce issues before undertaking this task. First, I know that the sqlite3 object can be accessed on multiple threads, but it must only be used by one thread of control at a time. It is also my understanding that this same constraint applies to sqlite3_stmt objects - they can only be used by one thread of control at a time but accessed on multiple ones. What I am not so sure about, however, is if I have to protect the sqlite3 object that "owns" the statement when I'm calling methods on it such as sqlite3_bind_* interfaces, sqlite3_step, sqlite3_reset, and sqlite3_finalize. Conservatively, I'm assuming yes on all of the above, but I hope I'm wrong for at least some of those. I would, however, expect to have to protect the sqlite3 object when calling sqlite3_prepare_v2. Clarification on this would be greatly appreciated. Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
