SQLAlchemy Core插入使得（金字塔）范围的会话挂起(SQLAlchemy Core inserts make (Pyramid) scoped session hanging)

 我有很多子对象要添加到父SQA对象，有时10,000左右。 代码在Pyramid应用程序中运行，该应用程序使用作用域会话和ZopeTransactionExtension ： 
 
DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
 
 这没有问题： 
 
lines = [{'col1':1, 'col2':2, 'related_id':1}, ...]
for d in lines:
    d['related_id'] = related_id
    f = SQAObject(**d)
    DBSession.add(f)
 
 为了性能，我使用SQLAlchemy Core insert重写了这个： 
 
lines = [{'col1':1, 'col2':2, 'related_id':1}, ...]
rbrf_table = SQAObject.__table__
eng = DBSession.get_bind()
conn = eng.connect()
for d in lines:
    d['related_id'] = related_id
conn.execute(rbrf_table.insert().values(lines))
conn.close()
eng.dispose()
 
 这会导致一个相当奇怪的问题：代码执行（更快），但HTTP请求完成后，结果不会返回给HTTP客户端。 相反，Python进程会无限期地持续占用100％的CPU（我等了半个小时，以确定它是否在任何时间完成）。 
 
 我检查了Postgres锁，以确保它们不会阻止它以及活动（ select * from pg_stat_activity ），但似乎没有任何异常。 
 
 可能是这个问题的原因是什么？ 
 
 PS怀疑配置一个引擎可能会导致这个问题，我运行相同的代码有和没有eng.dispose() ，具有相同的结果。 
 
 UPDATE 
 
 情节变浓了。 如果我执行单个插入，它可以工作： 
 
rbrf_table = RTABomRowFile.__table__
eng = DBSession.get_bind()
conn = eng.connect()
for d in lines:
    ...
    ins = rbrf_table.insert().values(d)
    conn.execute(ins)
conn.execute('COMMIT')
conn.close()
eng.dispose()

I have lots of child objects to add to a parent SQA object, sometimes 10,000 or so. The code runs in Pyramid app, which makes use of scoped session and ZopeTransactionExtension:
 
DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
 
This works without a problem:
 
lines = [{'col1':1, 'col2':2, 'related_id':1}, ...]
for d in lines:
    d['related_id'] = related_id
    f = SQAObject(**d)
    DBSession.add(f)
 
For performance I rewrote this using SQLAlchemy Core inserts:
 
lines = [{'col1':1, 'col2':2, 'related_id':1}, ...]
rbrf_table = SQAObject.__table__
eng = DBSession.get_bind()
conn = eng.connect()
for d in lines:
    d['related_id'] = related_id
conn.execute(rbrf_table.insert().values(lines))
conn.close()
eng.dispose()
 
This causes a rather weird problem: the code executes (faster), but after HTTP request is finished, the result is not returned to HTTP client. Instead the Python process keeps consuming 100% CPU indefinitely (I waited half an hour to make sure if it completes in any time).
 
I checked Postgres locks to make sure they do not block it as well as activity (select * from pg_stat_activity), but there does not seem to be anything unusual.
 
What might be the cause of this problem?
 
P.S. Suspecting that disposing an engine might cause this problem, I've ran the same code with and without eng.dispose(), with the same result.
 
UPDATE 
 
The plot thickens. If I execute individual inserts instead, it works:
 
rbrf_table = RTABomRowFile.__table__
eng = DBSession.get_bind()
conn = eng.connect()
for d in lines:
    ...
    ins = rbrf_table.insert().values(d)
    conn.execute(ins)
conn.execute('COMMIT')
conn.close()
eng.dispose()

原文：https://stackoverflow.com/questions/33692443