Repository navigation
sqlite3 SELECT does not BEGIN a transaction, but should according to spec #54133
Closed as not planned
Description
| BPO | 9924 |
|---|---|
| Nosy | @pitrou, @bitdancer, @dholth, @berkerpeksag, @palaviv, @erlend-aasland |
| Files |
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 = None
closed_at = None
created_at = <Date 2010-09-23.03:56:05.034>
labels = ['3.7', 'type-bug', 'library']
title = 'sqlite3 SELECT does not BEGIN a transaction, but should according to spec'
updated_at = <Date 2021-05-19.11:16:15.521>
user = 'https://bugs.python.org/zzzeek'bugs.python.org fields:
activity = <Date 2021-05-19.11:16:15.521>
actor = 'erlendaasland'
assignee = 'none'
closed = False
closed_date = None
closer = None
components = ['Library (Lib)']
creation = <Date 2010-09-23.03:56:05.034>
creator = 'zzzeek'
dependencies = []
files = ['42260']
hgrepos = []
issue_num = 9924
keywords = []
message_count = 7.0
messages = ['117167', '117168', '117180', '129128', '207794', '262284', '307684']
nosy_count = 11.0
nosy_names = ['ghaering', 'pitrou', 'rnortman', 'r.david.murray', 'zzzeek', 'dholth', 'berker.peksag', 'palaviv', 'rhunter', 'Allen Li', 'erlendaasland']
pr_nums = []
priority = 'normal'
resolution = None
stage = None
status = 'open'
superseder = None
type = 'behavior'
url = 'https://bugs.python.org/issue9924'
versions = ['Python 3.6', 'Python 3.7']
Activity
zzzeek commentedon Sep 23, 2010
Copying this bug from the pysqlite tracker, at http://code.google.com/p/pysqlite/issues/detail?id=21 , as the issue has been opened for two days with no reply. (side node - should sqlite3 bugs be reported here or on the pysqlite tracker ?) The text below was originally written by Randall Nortman:
Pysqlite does not open a transaction in the database until a DML statement is encountered (INSERT, UPDATE, or DELETE). A DQL (SELECT) statement will not cause a transaction to be opened if one is not already opened. This is the documented behavior, but it is not what is intended by the spec (PEP-249). The spec intends a transaction to always be open (per the spec author), and this is what happens in other DB-API drivers. For more information, see the this DB-SIG mailing list post (by the PEP-249 author):
http://mail.python.org/pipermail/db-sig/2010-September/005645.html
For additional background, see this thread on the SQLAlchemy mailing list, which is the source of the attached test case:
http://groups.google.com/group/sqlalchemy/browse_thread/thread/2f47e28c1fcdf9e6/0ef1666759ce0724#0ef1666759ce0724
What steps will reproduce the problem?
What is the expected output? What do you see instead?
The BEGIN should be issued implicitly, and even without doing it explicitly, the commit should block and then return the DB locked error.
What version of the product are you using? On what operating system?
Python 2.6.6 with its built-in sqlite3 module, on Debian Squeeze x86.
c1.execute("""create table foo (id integer primary key, data varchar(30))""")
c1.execute("insert into foo(id, data) values (1, 'data1')")
c1.close()
conn1.commit()
print "About to commit conn1..."
conn1.commit()
zzzeek commentedon Sep 23, 2010
My own comment here is that I'm supposing the "late BEGIN" behavior is to cut down on SQLite's file locking. I think a way to maintain that convenience for most cases, while allowing the stricter behavior that makes SERIALIZABLE isolation worthwhile, would be an option to sqlite3.connect() that moves the implicit BEGIN to before any DQL, not just DML, statement.
ghaering commentedon Sep 23, 2010
Yes Mike. Avoiding unnecessary locks was exactly the reason for this behaviour. I agree that for serializable transactions I'd need to make some changes.
dholth commentedon Feb 22, 2011
What should this option be called?
connect(strict=True) ?
zzzeek commentedon Jan 9, 2014
see also http://bugs.python.org/issue10740, which also relates to pysqlite attempting to make guesses as to when transactions should begin and end.
rhunter commentedon Mar 23, 2016
This bug can also lead to subtle and unintuitive "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"). I've attached code that demonstrates this issue.
I disagree that the current behavior cuts down on SQLite file locking. As soon as any SELECT statement is opened, an implicit lock is held by SQLite (whether it resides within a BEGIN block or not): https://www.sqlite.org/lockingv3.html
SQLite has been designed to do its own "late locking." Pysqlite does not need to duplicate this behavior.
This is a clear-as-day bug and should be fixed.
pitrou commentedon Dec 5, 2017
See https://bugs.python.org/issue32215 for what seems to be a related bug.
Also note that pysqlite now seems to be using a different logic:
https://github.com/ghaering/pysqlite/blob/master/src/cursor.c#L537-L548
Also this changeset:
ghaering/pysqlite@94eae50
As a sidenote, this seems to mean that the stdlib sqlite module doesn't receive updates anymore from its author...?
erlend-aasland commentedon Jun 21, 2022
As of the recent discussion on Discourse, I'm closing this as superseded by the proposed solution in gh-83638.
TL;DR: We cannot change the behaviour because of backwards compatibility. The proposed fix is to introducing a new, PEP 249 compliant way of controlling transactions. The old transaction handling (
isolation_level) will be deprecated.