# Is sqlite3.threadsafety the same thing as sqlite3\_threadsafe() from the C library? **URL:** \ **Category:** Core Development\ **Tags:** documentation\ **Created:** [October 25, 2021, 6:25pm UTC](https://discuss.python.org/t/is-sqlite3-threadsafety-the-same-thing-as-sqlite3-threadsafe-from-the-c-library/11463 "2021-10-25T18:25:29Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![nchammas](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/nchammas/32/233_2.png) [@nchammas](https://discuss.python.org/u/nchammas)\ **Post date:** [October 25, 2021, 6:25pm UTC](https://discuss.python.org/t/is-sqlite3-threadsafety-the-same-thing-as-sqlite3-threadsafe-from-the-c-library/11463/1 "2021-10-25T18:25:29Z")
I’m looking at [`sqlite3.threadsafety`](https://github.com/python/cpython/blob/bb3e0c240bc60fe08d332ff5955d54197f79751c/Lib/sqlite3/dbapi2.py#L31) and wondering if it’s the same thing as [`sqlite3_threadsafe()`](https://www.sqlite.org/c3ref/threadsafe.html) 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`: ```python >>> ( sqlite3.connect(':memory:') .execute(""" select * from pragma_COMPILE_OPTIONS where compile_options like 'THREADSAFE=%' """) .fetchall() ) [('THREADSAFE=1',)] ``` According to [the SQLite docs](https://www.sqlite.org/compile.html#threadsafe), 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](https://github.com/python/cpython/blob/bb3e0c240bc60fe08d332ff5955d54197f79751c/Lib/sqlite3/dbapi2.py#L31). ---
**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\ **Post date:** [October 25, 2021, 8:34pm UTC](https://discuss.python.org/t/is-sqlite3-threadsafety-the-same-thing-as-sqlite3-threadsafe-from-the-c-library/11463/2 "2021-10-25T20:34:00Z")
`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](https://sqlite.org/threadsafe.html): _" The [sqlite3\_threadsafe()](https://sqlite.org/c3ref/threadsafe.html) 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()](https://sqlite.org/c3ref/config.html) 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](https://sqlite.org/compile.html#threadsafe) 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`: ```auto $ /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. ---
**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\ **Post date:** [October 25, 2021, 8:40pm UTC](https://discuss.python.org/t/is-sqlite3-threadsafety-the-same-thing-as-sqlite3-threadsafe-from-the-c-library/11463/3 "2021-10-25T20:40:31Z")
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. ---
**Author:** ![pf\_moore](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/pf_moore/32/35_2.png) [@pf\_moore](https://discuss.python.org/u/pf_moore)\ **Post date:** [October 25, 2021, 8:52pm UTC](https://discuss.python.org/t/is-sqlite3-threadsafety-the-same-thing-as-sqlite3-threadsafe-from-the-c-library/11463/4 "2021-10-25T20:52:59Z")
The `threadsafety` attribute is [required by the DB-API 2.0 spec](https://www.python.org/dev/peps/pep-0249/#globals). ---
**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\ **Post date:** [October 25, 2021, 8:53pm UTC](https://discuss.python.org/t/is-sqlite3-threadsafety-the-same-thing-as-sqlite3-threadsafe-from-the-c-library/11463/5 "2021-10-25T20:53:35Z")
> [@pf\_moore](#): > > The `threadsafety` attribute is [required by the DB-API 2.0 spec](https://www.python.org/dev/peps/pep-0249/#globals). Aaah. Thanks. That’s a bummer 🙂 ---
**Author:** ![malemburg](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/malemburg/32/50_2.png) [@malemburg](https://discuss.python.org/u/malemburg)\ **Post date:** [October 25, 2021, 8:54pm UTC](https://discuss.python.org/t/is-sqlite3-threadsafety-the-same-thing-as-sqlite3-threadsafe-from-the-c-library/11463/6 "2021-10-25T20:54:22Z")
Wait, not so fast 🙂 The threadsafety attribute is a DB-API 2.0 attribute which defines the level of threadsafety of the module: > **[PEP 249 -- Python Database API Specification v2.0](https://www.python.org/dev/peps/pep-0249/#threadsafety)** > > The official home of the Python Programming Language 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,…” ---
**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\ **Post date:** [October 25, 2021, 8:57pm UTC](https://discuss.python.org/t/is-sqlite3-threadsafety-the-same-thing-as-sqlite3-threadsafe-from-the-c-library/11463/7 "2021-10-25T20:57:52Z")
> [@malemburg](#): > > 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,…” Yes, that is true, @malemburg 🙂 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 🙂 ---
**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\ **Post date:** [October 25, 2021, 8:59pm UTC](https://discuss.python.org/t/is-sqlite3-threadsafety-the-same-thing-as-sqlite3-threadsafe-from-the-c-library/11463/8 "2021-10-25T20:59:52Z")
> [@malemburg](#): > > If it doesn’t show up in the sqlite3 docs, it should probably be > added. Definitely. I’ll create a PR for that. **UPDATE** : I’ve created [bpo-45608](https://bugs.python.org/issue45608) and [GH-29219](https://github.com/python/cpython/pull/29219) ---
**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\ **Post date:** [October 25, 2021, 9:06pm UTC](https://discuss.python.org/t/is-sqlite3-threadsafety-the-same-thing-as-sqlite3-threadsafe-from-the-c-library/11463/9 "2021-10-25T21:06:52Z")
We _could_ replace the hard coded value with a query of the compile time selected default threading mode. ---
**Author:** ![nchammas](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/nchammas/32/233_2.png) [@nchammas](https://discuss.python.org/u/nchammas)\ **Post date:** [October 25, 2021, 9:07pm UTC](https://discuss.python.org/t/is-sqlite3-threadsafety-the-same-thing-as-sqlite3-threadsafe-from-the-c-library/11463/10 "2021-10-25T21:07:35Z")
> [@erlendaasland](#): > > For example, macOS 11.6 ship with `SQLITE_THREADSAFE=2` 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`. > [@malemburg](#): > > The threadsafety attribute is a DB-API 2.0 attribute which defines > the level of threadsafety of the module: 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! ---
**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\ **Post date:** [October 25, 2021, 9:14pm UTC](https://discuss.python.org/t/is-sqlite3-threadsafety-the-same-thing-as-sqlite3-threadsafe-from-the-c-library/11463/11 "2021-10-25T21:14:53Z")
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`. **UPDATE** : I’ve opened [bpo-45613](https://bugs.python.org/issue45613) and [GH-29227](https://github.com/python/cpython/pull/29227) for this. ---
**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\ **Post date:** [October 28, 2021, 7:41pm UTC](https://discuss.python.org/t/is-sqlite3-threadsafety-the-same-thing-as-sqlite3-threadsafe-from-the-c-library/11463/12 "2021-10-28T19:41:03Z")
@nchammas: `sqlite3.threadsafety` is now documented. It’ll take some time before the webpages are updated though. ---
**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\ **Post date:** [November 3, 2021, 9:05pm UTC](https://discuss.python.org/t/is-sqlite3-threadsafety-the-same-thing-as-sqlite3-threadsafe-from-the-c-library/11463/13 "2021-11-03T21:05:50Z")
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`). [1] Now, as in Python 3.11 😉