I’m looking at sqlite3.threadsafety and wondering if it’s the same thing as sqlite3_threadsafe() from the C library. The names suggest they are the same, but since sqlite3.threadsafety is hardcoded that suggests their values are independent.
There is way to query SQLite itself for the thread safety compiler option, via pragma COMPILE_OPTIONS:
>>> (
sqlite3.connect(':memory:')
.execute("""
select *
from pragma_COMPILE_OPTIONS
where compile_options like 'THREADSAFE=%'
""")
.fetchall()
)
[('THREADSAFE=1',)]
According to the SQLite docs, the value returned by checking pragma COMPILE_OPTIONS is the same value returned by sqlite3_threadsafe().
So my question is: Is it possible for Python’s sqlite3.threadsafety to ever disagree with what’s returned by pragma COMPILE_OPTIONS?
If not, what keeps sqlite3.threadsafety in sync with COMPILE_OPTIONS? It’s not obvious to me since its value is hardcoded.
sqlite3.threadsafety is, as you say, a hardcoded value; it is fully possible that it disagrees with what’s returned by pragma compile_options and sqlite3_threadsafe(). Note also, that sqlite3_threadsafe does not take start-time or run-time mode changes into account. Quoting from the SQLite docs:
" The sqlite3_threadsafe() interface predates the multi-thread mode and start-time and run-time mode selection and so is unable to distinguish between multi-thread and serialized mode nor is it able to report start-time or run-time mode changes."
See sqlite3_config() for a better way to query (and set) the current threading mode.
The official Python binaries for Windows and macOS ship with the default SQLite threading mode: SQLITE_THREADSAFE=1 AKA serialized mode. Other binaries may be shipped with a SQLite library compiled with different default threading mode, for example multi-thread mode (SQLITE_THREADSAFE=2). For example, macOS 11.6 ship with SQLITE_THREADSAFE=2:
$ /usr/bin/python3
Python 3.8.9 (default, Aug 21 2021, 15:53:23)
[Clang 13.0.0 (clang-1300.0.29.3)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.32.3'
>>> cx = sqlite3.connect(":memory:")
>>> cx.execute("select * from pragma_compile_options where compile_options like 'THREADSAFE=%'").fetchall()
[('THREADSAFE=2',)]
Building a custom CPython with a custom SQLite SQLITE_THREADSAFE=0 library should be ok for a single-threaded application, but I haven’t tried it myself.
FYI, the threadsafety attribute has been a part of the sqlite3 module since the initial commit from when it was a third party library (pysqlite). There has been no commits that have touched it ever since. It is currently, and has always been, undocumented.
The threadsafety attribute is a DB-API 2.0 attribute which defines
the level of threadsafety of the module:
If it doesn’t show up in the sqlite3 docs, it should probably be
added. It is implicitly documented via the sentence “The sqlite3
module was written by Gerhard Häring. It provides a SQL interface
compliant with the DB-API 2.0 specification described by PEP 249,…”
Being hard coded, it may return the wrong answer. For example, if you’ve opened a connection without mutexes, or if you’re using a custom built library. But, in those two cases, the user is (or should) be well aware of the threaded mode, and would probably ignore the DB-API attribute anyways
Great catch! That’s precisely the scenario I was worried about, where Python’s hardcoded value disagrees with the actual compiler flag reported by pragma COMPILE_OPTIONS.
Thanks for the reference! It seems then that these are totally different things.
Amusingly, SQLite’s THREADSAFE=1 means you can share connections, whereas DB-API 2.0’s threadsafety == 1 means you cannot share connections.
I’m glad I asked. Thanks for the quick responses, everyone. What I need then is to check pragma directly. And yes, some documentation for sqlite3.threadsafety would be helpful, just so people don’t assume it has anything to do with the SQLite compiler option!
It seems to me that the default SQLite threaded mode (serialized, SQLITE_THEADSAFE=1) actually implies DB-API 2.0 threadsafety=3, because the module, connections, and cursors (prepared statements) can be shared. SQLITE_THREADSAFE=2 (multi-thread mode) implies DB-API 2.0 threadsafety=1, and SQLITE_THREADSAFE=0 (single-thread mode) implies DB-API 2.0 threadsafety=0.
GH-29227 has now been merged; the sqlite3 module now[1] sets sqlite3.threadsafety dynamically based on the default threading mode SQLite has been compiled with (SQLITE_THREADSAFE).
