Title: sqlite3 SELECT does not BEGIN a transaction, but should according to spec
Type: behavior Stage:
Components: Library (Lib) Versions: Python 3.7, Python 3.6
Status: open Resolution:
Dependencies: Superseder:
Assigned To: Nosy List: Allen Li, berker.peksag, dholth, ghaering, palaviv, pitrou, r.david.murray, rhunter, rnortman, zzzeek
Priority: normal Keywords:

Created on 2010-09-23 03:56 by zzzeek, last changed 2017-12-05 21:06 by pitrou.

File name Uploaded Description Edit rhunter, 2016-03-23 16:38
Messages (7)
msg117167 - (view) Author: mike bayer (zzzeek) * Date: 2010-09-23 03:56
Copying this bug from the pysqlite tracker, at , 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):

For additional background, see this thread on the SQLAlchemy mailing list, which is the source of the attached test case:

What steps will reproduce the problem?
1. See attached test case.  Run it as is, and the final conn1.commit() statement will complete successfully.
2. Uncomment the c2.execute("BEGIN") line and run again; this time conn1.commit() hangs until a timeout, then a "Database is locked" error is returned.

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.

import sqlite3
import os

if os.path.exists("file.db"):
conn1 = sqlite3.connect("file.db")

c1 = conn1.cursor()

c1.execute("PRAGMA read_uncommitted=SERIALIZABLE")

c1.execute("""create table foo (id integer primary key, data varchar(30))""")
c1.execute("insert into foo(id, data) values (1, 'data1')")

c1 = conn1.cursor()
c1.execute("select * from foo where id=1")
row1 = c1.fetchone()

conn2 = sqlite3.connect("file.db")
c2 = conn2.cursor()

c2.execute("PRAGMA read_uncommitted=SERIALIZABLE")

# sqlite3 should be doing this automatically.
# when called, conn1's commit blocks
c2.execute("select * from foo where id=1")
row2 = c2.fetchone()

c1 = conn1.cursor()
c1.execute("update foo set data='data2'")

print "About to commit conn1..."
msg117168 - (view) Author: mike bayer (zzzeek) * Date: 2010-09-23 04:02
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.
msg117180 - (view) Author: Gerhard Häring (ghaering) * (Python committer) Date: 2010-09-23 10:44
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.
msg129128 - (view) Author: Daniel Holth (dholth) Date: 2011-02-22 21:10
What should this option be called?

connect(strict=True) ?
msg207794 - (view) Author: mike bayer (zzzeek) * Date: 2014-01-09 21:13
see also, which also relates to pysqlite attempting to make guesses as to when transactions should begin and end.
msg262284 - (view) Author: Rian Hunter (rhunter) Date: 2016-03-23 16:38
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):

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.
msg307684 - (view) Author: Antoine Pitrou (pitrou) * (Python committer) Date: 2017-12-05 21:02
See for what seems to be a related bug.

Also note that pysqlite now seems to be using a different logic:
Also this changeset:

As a sidenote, this seems to mean that the stdlib sqlite module doesn't receive updates anymore from its author...?
Date User Action Args
2017-12-05 21:06:56pitrousetassignee: ghaering ->
versions: + Python 3.7, - Python 2.7, Python 3.2, Python 3.3, Python 3.4, Python 3.5
2017-12-05 21:06:41pitrousetnosy: + berker.peksag
2017-12-05 21:02:38pitrousetnosy: + pitrou, r.david.murray
messages: + msg307684
2017-12-02 08:22:53Allen Lisetnosy: + Allen Li
2017-01-20 09:11:22palavivsetnosy: + palaviv
2016-03-23 17:29:08berker.peksaglinkissue26625 superseder
2016-03-23 16:39:00rhuntersetversions: + Python 3.2, Python 3.5, Python 3.6
2016-03-23 16:38:51rhuntersetfiles: +
nosy: + rhunter
messages: + msg262284

2015-01-11 02:03:56ghaeringsetassignee: ghaering
2014-01-09 23:18:02pitrousetversions: + Python 3.3, Python 3.4, - Python 3.1, Python 3.2
2014-01-09 21:13:42zzzeeksetmessages: + msg207794
2011-02-22 21:10:54dholthsetnosy: + dholth
messages: + msg129128
2010-09-23 13:19:17rnortmansetnosy: + rnortman
2010-09-23 10:44:47ghaeringsetmessages: + msg117180
2010-09-23 08:10:35ned.deilysetnosy: + ghaering

versions: - Python 2.6, Python 2.5, Python 3.3
2010-09-23 04:02:36zzzeeksetmessages: + msg117168
2010-09-23 03:56:05zzzeekcreate