Issue found on page 'SQLite Scanner Extension'

[yeturi]

Please describe the issue on the page and include the "Page URL" link shown below.

Page URL: https://duckdb.org/docs/archive/0.9.0/extensions/sqlite.html

Hello Team, We are using DuckDB in our application. Currently we are trying to implement the SQLite extension as it is mentioned that we execute both read and write operations concurrently. We are using DuckDB driver to connect to SQLite DB and trying to execute an Update query and always getting an error that database is locked. “duckdb.duckdb.Error: Invalid Error: database is locked”

Sample python code snippet is provided below as well

conn = duckdb.connect()
conn.install_extension("sqlite")
conn.load_extension("sqlite")

conn.execute("ATTACH 'sqlite_name.db' AS sqlite_db (TYPE SQLITE)")

conn.execute("<UPDATE_QUERY>")

Let us know how to resolve the issue of concurrently executing the Select and Update at the same time and also if this can be achieved using SQLite extension to DuckDB? Appreciate your help in this regard! -Naveen

[szarnyasg]

Hi @yeturi, the duckdb-web repository is intended to host the documentation of DuckDB. Issues should be files related to the documentation itself – please do not file questions about DuckDB features. Hence, I'm transferring the issue to the sqlite_scanner repository.

[Mytherin]

Thanks for the report! Internally the SQLite extension uses SQLite itself to talk to the database file, as such it is subject to the same locking restrictions as SQLite itself. You might be able to get some more information from the SQLite documentation on this.

If you aren't using multiple processes and seeing this error then this might be an issue in the extension itself - in which case a reproducible example would be helpful to try and track down the error.

[yeturi]

Ok thanks Mytherin, we were able to test concurrent select queries and they do work but the Update statement execution is failing.

[yeturi]

@Mytherin , The issue can be reproduced by running 2 processes with Duckdb driver connecting to SQLite DB. One process running a SELECT query and the other process executing an Update query where it throws error mentioning Database is locked. What is your recommendation for having to run parallel process on SQLite, would it be something you will be able to fix? Even if you have any other recommendation which doesn't use SQLite, please do so as we are blocked now.