classification
Title: SELECT-initiated transactions can cause "database is locked" in sqlite
Type: behavior Stage: resolved
Components: Library (Lib) Versions: Python 3.6, Python 3.2, Python 3.3, Python 3.4, Python 3.5, Python 2.7
process
Status: closed Resolution: duplicate
Dependencies: Superseder: sqlite3 SELECT does not BEGIN a transaction, but should according to spec
View: 9924
Assigned To: Nosy List: rhunter
Priority: normal Keywords:

Created on 2016-03-23 16:28 by rhunter, last changed 2016-03-23 17:29 by berker.peksag. This issue is now closed.

Files
File name Uploaded Description Edit
unintuitive_sqlite_behavior.py rhunter, 2016-03-23 16:28
Messages (2)
msg262282 - (view) Author: Rian Hunter (rhunter) Date: 2016-03-23 16:28
When a transaction starts with a SELECT statement this can invoke a "database is locked" error if the SELECT statement is not exhausted or explicitly closed.

This can lead to subtle "database is locked" bugs, even when a large timeout is set on the connection. Many, many people are affected by this bug (search the web for "python sqlite database is locked").

The attached code demonstrates this bug and possible (unintuitive) fixes. The best workaround is to "explicitly" start a transaction in these cases by issuing a dummy DML statement. This seems very clumsy.

My proposed fix is to implicitly open a transaction before all non-DDL statements (including SELECT statements), not just DML statements.

If there won't be a fix soon, then at least the documentation should note this quirky behavior.
msg262283 - (view) Author: Rian Hunter (rhunter) Date: 2016-03-23 16:31
duplicate of #9924
History
Date User Action Args
2016-03-23 17:29:08berker.peksagsetsuperseder: sqlite3 SELECT does not BEGIN a transaction, but should according to spec
stage: resolved
2016-03-23 16:31:37rhuntersetstatus: open -> closed
resolution: duplicate
messages: + msg262283
2016-03-23 16:28:34rhuntercreate