Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[Keith Medcalf]

On Monday, 9 March, 2020 18:18, Peng Yu  wrote:

>But I never experience the problem in my original email when I used
>python3's default sqlite3 module (WAL was not used). What is the
>difference between the default sqlite3 module and apsw? Thanks.

THe relevant difference is that the sqlite3 wrapper sets a default busy timeout 
of 5000 milliseconds when opening a connection, which you can change with the 
timeout parameter on the sqlite3.connect connection constructor.

APSW does not do anything behind your back, so if you want a non-zero 
busy_timeout you have to set one.

cn = apsw.Connection( ... )

cn.setbusytimeout(5000)
or
cn.cursor().execute('pragma busy_timeout=5000;')

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[Peng Yu]

On 3/8/20, Keith Medcalf  wrote:
>
> On Sunday, 8 March, 2020 21:24, Peng Yu  wrote:
>
>>When I open an sqlite3 db using the following python code,
>
>>conn=apsw.Connection(filepath, flags = apsw.SQLITE_OPEN_READONLY)
>
>>, I got the following error.
>
>>Traceback (most recent call last):
>>  File "/xxx.py", line 21, in 
>>for x in c.execute('SELECT (data) FROM sqlar'):
>>  File "src/cursor.c", line 236, in resetcursor
>>apsw.BusyError: BusyError: database is locked
>
>>The db file is currently processed by a python script which opens the
>>db file for writing in the following way.
>
>>conn = apsw.Connection(filepath)
>
>>Since the first process just reads, I'd like it not be blocked. What
>>is the correct way to do so? Thanks.
>
> Opening a connection with the SQLITE_OPEN_READONLY only means that the
> connection cannot write to the database using that connection.  It does not
> affect the locking and transaction system in any way.  Merely that if you
> try to "write" using the readonly connection that you will get an error to
> the effect that the connection is read-only.
>
> Perhaps you want to set a busy timeout or use WAL journal mode.

But I never experience the problem in my original email when I used
python3's default sqlite3 module (WAL was not used). What is the
difference between the default sqlite3 module and apsw? Thanks.


-- 
Regards,
Peng
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[Keith Medcalf]

On Monday, 9 March, 2020 08:33, Simon Slavin  wrote:

>If your .shm and .wal files still exist when no apps are accessing the
>database, the most likely cause is that at least one of the apps is not
>closing its connection correctly.

or you are opening the database connection with SQLITE_OPEN_READNLY because a 
READONLY connection cannot delete the shm and wal files as that would require 
writing, and that readonly connection happens to be the last one to close.

In other words where there are multiple connections to a WAL database, the 
associated WAL and SHM files will be deleted when the last connection to that 
database closes cleanly *unless* that connection is not permitted to write (was 
opened with SQLITE_OPEN_READONLY) or was otherwise configured not to delete the 
WAL and SHM files.

This is, of course, documented.

The original unreferenced contextless fragment of text was this:

There is an additional quasi-persistent "-wal" file and "-shm" shared
memory file associated with each database, which can make SQLite less
appealing for use as an application file-format.

This is because now you have three things which may in certain circumstances 
constitute 1 thing.  If journal_mode=DELETE you only have one thing be one 
thing -- the database file -- and if there is a -journal file then you know 
something "is broked".  However in journal_mode=WAL it is not so simple.  More 
watts (not merely milliwatts) much be expended to understand why there may be 
three things consitituting one thing, and that it does not necessarily indicate 
any "brokeness" but may rather be a necessary and normal state of affairs.  Or 
not.  But an external observed will not be able to tell.  Hence a version of 
Word that stores its documents in an SQLite database in WAL mode may not be 
suitable for use by someone who expects that "one document is one file".

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[Simon Slavin]

On 9 Mar 2020, at 2:16pm, David Raymond  wrote:

> In general I believe the last connection tries to do a complete checkpoint 
> when it closes, and if it succeeds then it'll delete the -wal and -shm files. 
> If you have automatic checkpointing turned off (maybe you're doing regular 
> checkpoints from a separate dedicated process) then it's possible for all 
> connections to finish and close the database without error, but still have 
> those files sitting around because they haven't been checkpointed yet.

If your .shm and .wal files still exist when no apps are accessing the 
database, the most likely cause is that at least one of the apps is not closing 
its connection correctly.

If your app runs code to close connections, and the connections are still not 
closing properly, then the most likely cause is that you have a statement 
active when you close the connection.

To make sure your statements are not active, make sure you call 
sqlite3_finalize() on each statement when you no longer need it.  If you are 
not using the C library to execute SQLite calls, look for some equivalent in 
your SQLite library.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[David Raymond]

I see this. What does it mean? Does it mean even when the sqlite3
session is closed there is still -wal and -shm left on the disk?

"""
There is an additional quasi-persistent "-wal" file and "-shm" shared
memory file associated with each database, which can make SQLite less
appealing for use as an application file-format.
"""


In rollback journal mode the -journal file only shows up when you're making 
changes. In wal mode the -wal and -shm files are there for the entire life of 
the connection, as long as there's anything even reading the file, they're 
there.

In general I believe the last connection tries to do a complete checkpoint when 
it closes, and if it succeeds then it'll delete the -wal and -shm files. If you 
have automatic checkpointing turned off (maybe you're doing regular checkpoints 
from a separate dedicated process) then it's possible for all connections to 
finish and close the database without error, but still have those files sitting 
around because they haven't been checkpointed yet.

Some combination of those two is what is being referred to there I believe.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[Peng Yu]

I see this. What does it mean? Does it mean even when the sqlite3
session is closed there is still -wal and -shm left on the disk?

"""
There is an additional quasi-persistent "-wal" file and "-shm" shared
memory file associated with each database, which can make SQLite less
appealing for use as an application file-format.
"""

On 3/8/20, Simon Slavin  wrote:
> [This explanation is much simplified for clarity.  Before filling in missing
> details please consider what the OP wants.  Don't just show off your
> exhaustive knowledge of SQLite.]
>
> A database is normally in delete journal mode, as if you'd executed
>
> PRAGMA journal_mode=DELETE
>
> In this journal mode you can't read from a database which is being changed.
> If one process is changing the database it has the database locked.  Another
> process cannot read it until it is unlocked.  Because the reading process
> might read some of the row before it is changed, and the rest of the row
> after it is changed.
>
> To fix this, change the journal mode to WAL:
>
> PRAGMA journal_mode=WAL
>
> In this mode one process can read a database while another process is
> changing it.  The process that reads the database gets the data as it was
> before the change.
>
> For further details see
>
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Regards,
Peng
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[Simon Slavin]

[This explanation is much simplified for clarity.  Before filling in missing 
details please consider what the OP wants.  Don't just show off your exhaustive 
knowledge of SQLite.]

A database is normally in delete journal mode, as if you'd executed

PRAGMA journal_mode=DELETE

In this journal mode you can't read from a database which is being changed.  If 
one process is changing the database it has the database locked.  Another 
process cannot read it until it is unlocked.  Because the reading process might 
read some of the row before it is changed, and the rest of the row after it is 
changed.

To fix this, change the journal mode to WAL:

PRAGMA journal_mode=WAL

In this mode one process can read a database while another process is changing 
it.  The process that reads the database gets the data as it was before the 
change.

For further details see


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[Keith Medcalf]

On Sunday, 8 March, 2020 21:24, Peng Yu  wrote:

>When I open an sqlite3 db using the following python code,

>conn=apsw.Connection(filepath, flags = apsw.SQLITE_OPEN_READONLY)

>, I got the following error.

>Traceback (most recent call last):
>  File "/xxx.py", line 21, in 
>for x in c.execute('SELECT (data) FROM sqlar'):
>  File "src/cursor.c", line 236, in resetcursor
>apsw.BusyError: BusyError: database is locked

>The db file is currently processed by a python script which opens the
>db file for writing in the following way.

>conn = apsw.Connection(filepath)

>Since the first process just reads, I'd like it not be blocked. What
>is the correct way to do so? Thanks.

Opening a connection with the SQLITE_OPEN_READONLY only means that the 
connection cannot write to the database using that connection.  It does not 
affect the locking and transaction system in any way.  Merely that if you try 
to "write" using the readonly connection that you will get an error to the 
effect that the connection is read-only.

Perhaps you want to set a busy timeout or use WAL journal mode.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users