After setting expire_on_commit to False, I hit inconsistency after
commits in DB relations.
For example, I've two tables Host and HostPort, where Host have a
relation to Ports:
class HostPort(Base):
__tablename__ = 'hostport'
db_id = Column('id', Integer, primary_key=True) # the port's id
db_host_id = Column('host_id', Integer, ForeignKey('host.id'))
db_host = relation("HostDb")
class Host(Base):
__mapper_args__ = {'polymorphic_identity' : 'single'}
__tablename__ = 'host'
db_id = Column('id', Integer, primary_key=True) # the host's id
db_name = Column('name', String(100))
db_ports = relation("HostPortDb")
After I create a new HostPort (with an existing host as the host_id)
and queries the host for it ports I do not get the newly created port.
This problem did not occur when expire_on_commit was set to True.
As I understand the problem occures since the appropriate host
instance is not expired after the commit.
Is there any way overcome this issue, without expiring all the DB
instances ?
Thanks,
Amir
On Nov 28, 5:02 pm, Michael Bayer <[email protected]> wrote:
> On Nov 28, 2011, at 6:21 AM, Amir Sasson wrote:
>
>
>
> > Our Application is using a single session to access the DB. This
> > session is opened when the Application is started and closed when the
> > Application is terminated. The Application holds references to all the
> > DB objects via some kind of DB objects registry. The multithreading DB
> > access is protected at the application level using locking mechanism.
>
> > My main problem is that a single commit takes ~200-250ms. After
> > performing some testing, I figured out that the cause for the long
> > commit duration is due to the SQLAlchemy expire attribute mechanism.
> > That is, the Application holds references to all the DB objects
> > (~20,000 objects) and as a result all the DB objects are expired
> > during the commit which takes most of the commit duration.
> > When I set expire_on_commit to False, it reduced the commit duration
> > to only 10ms.
>
> > My question is whether I can set the expire_on_commit to False, in our
> > Application where we hold references to all DB objects, without
> > comprising the coherency of data while accessing the DB?
>
> There's nothing magic about expire_on_commit - basically if you are confident
> that you either aren't using those 20K object references subsequent to the
> commit, *or* you're confident that subsequent changes to their rows are not a
> problem (i.e. you won't see them without an expiration), then the flag is
> safe to turn off.
>
> A bulk operation like 20K rows is generally not expected to be blazingly fast
> with the ORM, as there's a lot of bookkeeping overhead that you wouldn't get
> if you just did direct inserts (such as, the newly generated primary key
> values and such).
>
> Also the expire operation has been sped up in recent releases are you on 0.7 ?
--
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.
