Re: DBIx::Class and Parallel SQLite Transactions

My visceral reaction to this approach is that, since there is one resource being accessed here (and, it is being accessed “as a regular file” by software that is executing in the context of each process, then there is not likely to be any performance advantage in asking multiple threads to do the work. The process is fundamentally I/O-bound, and the database file is a shared resource. Realistically speaking, the operations will be serialized. Thus, the advantages of multi-threading are voided.

Now, when you do use SQLite, remember that it is imperative to do things within transactions, even in a single thread. Otherwise, SQLite will (quite by design...) verify each and every write, each and every time.

Replies are listed 'Best First'.
Re^2: DBIx::Class and Parallel SQLite Transactions by learnedbyerror (Monk) on Jul 18, 2011 at 11:42 UTC
I would agree with your statement if there were only insert or update calls being made during transaction; however, there are additional cycles spent and database calls made to determine whether updates or inserts are needed. Also added to this is that the transaction contains one initial operation to set an active flag inactive for all affected rows, followed by the update/insert action. So there are opportunities to for parallel execution, at least in theory After mulling it over, I am currently deciding to stick with a single writer; however, I am going to refactor the update procedure to utilize a temporary table. My initial plan looks something like: Create database transaction semaphore Create update thread Create temporary table with the same structure as the destination table Copy rows of interest from the destination table to the temporary table Perform all operations on the temporary table where there will be no concurrency issues Grab database transaction semaphore Update destination table from temporary table Release database transaction semaphore Flush data in temporary table Start over for next set of data I believe that this approach, while more complex than my current approach, will squeeze most of if not all of the non update/insert specific processing cycles out of the actual transaction where concurrency is an issue while insuring that multiple writers will not be attempting write transaction at the same time I recognize that this essentially duplicates the locking performed by SQLite; however, I cannot figure out how to do to keep DBIC from either throwing an exception or to handle the exception properly because DBIC's exception returns a null error message in $@. Additionally, I am having problems identifying how to create the temporary table using DBIC. I have tried to build on some work documented in some early threads in here the monastery but realized little success with it yet. I'll start a new thread on that topic later today and will update this thread with its link Thanks to all of the Monks who have replied with input!	[reply]

Replies are listed 'Best First'.

Re^2: DBIx::Class and Parallel SQLite Transactions
by learnedbyerror (Monk) on Jul 18, 2011 at 11:42 UTC

I would agree with your statement if there were only insert or update calls being made during transaction; however, there are additional cycles spent and database calls made to determine whether updates or inserts are needed. Also added to this is that the transaction contains one initial operation to set an active flag inactive for all affected rows, followed by the update/insert action. So there are opportunities to for parallel execution, at least in theory

After mulling it over, I am currently deciding to stick with a single writer; however, I am going to refactor the update procedure to utilize a temporary table. My initial plan looks something like:

Create database transaction semaphore
Create update thread
Create temporary table with the same structure as the destination table
Copy rows of interest from the destination table to the temporary table
Perform all operations on the temporary table where there will be no concurrency issues
Grab database transaction semaphore
Update destination table from temporary table
Release database transaction semaphore
Flush data in temporary table
Start over for next set of data

I believe that this approach, while more complex than my current approach, will squeeze most of if not all of the non update/insert specific processing cycles out of the actual transaction where concurrency is an issue while insuring that multiple writers will not be attempting write transaction at the same time

I recognize that this essentially duplicates the locking performed by SQLite; however, I cannot figure out how to do to keep DBIC from either throwing an exception or to handle the exception properly because DBIC's exception returns a null error message in $@.

Additionally, I am having problems identifying how to create the temporary table using DBIC. I have tried to build on some work documented in some early threads in here the monastery but realized little success with it yet. I'll start a new thread on that topic later today and will update this thread with its link

Thanks to all of the Monks who have replied with input!

[reply]