[sqlalchemy] Re: Transactions and read-only operations
Wow, thanks for the detailed response!
That was very helpful (and I'm going to do some more reading now on
database transactions in general, and then dig deeper into how
SQLAlchemy can use the different options).
On Jun 8, 7:23 am, Kyle Schaffrick [EMAIL PROTECTED] wrote:
On Sat, 7 Jun 2008 22:21:20 -0700 (PDT)
Tomer [EMAIL PROTECTED] wrote:
I have two follow-up questions:
1) From what I understand, if I read an object (eg, Student) from
the database, modify that object or other objects, and then commit, I
have no guarantee that the object didn't change between the time I
read it and the time I committed.
Well, I think I can describe PostgreSQL's behavior as I understand it, I
would guess other DBMSs have reasonably similar semantics. I can't speak
to whether SA has any precautions of it's own above and beyond what the
DBMS provides, but I would tend to doubt it.
In the case of selecting a Student, altering it, and flushing it back
all within onetransaction: If a concurrenttransactionhas uncommitted
UPDATEs or DELETEs on any rows you try to UPDATE or DELETE, your
statement will block until the concurrent writer commits or rolls back.
Then, either
A) You are using read committed isolation (allows nonrepeatable
reads), in which case it then runs the statement against the new view
of the world, or
B) You are using serializable isolation (no nonrepeatable reads), in
which case it aborts yourtransactionif the other writer changed the
rows you were trying to write.
Whether nonrepeatable reads cause breakage depends on the specific
nature of the updates.
For example:
if len(Session.query(Student).filter_by(Student.name ==
'Bill').all()) 0: # are there any students named 'Bill'?
school = Session.query(School).one() # assume there is one
school in the database
school.hasStudentNamedBill = True
Session.commit()
When the commit is issued, I might end up with an inconsistent
database, because a differenttransaction(in another thread, for
example) may have changed the name of the student after I checked if
there is a student named Bill, but before I committed.
This is interesting, and I think it's where SELECT FOR SHARE (or
similar) can help. In the non-contended case it's mostly just a normal
SELECT. If a concurrenttransactionhas uncommitted UPDATEs or DELETEs
on any rows selected by this statement, the SELECT FOR SHARE will wait,
as above, until the concurrent writer commits or rolls back. Then, it
either
A) (Read committed isolation) Re-runs the SELECT to get the new view
of the world and returns that, or
B) (Serializable isolation) Aborts yourtransactionif the other
writer changed any of the rows SELECTed.
In SA I think it would look like this, modulo note [1]:
if len(Session.query(Student).with_lockmode('read')
.filter_by(Student.name == 'Bill').all()):
# ...and so on
It is also possible to use SELECT FOR SHARE NOWAIT to request that the
DB abort yourtransactionif the operation would have blocked. SA
doesn't implement NOWAIT except on SELECT FOR UPDATE, as far as I can
tell.
From the last
answer it seems like databases that support transactions might not
suffer from this problem (if they take locks on SELECTed rows), but I
tried in SQLite (which supports transactions) and it didn't help.
Would a different database help solve the problem?
That would probably be the case. I have never attempted to use SQLite in
a high-concurrency situation, and I get the impression it's not
particularly attuned for it: SQLite's lock granularity is the entire
database. It doesn't support SELECT FOR UPDATE/SHARE or anything like
it that I can tell.
-Kyle
[1] I happened to notice in the process of writing this email
that SA's PG dialect doesn't support select(for_update='read')
a.k.a. SELECT FOR SHARE. Bug?
http://www.postgresql.org/docs/8.2/static/sql-select.html#SQL-FOR-UPD...
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---
[sqlalchemy] Transactions and read-only operations
If I am just issuing a read-only query, and I want to make sure that nothing will change in the middle, do I need a transaction? If so, what do I do with it when I'm done - commit or rollback? For example, I want to do this: for user in Session.query(User): print user.name Should I use a transaction like this: Session.begin() for user in Session.query(User): print user.name Session.commit() If not, how would I deal with this if my session (created with scoped_session) is transactional? Thanks, Tomer --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Transactions and read-only operations
Thanks. What would happen if I didn't do anything (I've seen lots of examples online that will just issue a query like Session.query(User).all() and that's all). Will that query start a transaction if it's a transactional session? On Jun 7, 11:58 am, [EMAIL PROTECTED] wrote: On Saturday 07 June 2008 21:35:35 Tomer wrote: If I am just issuing a read-only query, and I want to make sure that nothing will change in the middle, do I need a transaction? If so, what do I do with it when I'm done - commit or rollback? For example, I want to do this: for user in Session.query(User): print user.name Should I use a transaction like this: Session.begin() for user in Session.query(User): print user.name Session.commit() If not, how would I deal with this if my session (created with scoped_session) is transactional? if the __str__ happens to self-change (well, shit happens), then u should not do commit nrt flush. the safest is to rollback - regardless what u do. or maybe close() or clear()? i think there was some way to make the session readonly alltogether -- but i'm not sure. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Transactions and read-only operations
Thanks for the detailed response. I have two follow-up questions: 1) From what I understand, if I read an object (eg, Student) from the database, modify that object or other objects, and then commit, I have no guarantee that the object didn't change between the time I read it and the time I committed. For example: if len(Session.query(Student).filter_by(Student.name == 'Bill').all()) 0: # are there any students named 'Bill'? school = Session.query(School).one() # assume there is one school in the database school.hasStudentNamedBill = True Session.commit() When the commit is issued, I might end up with an inconsistent database, because a different transaction (in another thread, for example) may have changed the name of the student after I checked if there is a student named Bill, but before I committed. From the last answer it seems like databases that support transactions might not suffer from this problem (if they take locks on SELECTed rows), but I tried in SQLite (which supports transactions) and it didn't help. Would a different database help solve the problem? 2) You mentioned that the recommended approach to roll back in SA 0.4 is to call rollback() and then clear(). Can I just call close() on the session instead? From the documentation it seems that close() is essentially a rollback combined with a clear. Thanks! On Jun 7, 9:29 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 7, 2008, at 3:50 PM, Tomer wrote: Thanks. What would happen if I didn't do anything (I've seen lots of examples online that will just issue a query like Session.query(User).all() and that's all). Will that query start a transaction if it's a transactional session? when you use the DBAPI (which every SQLA driver does), anytime you get a hold of a connection, there is a transaction in progress. Depending on what database backend you're using, this might have different meaning; such as if you're using MySQL with ISAM tables, there isnt really much transactional. But generally, selecting from tables implies that those rows might be pulled into the transaction such that changes made external to the trans aren't visible (depending on isolation behavior). But this doesn't actually write anything to the database. Only INSERT/UPDATE/DELETE has that effect. When using the ORM, these operations are encapsulated entirely wihtin a flush(), so if your transaction does not flush(), then nothing is written. Doing a rollback() will ensure completely that nothing is written, even if flushes have taken place. With SQLA 0.4, rollback() has the caveat that the Session is not really usable after a rollback() until it is clear()'ed. With 0.5 this restriction is lifted. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Inheritance and self-relation in child
Hi,
I have two tables: Image and PersistentImage, where the latter
inherits the former. In addition, PersistentImage has a foreign key to
itself (ie, all PersistentImage objects form a hierarchy among
themselves). I haven't been able to get this to work - first it
complained about wanting a primaryjoin, then it seemed to get confused
between the parent/children relation among PersistentImage objects and
the inheritance between PersistentImage and Image. Here are my
mappers:
mapper(
Image,
imageTable,
polymorphic_on=imageTable.c.type,
polymorphic_identity='Image'
)
mapper(
PersistentImage,
persistentImageTable,
inherits=Image,
polymorphic_identity='PersistentImage',
properties={
'children': relation(PersistentImage, backref='parent')
})
And here are the actual table definitions:
imageTable = Table('Images', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(256), nullable=False),
Column('type', String(30), nullable=False)
)
persistentImageTable = Table('PersistentImages', metadata,
Column('id', Integer, ForeignKey('Images.id'), primary_key=True),
Column('parentId', Integer, ForeignKey('PersistentImages.id')),
Column('userId', Integer, ForeignKey('Users.id'), nullable=False)
)
Thanks!
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Inheritance and self-relation in child
Worked like magic!! Thanks!
BTW, why wasn't SQLA able to determine this automatically like it
usually does?
On Jun 5, 11:30 am, Michael Bayer [EMAIL PROTECTED] wrote:
On Jun 5, 2008, at 11:18 AM, Tomer wrote:
Hi,
I have two tables: Image and PersistentImage, where the latter
inherits the former. In addition, PersistentImage has a foreign key to
itself (ie, all PersistentImage objects form a hierarchy among
themselves). I haven't been able to get this to work - first it
complained about wanting a primaryjoin, then it seemed to get confused
between the parent/children relation among PersistentImage objects and
the inheritance between PersistentImage and Image. Here are my
mappers:
mapper(
Image,
imageTable,
polymorphic_on=imageTable.c.type,
polymorphic_identity='Image'
)
mapper(
PersistentImage,
persistentImageTable,
inherits=Image,
polymorphic_identity='PersistentImage',
properties={
'children': relation(PersistentImage, backref='parent')
})
And here are the actual table definitions:
imageTable = Table('Images', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(256), nullable=False),
Column('type', String(30), nullable=False)
)
persistentImageTable = Table('PersistentImages', metadata,
Column('id', Integer, ForeignKey('Images.id'), primary_key=True),
Column('parentId', Integer, ForeignKey('PersistentImages.id')),
Column('userId', Integer, ForeignKey('Users.id'), nullable=False)
)
here it is:
mapper(PersistentImage, persistentImageTable, inherits=Image,
inherit_condition=persistentImageTable.c.id==imageTable.c.id,
polymorphic_identity='PersistentImage',
properties={
'children':relation(PersistentImage,
primaryjoin=persistentImageTable.c.parentId==persistentImageTable.c.id,
backref=backref('parent',
primaryjoin
=persistentImageTable.c.parentId==persistentImageTable.c.id,
remote_side=[persistentImageTable.c.id])
)
})
SQLA would probably slightly happier if you had the parentId foreign
key referencing Images.id instead of PersistentImages.id but this
should not be required.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Inheritance and self-relation in child
Right, I missed that part.
Thanks for the explanation...
On Jun 5, 12:02 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Jun 5, 2008, at 11:42 AM, Tomer wrote:
Worked like magic!! Thanks!
BTW, why wasn't SQLA able to determine this automatically like it
usually does?
when you join PersistentImage-PersistentImage, theres two ways to
join on foreign keys between those (remember that a PersistentImage is
also an Image). So SQLA reports that this is ambiguous.
On Jun 5, 11:30 am, Michael Bayer [EMAIL PROTECTED] wrote:
On Jun 5, 2008, at 11:18 AM, Tomer wrote:
Hi,
I have two tables: Image and PersistentImage, where the latter
inherits the former. In addition, PersistentImage has a foreign
key to
itself (ie, all PersistentImage objects form a hierarchy among
themselves). I haven't been able to get this to work - first it
complained about wanting a primaryjoin, then it seemed to get
confused
between the parent/children relation among PersistentImage objects
and
the inheritance between PersistentImage and Image. Here are my
mappers:
mapper(
Image,
imageTable,
polymorphic_on=imageTable.c.type,
polymorphic_identity='Image'
)
mapper(
PersistentImage,
persistentImageTable,
inherits=Image,
polymorphic_identity='PersistentImage',
properties={
'children': relation(PersistentImage, backref='parent')
})
And here are the actual table definitions:
imageTable = Table('Images', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(256), nullable=False),
Column('type', String(30), nullable=False)
)
persistentImageTable = Table('PersistentImages', metadata,
Column('id', Integer, ForeignKey('Images.id'), primary_key=True),
Column('parentId', Integer, ForeignKey('PersistentImages.id')),
Column('userId', Integer, ForeignKey('Users.id'), nullable=False)
)
here it is:
mapper(PersistentImage, persistentImageTable, inherits=Image,
inherit_condition=persistentImageTable.c.id==imageTable.c.id,
polymorphic_identity='PersistentImage',
properties={
'children':relation(PersistentImage,
primaryjoin
=persistentImageTable.c.parentId==persistentImageTable.c.id,
backref=backref('parent',
primaryjoin
=persistentImageTable.c.parentId==persistentImageTable.c.id,
remote_side=[persistentImageTable.c.id])
)
})
SQLA would probably slightly happier if you had the parentId
foreign
key referencing Images.id instead of PersistentImages.id but this
should not be required.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---
