[New-bugs-announce] [issue16958] The sqlite3 context manager does not work with isolation_level=None

[R. David Murray]

New submission from R. David Murray:

Its operation is also not particularly intuitive if isolation_level is not None, so its documentation needs some clarification.

Currently the transaction manager does nothing on enter, and does a commit or rollback on exit, depending on whether or not there was an exception inside the with block.  With isolation_level set to None, the sqlite3 library is in autocommit mode, so changes will get committed immediately inside the with, which is simply broken.

If isolation_level is not None, then the behavior of the transaction manager depends heavily on what happens inside the with block.  If the with block contains only the defined DQL statements (insert, update, delete, replace) and select statements, then things will work as expected.  However, if another statement (such as a CREATE TABLE or a PRAGMA) is included in the with block, an intermediate commit will be done and a new transaction started.

I propose to do two things to fix this issue: explain the above in the transactions manager docs, and have the context manager check to see if we are in isolation_level None, and if so, issue a begin (and then document that as well).

One question is, can the fix be backported?  It will change the behavior of code that doesn't throw an error, but most such code won't be doing what the author expected (run the with block inside a transaction...in pure autocommit mode the transaction manager is a no-op).  One place code could break is if someone figured out this issue and worked around it by explicitly starting a transaction before (or after) entering the with block.  In this case they would now get an error that a transaction cannot be started inside another.  I would think this is unlikely...the more obvious workaround would be to write a custom transaction manager, so I suspect that that is what is actually in the field.  But that's a (hopeful :) guess.

A fix for this problem would be to use 'savepoint' instead of 'begin' if the sqlite3 version supports it (it is apparently supported as of 3.6.8).

So, I'd like to see the fix, conditionally using SAVEPOINT, (once it written and tested) applied to all active python versions, but am open to the argument that it shouldn't be.

----------
components: Extension Modules, Library (Lib)
keywords: easy
messages: 179909
nosy: ghaering, r.david.murray
priority: normal
severity: normal
stage: needs patch
status: open
title: The sqlite3 context manager does not work with isolation_level=None
type: behavior
versions: Python 2.7, Python 3.2, Python 3.3, Python 3.4

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