DBD::SqLite issue while accessing DB from multiple processes

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;
[download]

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.

Comment on DBD::SqLite issue while accessing DB from multiple processes Download Code

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.	[reply]
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.	[reply]
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.	[reply]


Keep It Simple, Stupid
	PerlMonks