[odb-users] Permanent failure: Cannot start a transaction within a transaction

Boris Kolpackov boris at codesynthesis.com
Fri Aug 30 10:44:36 EDT 2013


Hi Kai,

Kai Nickel <nickel at videmo.de> writes:

> As expected I get the following exception:
> 
>     19: foreign key constraint failed
> 
> So far so good. But now the problem is: whatever operations I do on
> the database from that point on, I _always_ get the following
> exception:
> 
>     1: cannot start a transaction within a transaction
> 
> So it seems as if the insert with the wrong key did not properly
> terminate the transaction...?

Actually the exception is thrown by the common() call, not persist(),
since the constraint is deferred. And I think I know what happens:
when ODB issues COMMIT, SQLite detects the failed constraint and
returns an error. At this point ODB assume that since COMMIT failed,
the transaction has been automatically rolled back. But this appears
not to be the case in SQLite. The transaction is still going strong!

Luckily, the sqlite3_get_autocommit() function allows us to figure
out whether we are in a transaction or not. I, however, will still
have to think some more about how to properly fix this. For now
I lean towards just not marking the (ODB) transaction instance as
finalized and let the standard rollback-on-destruction semantics
take care of it. I will let you know when I have a fix ready to try.
And thanks for reporting this!

BTW, it seems someone else had exactly the same problem but using a
completely different database access API:

http://stackoverflow.com/questions/16429727/deferred-foreign-key-does-not-flush-changes-to-database

I guess I am not the only one caught off guard by the esoteric SQLite
semantics.

Boris



More information about the odb-users mailing list