[sqlalchemy] Re: how do I set an engine to autocommit?
On Feb 1, 3:02 pm, Kumar McMillan [EMAIL PROTECTED] wrote: I have a program that is entering a deadlock (pyscopg) after an exception since the test suite tries to delete stuff as part of cleanup. I tried wrapping everything in a transaction to be sure a rollback is called but it *appears* that when I start using transaction.session.bind_to.execute(stmt) directly, it is not part of the transaction. uh yeah...why would it be ? youre essentially pulling out the original bind_to engine which has no idea about what particular connection/transaction is used by the SessionTransaction. unless youre playing with the threadlocal engine (which i would advise is not worth it), why not do what the docs say ? http://www.sqlalchemy.org/docs/unitofwork.myt#unitofwork_transaction_sql All I'm doing are selects anyway (this may be why the transaction is ignored too) so I really don't need a transaction to begin with. How do I set an engine of a session to autocommit? any execute() that you call from an engine or connection without an associated transaction will call commit for you, *if* you did something like an INSERT/UPDATE/DELETE/etc. it does not call commit if you just call a select. it actually looks at the string you executed to determine this. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: how do I set an engine to autocommit?
...but it *appears* that when I start using transaction.session.bind_to.execute(stmt) directly, it is not part of the transaction. uh yeah...why would it be ? youre essentially pulling out the original bind_to engine which has no idea about what particular connection/transaction is used by the SessionTransaction. I misunderstood that about how transactions work. Explicitly adding a connection makes sense (but was not intuitive). http://www.sqlalchemy.org/docs/unitofwork.myt#unitofwork_transaction_sql thanks for the link, I completely missed that; thought I'd looked everywhere. _ the fact that the deadlock was solved when I added the connection to the transaction tells me that psycopg runs in commit mode automatically (even though dbapi 2 says it should not). The psycopg docs reinforce this too : http://www.initd.org/tracker/psycopg/wiki/psycopg2_documentation#setting-transaction-isolation-levels this is a little annoying because deadlocks are hard to track down and there is still no way [that I can see] to tell sqlalchemy in an agnostic way : autocommit=False for my engine. I would think this should be allowed like create_engine(dsn, autocommit=1) or as a query string arg in the dsn. Anyway, I can live with forcing everything in a transaction but it is definitely a workaround since all I'm doing are selects. thanks, Kumar --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: how do I set an engine to autocommit?
On 2/1/07, Kumar McMillan [EMAIL PROTECTED] wrote: the fact that the deadlock was solved when I added the connection to the transaction tells me that psycopg runs in commit mode automatically (even though dbapi 2 says it should not). The psycopg docs reinforce this too : http://www.initd.org/tracker/psycopg/wiki/psycopg2_documentation#setting-transaction-isolation-levels Wrong; psycopg2 does not autocommit by default. It automatically _creates new transactions_ but you still have to explicitly commit() or rollback(). This is what DBAPI2 calls for (and is really the only sane thing to do in non-autocommit mode if you think about it). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: how do I set an engine to autocommit?
heh, I was saying it backwards [lack of sleep] but this is what I was referring to ... Note that if the database supports an auto-commit feature, this must be initially off. An interface method may be provided to turn it back on. - http://www.python.org/dev/peps/pep-0249/ ...yes psycopg complies, but sqlalchemy does not expose its interface method to turn autocommit functionality back on. Am I missing it somewhere? I don't see this exposed anywhere in the psycopg dialect nor create_engine(). Deadlocks are not fun to debug. On 2/1/07, Jonathan Ellis [EMAIL PROTECTED] wrote: On 2/1/07, Kumar McMillan [EMAIL PROTECTED] wrote: the fact that the deadlock was solved when I added the connection to the transaction tells me that psycopg runs in commit mode automatically (even though dbapi 2 says it should not). The psycopg docs reinforce this too : http://www.initd.org/tracker/psycopg/wiki/psycopg2_documentation#setting-transaction-isolation-levels Wrong; psycopg2 does not autocommit by default. It automatically _creates new transactions_ but you still have to explicitly commit() or rollback(). This is what DBAPI2 calls for (and is really the only sane thing to do in non-autocommit mode if you think about it). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---