Well, it seems that no, as far as journaling is disabled.

#!/usr/bin/perl

use strict;
use warnings;

use POSIX qw(_exit);
use DBI;
use File::Temp;

$| = 1;
my $dbfile = "/tmp/db-$$";

my $dbh = DBI->connect("dbi:SQLite:$dbfile", "", "", {RaiseError => 1}
+);

$dbh->do("create table queue (child, ix)");

for my $id (0..3) {
    fork or do {
        print "<$id enter>";
        my $dbh = DBI->connect("dbi:SQLite:$dbfile", "", "", {RaiseErr
+or => 1});
        $dbh->do("PRAGMA journal_mode = OFF");
        my $sth;
        while (not defined $sth) {
            $sth = eval { $dbh->prepare("insert into queue values (?, 
+?)") };
        }
        print "<$id with sth>";
        for (1..1000) {
            eval {
                $sth->execute($id, $_);
                print $id;
            };
            $@ and print "<$id error: $@>"
        };
        print "<$id exit>";
        _exit(0);
    }
}

1 while (wait > 0);

my $sth = $dbh->prepare("select count(*) from queue");
$sth->execute;
my $row = $sth->fetchrow_arrayref;
print "\n@$row\n";
[download]

On my Linux box it does 4000 insertions in 35 seconds and having several concurrent processes accessing the same database does not affect its performance noticeably.

update: It seems that even better than disabling the journal, it is to disable synchronous writes (= ensuring that written data actually gets into the disk surface):

$dbh->do("PRAGMA synchronous = OFF");
[download]

That makes 4000 inserts in 2.5 seconds in my computer, and is safer as the database would not get corrupted unless the OS failed to flush cache data to disk.