[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] Re: Transactions and read-only operations
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. 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
On Saturday 07 June 2008 22:50:36 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? no idea, never used those, see docs. as long as u know your objects, these things should be fine. But if it's not _your_ objects and/or u expect anything from those or u're paranoic about the db safety... open it with readonly access? 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
On Sat, Jun 7, 2008 at 2:50 PM, Tomer [EMAIL PROTECTED] 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? Currently that is what I use to do to get readonly. Since I am the one that manages sa code as long as I don't say, User.somefiled= 123, I should have no worries about any changes being made. User.query.filter.is just a select statement. Unless proven/shown otherwise. Lucas --~--~-~--~~~---~--~~ 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
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] 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 -~--~~~~--~~--~--~---
