On 4/19/15 5:58 PM, George Reilly wrote:
The aggregated centralized log looks like
first interleaved request, 254c1046-0da701ca, at 19:09:03, which
switches moods from `['Grumpy', 'Tired', 'Disgruntled']` to `['Elated']`
Apr 15 19:09:03 [3843:MainThread] INFO [254c1046-0da701ca]
REST request: PUT http://<hostname>/avatar/<avatar_id> received...
Second interleaved request, 711bc322-df5afba6, at 19:09:04, also
with moods=`['Elated']`
Apr 15 19:09:04 [4572:MainThread] INFO [711bc322-df5afba6]
REST request: PUT http://<hostname>/avatar/<avatar_id> received...
Second request fails
Apr 15 19:09:05 [4572:MainThread] WARNI [711bc322-df5afba6]
REST request: PUT http://<hostname>/avatar/<avatar_id> failed: 400 Bad
Request (GENERAL): 'DELETE statement on table 'avatar_moods' expected
to delete 3 row(s); Only 0 were matched.' (DELETE statement on table
'avatar_moods' expected to delete 3 row(s); Only 0 were matched.)
First request succeeds
Apr 15 19:09:05 [3843:MainThread] INFO [254c1046-0da701ca]
REST response: [1570ms] PUT http://<hostname>/avatar/<avatar_id>
responded: 200 OK
you saw what's called a phantom read:
http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Phantom_reads
Transaction #2 in PostgreSQL now had visibility to those updated rows
in the DB,
so it returned "0 rows deleted" to SQLAlchemy.
This confounded SA's expectations and it blew up.
I think.
sure, the ORM tries where possible to catch scenarios where what's in
the database does not match the mutations being sent in. Because of
course to continue with incorrect assumptions instead of stopping
immediately is a good way to really whack things up.
Finally, my question: What, if anything, could we have done to
mitigate this on the server?
Well, while it is not usually used, serializable isolation will solve
the problem of phantom reads. You will lose concurrency because
serialzable still has to lock things. If you wanted to go this route,
you could have just the methods in question use this isolation level
individually, so that they will not have this activity against each
other, without the rest of the application methods being impacted by
this. The Core has a lot of isolation level directives but also
recently I added the ability for a Session to set an isolation level per
individual transaction as well, see
http://docs.sqlalchemy.org/en/rel_1_0/orm/session_transaction.html#setting-isolation-for-individual-transactions
for that.
Another way to go is to have a method wrapped in a "retry" decorator of
some kind, which anticipates known error conditions such as a potential
race condition, and just retries the entire method all over again,
starting from re-loading the current data and trying the operation again.
A more specialized form of "retry" is to keep the main transaction going
and use a savepoint within the critical section, where you can again
retry the operation. Savepoints are available in the ORM via
begin_nested():
http://docs.sqlalchemy.org/en/rel_1_0/orm/session_transaction.html#using-savepoint
Yet another way, is to emit a SELECT for these rows using FOR UPDATE,
which will load the rows and lock them at the same time. You'd need to
emit a query that will work directly against the avatar_moods table so
that these rows are selected and set up as read-only to other
transactions. The various FOR UPDATE options are available at the ORM
level using with with_for_update() method:
http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html#sqlalchemy.orm.query.Query.with_for_update.
PG supports a few options here where you can see an overview at
http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-FOR-UPDATE-SHARE.
SQLAlchemy supports FOR UPDATE, FOR SHARE, with or without the NOWAIT
and OF options of Postgresql here.
Looking at dependency.py for 0.9.9, it looks like SQLAlchemy considers
this
to be an unrecoverable error.
you'd probably find that Postgresql itself would forbid the transaction
from continuing after this error in any case.
PS Congratulations on releasing SQLAlchemy 1.0! I've been a happy user
since 2008.
--
that's great !
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
