In Django models, modified row would help in handling optimistic concurrency control problems

[Steven Cummings]

Most agree that in webapps one cannot lock rows to be edited ahead of time. Optimistic concurrency is the typical choice here. So, it is then the job of the appdev to think about how to handle contention when it does occur. I've found scenarios when it would have been useful to know the rows-modified count for an update or delete:

1. Models have a "version" field, and we'd like to prevent two requests from clobbering each other with updates from the same version. These race conditions are generally the way of the web when two people are allowed to edit data simultaneously, but there are times when the data is a little more critical and we'd like to be able to present the conflict to the 2nd user trying to update (e.g., bugzilla mid-air collisions). In this case, it would be nice to be able to save() only on the precondition that the version in the DB hasn't been updated. At a low level, this means something like UPDATE ... WHERE ID = xxx AND VERSION = xxx, and then the rows-modified count would indicate whether it worked or not.
2. When we really want to know that a delete() call deleted data. Django ORM delete() passes silently if the object is already gone. However, there are cases when we want to know that the current request actually did the delete. One example is deleting OAuth auth codes: they should be used once and only once. (Yes, we could just mark it as used, but that still leaves us with the update/versioning problem above... and this is just an example). If two requests retrieve the auth code and otherwise believe they deleted (used) it, this would be considered a potential security problem in OAuth.

In both cases, having access to rows modified before commit would help greatly towards application-specific optimistic concurrency control handling. IFAICT, only that answer from the DB driver can tell us if the current request actually got to do a given data update. In looking at the django.db.models code it looks like rows-modified is available deep in the code, where DB specific connections and cursors are used, but is otherwise ignored there.

So, the actual proposal:

• Model.delete(), QuerySet.update(), and QuerySet.delete() return rows-modified counts
• New Model method save_if() that accepts a precondition, such as version=5 (which would be more like F('version')==5 under the covers, obviously we want to compare the actual value in storage). If the precondition fails, return MyModel.PreconditionFailed, which extends ObjectPreconditionFailed. In my example above I would probably translate that to a model-specific VersionObsolete exception, or similar.

Thoughts?

I can work on patches, but I wanted to log this first and see what kind of reaction it got before spending the time on that.