classification
Title: Documentation about sqlite3 isolation_level
Type: enhancement Stage: patch review
Components: Documentation Versions: Python 3.7, Python 3.6, Python 3.5, Python 2.7
process
Status: open Resolution:
Dependencies: Superseder:
Assigned To: docs@python Nosy List: Mariatta, berker.peksag, docs@python, ezio.melotti, fschulze, georg.brandl, ghaering, nagylzs, palaviv, r.david.murray
Priority: normal Keywords: easy, needs review, patch

Created on 2010-03-15 09:01 by nagylzs, last changed 2016-11-26 15:18 by palaviv.

Files
File name Uploaded Description Edit
sqlite3.rst.diff nagylzs, 2010-03-15 09:01 unified diff to http://svn.python.org/projects/python/branches/release26-maint/Doc/library/sqlite3.rst
sqlite_transaction_doc.patch r.david.murray, 2013-01-14 00:13
Messages (11)
msg101090 - (view) Author: Laszlo Nagy (nagylzs) Date: 2010-03-15 09:01
Clarify what isolation_level does, and how to use it, and why connections do not commit/rollback in some cases.

Details here:

http://mail.python.org/pipermail/python-list/2010-March/1239374.html

I'll paste code for ctx_manager_2.py here. This is a new file, I could not include it as a diff:

import sqlite3

class MyConn(sqlite3.Connection):
    def __enter__(self):
        self.execute("BEGIN")
        return self
    def __exit__(self,exc_type,exc_info,traceback):
        if exc_type is None:
            self.execute("COMMIT")
        else:
            self.execute("ROLLBACK")


conn = sqlite3.connect(':memory:',factory=MyConn)
conn.isolation_level = None
with conn:
    conn.execute("create table a ( i integer ) ")
    conn.execute("insert into a values (1)")
try:
    with conn:
        conn.execute("insert into a values (2)")
        conn.execute("savepoint sp1")
        conn.execute("insert into a values (3)")
        conn.execute("rollback to sp1")
        conn.execute("insert into a values (4)")
        print "Before rollback: 1,2,4"
        for row in conn.execute("select * from a"):
            print row[0] # prints 1,2,4
        raise Exception
except:
    pass
print "After rollback: 1"
for row in conn.execute("select * from a"):
    print row[0] # prints 1
msg109948 - (view) Author: Terry J. Reedy (terry.reedy) * (Python committer) Date: 2010-07-11 00:00
If the content of the patch is correct, it looks ready to apply, with only a touch of editing. Do we have a resident expert of sqlite3? Or Laszlo, do you have a reference for the statements made?
msg109965 - (view) Author: Laszlo Nagy (nagylzs) Date: 2010-07-11 05:29
2010/7/11 Terry J. Reedy <report@bugs.python.org>

>
> Terry J. Reedy <tjreedy@udel.edu> added the comment:
>
> If the content of the patch is correct, it looks ready to apply, with only
> a touch of editing. Do we have a resident expert of sqlite3? Or Laszlo, do
> you have a reference for the statements made?
>

Sorry, the only reference I have is that mailing list thread (
http://mail.python.org/pipermail/python-list/2010-March/1239374.html ).

There you can find an example with SQL statements, showing the problem.

Ryan Kelly wrote the following:

<quote>
I have a theory, based on a quick perusal of the sqlite3 bindings
source.

The bindings think that "SAVEPOINT sp1" is a "non-DML, non-query"
statement. So when isolation_level is something other than None, this
statement implicitly commits the current transaction and throws away
your savepoints!
</quote>

So the problem is with the sqlite binding code, not sqlite itself. I'm not
an expert in C, so I'm not sure I can help more.

Thanks,

   Laszlo
msg175909 - (view) Author: Ezio Melotti (ezio.melotti) * (Python committer) Date: 2012-11-18 19:32
Can someone review this patch?
(There is a typo: s/The/This/)
msg179686 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2013-01-11 14:57
I believe this patch is correct in essence, but I think it would be helpful to clarify the explanation of what autocommit mode is.  I'll work on an updated patch.

It could also be considered a bug that the standard context manager does not support autocommit mode, but I'm going to treat that as a separate bug.  Perhaps a fix will come out of fixing issue 10740.
msg179905 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2013-01-14 00:13
Here is a revised patch.  I am leaving out the changes relating to the transaction manager.  It turns out that the transaction manager doesn't do anything useful even if isolation_level is not None.  I'm going to open a new issue to discuss the best way to fix it, and any documentation changes relating to any leftover brokenness will be part of that issue.
msg179906 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2013-01-14 00:18
I misspoke, the transaction manager does do something useful in non-None isolation level.  I'm still going to open a bug about isolation_level None.
msg179910 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2013-01-14 00:47
Opened issue 16958 for the transaction manager problem.
msg273722 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2016-08-26 19:35
sqlite_transaction_doc.patch looks good to me and applies cleanly to current default branch. One minor note: It would be nice to document that the default mode is "BEGIN DEFERRED" in Connection.isolation_level documentation.
msg281710 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2016-11-25 12:40
The patch needs to be updated to address changes in issue 10740 so I'm moving the stage field back to 'patch review'.
msg281716 - (view) Author: Florian Schulze (fschulze) Date: 2016-11-25 13:21
The documentation patch is a definite improvement.
History
Date User Action Args
2016-11-26 15:18:23palavivsetnosy: + palaviv
2016-11-25 18:24:28terry.reedysetnosy: - terry.reedy
2016-11-25 13:21:46fschulzesetnosy: + fschulze
messages: + msg281716
2016-11-25 12:40:53berker.peksagsetstage: commit review -> patch review
messages: + msg281710
versions: + Python 3.7
2016-10-02 22:57:36Mariattasetnosy: + Mariatta
2016-08-26 19:35:37berker.peksagsetversions: + Python 3.5, Python 3.6, - Python 3.2, Python 3.3, Python 3.4
nosy: + berker.peksag

messages: + msg273722

stage: patch review -> commit review
2013-01-14 00:47:22r.david.murraysetmessages: + msg179910
2013-01-14 00:18:51r.david.murraysetmessages: + msg179906
2013-01-14 00:13:56r.david.murraysetfiles: + sqlite_transaction_doc.patch

messages: + msg179905
2013-01-11 14:57:54r.david.murraysetmessages: + msg179686
2013-01-10 18:46:11r.david.murraysetnosy: + r.david.murray
2012-11-18 19:32:51ezio.melottisetversions: + Python 3.3, Python 3.4, - Python 3.1
nosy: + ezio.melotti

messages: + msg175909

keywords: + easy, needs review
2010-12-20 19:27:07merwoksetfiles: - unnamed
nosy: georg.brandl, terry.reedy, ghaering, nagylzs, docs@python
2010-08-07 20:17:30terry.reedysetstage: patch review
versions: - Python 2.6
2010-07-11 05:29:53nagylzssetfiles: + unnamed

messages: + msg109965
2010-07-11 00:00:16terry.reedysetversions: + Python 3.1, Python 2.7, Python 3.2
nosy: + terry.reedy, docs@python

messages: + msg109948

assignee: georg.brandl -> docs@python
2010-03-16 14:01:34ghaeringsetnosy: + ghaering
2010-03-15 09:01:44nagylzscreate