"ResourceClosedError: The transaction is closed" error with celery beat and sqlalchemy + pyramid app -

i have pyramid app called mainsite.

the site works in pretty asynchronous manner through threads being launched view carry out backend operations.

it connects mysql sqlalchemy , uses zopetransactionextension session management.

so far application has been running great.

i need run periodic jobs on , needs use of same asynchronous functions being launched view.

i used apscheduler ran issues that. thought of using celery beat separate process treats mainapp library , imports functions used.

my celery config looks this:

from datetime import timedelta api.apiconst import rerun_check_interval, automation_check_interval, \     auth_delete_time  broker_url = 'sqla+mysql://em:em@localhost/edgem' celery_result_backend = "database" celery_result_dburi = 'mysql://em:em@localhost/edgem'  celerybeat_schedule = {     'rerun': {         'task': 'tasks.rerun_scheduler',         'schedule': timedelta(seconds=rerun_check_interval)     },     'automate': {         'task': 'tasks.automation_scheduler',         'schedule': timedelta(seconds=20)     },     'remove-tokens': {         'task': 'tasks.token_remover_scheduler',         'schedule': timedelta(seconds=2 * 24 * 3600 )     }, }  celery_timezone = 'utc' 

the tasks.py is

from celery import celery celery = celery('tasks') celery.config_from_object('celeryconfig')   @celery.task def rerun_scheduler():     mainsite.task import check_update_rerun_tasks     check_update_rerun_tasks()   @celery.task def automation_scheduler():     mainsite.task import automate     automate()   @celery.task def token_remover_scheduler():     mainsite.auth_service import delete_old_tokens     delete_old_tokens() 

keep in mind above functions return launch threads if required

the threads save objects db doing transaction.commit() after session.add(object).

the problem whole things works gem 30 minutes. after resourceclosederror: transaction closed errors starts happening wherever there transaction.commit(). not sure problem , need troubleshooting.

the reason import inside tasks rid of error. thought importing every time task needed run idea , may new transaction each time, looks not case.

in experience trying reuse session configured used pyramid (with zopetransactionextension etc.) celery worker results in terrible hard-to-debug mess.

zopetransactionextension binds sqlalchemy session pyramid's request-response cycle - transaction started , committed or rolled automatically, you're not supposed use transaction.commit() within code - if ok zte commit everything, if code raises , exception transaction rolled back.

with celery need manage sqlalchemy sessions manually, zte prevents doing, need configure dbsession differently.

something simple work:

dbsession = none  def set_dbsession(session):     global dbsession     if dbsession not none:         raise attributeerror("dbsession has been set %s!" % dbsession)      dbsession = session 

and pyramid startup code do

def main(global_config, **settings):     ...     set_dbsession(scoped_session(sessionmaker(extension=zopetransactionextension()))) 

with celery it's bit trickier - ended creating custom start script celery, in configure session.

in setup.py of worker egg:

  entry_points="""   # -*- entry points: -*-   [console_scripts]   custom_celery = worker.celeryd:start_celery   custom_celerybeat = worker.celeryd:start_celerybeat   """,   ) 

in worker/celeryd.py:

def initialize_async_session(db_string, db_echo):      import sqlalchemy sa     db import base, set_dbsession      session = sa.orm.scoped_session(sa.orm.sessionmaker(autoflush=true, autocommit=true))     engine = sa.create_engine(db_string, echo=db_echo)     session.configure(bind=engine)      set_dbsession(session)     base.metadata.bind = engine   def start_celery():     initialize_async_session(db_string, db_echo)     import celery.bin.celeryd     celery.bin.celeryd.main() 

the general approach you're using "threads being launched view carry out backend operations" feels bit dangerous me if ever plan deploy application production server - web server recycles, kills or creates new "workers" there no guarantees each particular process survive beyond current request-response cycle. never tried doing though, maybe you'll ok :)