[GRASS-user] sqlite db locking blocking

[Glynn Clements]

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 at gclements.plus.com>