Prev: dll in project?
Next: "Distributed" database in Python
From: Laszlo Nagy on 12 Mar 2010 03:35

> No it doesn't. The problem is that using a connection as a context
> manager doesn't do what you think.
>
> It does *not* start a new transaction on __enter__ and commit it on
> __exit__. As far as I can tell it does nothing on __enter__ and calls
> con.commit() or con.rollback() on exit. With isolation_level=None,
> these are no-ops.
>
Thank you Ryan! You are abolutely right, and thank you for reading the
source. Now everything works as I imagined.

The way the context manager and isolation_level works looks very very
strange to me. Here is a demonstration:

import sqlite3
def getconn():
conn = sqlite3.connect(':memory:')
conn.isolation_level = None
return conn
def main():
with getconn() as conn:
conn.execute("create table a ( i integer ) ")
try:
conn.execute("insert into a values (1)")
with conn:
conn.execute("insert into a values (2)")
raise Exception
except:
print "There was an error"
for row in conn.execute("select * from a"):
print row
main()


Output:

There was an error
(1,)
(2,)


Looks like the context manager did not roll back anything. If I remove
isolation_level=None then I get this:

There was an error

E.g. the context manager rolled back something that was executed outside
the context. I cannot argue with the implementation - it is that way.
But this is not what I would expect. I believe I'm not alone with this.

Using your connection manager, everything is perfect:

There was an error
(1,)


The only thing I have left is to implement a connection manager that
emulates nested transactions, using a stack of savepoints. :-)

Suggestions:

Just for clarity, we should put a comment at the end of the
documentation here:

http://docs.python.org/library/sqlite3.html#sqlite3-controlling-transactions

I would add at least these things:

#1. By using isolation_level = None, connection objects (used as a
context manager) WON'T automatically commit or rollback transactions.
#2. Using any isolation level, connection objects WON'T automatically
begin a transaction.
#3. Possibly, include your connection manager class code, to show how to
do it "the expected" way.

Also one should clarify in the documentation, what isolation_level does.
Looks like setting isolation_level to None is not really an "auto commit
mode". It is not even part of sqlite itself. It is part of the python
extension.

Thank you again.

Laszlo

From: Laszlo Nagy on 15 Mar 2010 03:38

> Annotating your example:
>
> # entering this context actually does nothing
> with conn:
> # a transaction is magically created before this statement
> conn.execute("insert into a values (1)")
> # and is implicitly committed before this statement
> conn.execute("SAVEPOINT sp1")
> # a new transaction is magically created
> conn.execute("insert into a values (2)")
> # and committed, discarding the first savepoint.
> conn.execute("SAVEPOINT sp2")
> # a new transaction is magically created
> conn.execute("insert into a values (3)")
> # and committed, discarding the very savepoint we are trying to use.
> conn.execute("ROLLBACK TO sp2")
> conn.execute("insert into a values (4)")
> conn.execute("RELEASE sp1")
>

We all know the Zen of Python. Explicit is better than implicit.

There is no point in using a savepoint outside a transaction. There is
no point in using a savepoint if it commits all previous changes
automatically.

Conclusion:

Sqlite's isolation_level is dark magic. It mixes real isolation levels
with behaviour of context managers, and automagical commits in the wrong
places.
Setting isolation_level=None is a must for anyone who want to do any
serious work with sqlite.

L



From: Ryan Kelly on 15 Mar 2010 03:53
On Fri, 2010-03-12 at 09:35 +0100, Laszlo Nagy wrote:
> > No it doesn't. The problem is that using a connection as a context
> > manager doesn't do what you think.
> >
> > It does *not* start a new transaction on __enter__ and commit it on
> > __exit__. As far as I can tell it does nothing on __enter__ and calls
> > con.commit() or con.rollback() on exit. With isolation_level=None,
> > these are no-ops.
> >
> Thank you Ryan! You are abolutely right, and thank you for reading the
> source. Now everything works as I imagined.

No problemo - isolation_level has given me my fair share of headaches in
the past, so I couldn't resist the opportunity to understand it a little
better.

> The way the context manager and isolation_level works looks very very
> strange to me. Here is a demonstration:
>
> import sqlite3
> def getconn():
> conn = sqlite3.connect(':memory:')
> conn.isolation_level = None
> return conn
> def main():
> with getconn() as conn:
> conn.execute("create table a ( i integer ) ")
> try:
> conn.execute("insert into a values (1)")
> with conn:
> conn.execute("insert into a values (2)")
> raise Exception
> except:
> print "There was an error"
> for row in conn.execute("select * from a"):
> print row
> main()
>
>
> Output:
>
> There was an error
> (1,)
> (2,)
>
>
> Looks like the context manager did not roll back anything.

Yes, because there were no transactions created so there was nothing to
roll back.

> If I remove
> isolation_level=None then I get this:
>
> There was an error
>
> E.g. the context manager rolled back something that was executed outside
> the context.

Yes, because the transactions created by the default isolation level do
not nest, so the rollback happens at outermost scope.

> I cannot argue with the implementation - it is that way.
> But this is not what I would expect. I believe I'm not alone with this.

That's at least two of us :-)

> Just for clarity, we should put a comment at the end of the
> documentation here:
>
> http://docs.python.org/library/sqlite3.html#sqlite3-controlling-transactions
>
> I would add at least these things:
>
> #1. By using isolation_level = None, connection objects (used as a
> context manager) WON'T automatically commit or rollback transactions.
> #2. Using any isolation level, connection objects WON'T automatically
> begin a transaction.
> #3. Possibly, include your connection manager class code, to show how to
> do it "the expected" way.
>
> Also one should clarify in the documentation, what isolation_level does.
> Looks like setting isolation_level to None is not really an "auto commit
> mode". It is not even part of sqlite itself. It is part of the python
> extension.

I think of it as almost the opposite - you have to set
isolation_level=None to get the unadulterated behaviour of the
underlying sqlite library.

I'm sure the devs would appreciate a documentation patch (submission
details at http://python.org/dev/patches/). I'm also pretty confident
that I won't have time to do one up anytime soon :-)


Good luck with your project!


Ryan

--
Ryan Kelly
http://www.rfk.id.au | This message is digitally signed. Please visit
ryan(a)rfk.id.au | http://www.rfk.id.au/ramblings/gpg/ for details

From: Laszlo Nagy on 12 Mar 2010 05:12

>> #1. By using isolation_level = None, connection objects (used as a
>> context manager) WON'T automatically commit or rollback transactions.
>> #2. Using any isolation level, connection objects WON'T automatically
>> begin a transaction.
>> #3. Possibly, include your connection manager class code, to show how to
>> do it "the expected" way.
>>
>> Also one should clarify in the documentation, what isolation_level does.
>> Looks like setting isolation_level to None is not really an "auto commit
>> mode". It is not even part of sqlite itself. It is part of the python
>> extension.
>>
>
> I think of it as almost the opposite - you have to set
> isolation_level=None to get the unadulterated behaviour of the
> underlying sqlite library.
>
> I'm sure the devs would appreciate a documentation patch (submission
> details at http://python.org/dev/patches/). I'm also pretty confident
> that I won't have time to do one up anytime soon :-)
>
Patch submitted.

http://bugs.python.org/issue8145


 | 
Pages: 1
Prev: dll in project?
Next: "Distributed" database in Python