[sheng]

I'm not familiar with SQLAlchemy but my overall SQL sense is tingling here:

  purchase.downloads_left -= 1

Is this the preferred method to update a field? I'm asking because i'm wondering if sqlalchemy will translate this to an sql query similar to:

  UPDATE purchase SET downloads_left = downloads_left - 1;

Because otherwise this might be dangerous.

[zzzeek]

not in that case. if the code wanted to do this decrement on the SQL side it would have to be:

    purchase.downloads_left = Purchase.downloads_left - 1

which would emit on the next flush. in this code specifically, using that approach it would have to call session.flush() and then re-query for that value since it wants to check what the database came up with. So in that sense it would be better just to emit an explicit UPDATE..RETURNING, which is easy to do with SQLA; this is an example of how "dropping down" a level of abstraction is a critical feature with SQL abstraction tools.

however, this is only one way to do it, which is the so-called pessimistic approach. An optimistic approach would just ensure that the transaction isolation is in repeatable read, so that the flush (occurs within the commit() here) would just fail in the very unlikely case a single user is submitting twice. SQLAlchemy also offers a "version counter" feature that can accomplish the same task if RR isn't an option. Both of these are configuration-level features that would allow the code to remain unchanged.

The "session.add(purchase)" is also unnecessary in that code sample, and the code also has a bug in that it does not commit the transaction when downloads_left reaches zero, so the number can never actually reach zero in the database.

[jknupp]

Both of these have been fixed. Thanks for pointing out the off by one error and redundant session.add()

[smoyer]

ORMs are only trusted if the SQL they create seems sane when compared to how you'd write the SQL manually. After six years with Hibernate and SQLAlchemy (via TurboGears), I still turn on SQL statement logging to check what's being sent for each transaction.

[j-kidd]

I am pretty sure SQLAlchemy will translate that into:

    UPDATE purchase SET downloads_left=%(downloads_left)s WHERE purchase.id = %(purchases_id)s;

By the way, the posted code has an off-by-one error, as it should do the checking first before the minus operation. Also, the line `db.session.add(purchase)` is redundant.

EDIT: remove bad sample

[icebraining]

Even if it did translate to that, what happens when downloads_left is 1 and two updates were to run concurrently?

Unless the data isn't that important, it's better to use an RDBMS with SSI[1] support, and retry or bail out if there's a concurrent write.

http://www.postgresql.org/docs/current/static/mvcc-intro.htm...

[orf]

yes, it should