[DBD-SQLite] Concurrency in SQLite

[Zbigniew Lukasiak]

Found this: http://www.reddit.com/r/programming/comments/8bl31/sqlite_can_actually_handle_quite_a_bit_of/
on reddit today and tried to check that in Perl.

My script:

use warnings;
use strict;

use DBI;

my $dbh = DBI->connect( 'dbi:SQLite:test.db' );

$dbh->do( "drop table user" );
$dbh->do( "
CREATE TABLE user (
   user_id INTEGER PRIMARY KEY,
   pid INTEGER,
   user_name VARCHAR(32),
   occupation VARCHAR(32)
);
");

sub do_stuff {
    my $pid = shift;
    $dbh->do( "INSERT INTO user ( pid, user_name, occupation ) values
( ?, 'asdfds', 'asdfsd' )", undef, $pid );
}

my @pids;
for my $j ( 0 .. 99 ){
    my $pid = fork();
    if (not defined $pid) {
        print "resources not avilable.\n";
    } elsif ($pid == 0) {
        for my $i ( 0 .. 99 ){
            do_stuff( $$ );
        }
        exit(0);
    }
    else{
        $pids[$j] = $pid;
    }
}

for my $j ( 0 .. 99 ){
    waitpid $pids[$j],0;
}
my @row_ary  = $dbh->selectrow_array( 'select count(*) from user' );

print "Inserted $row_ary[0] records\n";

__OUTPUT__

zby at zby:~/progs/sqlite-test$ time perl test.pl 2>errors; wc errors
Inserted 9880 records

real    1m41.155s
user    0m2.052s
sys     0m4.232s
  120  1680 10800 errors
zby at zby:~/progs/sqlite-test$

The errors file contains this line (multiplied):
DBD::SQLite::db do failed: database is locked(5) at dbdimp.c line 403
at test.pl line 20.

With only 120 fails it seems that the Perl driver is better here than
the Python one (still some room for improvement exists) - but I am not
sure about my forking technique.

When I do:
select pid, count(*) c from user group by pid order by c desc;

I get couple of screens with the end:
10874|98
10875|98
10876|98
10877|97
10878|97
10879|97
10880|97
10881|97
10882|97
10883|97
10884|97
10885|97
10886|97

So it is possible that most of the processes manage to finish the
inserts before the next process is forked.

I rerun this test with 1000 inserts per process with the following effect:

zby at zby:~/progs/sqlite-test$ time perl test.pl 2>errors; wc errors
Inserted 98701 records

real    13m28.606s
user    0m16.165s
sys     0m39.142s
  1290  18060 116100 errors
zby at zby:~/progs/sqlite-test$

Looks like 9 inserts are missing - I don't know what happened here.
But still the select above shows:
11018|978
11004|977
11005|976
11006|976
11007|976
11008|976
11009|976
11010|975
11012|975
11013|975
10977|974
10995|974
11003|974
11014|974
11019|974
sqlite>

Not much difference here - so the hyphotesis above is not very probable.

--
Zbigniew Lukasiak
http://brudnopis.blogspot.com/
http://perlalchemy.blogspot.com/