Long running transaction issues with commit

[Aalok Sood]

Hello Everyone

My mysql server wait_timeout is set to 35.
and if i run this code:

# Session s made with autocommit=False
mm=s.query(ss.Machine).get(1)

In [9]:

In [10]: for i in range(1000):
....: sleep(15)
....: print "commiting"
....: s.commit()
....: sleep(25)
....: mm.name=u'new name'
....: print "commiting2"
....: s.commit()

Even though the second sleep is only for 25 seconds, I see an error
while commiting which says
'Mysql server has gone away'

The SQLAlchemy documentation says that a new transaction is begun on a
commit(). If that is the case, I should not see the above error.
Maybe its an issue with commiting w/o any changes to the loaded
instances.

Can anyone throw some light on this.
Any help would be much appreciated.

[Michael Bayer]

The Session doesn't interact with the database until statements are first emitted, so while its being put into a new transaction each time with your block of code, probably nothing is being sent to the DB. If you stuck a line: s.execute("select 1") in there, that would likely wake it up.

> --
> 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.
>

[Aalok Sood]

Hello Michael

Thanks a lot for your quick response.
I changed this loop to:

or i in range(1000):
....: sleep(15)
....: print "commiting"

....: mm.name=u'old name'

....: s.commit()
....: sleep(25)
....: mm.name=u'new name'
....: print "commiting2"
....: s.commit()

That is to say, I always have something in s.dirty when i commit. This
makes session interact with DB for sure. This loop works fine w/o any
exceptions.
So what you say will definitely solve the issue. However my doubts are
more theoretical in nature.

SQLAlchemy documentation says that if autocommit=False, on every
commit(), a new transaction is begun. So even if I do not have any
dirty changes and session need not send any data to the DB, still it
would have to contact the DB to begin a new transaction.
The behaviour as we saw, however, does not comply with this logic.
Can you please tell me where the loophole in my understanding of the
whole thing is.

Once again, Thanks a lot for your help. I really appreciate it.

Regards
Aalok Sood

[Michael Bayer]

The confusion here is that the documentation for "commit", which I'm assuming you're reading is http://www.sqlalchemy.org/docs/orm/session.html#committing, refers to the term "transaction", which, while it's not deeply described at that point for the sake of clarity, is not the same thing as an actual transaction on an individual DBAPI connection connected to a database across a network.

An in-depth description of what "transaction" means with regards to the session is later down the page at http://www.sqlalchemy.org/docs/orm/session.html#managing-transactions , pretty much the first paragraph.

To restate, a "transaction" with regards to the Session is a boundary which will be terminated upon the next call to commit() or rollback(). The transaction itself is an in-memory structure which includes a collection of zero or more DBAPI connections. When the transaction is committed or rolled back, the commit() or rollback() method is ultimately called upon each DBAPI connection that is present. A DBAPI connection becomes present in the current "transaction" when it is first asked to participate in a statement execution. For those engines for which no statement execution has occurred within the scope of the Session's "transaction", no DBAPI connection is procured and no network communication occurs.

Hope this clears it up !

[Aalok Sood]

Yes its all so very clear now, kind of obvious :)
Thank you for taking out time to help me.

Regards
Aalok Sood.

> The confusion here is that the documentation for "commit", which I'm assuming you're reading ishttp://www.sqlalchemy.org/docs/orm/session.html#committing, refers to the term "transaction", which, while it's not deeply described at that point for the sake of clarity, is not the same thing as an actual transaction on an individual DBAPI connection connected to a database across a network.
>
> An in-depth description of what "transaction" means with regards to the session is later down the page athttp://www.sqlalchemy.org/docs/orm/session.html#managing-transactions, pretty much the first paragraph.