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

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?

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

Can someone review this patch?
(There is a typo: s/The/This/)

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.

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.

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.

Opened issue 16958 for the transaction manager problem.