Does limit() work with update()?
Darren Govoni
I have 2 records in the database. I made an expression to update only
1 record, but all are getting updated.
works=session.query(Work).filter(tnow-Work.taken<timedelta(minutes=15))
.filter(Work.completed==None).limit(1).with_lockmode(mode='update').update(values)
Shouldn't the above query only perform the update on the limited results
of the query?
Darren
Michael Bayer
query.update() just issues an UPDATE statement. UPDATE doesn't
support any kind of LIMITing keywords (maybe MySQL does, but thats not
SQL). The Query should really be raising an error here without
emitting any SQL.
Darren Govoni
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.
Michael Bayer
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.
Darren Govoni
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.
Michael Bayer
yeah I understood all that (except for the session.add(row) part,
which doesn't seem related). And no, it does not sound correct, in
that when B is unblocked into the transaction, it should see the same
thing as if it had just been run completely after the A transaction
were committed.
in any case you should view the SQL logs (echo=True) to see what the
conversation is saying. If that all looks as expected, open up two
postgres consoles and try the same conversation manually.
Darren Govoni
If I leave off the limit 1, it produces expected results. So at least SA
is consistent with PG at the moment, even if PG is broken. Going to
post on their list now, about this. Thanks.