Sorry for the haze. I'm using PostgreSQL and am checking their docs on
isolation to see that it is consistent with SA.
but here is a simplified example.
I have 2 rows in the database with 1 column "FOO". Both rows have a NULL
value for column "FOO".
Two programs, A and B. They are the same program.
I have a query that: selects for update, all rows with FOO=NULL, limit
1. This will block all other processes attempting the same query.
A goes first and selects for update. B thus blocks. Both are looking for
1 row, FOO=NULL.
A query returns 1 row. Inside the transaction. A updates that row to
FOO=A. B is still waiting. Only 1 row is updated, because 1 is returned
from the select. I use session.add(row) to simply re-add the mapped
object after changing the value.
A commits its change to 1 row. The second row is still FOO=NULL.
B unblocks. B returns 0 results.
A, B exit.
Re-run B.
B finds 1 row where FOO=NULL and sets FOO=B.
B exits.
Does that help clarify?
thank you.
On Sun, 2009-01-11 at 11:33 -0500, Michael Bayer wrote:
>
> On Jan 11, 2009, at 11:18 AM, Darren Govoni wrote:
>
> >
> > Thank you,
> >
> > So I changed my query to a select/for update. then re-added the
> > updated
> > rows in the transaction, then committed.
> >
> >
> > works=session.query(Work).filter(tnow-
> > Work.taken<timedelta(minutes=60)).
> > filter
> > (Work.completed==None).limit(1).with_lockmode(mode='update').all()
> >
> > When I run two instances of the program, the second one will block on
> > the query while the first is inside the transaction ('update'). BUT.
> > the
> > second one should return 1 row when it unblocks because the first
> > instance only modified 1 row, leaving the other to satisfy the
> > blockers
> > query. It doesn't return anything when the transaction is released to
> > the second instance. Peculiar.
>
> >
> >
> > I re-run the second instance after that and it then is able to find
> > the
> > qualifying row. Is that correct behavior? Both program instances are
> > the
> > same code.
>
> what I'm not sure about here is if you are expecting the UPDATE to
> return the number of rows actually modified, which again is a MySQL
> only thing, or the number of rows actually matched. I'm also not
> sure if you are updating the rows in such a way that they won't match
> after they're updated. So I only have a hazy view of the actual
> operation. But from what I'm reading the behavior doesn't sound
> correct. Check the SQL log output of both applications which should
> illustrate the full conversation.
>
> >
--~--~---------~--~----~------------~-------~--~----~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
