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 [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
-~----------~----~----~----~------~----~------~--~---
