Guys,

I've been battling with this for a week or so now and I'm really becoming quite confused by how I should be organising the sessions within my application so thought I would post a little example of what I'm trying to achieve in the hope you'll be able to help me structure this correctly.

I'm trying to build my application around a 'service layer' model, in the hope that if in the future we change the ORM implementation process, or upgrade to a new version of SQLAlchemy it'll make the change simpler to implement as the core application work process won't have to be changed. For those of you not familiar with a service layer, the concept is that if I have an object such as 'foo' within my application, I would create a service like this:

import foo

class foo_service(Object):

def __init__(self):

# Init the service/

def get_foo(self, foo_id=""):

# return an instance of foo if one exists in the database, if none exists, create a new instance and return that.

def save_foo(self, foo_object):

# Save the supplied foo object into the database.

def delete_foo(self, foo_object):

# Delete the supplied foo object from the database.

We effectively, on a basic level, use the service as a wrapper for the ORM calls, then from anywhere in my application that I need to get, save or delete foo I use the service methods. This way, if the process of getting a 'foo' changes, for instance, an upgrade on the ORM, I only have to implement those changes in the one place J

Now, the confusion lies with me being new to db work in python and the ORM in general. This is a multithread application and calls to the service need to be protected against that.

In the application the idea is that I would pull the foo from the database, or create a new foo using the get_foo method, this would then be used in the application, modified and whatever, and then saved back to the database through the service layer.

The challenge I'm meeting at the moment is that I keep running into what seem to be race conditions where multiple operations are being performed on the same objects, resulting in "InvalidRequestError: The transaction is inactive due to a rollback in a subtransaction. Issue rollback() to cancel the transaction" type errors.

Can someone please give me an example, using the foo_service class above of how I should be creating and disposing of sessions in this application? Or perhaps advise on whether the service layer model is a good one? Perhaps a different approach needs to be taken? I'm really open to ideas.

Many thanks guys, I really look forward to reading your replies.

Heston