Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

DBD::SqLite issue while accessing DB from multiple processes

by zuverlassig (Novice)
on Feb 04, 2014 at 20:03 UTC ( #1073462=perlquestion: print w/replies, xml ) Need Help??

zuverlassig has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks, I have a code in which I am trying to access SqLite DB file from multiple processes. Here is my code for one of such processes :

my $driver = "SQLite"; my $database = "some.db"; my $dsn = "DBI:$driver:dbname=$database"; my $userid = ""; my $password = ""; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1, sq +lite_use_exclusive_transaction => 1 }) or die $DBI::errstr; #some drop statements my $dropstmt = qq(DROP TABLE IF EXISTS $db_name); my $rv = $dbh->do($dropstmt); #some create statements my $createstmt = (something); $rv = $dbh->do($createstmt); #some insert statements $insertstmt=qq(some insert statement); $rvw = $dbh->do($insertstmt) or die $DBI::errstr; #some update statments my $sth = $dbh->prepare(some updatestatement); $sth->execute() or die $DBI::errstr;

I am getting these error messages: DBD::SQLite::db do failed: database schema has changed(17) at dbdimp.c line 403 at childprocess.pl line 88.

DBD::SQLite::db do failed: database is locked(5) at dbdimp.c line 403 at childprocess.pl line 121.

DBD::SQLite::db do failed: database is locked(5) at dbdimp.c line 403 at childprocess.pl line 224.

Where line 88 is the drop statement, and line 121 is the insert statement and 224 is the update statement Note that there is no commit statement here. Please help me with this.

Replies are listed 'Best First'.
Re: DBD::SqLite issue while accessing DB from multiple processes
by mje (Curate) on Feb 05, 2014 at 09:17 UTC

    I am successfully using DBD::SQLite from 9 concurrent processes with sqlite_use_immediate_transaction set to true. However, although I'm doing inserts and updates I am NOT actually changing the schema (dropping tables). I'm not at all surprised if SQLite cannot cope with tables being dropped and recreated when a connection is already open.

Re: DBD::SqLite issue while accessing DB from multiple processes
by Anonymous Monk on Feb 04, 2014 at 20:53 UTC
    A Google search of sqlite multiple processes provides a variety of (not Perl-specific) cautions and suggestions about using multiple processes with SQLite. I cordially suggest that you review these. SQLite's architecture does indeed support concurrency, but since there is no server, it is a mixed bag. Yours is not a Perl-specific issue by any means.
Re: DBD::SqLite issue while accessing DB from multiple processes
by dsheroh (Monsignor) on Feb 05, 2014 at 08:51 UTC
    SQLite isn't really designed to handle concurrency. If you're dead-set on accessing an SQLite database from multiple processes, then you're going to need to write the code to detect and handle errors of this type for yourself.

    The saner approach would be to switch to a database engine which is designed to handle concurrency, such as PostgreSQL, MySQL, etc. It would be much easier, plus it would provide a solution which is vastly more robust than any kind of homebrew concurrency handling is likely to be.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1073462]
Approved by boftx
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (4)
As of 2022-12-05 08:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?