Description

erlend-aasland

opened on Oct 26, 2021

Issue body actions

BPO	45613
Nosy	@malemburg, @pfmoore, @serhiy-storchaka, @pablogsal, @erlend-aasland
PRs	bpo-45613: Set sqlite3.threadsafety dynamically #29227

^{Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.}

Show more details

GitHub fields:

assignee = 'https://github.com/erlend-aasland'
closed_at = <Date 2021-11-03.21:07:02.629>
created_at = <Date 2021-10-26.12:04:26.503>
labels = ['extension-modules', '3.11']
title = '[sqlite3] set threadsafety attribute based on default SQLite threaded mode'
updated_at = <Date 2021-11-03.21:07:02.629>
user = 'https://github.com/erlend-aasland'

bugs.python.org fields:

activity = <Date 2021-11-03.21:07:02.629>
actor = 'erlendaasland'
assignee = 'erlendaasland'
closed = True
closed_date = <Date 2021-11-03.21:07:02.629>
closer = 'erlendaasland'
components = ['Extension Modules']
creation = <Date 2021-10-26.12:04:26.503>
creator = 'erlendaasland'
dependencies = []
files = []
hgrepos = []
issue_num = 45613
keywords = ['patch']
message_count = 5.0
messages = ['405035', '405039', '405040', '405041', '405649']
nosy_count = 5.0
nosy_names = ['lemburg', 'paul.moore', 'serhiy.storchaka', 'pablogsal', 'erlendaasland']
pr_nums = ['29227']
priority = 'normal'
resolution = 'fixed'
stage = 'resolved'
status = 'closed'
superseder = None
type = None
url = 'https://bugs.python.org/issue45613'
versions = ['Python 3.11']

Activity

1. erlend-aasland commented on Oct 26, 2021

   

   erlend-aasland

   on Oct 26, 2021

   ContributorAuthor

   More actions

   Currently, the sqlite3 DB-API 2.0 attribute 'threadsafety' is hard-coded to 1, meaning "threads may share the module, but not connections". This is not always true, since it depends on the default SQLite threaded mode, selected at compile-time with the SQLITE_THREADSAFE define.

   SQLite's default compile-time threaded mode is SQLITE_THREADSAFE=1, also known as "serialized threaded mode", meaning SQLite can be safely used by multiple threads with no restriction. This mode equals DB-API 2.0 threadsafety level 3: threads may share the module, connections and cursors.

   On macOS 11.6, the system supplied Python 3 and SQLite 3 uses SQLITE_THREADSAFE=2 (also known as "multi-thread mode"), meaning SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads. This mode equals DB-API 2.0 threadsafety level 1: threads may share the module, but not connections.

   With SQLITE_THREADSAFE=0 (also known as "single-thread mode"), meaning all mutexes are disabled and SQLite is unsafe to use in more than a single thread at once. This mode equals DB-API 2.0 threadsafety level 0: threads may not share the module.

   Suggesting to set the 'treadsafety' dynamically at sqlite3 module load time, either via Lib/sqlite3/dbapi2.py, or in C during module init (slightly faster).

   See also:

   • https://www.python.org/dev/peps/pep-0249/
   • https://sqlite.org/threadsafe.html
   • https://discuss.python.org/t/is-sqlite3-threadsafety-the-same-thing-as-sqlite3-threadsafe-from-the-c-library/11463/11
2. erlend-aasland

   added

   3.11only security fixesonly security fixes

   on Oct 26, 2021
3. erlend-aasland

   self-assigned this

   on Oct 26, 2021
4. erlend-aasland

   added

   extension-modulesC modules in the Modules dirC modules in the Modules dir

   3.11only security fixesonly security fixes

   on Oct 26, 2021
5. erlend-aasland

   self-assigned this

   on Oct 26, 2021
6. erlend-aasland

   added

   extension-modulesC modules in the Modules dirC modules in the Modules dir

   on Oct 26, 2021
7. malemburg commented on Oct 26, 2021

   

   malemburg

   on Oct 26, 2021

   Member

   More actions

   +1 on setting the attributes dynamically. Other DB-API modules use a
   similar approach.
8. erlend-aasland commented on Oct 26, 2021

   

   erlend-aasland

   on Oct 26, 2021

   ContributorAuthor

   More actions

   FYI, it is also possible to change the threaded mode using sqlite3_config(), however that API is not exposed to the sqlite3 module, and there is no plans for doing so in the near future :)
9. erlend-aasland commented on Oct 26, 2021

   

   erlend-aasland

   on Oct 26, 2021

   ContributorAuthor

   More actions

   BTW, I've verified that the overhead of adding this query is negligible.

   $ python -m pyperf compare_to --min-speed 1 main.json patched.json 
   Benchmark hidden because not significant (1): sqlite_synth

   git switch -
   Switched to branch 'main'
   $ ./python.exe -m timeit "import sqlite3; sqlite3.threadsafety"
   1000000 loops, best of 5: 225 nsec per loop
   $ git switch -
   Switched to branch 'sqlite-threadsafety'
   $ ./python.exe -m timeit "import sqlite3; sqlite3.threadsafety"
   1000000 loops, best of 5: 221 nsec per loop
10. pablogsal commented on Nov 3, 2021

    

    pablogsal

    on Nov 3, 2021

    Member

    More actions

    New changeset c273986 by Erlend Egeberg Aasland in branch 'main':
    bpo-45613: Set sqlite3.threadsafety dynamically (GH-29227)
    c273986
11. erlend-aasland

    closed this as completedon Nov 3, 2021
12. erlend-aasland

    closed this as completedon Nov 3, 2021
13. ezio-melotti

    transferred this issue fromon Apr 10, 2022
14. erlend-aasland

    added

    topic-sqlite3

    on May 16, 2022

Issue actions