How to open an sqlite3 for reading without being blocked by process writing to it?

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

How to open an sqlite3 for reading without being blocked by process writing to it?

Peng Yu
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 <module>
    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.

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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 <module>
>    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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
In reply to this post by Peng Yu
[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

<https://sqlite.org/wal.html>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
>
> <https://sqlite.org/wal.html>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
On 9 Mar 2020, at 2:16pm, David Raymond <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Peng Yu
In reply to this post by Keith Medcalf
On 3/8/20, Keith Medcalf <[hidden email]> wrote:

>
> On Sunday, 8 March, 2020 21:24, Peng Yu <[hidden email]> 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 <module>
>>    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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users