Limitation of SQLite

[samseaver]

Even though most of us may be running commands that take 5 minutes or less, and thus are not easily affected by this limitation, I am effectively locked out from doing any more work when I'm running a DB import that takes 9 hours. The limitation in question is that SQLite doesn't seem to allow multiple transactions to occur at the same time.

This means that when one process is running, and if I try to run another process (in this example, while running mdl-importmodel, I tried to run mdl-loadbiomass) I get this:

[PPOBackend.pm 447]Transaction aborted: DBD::SQLite::db commit failed: database is locked

[devoid]

No database allows multiple transactions to occur at the same time ( http://en.wikipedia.org/wiki/Database_transaction ). The issue, though is that mdl-importmodel seems to request a transaction for the entire length of the import process; I don't know why or where it does that. As for the 9-hour import procedure, I'm guessing this runs much faster with MySQL? SQLite is limited to ~10 transactions / second. If each "add reaction", "add alias", etc. is within an individual transaction, that's gonna be REALLY slow. Put all of them in a single transaction and SQLite can do 10,000+ inserts / second. Obviously we don't have the capability to do this now with PPO, but we are targeting this capability for the new ORM.

~ Scott

On Wed, Nov 16, 2011 at 11:57 AM, samseaver < reply@reply.github.com

wrote:

Even though most of us may be running commands that take 5 minutes or less, and thus are not easily affected by this limitation, I am effectively locked out from doing any more work when I'm running a DB import that takes 9 hours. The limitation in question is that SQLite doesn't seem to allow multiple transactions to occur at the same time.

This means that when one process is running, and if I try to run another process (in this example, while running mdl-importmodel, I tried to run mdl-loadbiomass) I get this:

[PPOBackend.pm 447]Transaction aborted: DBD::SQLite::db commit failed: database is locked

---

Reply to this email directly or view it on GitHub: https://github.com/ModelSEED/Model-SEED-core/issues/100

[samseaver]

Well, I'm not sure if individual tables are locked, or if the entire database is locked, but if the locking occurs when an object is created, then the model object, which is first created and the last to be used at the end of the function could be why a single transaction is created for the duration of which the model object is 'active' in the code.

Otherwise, the vast majority of the transactions occur with the biochemistry flat files via the FIGMODELTable object (KEGG processes 13K compounds), but when processing reactions, they are added to the 'reaction_model' table in the SQLite database, though I don't know if they do this as individual transactions or not.

[cshenry]

The 9 hour import is due to the flat files. I actually think we could massively speed the imports if we switched the provenance databases to use sqllite instead of flat files. I'm not sure it would require much work either. I will look into this. If we can resolve the 9 hour import problem, it may reduce the pain caused by the limitations of sqllite.

[samseaver]

As an update to this issue, here's what I found out. I was trying to use the convert_to_search_name function in FIGMODELcompound to convert some names to searchnames independently, so I did:

my $gencpd=ModelSEED::FIGMODEL->new()->get_compound();

If I do this, I get a Database locked error if I try to alter anything in the compound_alias table.

Furthermore, if I simply do:

my $fig=ModelSEED::FIGMODEL->new();

I get the same error.

If I just randomnly update something in the compound_alias table without calling either of these two lines of code, it works, otherwise, it tells me the database is locked.

[samseaver]

I figured out a work-around for the previous global lock, for the use of the convert_to_search_name function anyway. This only works if the actual figmodel and compound objects are irrelevant.

`my $confunc=&ModelSEED::FIGMODEL::FIGMODELcompound::convert_to_search_name; my $fake_self;

foreach my $n (@names){ my @sn=&$confunc($fake_self,$n); }`