On 10 February 2010 19:25, Steffen Mueller <[email protected]> wrote: > I sense danger. Let me get a few bits straight: > > 1) Can threads other than the main thread access the DB? I think yes. > Barring them from using the configuration would be a problem.
The configuration should already be loaded (I hope) at the time of the first thread spawn, because you need the configuration to work out if the user wants to do threading at all. There is certainly the issue that changes in the main thread configuration object won't be reflected in the configuration in the threads, but we have that problem already. Assuming there are no active connections to the database at the time of the thread spawn, I don't know if DBI/etc are "safe enough" to support different connections from the main and background threads. > => Therefore, your point 4. isn't enough. It's asking for strange race > conditions. > > 2) Can ORLite have multiple connections to the same DB? > => I guess not. No. In fact, it's very important that the same Perl interpreter never has more than one connection. If the two connections are doing statements with AutoCommit turned on, you might as well just use one connection as it will be more efficient. If either of the connections does a transaction, you end up holding an exclusive lock on the file, which means that the OTHER connection will block waiting. Which means instant interpreter deadlock and either Padre or a background thread hangs forever (as will anything else with a database connection). Part of the reason I wrote an entire ORM specifically for SQLite was to help protect against (and take advantage of) these idiosyncracies for SQLite. ORLite is designed to make sure the interpreter can only ever have one connection, and to make use of that one connection to best effect. > 3) Can SQLite handle multiple connections (i.e. from different > processes) to the same DB? > => I guess yes. Yes, but as stated they must not be from the same interpreter. > 4) Can DBD::SQLite handle multiple connections from the same process to > the same DB? > => No idea. Yes it can. Whether or not we can do it from different threads is a different question entirely. > I was thinking that maybe each thread should/could handle its own DB > connection. ORLite is pure Perl, so by just spawning off a new ithread, > it should be safe unless it holds a db handle. And therein lies the problem. It does hold a database connection. > The db handle itself is > an opaque XS object (DBI, I presume). Now, let me go on a wild rampage: > If we create a class that trivially inherits from DBI and use that class > in place of DBI everywhere. Then we can use XS to add a thread cloning > hook to our new class. When a new thread is spawned, this hook simply > creates a new DB connection for the new thread -- an entirely new DBI > handle. > Now, there are a few drawbacks to this plan: > a) It's a lot of work and potentially fragile. > b) DBI docs IIRC say something like "should be fine in threads, but > having handles to the same DB in different threads might be weird", i.e. > warn about this scenario specifically. > c) DBD's are usually not thread-safe. This could manifest in that they > use some C-level storage for the db handles. This in turn would make it > impossible to have separate connections to the same per thread. I have > no idea about DBD::SQLite, but if in doubt, it's probably not up to par > with the likes of DBD::Pg/mysql/Oracle. I'm confident that we will NEVER need more than one connection per-thread to the same database. I'm also sure we can avoid the need to carry a connection across the thread spawning boundary using only changes in Perl at the higher ORLite+Padre::Locker level. So the question then is, if there are no active connections can the driver itself survive the thread boundary without going corrupt, and could the presence of two connections in two different threads go corrupt. > Okay, so let's consider this a thought experiment for now and move on. > Back to Adam' suggestion > > One could make this thread-safe-ish by using a global semaphore > (Thread::Semaphore or whatever) in the Locking system to synchronize all > interaction with the database. This may be a problem for applications > that hit the DB a lot, but feels safe for us. If we can't run two connections in two different threads, and we MUST have access to the database in a background thread, then the semaphore should be sufficient to prevent that. > Suppose I want to access the config DB. What's the way this works? Is it: > > a) I get a lock on it > b) I use the DB > c) lock goes out of scope > d) other bits of code could use the DB? > > If so, we're in luck. Just add the afortmentioned semaphore to the DB > locking. AND, of course, we need to implement Adam's plan. > The rest, I agree. Adam K _______________________________________________ Padre-dev mailing list [email protected] http://mail.perlide.org/mailman/listinfo/padre-dev
