On Thu, 29 Jul 2010 15:18:33 +0000 (UTC) Faheem Mitha <fah...@email.unc.edu> wrote:
> On Thu, 29 Jul 2010 11:36:43 +0100, King Simon-NFHD78 > <simon.k...@motorola.com> wrote: > > > You can tell meta.create_all() to use the same underlying DB > > connection as the session by using the session.connection() method > > with the 'bind' parameter to create_all(). > > > > Ie. > > > > connection = session.connection() > > meta.create_all(bind=connection) > > > > See the docs at > > http://www.sqlalchemy.org/docs/session.html#using-sql-expressions-with-s > > essions and > > http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html#sqlalche > > my.schema.MetaData.create_all > > > > Hope that helps, > > Hi Simon, > > Thanks. Do you understand why this blocking takes place? I assume by > default create_all tries to make a different connection, and fails for > some reason? > > Regards, Faheem. On PostgreSQL what's probably happening is that the connection that the session object is using is IDLE IN TRANSACTION but is holding some lock, probably RowExclusiveLock from uncommitted UPDATE/INSERT/DELETEs. Then, create_all(), when executed on a different connection, enters into a lock wait because virtually all DDL changes automatically acquire AccessExclusiveLock, which conflicts with locks the session is holding. The blocking is because the DDL connection cannot commit until the lock conflict is resolved, by either the session's connection rolling back or committing (causing the DDL-containing transaction to commit or abort, respectively). Here's a PostgreSQL wiki page with an good query for troubleshooting lock waits: http://wiki.postgresql.org/wiki/Lock_dependency_information Also, if you use pgAdminIII, it has an excellent lock monitor tool built in. I suggest doing a run of your application such that it gets blocked in create_all(), and then while it's blocked, check to see what lock is blocking it using the above linked query. Hope this helps.. -Kyle -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
