Django: How can I protect against concurrent modification of data base entries

Question

If there a way to protect against concurrent modifications of the same data base entry by two or more users?

It would be acceptable to show an error message to the user performing the second commit/save operation, but data should not be silently overwritten.

I think locking the entry is not an option, as a user might use the "Back" button or simply close his browser, leaving the lock for ever.

Answer 1

To be safe the database needs to support transactions.

If the fields is "free-form" e.g. text etc. and you need to allow several users to be able to edit the same fields (you can't have single user ownership to the data), you could store the original data in a variable. When the user committs, check if the input data has changed from the original data (if not, you don't need to bother the DB by rewriting old data), if the original data compared to the current data in the db is the same you can save, if it has changed you can show the user the difference and ask the user what to do.

If the fields is numbers e.g. account balance, number of items in a store etc., you can handle it more automatically if you calculate the difference between the original value (stored when the user started filling out the form) and the new value you can start a transaction read the current value and add the difference, then end transaction. If you can't have negative values, you should abort the transaction if the result is negative, and tell the user.

I don't know django, so I can't give you teh cod3s.. ;)

Answer 2

Actually, transactions don't help you much here ... unless you want to have transactions running over multiple HTTP requests (which you most probably don't want).

What we usually use in those cases is "Optimistic Locking". The Django ORM doesn't support that as far as I know. But there has been some discussion about adding this feature.

So you are on your own. Basically, what you should do is add a "version" field to your model and pass it to the user as a hidden field. The normal cycle for an update is :

1. read the data and show it to the user
2. user modify data
3. user post the data
4. the app saves it back in the database.

To implement optimistic locking, when you save the data, you check if the version that you got back from the user is the same as the one in the database, and then update the database and increment the version. If they are not, it means that there has been a change since the data was loaded.

You can do that with a single SQL call with something like :

UPDATE ... WHERE version = 'version_from_user';

This call will update the database only if the version is still the same.

Answer 3

Another thing to look for is the word "atomic". An atomic operation means that your database change will either happen successfully, or fail obviously. A quick search shows this question asking about atomic operations in Django.

Answer 4

You should probably use the django transaction middleware at least, even regardless of this problem.

As to your actual problem of having multiple users editing the same data... yes, use locking. OR:

Check what version a user is updating against (do this securely, so users can't simply hack the system to say they were updating the latest copy!), and only update if that version is current. Otherwise, send the user back a new page with the original version they were editing, their submitted version, and the new version(s) written by others. Ask them to merge the changes into one, completely up-to-date version. You might try to auto-merge these using a toolset like diff+patch, but you'll need to have the manual merge method working for failure cases anyway, so start with that. Also, you'll need to preserve version history, and allow admins to revert changes, in case someone unintentionally or intentionally messes up the merge. But you should probably have that anyway.

There's very likely a django app/library that does most of this for you.

Answer 5

From here:
http://stackoverflow.com/questions/467134/how-to-prevent-overwriting-an-object-someone-else-has-modified

I'm assuming that the timestamp will be held as a hidden field in the form you're trying to save the details of.

def save(self):
    if(self.id):
        foo = Foo.objects.get(pk=self.id)
        if(foo.timestamp > self.timestamp):
            raise Exception, "trying to save outdated Foo" 
    super(Foo, self).save()

Answer 6

This is how I do optimistic locking in Django:

updated = Entry.objects.filter(Q(id=e.id) && Q(version=e.version))\
          .update(updated_field=new_value, version=e.version+1)
if not updated:
    raise ConcurrentModificationException()

The code listed above can be implemented as a method in Custom Manager.

I am making the following assumptions:

• filter().update() will result in a single database query because filter is lazy
• a database query is atomic

This assumptions are enough to ensure that no one else has updated the entry before. If multiple rows are updated this way you should transactions.

WARNING Django Doc:

Be aware that the update() method is converted directly to an SQL statement. It is a bulk operation for direct updates. It doesn't run any save() methods on your models, or emit the pre_save or post_save signals

Answer 7

For future reference, check out http://github.com/stdbrouw/django-locking It's still very new, but it does locking in a way that doesn't leave everlasting locks, by a mixture of javascript unlocking when the user leaves the page, and lock timeouts (e.g. in case the user's browser crashes). The documentation is pretty complete, methinks, even though I don't mean to brag about something I've written myself :-)