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

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

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

Comments

@erlend-aasland
Copy link
Contributor

erlend-aasland commented Oct 26, 2021

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

    erlend-aasland commented Oct 26, 2021

    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 self-assigned this Oct 26, 2021
    @erlend-aasland erlend-aasland added extension-modules C modules in the Modules dir 3.11 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

    malemburg commented Oct 26, 2021

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

    @erlend-aasland
    Copy link
    Contributor Author

    erlend-aasland commented Oct 26, 2021

    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

    erlend-aasland commented Oct 26, 2021

    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

    pablogsal commented Nov 3, 2021

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

    Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
    Labels
    Projects
    None yet
    Development

    No branches or pull requests

    3 participants