zope.sqlalchemy: Decoupling Zope3 and SQLAlchemy commit - how? Should I?
Hi, In one of my Zope3 projects, I use lovely.remotetask to run specific tasks in background. These tasks do lengthy database operations via zope.sqlalchemy. Currently, lovely.remotetask seems to do a Zope3 commit() at the end of the task, which also does the SQLAlchemy commit. However, some tasks consist of a loop over self-contained operations, therefore it would be handy to commit after each such operation. This way, if one of the operations fail (e.g. due to some database error, such as locking, refrential integrity violation etc.), not the whole task is rolled back but only one operation. Moreover, the probability of database locks is reduced. But, how would I do that? First of all, I'd have to catch all database error, so that lovely.remotetask does not do a rollback on its own (which errors would that be?). Then I'd have to somehow commit - should I simply do a Zope3 commit? Or should I somehow do a database commit via zope.sqlalchemy? Best Regards, Hermann -- hermann@qwer.tk GPG key ID: 299893C7 (on keyservers) FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7
This depends on whether you have other resources involved in the transaction or not. I would assume that you will only have the sql database committing in the transaction, in which case it makes not a blind bit of difference if you commit separately or not - just make sure each individual task is a separate item in the queue and ensure your queue processor can retry failures. Laurence 2008/11/2 Hermann Himmelbauer <dusty@qwer.tk>:
Hi, In one of my Zope3 projects, I use lovely.remotetask to run specific tasks in background. These tasks do lengthy database operations via zope.sqlalchemy. Currently, lovely.remotetask seems to do a Zope3 commit() at the end of the task, which also does the SQLAlchemy commit.
However, some tasks consist of a loop over self-contained operations, therefore it would be handy to commit after each such operation. This way, if one of the operations fail (e.g. due to some database error, such as locking, refrential integrity violation etc.), not the whole task is rolled back but only one operation. Moreover, the probability of database locks is reduced.
But, how would I do that? First of all, I'd have to catch all database error, so that lovely.remotetask does not do a rollback on its own (which errors would that be?). Then I'd have to somehow commit - should I simply do a Zope3 commit? Or should I somehow do a database commit via zope.sqlalchemy?
Best Regards, Hermann
-- hermann@qwer.tk GPG key ID: 299893C7 (on keyservers) FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7
On Sun, Nov 02, 2008 at 09:37:56PM +0100, Hermann Himmelbauer wrote:
Hi, In one of my Zope3 projects, I use lovely.remotetask to run specific tasks in background. These tasks do lengthy database operations via zope.sqlalchemy. Currently, lovely.remotetask seems to do a Zope3 commit() at the end of the task, which also does the SQLAlchemy commit.
However, some tasks consist of a loop over self-contained operations, therefore it would be handy to commit after each such operation. This way, if one of the operations fail (e.g. due to some database error, such as locking, refrential integrity violation etc.), not the whole task is rolled back but only one operation. Moreover, the probability of database locks is reduced.
I've never used lovely.remotetask, but I would suggest trying: * Have your "loop over self-contained operations" create more tasks. * Move the loop to the application and create many tasks, one for each operation. -- Brian Sutherland
participants (3)
-
Brian Sutherland -
Hermann Himmelbauer -
Laurence Rowe