SQLite rollback() fails

[Gombas, Gabor]

Hi,

 

I’ve run into a rather interesting issue, where calling session.rollback() dies. The error I get with SQLA 0.7 is:

 

<class 'sqlalchemy.exc.InvalidRequestError'>: Can't attach instance <Foo at 0x2acf68808710>; another instance with key (<class 'module.Foo'>, (342,)) is already present in this session.

 

Traceback:

 

       sqlalchemy-rel_0_7/lib/sqlalchemy/orm/session.py:672:rollback

       sqlalchemy-rel_0_7/lib/sqlalchemy/orm/session.py:386:rollback

       sqlalchemy-rel_0_7/lib/sqlalchemy/orm/session.py:417:_rollback_impl

       sqlalchemy-rel_0_7/lib/sqlalchemy/orm/session.py:274:_restore_snapshot

       sqlalchemy-rel_0_7/lib/sqlalchemy/orm/session.py:1620:_update_impl

       sqlalchemy-rel_0_7/lib/sqlalchemy/orm/session.py:1648:_attach

 

0.8 gives a slightly different message, but I think it is essentially the same:

 

<type 'exceptions.AssertionError'>: A conflicting state is already present in the identity map for key (<class 'module.Foo'>, (342,))

 

Traceback:

 

        sqlalchemy-default/lib/sqlalchemy/orm/session.py:637:rollback

        sqlalchemy-default/lib/sqlalchemy/orm/session.py:346:rollback

        sqlalchemy-default/lib/sqlalchemy/orm/session.py:377:_rollback_impl

        sqlalchemy-default/lib/sqlalchemy/orm/session.py:233:_restore_snapshot

        sqlalchemy-default/lib/sqlalchemy/orm/session.py:1578:_update_impl

        sqlalchemy-default/lib/sqlalchemy/orm/identity.py:115:add

 

What I see when turning on query logging:

 

-          The Foo object with primary key 342 is loaded (using a non-primary mapper, if that makes any difference), and then deleted. The session is flushed.

-          A new object is created (this time using the primary mapper), and during INSERT, SQLite assigns the same primary key to it as the previously deleted object had (I can see that from subsequent SELECT statements referencing the new object; it would be nice if SQLA logged the primary key after an INSERT at debug level)

-          An application error (unrelated to SQLAlchemy) occurs, which causes rollback() to be called

 

I have a reliable reproducer, but it is the unit test of a large application, so there are lots of possibly unrelated things going on. I’ve spent two days coming up with a standalone reproducer, but I’ve failed, that’s why I’m sending this mail instead of opening a ticket.

 

Any ideas?

 

Gabor

---

NOTICE: Morgan Stanley is not acting as a municipal advisor and the opinions or views contained herein are not intended to be, and do not constitute, advice within the meaning of Section 975 of the Dodd-Frank Wall Street Reform and Consumer Protection Act. If you have received this communication in error, please destroy all electronic and paper copies and notify the sender immediately. Mistransmission is not intended to waive confidentiality or privilege. Morgan Stanley reserves the right, to the extent permitted under applicable law, to monitor electronic communications. This message is subject to terms available at the following link: http://www.morganstanley.com/disclaimers. If you cannot access these links, please notify us by reply message and we will send the contents to you. By messaging with Morgan Stanley you consent to the foregoing.

[Michael Bayer]

On Oct 3, 2012, at 5:21 AM, Gombas, Gabor wrote:

Hi,

 

I’ve run into a rather interesting issue, where calling session.rollback() dies. The error I get with SQLA 0.7 is:

 

 

What I see when turning on query logging:

 

-          The Foo object with primary key 342 is loaded (using a non-primary mapper, if that makes any difference), and then deleted. The session is flushed.

-          A new object is created (this time using the primary mapper), and during INSERT, SQLite assigns the same primary key to it as the previously deleted object had (I can see that from subsequent SELECT statements referencing the new object; it would be nice if SQLA logged the primary key after an INSERT at debug level)

-          An application error (unrelated to SQLAlchemy) occurs, which causes rollback() to be called

 

I have a reliable reproducer, but it is the unit test of a large application, so there are lots of possibly unrelated things going on. I’ve spent two days coming up with a standalone reproducer, but I’ve failed, that’s why I’m sending this mail instead of opening a ticket.

So when the rollback goes to "restore the snapshot", it goes through an aggregated ".new" collection for every INSERT that's happened in the transaction, and expunges these objects - that is, removes them from the identity map.  Then it goes through everything that was DELETEd in the transaction by going through an aggregated ".deleted" collection for every DELETE that's happened in the transaction, and restores them to their former state in the identity map.   The assumption here is that only way a key in the identity map would be replaced would be the DELETE via session + subsequent INSERT via session.

So let me just note that this has worked without anyone reporting an issue for over three years.   But now that you're showing me this, I can confirm there are two additional paths where a conflicting identity might be in the map, for which we will need to add some special handling.

Starting like this:

s = Session(e)

a1 = A(id=1, data='a1')

a2 = A(id=2, data='a2')

s.add(a1)

s.add(a2)

s.commit()

If we first delete one of the rows:

s.delete(a1)

s.flush()

Then we UPDATE another row to point to that PK, it's unhandled, this will blow it up:

a2.id = 1

s.flush()

The other, if an INSERT occurred that the Session was unaware of:

s.execute("insert into a (id, data) values (1, 'a3')")

a3 = s.query(A).get(1)

Catching both of these is easy enough and a patch is added to #2583: http://www.sqlalchemy.org/trac/ticket/2583

The SQLite case you describe isn't exactly like either of these; if the object were flushed by the session and received the same PK, that case is already handled since the object would be in ._new.  So my only guess is that, that's not the trigger of the issue.    So at the very least you need to try that patch and see if it clears things up.

Assuming that's the fix, I don't know that there's a clean way to patch this in, except for patching your install or monkeypatching a new Session class in.     I should have it committed in both 0.7/0.8 within 30, unless I hit some snags in the tests.

[Michael Bayer]

On Oct 3, 2012, at 10:34 AM, Michael Bayer wrote:

Catching both of these is easy enough and a patch is added to #2583: http://www.sqlalchemy.org/trac/ticket/2583

The SQLite case you describe isn't exactly like either of these; if the object were flushed by the session and received the same PK, that case is already handled since the object would be in ._new.  So my only guess is that, that's not the trigger of the issue.    So at the very least you need to try that patch and see if it clears things up.

Assuming that's the fix, I don't know that there's a clean way to patch this in, except for patching your install or monkeypatching a new Session class in.     I should have it committed in both 0.7/0.8 within 30, unless I hit some snags in the tests.

OK those are in tip for default + 0.7, so you can get those fixes now from the snapshot releases.    

[Gombas, Gabor]

I’ve tested the 0.7 & 0.8 snapshots, and both work fine now. Thanks!

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.