[GRASS-user] turning sqlite journal off

GRASS GRASS user
1

Hi Laura,

This thread seems to be related: http://osgeo-org.1560.x6.nabble.com/SQLITE-db-locking-problem-td5182180.html
I also had a somewhat similar problem that was related to the VACUUM command issued when closing a GRASS session (new session started before the VACUUM of the previous session was finished)

If I understand this correctly, you are not supposed to concurrently use the same sqlite database.

with kind regards,

Panos

2

On Tue, May 14, 2019 at 11:12 AM Panagiotis Mavrogiorgos <pmav99@gmail.com> wrote:

Hi Laura,

This thread seems to be related: http://osgeo-org.1560.x6.nabble.com/SQLITE-db-locking-problem-td5182180.html
I also had a somewhat similar problem that was related to the VACUUM command issued when closing a GRASS session (new session started before the VACUUM of the previous session was finished)

If I understand this correctly, you are not supposed to concurrently use the same sqlite database.

Yes, this is a limitation of sqlite, and the GRASS-internal sqlite driver has been adapted accordingly as much as possible.

Markus M

3

* Markus Metz <markus.metz.giswork@gmail.com> [2019-05-15 22:55:13 +0200]:

On Tue, May 14, 2019 at 11:12 AM Panagiotis Mavrogiorgos <pmav99@gmail.com>
wrote:

Hi Laura,

This thread seems to be related:

http://osgeo-org.1560.x6.nabble.com/SQLITE-db-locking-problem-td5182180.html

I also had a somewhat similar problem that was related to the VACUUM

command issued when closing a GRASS session (new session started before the
VACUUM of the previous session was finished)

If I understand this correctly, you are not supposed to concurrently use

the same sqlite database.

Yes, this is a limitation of sqlite, and the GRASS-internal sqlite driver
has been adapted accordingly as much as possible.

Nevertheless, concurrent reading is allowed. Reading the discussion so
far, one may think that no concurrent use is possible at all.

See also https://sqlite.org/whentouse.html :

  "SQLite supports an unlimited number of simultaneous readers, but it
  will only allow one writer at any instant in time."

And in https://sqlite.org/lockingv3.html see 'SHARED'.
Also, https://stackoverflow.com/a/4060838/1172302.

Nikos

4

Dear all,
thanks for the answers and apologies if my question at the beginning was not so clear.
SQLITE can support multiple readers and only one writer. But it is not the main problem.
The issue here is the creation of the journal file. Each time a GRASS session that modified the sqlite.db file is closed, the journal file is created. This can take quite some time and during this time the database is locked. This behaviour is expected.
I can turn off the creation of the journal file (being aware of the potential risks) using sqlite options and thus reducing the time the database is locked. However these options do not apply when the file is modified by GRASS.
Is it possible to turn off the creation of the journal file when a GRASS session is involved?

Thanks!

Laura

On Thu, 16 May 2019 at 00:07, Nikos Alexandris <nik@nikosalexandris.net> wrote:

On Tue, May 14, 2019 at 11:12 AM Panagiotis Mavrogiorgos <pmav99@gmail.com>
wrote:

Hi Laura,

This thread seems to be related:
http://osgeo-org.1560.x6.nabble.com/SQLITE-db-locking-problem-td5182180.html
I also had a somewhat similar problem that was related to the VACUUM
command issued when closing a GRASS session (new session started before the
VACUUM of the previous session was finished)
If I understand this correctly, you are not supposed to concurrently use
the same sqlite database.

Yes, this is a limitation of sqlite, and the GRASS-internal sqlite driver
has been adapted accordingly as much as possible.

Nevertheless, concurrent reading is allowed. Reading the discussion so
far, one may think that no concurrent use is possible at all.

See also https://sqlite.org/whentouse.html :

“SQLite supports an unlimited number of simultaneous readers, but it
will only allow one writer at any instant in time.”

And in https://sqlite.org/lockingv3.html see ‘SHARED’.
Also, https://stackoverflow.com/a/4060838/1172302.

Nikos

grass-user mailing list
grass-user@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-user

5

Hi Laura,

(I’m coming at this as someone familiar with SQLite but not GRASS)

As far as I can tell, the journal_mode PRAGMA is non-persistent. So if you set it within SQLite at the command line, then that setting is only going to remain set during that connection. GRASS uses its own connection so will use the default (DELETE) unless otherwise set.

The exception is WAL - Write Ahead Log. This setting persists. Take a look at https://sqlite.org/wal.html to see if it’s suited to your use-case. Then if you set it, it should remain set when GRASS uses the db.

As far as I can tell doing a quick search of the GRASS code using github, the journal_mode is only set once, via the temporal python library to MEMORY - https://github.com/OSGeo/grass/search?utf8=%E2%9C%93&q=journal_mode&type=

The other way to work around this non-persistent journal_mode is: “Note also that the journal_mode cannot be changed while a transaction is active.” - so keep a connection-with-transaction open via not-GRASS while GRASS is doing its thing.

Cheers,

Jonathan

···

On 16/05/2019 09:28, Laura Poggio wrote:

Dear all,
thanks for the answers and apologies if my question at the beginning was not so clear.
SQLITE can support multiple readers and only one writer. But it is not the main problem.
The issue here is the creation of the journal file. Each time a GRASS session that modified the sqlite.db file is closed, the journal file is created. This can take quite some time and during this time the database is locked. This behaviour is expected.
I can turn off the creation of the journal file (being aware of the potential risks) using sqlite options and thus reducing the time the database is locked. However these options do not apply when the file is modified by GRASS.
Is it possible to turn off the creation of the journal file when a GRASS session is involved?

Thanks!

Laura

On Thu, 16 May 2019 at 00:07, Nikos Alexandris <nik@nikosalexandris.net> wrote:

On Tue, May 14, 2019 at 11:12 AM Panagiotis Mavrogiorgos <pmav99@gmail.com>
wrote:

Hi Laura,

This thread seems to be related:
http://osgeo-org.1560.x6.nabble.com/SQLITE-db-locking-problem-td5182180.html
I also had a somewhat similar problem that was related to the VACUUM
command issued when closing a GRASS session (new session started before the
VACUUM of the previous session was finished)
If I understand this correctly, you are not supposed to concurrently use
the same sqlite database.

Yes, this is a limitation of sqlite, and the GRASS-internal sqlite driver
has been adapted accordingly as much as possible.

Nevertheless, concurrent reading is allowed. Reading the discussion so
far, one may think that no concurrent use is possible at all.

See also https://sqlite.org/whentouse.html :

“SQLite supports an unlimited number of simultaneous readers, but it
will only allow one writer at any instant in time.”

And in https://sqlite.org/lockingv3.html see ‘SHARED’.
Also, https://stackoverflow.com/a/4060838/1172302.

Nikos

grass-user mailing list
grass-user@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-user

_______________________________________________
grass-user mailing list
[grass-user@lists.osgeo.org](mailto:grass-user@lists.osgeo.org)
[https://lists.osgeo.org/mailman/listinfo/grass-user](https://lists.osgeo.org/mailman/listinfo/grass-user)