[issue3846] sqlite3 module: Improved concurrency
New submission from Gerhard Häring [EMAIL PROTECTED]: I'd really like this change to get into Python 2.6. It's pretty trivial (just releases the GIL when compiling SQLite statements), but improves concurrency for SQLite. This means less database is locked errors for our users. Could somebody please review this and give me an ok to check it in? -- assignee: ghaering files: sqlite_concurrency_prepare.diff keywords: patch messages: 73086 nosy: ghaering severity: normal status: open title: sqlite3 module: Improved concurrency type: performance versions: Python 2.6 Added file: http://bugs.python.org/file11473/sqlite_concurrency_prepare.diff ___ Python tracker [EMAIL PROTECTED] http://bugs.python.org/issue3846 ___ ___ Python-bugs-list mailing list Unsubscribe: http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
[issue3846] sqlite3 module: Improved concurrency
Changes by Gerhard Häring [EMAIL PROTECTED]: -- keywords: +needs review -patch priority: - high ___ Python tracker [EMAIL PROTECTED] http://bugs.python.org/issue3846 ___ ___ Python-bugs-list mailing list Unsubscribe: http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
[issue3846] sqlite3 module: Improved concurrency
Amaury Forgeot d'Arc [EMAIL PROTECTED] added the comment: Your patch seems obvious, however: - Is there the possibility that sqlite3_prepare() calls back into a function in the _sqlite module or other python code? In this case the GIL has to be re-acquired. - What if another thread closes the current connection (or drop a table, etc.) in-between? Is sqlite really thread-safe? -- nosy: +amaury.forgeotdarc ___ Python tracker [EMAIL PROTECTED] http://bugs.python.org/issue3846 ___ ___ Python-bugs-list mailing list Unsubscribe: http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
[issue3846] sqlite3 module: Improved concurrency
Gerhard Häring [EMAIL PROTECTED] added the comment: 1. SQLite calling back Good that you mention it. During sqlite3_prepare, SQLite can call the authorizer_callback. Fortunately, it already acquires/releases the GIL appropriately already. 2. Other thread closing connection, etc. Connections/cursors cannot be shared among Python threads. Well, with newer SQLite releases I think it is possible. But we have checks in place that raise an error if you do it. These are the various calls to pysqilte_check_thread in the code. If the table is dropped, sqlite3_prepare will just raise an error about the statement not being valid. If, however, the table is dropped between sqlite3_prepare and sqlite3_step, then the SQLITE_SCHEMA error code is returned. We then try to recompile the statement (it might have just been an ALTER TABLE and the statement is valid after compiling it again). If that still fails, we promote the error to Python (cursor.c lines 605ff). ___ Python tracker [EMAIL PROTECTED] http://bugs.python.org/issue3846 ___ ___ Python-bugs-list mailing list Unsubscribe: http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
[issue3846] sqlite3 module: Improved concurrency
Gerhard Häring [EMAIL PROTECTED] added the comment: Interesting. I was smart enough to not document check_same_thread in the sqlite3 incarnation of the module. This has nothing to do with releasing/aquiring the GIL around sqlite3_prepare, though. Adding the macros was just an oversight. They're there for all other nontrivial SQLite API calls (sqlite3_step, sqlite3_reset, sqlite3_open, sqlite3_finalize and also already for the BEGIN/COMMIT and ROLLBACK versions of sqlite3_prepare in connection.c. ___ Python tracker [EMAIL PROTECTED] http://bugs.python.org/issue3846 ___ ___ Python-bugs-list mailing list Unsubscribe: http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
[issue3846] sqlite3 module: Improved concurrency
Gerhard Häring [EMAIL PROTECTED] added the comment: Just to be explicit: check_same_thread is unsupported and while it's undocumented in sqlite3, the old pysqlite docs say that when you use it, you have to make sure the connections/cursors are protected otherwise (via your own mutexes). [In the current pysqlite docs it's not documented at all at the moment, because I derived these from the Python sqlite3 ones] ___ Python tracker [EMAIL PROTECTED] http://bugs.python.org/issue3846 ___ ___ Python-bugs-list mailing list Unsubscribe: http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
[issue3846] sqlite3 module: Improved concurrency
Martin v. Löwis [EMAIL PROTECTED] added the comment: This has nothing to do with releasing/aquiring the GIL around sqlite3_prepare, though. You mean, it doesn't make things worse, but I believe they do (even if so only slightly). If you have two threads simultaneously attempting an execute on a cursor, one will run into the prepare. That thread will (now) release the GIL, allowing the second thread to run into the prepare. That thread will replace the statement object in the cursor, so when the first thread returns, the statement has changed underneath, and it will associate any return code with the incorrect statement object. In this form, this can't currently happen. Without detailed review, I can readily believe that there are many other cases where a False check_same_thread can give surprising results. They're there for all other nontrivial SQLite API calls (sqlite3_step, sqlite3_reset, sqlite3_open, sqlite3_finalize and also already for the BEGIN/COMMIT and ROLLBACK versions of sqlite3_prepare in connection.c. On the grounds that the code is already full of these GIL release calls, I'll approve this additional one. I encourage you to review the entire issue, though, and document somewhere what promises are made under what conditions. Then a later review can validate that the promises are really kept. ___ Python tracker [EMAIL PROTECTED] http://bugs.python.org/issue3846 ___ ___ Python-bugs-list mailing list Unsubscribe: http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
[issue3846] sqlite3 module: Improved concurrency
Changes by Martin v. Löwis [EMAIL PROTECTED]: -- resolution: - accepted ___ Python tracker [EMAIL PROTECTED] http://bugs.python.org/issue3846 ___ ___ Python-bugs-list mailing list Unsubscribe: http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
[issue3846] sqlite3 module: Improved concurrency
Gerhard Häring [EMAIL PROTECTED] added the comment: Thanks, Martin. Commited as r66414. ___ Python tracker [EMAIL PROTECTED] http://bugs.python.org/issue3846 ___ ___ Python-bugs-list mailing list Unsubscribe: http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
[issue3846] sqlite3 module: Improved concurrency
Gerhard Häring [EMAIL PROTECTED] added the comment: Issue3854 was created for documenting sqlite3 vs. threads. -- status: open - closed ___ Python tracker [EMAIL PROTECTED] http://bugs.python.org/issue3846 ___ ___ Python-bugs-list mailing list Unsubscribe: http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com