sqlite savepoint problem (was: Re: sqlite3 is sqlite 2?)

[Ryan Kelly]

On Fri, 2010-03-12 at 07:46 +0100, Laszlo Nagy wrote:
> >
>  >>> import sqlite3
>  >>> conn = sqlite3.connect(':memory:')
>  >>> with conn:
> ...     conn.execute("BEGIN")
> ...     conn.execute("create table a ( i integer)")
> ...     conn.execute("insert into a values (1)")
> ...     conn.execute("savepoint sp1")
> ...     conn.execute("insert into a values(2)")
> ...     conn.execute("release sp1")
> ...     conn.execute("COMMIT")
> ...
> <sqlite3.Cursor object at 0xb7e29b30>
> <sqlite3.Cursor object at 0xb7e29b60>
> <sqlite3.Cursor object at 0xb7e29b30>
> <sqlite3.Cursor object at 0xb7e29b60>
> <sqlite3.Cursor object at 0xb7e29b30>
> Traceback (most recent call last):
>   File "<stdin>", line 7, in <module>
> sqlite3.OperationalError: no such savepoint: sp1
>  >>>
>
> The syntax is correct: http://www.sqlite.org/lang_savepoint.html
> The savepoint was really created.
> But I get this error, telling "no such savepoint". What is wrong here? 
> Maybe it has to do something with transaction isolation? :-s

From memory you can't issue a "CREATE TABLE" statement inside a
transaction, at least not at the default isolation level.  Such a
statement will automatically commit the current transaction.  Doesn't
help with your current problem but worth pointing out :-)

When debugging strange transaction behaviour, I find it easiest to
create the connection with isolation_level=None so that are no implicit
transactions being created behind your back.  Not sure why, but setting
this makes your example work for me.

  Ryan




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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 197 bytes
Desc: This is a digitally signed message part
URL: <http://mail.python.org/pipermail/python-list/attachments/20100315/7e3a0fcf/attachment.sig>