Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[sqlite3] set threadsafety attribute based on default SQLite threaded mode #89776

Closed
erlend-aasland opened this issue Oct 26, 2021 · 5 comments
Closed

[sqlite3] set threadsafety attribute based on default SQLite threaded mode #89776

erlend-aasland opened this issue Oct 26, 2021 · 5 comments
Assignees
@erlend-aasland
Labels
3.11 only security fixes extension-modules C modules in the Modules dir topic-sqlite3

Comments

@erlend-aasland
Copy link
Contributor

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']
    @erlend-aasland
    Copy link
    Contributor Author

    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:

    @erlend-aasland erlend-aasland added the 3.11 only security fixes label Oct 26, 2021
    @erlend-aasland erlend-aasland self-assigned this Oct 26, 2021
    @erlend-aasland erlend-aasland added extension-modules C modules in the Modules dir 3.11 only security fixes labels Oct 26, 2021
    @erlend-aasland erlend-aasland self-assigned this Oct 26, 2021
    @erlend-aasland erlend-aasland added the extension-modules C modules in the Modules dir label Oct 26, 2021
    @malemburg
    Copy link
    Member

    +1 on setting the attributes dynamically. Other DB-API modules use a
    similar approach.

    @erlend-aasland
    Copy link
    Contributor Author

    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 :)

    @erlend-aasland
    Copy link
    Contributor Author

    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

    @pablogsal
    Copy link
    Member

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

    @ezio-melotti ezio-melotti transferred this issue from another repository Apr 10, 2022
    @frabyn frabyn mentioned this issue Sep 8, 2022
    Closed
    @nmaytan nmaytan mentioned this issue Mar 19, 2024
    Open
    Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
    Assignees

    @erlend-aasland erlend-aasland

    Labels
    3.11 only security fixes extension-modules C modules in the Modules dir topic-sqlite3
    Projects
    None yet
    Milestone
    No milestone
    Development

    No branches or pull requests
    3 participants
    @malemburg @pablogsal @erlend-aasland