[New-bugs-announce] [issue9924] sqlite3 SELECT does not BEGIN a transaction, but should according to spec

[mike bayer]

New submission from mike bayer <mike_mp at zzzcomputing.com>:

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?
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"):
    os.unlink("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.close()
conn1.commit()

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

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("BEGIN")
c2.execute("select * from foo where id=1")
row2 = c2.fetchone()
c2.close()

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

print "About to commit conn1..."
conn1.commit()

----------
components: Library (Lib)
messages: 117167
nosy: zzzeek
priority: normal
severity: normal
status: open
title: sqlite3 SELECT does not BEGIN a transaction, but should according to spec
type: behavior
versions: Python 2.5, Python 2.6, Python 2.7, Python 3.1, Python 3.2, Python 3.3

_______________________________________
Python tracker <report at bugs.python.org>
<http://bugs.python.org/issue9924>
_______________________________________