sqlite savepoint problem [solved]

[Laszlo Nagy]

> 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