Menu
I have very simple code that cause my MySQL db to hang:
import sqlalchemy as sa
from sqlalchemy import orm
# creating the engine, the base, etc
import utils
import config
utils.base_init(config)
Base = config.Base
class Parent(Base):
__tablename__ = 'Parents'
id = sa.Column(sa.Integer, primary_key=True)
children = orm.relationship('Child', backref='parent')
class Child(Base):
id = sa.Column(sa.Integer, primary_key=True)
parent_id = sa.Column(sa.Integer)
__tablename__ = 'Children'
__table_args__ = (sa.ForeignKeyConstraint(
['parent_id'],
['Parents.id'],
onupdate='CASCADE', ondelete='CASCADE'),{})
Base.metadata.create_all()
session = orm.sessionmaker(bind=config.Base.metadata.bind)()
p = Parent(id=1)
c1 = Child(id=1)
c2 = Child(id=2)
session.add(p)
session.add(c1)
session.add(c2)
session.commit()
# Works
# Base.metadata.drop_all()
c1.parent
# 2012-08-17 20:16:21,459 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
# 2012-08-17 20:16:21,460 INFO sqlalchemy.engine.base.Engine SELECT `Children`.id AS `Children_id`, `Children`.parent_id AS `Children_parent_id`
# FROM `Children`
# WHERE `Children`.id = %s
# 2012-08-17 20:16:21,460 INFO sqlalchemy.engine.base.Engine (1,)
Base.metadata.drop_all()
# hangs until i kill the connection above.
# server status: 'Waiting for table metadata lock'
It looks like SQL Alchemy doesn't release a metadata lock after issuing the select query needed to load a relationship attribute? How can i get it to release it? I don't even understand why a select statement would need to lock the table in the first place!
Of course, I can get this specific piece of code to work by closing the session, but that isn't practical in my actual program.
960
Lazy loading refers to objects are returned from a query without the related objects loaded at first. When the given collection or reference is first accessed on a particular object, an additional SELECT statement is emitted such that the requested collection is loaded.
The create_engine() method of sqlalchemy library takes in the connection URL and returns a sqlalchemy engine that references both a Dialect and a Pool, which together interpret the DBAPI's module functions as well as the behavior of the database.
The grouping is done with the group_by() query method, which takes the column to use for the grouping as an argument, same as the GROUP BY counterpart in SQL. The statement ends by calling subquery() , which tells SQLAlchemy that our intention for this query is to use it inside a bigger query instead of on its own.
SQLAlchemy considers the database to be a relational algebra engine, not just a collection of tables. Rows can be selected from not only tables but also joins and other select statements; any of these units can be composed into a larger structure. SQLAlchemy's expression language builds on this concept from its core.
You need to start a new transaction before the .drop_all() call; MySQL sees you reading from the table in this transaction, and locks the table against being dropped:
session.commit()
Base.metadata.drop_all()
Committing a transaction implicitly begins a new transaction.
MySQL makes guarantees about transaction isolation; your transaction will read consistent data and won't see changes committed by other transactions until you start a new transaction. A DROP TABLE statement however makes it impossible for MySQL to keep these guarantees so the table is being locked.
Alternatively, you could alter the transaction isolation level, telling MySQL you don't care about the isolation guarantees. Because session connections are pooled, this can only be done for all connections or none at all; use the isolation_level argument to create_engine():
engine = create_engine(
'mysql://username:passwd@localhost/databasename',
isolation_level='READ UNCOMMITTED')
See the SET TRANSACTION documentation for details about each isolation level.
105
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
