turning sqlite journal off

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

turning sqlite journal off

Laura POggio
Dear all,
we need to run a series of operations on a sqlite database. We noticed that after each grass session close there is a journal file created. This can take some time to be finished. Therefore it takes some time for the DB to be ready for the next session.

We tried to turn off the journal mode directly on the sqlite file (being aware of the potential risks): 

sqlite3 $GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db
sqlite> PRAGMA journal_mode = OFF;

This worked as any commands run directly from sqlite3 do not create the journal file anymore. However if the sqlite file is accessed by GRASS the journal file is created again and we often experience locking problems, with the following warning is often issued: WARNING: Busy SQLITE db, already waiting for 10 seconds...

Is there a way to turn off the journal mode in SQLite for GRASS?

Thanks in advance for any suggestions

Laura


_______________________________________________
grass-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user
Reply | Threaded
Open this post in threaded view
|

Re: turning sqlite journal off

Panagiotis Mavrogiorgos
Hi Laura,

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

_______________________________________________
grass-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user
Reply | Threaded
Open this post in threaded view
|

Re: turning sqlite journal off

Markus Metz-3


On Tue, May 14, 2019 at 11:12 AM Panagiotis Mavrogiorgos <[hidden email]> 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


_______________________________________________
grass-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user
Reply | Threaded
Open this post in threaded view
|

Re: turning sqlite journal off

NikosAlexandris
* Markus Metz <[hidden email]> [2019-05-15 22:55:13 +0200]:

>On Tue, May 14, 2019 at 11:12 AM Panagiotis Mavrogiorgos <[hidden email]>
>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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user
Reply | Threaded
Open this post in threaded view
|

Re: turning sqlite journal off

Laura POggio
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 <[hidden email]> wrote:
* Markus Metz <[hidden email]> [2019-05-15 22:55:13 +0200]:

>On Tue, May 14, 2019 at 11:12 AM Panagiotis Mavrogiorgos <[hidden email]>
>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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user

_______________________________________________
grass-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user
Reply | Threaded
Open this post in threaded view
|

Re: turning sqlite journal off

Jonathan Moules-4

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 <[hidden email]> wrote:
* Markus Metz <[hidden email]> [2019-05-15 22:55:13 +0200]:

>On Tue, May 14, 2019 at 11:12 AM Panagiotis Mavrogiorgos <[hidden email]>
>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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user


_______________________________________________
grass-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user


_______________________________________________
grass-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-user