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
