[GRASS-user] sqlite db locking blocking

GRASS GRASS user
1

Hi,

does SQLite let you both read and write to the DB same at the same time?
-or-, why would sqlite lock the DB on a read/select?

I'm trying to read two attribute column numbers, feed them into an
equation in awk, compose a SET,WHERE sql string, and pipe that back
into db.execute in a single step.

v.db.addcol map column="new DOUBLE PRECISION"
v.db.select map column=cat,old | awk -F'|' \
    '{printf("UPDATE map SET new=%f WHERE cat=%d;\n", cos($2), $1)}' \
  | db.execute

the error looks like:
---
DBMI-SQLite driver error:
Error in sqlite3_step():
database is locked

ERROR: Error while executing: 'UPDATE map SET new=0.000000 WHERE cat=0'
---

(there is no cat 0 in the data)

It works for a few dozen rows of data, but not when I scale up the
attribute db to 10s of thousands of entries.

Is it possible to avoid writing out a temp file and doing it in two steps?

If there's just a single sqlite.db for the entire mapset, will I get the
same thing if I read/send the output to a different map's table in the
same mapset?

?
thanks,
Hamish

2

Hamish wrote:

does SQLite let you both read and write to the DB same at the same
time? -or-, why would sqlite lock the DB on a read/select?

AFAIK, any read/write operation locks the file (or the portion of the
file corresponding to the relevant tables) accordingly (i.e. read
operations impose a read lock, write operations a write lock).

There are mechanisms which can be used for "lockless" operation, but I
don't think that SQLite uses them.

I'm trying to read two attribute column numbers, feed them into an
equation in awk, compose a SET,WHERE sql string, and pipe that back
into db.execute in a single step.

v.db.addcol map column="new DOUBLE PRECISION"
v.db.select map column=cat,old | awk -F'|' \
    '{printf("UPDATE map SET new=%f WHERE cat=%d;\n", cos($2), $1)}' \
  | db.execute

the error looks like:
---
DBMI-SQLite driver error:
Error in sqlite3_step():
database is locked

ERROR: Error while executing: 'UPDATE map SET new=0.000000 WHERE cat=0'
---

(there is no cat 0 in the data)

It works for a few dozen rows of data, but not when I scale up the
attribute db to 10s of thousands of entries.

Is it possible to avoid writing out a temp file and doing it in two steps?

I don't think so. db.select will take a read lock until it has
finished writing the data, while db.execute will take a write lock.

If the output from v.db.select is less than the size of a pipe buffer,
you may get away with it. If it's more than that, db.execute will try
to take a write lock while v.db.select still has the read lock.

If there's just a single sqlite.db for the entire mapset, will I get the
same thing if I read/send the output to a different map's table in the
same mapset?

I don't know; try it. The documentation implies that locks operate on
individual tables rather than the entire database.

--
Glynn Clements <glynn@gclements.plus.com>

3

On Wed, Feb 23, 2011 at 6:19 AM, Hamish <hamish_b@yahoo.com> wrote:
...

the error looks like:
---
DBMI-SQLite driver error:
Error in sqlite3_step():
database is locked

For the record:
we get such error here, too. It turned out to be a NFS problem: SQLite
fails likely when the file is on a NFS mounted file system due to
bugs in older NFS versions.

Here with Mandriva 2011 it works, with an older Scientific Linux 5.5 it fails.

Markus