Skip site navigation (1) Skip section navigation (2)

connection lost with concurrent transactions

From: Otto Vazquez <otto(dot)vazquez(at)gmail(dot)com>
To: psycopg(at)postgresql(dot)org
Subject: connection lost with concurrent transactions
Date: 2011-05-13 16:12:06
Message-ID: BANLkTik5zNVAA4LQG8x4u9HZ_Y2ANLzm3A@mail.gmail.com (view raw or flat)
Thread:
Lists: psycopg
Hi all,

We are using django (1.3) with django-celery (2.2.4), database in postgres
(9.0.1) with psycopg2 (2.2.2) connector for a large project (also large
company).

Executing celery tasks one by one works fine. When requested, django inserts
a new row in db (django starts a transaction) and two task and are invoked
and both tasks write in the same row but in different fields. With
CELERY_ALWAYS_EAGER set, the tasks execute sequentially.

The problem comes when switching celery from synchronous to asynchronous.
Executing the same code, the celery daemon raises this exception:

[2011-05-13 15:37:51,981: WARNING/PoolWorker-1]
/prj/env/lib/python2.6/site-packages/celery/worker/job.py:114: UserWarning:
Exception outside body: <class 'psycopg2.OperationalError'>: no connection
to the server
[2011-05-13 15:37:51,982: ERROR/MainProcess] Task
cds.tasks.resize[2f198cdd-a1d5-4e61-b4cd-a69a8e6586f4] raised exception:
OperationalError('no connection to the server\n',)
Traceback (most recent call last):
  File "/prj/env/lib/python2.6/site-packages/celery/worker/job.py", line
108, in execute_safe
    return self.execute(*args, **kwargs)
  File "/prj/env/lib/python2.6/site-packages/celery/worker/job.py", line
126, in execute
    return super(WorkerTaskTrace, self).execute()
  File "/prj/env/lib/python2.6/site-packages/celery/execute/trace.py", line
76, in execute
    retval = self._trace()
  File "/prj/env/lib/python2.6/site-packages/celery/execute/trace.py", line
92, in _trace
    return handler(trace.retval, trace.exc_type, trace.tb, trace.strtb)
  File "/prj/env/lib/python2.6/site-packages/celery/worker/job.py", line
147, in handle_failure
    exc = self.task.backend.mark_as_failure(self.task_id, exc, strtb)
  File "/prj/env/lib/python2.6/site-packages/celery/backends/base.py", line
45, in mark_as_failure
    traceback=traceback)
  File "/prj/env/lib/python2.6/site-packages/celery/backends/base.py", line
157, in store_result
    return self._store_result(task_id, result, status, traceback, **kwargs)
  File "/prj/env/lib/python2.6/site-packages/djcelery/backends/database.py",
line 20, in _store_result
    traceback=traceback)
  File "/prj/env/lib/python2.6/site-packages/djcelery/managers.py", line 46,
in _inner
    transaction.rollback_unless_managed()
  File "/prj/env/lib/python2.6/site-packages/django/db/transaction.py", line
133, in rollback_unless_managed
    connection.rollback_unless_managed()
  File
"/prj/env/lib/python2.6/site-packages/django/db/backends/__init__.py", line
193, in rollback_unless_managed
    self._rollback()
  File
"/prj/env/lib/python2.6/site-packages/django/db/backends/__init__.py", line
50, in _rollback
    return self.connection.rollback()
OperationalError: no connection to the server

For testing and isolate the problem, we tried with MySQL (5.1.49) and
mysql-django (1.2.3). Just changed db connector and port, and worked like
charm (no fails over ~400 tasks).
We also have tried modifying celery parameters (CELERY_DB_REUSE_MAX,
CELERY_TASK_PUBLISH_RETRY mainly) with some code tweaks.
After a couple of hours, we found the best fix-approach, wich has minimized
the number of exception, but still fails.
http://thebuild.com/blog/2010/10/25/django-and-postgresql-idle-in-transaction-connections/

Even with the auto commit enabled. Anyway, we don't want to change django
code.

We believe it's a connector problem, just google a little and you will find
lots a posts with same/similar problem.
Some other useful info/samples:
http://stackoverflow.com/questions/1303654/threaded-django-task-doesnt-automatically-handle-transactions-or-db-connections
http://groups.google.com/group/django-developers/browse_frm/thread/5249b9ba993431ca/4d1b9d65329c8b75
http://code.djangoproject.com/ticket/9964

Can you give any light on this? If we don't find any solution, we'll move to
mysql, which by now seems to be the best (extreme as well) option.

Otto Vazquez

Responses

psycopg by date

Next:From: Israel Ben Guilherme FonsecaDate: 2011-05-13 16:33:40
Subject: Re: Input and Output data traffic
Previous:From: Israel Ben Guilherme FonsecaDate: 2011-05-13 11:23:25
Subject: Re: Input and Output data traffic

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group