in reply to Re^2: DBIx::Class and Parallel SQLite Transactions
in thread DBIx::Class and Parallel SQLite Transactions
DBIx::Class is easily fork/threadsafe, as it is pure Perl. But its README says
... [ DBIx::Class ] is fork- and thread-safe out of the box (although your DBD may not be).
... and I don't expect DBD::SQLite to be fork and/or threadsafe. Likely, the SQLite documentation and/or DBD::SQLite documentation even say so.
|
|---|
| Replies are listed 'Best First'. | |
|---|---|
|
Re^4: DBIx::Class and Parallel SQLite Transactions
by moritz (Cardinal) on Jul 14, 2011 at 13:38 UTC | |
| [reply] |
|
Re^4: DBIx::Class and Parallel SQLite Transactions
by learnedbyerror (Monk) on Jul 14, 2011 at 13:30 UTC | |
I'm certainly no expert here. My belief that DBIx::Class with SQLite is threadsafe is based upon not just the CPAN documentation that you referenced but also several threads that I have read from the DBIx::Class developers. This thread from GrokBase seems to be the most complete, at least that I remember, : [Dbix-class ] "fork- and thread-safe" Things certainly seem to behave properly when reading from SQLite; however, I can't say the same for writing to SQLite. But as I previously mentioned, this appears to be an issue with how the lock state of the database is handled. I haven't tested this across separate autonomous processes, though while typing this note I thought of a way I can probably test this without too much work. I'll give it a try this evening when I have a little time. | [reply] |
by Marshall (Canon) on Jul 14, 2011 at 16:20 UTC | |
But as I previously mentioned, this appears to be an issue with how the lock state of the database is handled. The write performance of SQLite will be far less than say MySQL. SQLite needs to gain an exclusive lock on the entire DB file in order to do a write. There is no table level or row level locking. So I figure that having multiple writers will bring nothing but trouble to you. Since in a transaction your rows are exclusive (don't overlap with other transactions), then a DB that can do row level locking could yield a lot higher performance. I am working on a SQLite project and have found the O'Reilly book, "Using SQLite" by Jay A. Kreibich to be helpful. It is mainly oriented around the C interface, but there is plenty of great info for the Perl users too. There are discussions about how and why the DB can be busy - it is locking related. Update: Oh, I'm not sure how well the DBI does with threads. The implementation may be "safe", but not high performance, i.e. it may just wind up serializing things. With MySQL, you may get higher performance with a process per writer instead of a thread. Benchmarks will tell. | [reply] |
by learnedbyerror (Monk) on Jul 14, 2011 at 20:30 UTC | |
I'm well into Kreibich's book already. While it is a little dated and focused on C, there IS a lot of good stuff in it when it comes to how SQLite works. Insofar as the locking is concerned, if I read Kreibich correctly, the exclusive lock is not requested until the commit is initiated. So other processes can write to the SQLite cache at the same time, but only one can commit at a time. Am I reading this wrong? Sounds like my reading topic for the bus ride home this afternoon :) Insofar as whether there is an advantage for multiple threads is concerned, my application does a lot of inserts the first time through and afterwards updates most if not all of the same records. So my thoughts are that since there is a fair amounts of reads to identify whether to update or insert that there would be advantages to some degree of parallelization, maybe two threads versus just one. This spurs my thought of having a two thread write procedure. The first thread would identify what records are updates and which are insert. This would be queued for the second process so that it is writing continuously. I'll have to give this a test also. Thanks! lbe | [reply] |
by Marshall (Canon) on Jul 14, 2011 at 21:57 UTC | |
by Corion (Patriarch) on Jul 14, 2011 at 13:41 UTC | |
I see no mention of threads and forking in the DBD::SQLite documentation. Grepping the source also lists no mention of threads in the SQLite.xs file, but only within sqlite3.c itself. This indicates to me that little thought has been given to how to make DBD::SQLite and threads/fork play nice together. You can maybe check whether the problem is inherent to fork() and/or threads by launching multiple writes as real, separate processes via exec resp. system instead of fork/threads. If you still experience the segfaults/crashes, the problem likely is with your version of DBD::SQLite or some other XS code loaded in the separate process. If the problem goes away, then the problem likely is related to fork/threads and I see no other way than to change to a different DBD if you want to keep going with fork/threads. You haven't told us your OS so far, but if you are on Windows, threads and fork() are basically the same thing there anyway, and all problems with one system are present in the other as well. | [reply] [d/l] [select] |
by learnedbyerror (Monk) on Jul 14, 2011 at 14:38 UTC | |
I'll take a shot a testing this evening. Sorry for forgetting the obvious. My primary environment is currently Fedora 13
uname -a 2.6.18-194.26.1.el5.028stab079.2 #1 SMP Fri Dec 17 19:25:15 MSK 2010 x86_64 x86_64 x86_64 GNU/Linux. My perl version info is:
| [reply] |