SQLite concurrent access to database

[Kilian CAVALOTTI]

Hi all,

I'm currently developing a little django application for monitoring
workstations. Each monitored host reports its own data by creating an object
in django db. It can obviously occur that several workstations try to access
the db at the same time. And I commonly get the following error (using the
SQLite backend):

Traceback (most recent call last):
File "/home/lw/localWatch/collect/collect.py", line 168, in ?
h.save()
File "/home/lw/django/django/db/models/base.py", line 166, in save
db_values + [pk_val])
File "/home/lw/django/django/db/backends/util.py", line 12, in execute
return self.cursor.execute(sql, params)
File "/home/lw/django/django/db/backends/sqlite3/base.py", line 69, in
execute
return Database.Cursor.execute(self, query, params)
pysqlite2.dbapi2.OperationalError: unable to open database file

When I manually launch my application, on only one host, there's no error, and
the script correctly completes.

I thought that SQLite gracefully handled concurrent accesses, so I wonder why
I get these errors. Is there a way to tune framework locks on the database,
to change retry limits, or something else, to avoid this?

Thanks in advance,
--
Kilian CAVALOTTI Administrateur réseaux et systèmes
UPMC / CNRS - LIP6 (C870)
8, rue du Capitaine Scott Tel. : 01 44 27 88 54
75015 Paris - France Fax. : 01 44 27 70 00

[Graham King]

Kilian,

I does indeed say here that SQLite can handle concurrent transaction,
by queuing them up:

http://www.sqlite.org/faq.html#q7

You're not using an NFS share are you ? Apparently that would break it.

Are you running the app / script on each users machine, or on a web
server they all access ?

I hope I don't get into too much trouble for saying this, but for
multi-user production systems (i.e. web apps) MySQL or Postgres would be
a better choice. As the link above says they "support a higher level of
concurrency and allow multiple processes to be writing to the same
database at the same time. "

Best regards,
Graham.

[Kilian CAVALOTTI]

On Sunday 21 May 2006 21:26, Graham King wrote:
> Kilian,
>
> I does indeed say here that SQLite can handle concurrent transaction,
> by queuing them up:
>
> http://www.sqlite.org/faq.html#q7
>
> You're not using an NFS share are you ? Apparently that would break it.

Gosh, it didn't cross my mind, but that's it, the database is accessed from a
NFS share. Thanks for pointing this out.

> Are you running the app / script on each users machine, or on a web
> server they all access ?

It's executed by cron, on each monitored host.

> I hope I don't get into too much trouble for saying this, but for
> multi-user production systems (i.e. web apps) MySQL or Postgres would be
> a better choice. As the link above says they "support a higher level of
> concurrency and allow multiple processes to be writing to the same
> database at the same time. "

Indeed. I just wanted to use SQLite to avoid the MySQL
installation/setup/upgrade cycle, but if I can't find a way to configure my
NFS mounts to allow better locking, I'd switch to MySQL.

Thanks a lot,

[Joseph Heck]

Consider a django SQLite DB for each of the machines or have a central "collection" server running somewhere that the various individual agents can talk to - a REST, XML-RPC, or so API.

-joe

X-Google-Language: ENGLISH,UTF8
Received: by 10.54.73.18 with SMTP id v18mr187201wra;
        Mon, 22 May 2006 01:26:17 -0700 (PDT)
Return-Path: < kilian.c...@lip6.fr>
Received: from isis.lip6.fr (isis.lip6.fr [132.227.60.2])
        by mx.googlegroups.com with ESMTP id v11si902074cwb.2006.05.22.01.26.16;
        Mon, 22 May 2006 01:26:17 -0700 (PDT)
Received-SPF: pass (googlegroups.com: best guess record for domain of kilian.c...@lip6.fr designates 132.227.60.2 as permitted sender)
Received: from poleia.lip6.fr ( poleia.lip6.fr [132.227.205.24])
          by isis.lip6.fr (8.13.6/jtpda-5.4+mv) with ESMTP id k4M8Q3wn020229
          ; Mon, 22 May 2006 10:26:03 +0200
X-pt: isis.lip6.fr
Received: from spip.lip6.fr (mailia [127.0.0.1])
          by poleia.lip6.fr (8.13.3/jtpda-5.4+CB) with ESMTP id k4M8Q2cd006328
          ; Mon, 22 May 2006 10:26:02 +0200
From: Kilian CAVALOTTI <kilian.c...@lip6.fr>
Organization: LIP6
To: django...@googlegroups.com
Subject: Re: SQLite concurrent access to database
Date: Mon, 22 May 2006 10:26:07 +0200
User-Agent: KMail/1.9.1
Cc: Graham King <gra...@darkcoding.net>
References: <200605211825.0130...@lip6.fr> < 4470BEDC...@darkcoding.net>
In-Reply-To: <4470BEDC...@darkcoding.net>
Mime-Version: 1.0
Content-Type: text/plain; charsetUTF-8
Content-Transfer-Encoding: quoted-printable
Message-Id: <200605221026.0766...@lip6.fr>
X-Greylist: Sender IP whitelisted, not delayed by milter-greylist-2.0.2 (isis.lip6.fr [132.227.60.2]); Mon, 22 May 2006 10:26:03 +0200 (CEST)
X-Scanned-By: isis.lip6.fr