Hello everyone, I have a particular case I first would like to describe to you: I am working on a component to process a spooler that is stored in a table of a MySQL database. This spooler component is running in its own thread. The spooler component is frequently fetching all entries (resp. rows) of the spooler from the database, with each entry being mapped to a SpoolEntryObject.
The SpoolEntryObject contains logic. As I need simultaneous processing of spooler entries, each entry is required to run in its own thread. SpoolEntryObject therefore makes use of multiple inheritance to inherit not only from declarative base, but also from threading.Thread. The logic is contained in the run() method that is called via the inherited method threading.Thread.start() for the entry to be processed. On fetching the spooler entries, the spooler component iterates through them and calls their start() method, thereby creating one new thread per spooler entry, with the lot of them then running in parallel. The spooler component uses a non-transactional (autocommit=False) session. This session is used to fetch all spooler entries. It is being kept open for the lifespan of the spooler component. I need each spooler entry, that is bound to this one non-transactional session, to keep track of its current status (f.e. pending, processing, done, failed), which is stored in a field in the spooler database table and thus may be altered by modifying an attribute of the spooler entry object itself. Each spooler entry may also, during its operation, make changes to other, non-spooler tables in the same database - the spooler component is just one of several components of this application. These changes need to be transactional. Therefore, in the logic of a spooler entry, I create a new transactional session, bound to the same engine as the non-transactional session, and at the end of that logic, I commit it or roll it back. The best practice I would very much appreciate advice for concerns the non-transactional session resp. the session used to fetch all spooler entries and (inevitably?) update their status with. As each spooler entry is processed in its very own thread, this one session is open and in use in several threads (the # spooler entries currently being processed + the spooler component itself, to be exact) and prone to errors related to this threaded use of the non-thread-safe session. (f.e. when 2+ spooler entries update their status simultaneously) I could implement locking to avoid simultaneous use of the session, but would like to consider this as a fallback method only. Any ideas on how I could avoid inter-thread use of this non-transactional, non- thread-safe session? Something I have missed so far or the possibility to reassign existing SpoolEntryObjects to new, non-transactional sessions for their status updates maybe? Thanks in advance! Best, Thomas --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
