Django: how can I protect against simultaneously modifying database records

Question

If there is a way to protect against two simultaneous modifications of the same database record by two or more users?

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

I think locking a record is not an option, as the user can use the back button or just close their browser, leaving the lock permanently.

Answer 1

Here's 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 above code can be implemented as a method in Custom Manager .

I make the following assumptions:

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

These assumptions are sufficient so that no one updates the record before. If multiple rows are updated this way, you should use transactions.

WARNING Django Doc :

Remember that the update () method is converted directly to an SQL expression. This is a massive operation for direct updates. It does not run any save () on your models or emit pre_save or post_save signals

Answer 2

This question is a bit outdated, and my answer is a bit late, but after what I understand, it was fixed in Django 1.4 using:

 select_for_update(nowait=True) 

see docs

Returns a query set that locks rows until the end of the transaction, generating a SELECT ... FOR UPDATE SQL statement for supported databases.

Usually, if another transaction has already acquired a lock on one of the selected rows, the request will be blocked until the lock is released. If this is not the behavior you want, call select_for_update (nowait = True). This will make the call non-blocking. If a conflicting lock is already received by another transaction, a DatabaseError will be raised when evaluating the request.

Of course, this will only work if the internal support supports the "select for upgrade" function, which, for example, sqlite, does not work. Unfortunately: nowait=True not supported by MySql, there you need to use: nowait=False , which will only be locked until the lock is released.

Answer 3

In fact, transactions will not help you here ... if you do not want transactions to be executed over several HTTP requests (which you most likely do not need).

What we usually use in these cases is Optimistic Blocking. As far as I know, ORM Django does not support this. But there was some discussion about adding this feature.

So you yourself. Basically, what you need to do is add a “version” to your model and pass it to the user as a hidden field. Normal update cycle:

• read the data and show it to the user
• user changes data
• user publishes data
• The application saves it back to the database.

To implement optimistic locking when you save the data, you check whether the version that you received from the user matches the same as in the database, and then updates the database and increases the version. If this is not the case, it means that there have been changes since the data was downloaded.

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

 UPDATE ... WHERE version = 'version_from_user'; 

This call will update the database only if the version remains the same.

Answer 4

Django 1.11 has three convenient options for handling this situation depending on the requirements of your business logic:

• Something.objects.select_for_update() will block until the model becomes free.
• Something.objects.select_for_update(nowait=True) and catch a DatabaseError if the model is currently locked for updating
• Something.objects.select_for_update(skip_locked=True) will not return objects that are currently locked

In my application, which has both interactive and batch workflows on different models, I found these three options for solving most of my parallel processing scenarios.

The "wait" select_for_update very convenient in sequential batch processes - I want all of them to execute, but don't let them rush. nowait used when the user wants to change the object that is currently locked for updating - I will just tell them that it is changed at the moment.

skip_locked is useful for another type of update where users can re-scan an object — and I don’t care who runs it while it fires, so skip_locked allows me to silently skip duplicated triggers.

Answer 5

Further refer to https://github.com/RobCombs/django-locking . It is blocked in a way that does not leave eternal locks, by unlocking javascript when the user leaves the page, and blocks timeouts (for example, in case of a user’s browser failure). The documentation is pretty complete.

Answer 6

You should probably be using django middleware, even despite this problem.

As for your real problem, when multiple users are editing the same data ... yes, use a lock. OR:

Check in which version the user is updating (do it safely, so that users can’t just hack into the system to say that they are updating the latest copy!) And update only if this version is current. Otherwise, send the user a new page with the original version that they edited, with their submitted version and a new version written by others. Ask them to merge the changes into one fully updated version. You can try to automatically combine them with a toolbox such as diff + patch, but in any case you will need a manual merge method that works in case of failure, so start with this. In addition, you need to keep the version history and allow administrators to revert changes if someone inadvertently or intentionally ruins the merge. But probably you should have it anyway.

Most likely the django application or library that does most of this for you.

Answer 7

Another thing to look for is the word atomic. The atomic operation means that the database change will either succeed or fail. A quick search reveals this question with a question about atomic operations in Django.

Answer 8

Idea above

 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() 

Looks great and should work fine even without serializable transactions.

The problem is how to increase the behavior of deafult.save (), since there is no need to do manual tuning to call the .update () method.

I reviewed the idea of ​​Custom Manager.

My plan is to override the Manager _update method, which Model.save_base () calls to perform the update.

This is the current code in Django 1.3

 def _update(self, values, **kwargs): return self.get_query_set()._update(values, **kwargs) 

What IMHO needs to do is something like:

 def _update(self, values, **kwargs): #TODO Get version field value v = self.get_version_field_value(values[0]) return self.get_query_set().filter(Q(version=v))._update(values, **kwargs) 

A similar thing should happen upon removal. However, uninstalling is a bit more complicated as Django implements quite some voodoo in this area through django.db.models.deletion.Collector.

It is strange that a modren tool such as Django does not have a guide for Optimictic Concurency Control.

I will update this post when I solve the riddle. We hope that the solution will be in a good pythonic key, which will not include tons of coding, strange views, skipping the main parts of Django, etc.

Answer 9

To be safe, the database must support transactions .

If the fields are "free forms", for example. text, etc., and you must allow several users to edit the same fields (you cannot have access rights to one user), you can save the original data in a variable. When the user makes transactions, check if the original data from the original data has changed (if not, you do not need to worry about DB by overwriting the old data) if the original data are the same as the current data in db, you can save if it has changed, you can show the user the difference and ask the user what to do.

If the fields are numbers, for example. account balance, number of items in the store, etc., you can process it more automatically if you calculate the difference between the original value (saved when the user started filling out the form) and the new value that you can start the transaction, read the current value and add the difference, and then complete the transaction. If you cannot have negative values, you must abort the transaction if the result is negative, and inform the user.

I do not know django, so I can not give you cod3s ..;)

Answer 10

From here:
How to prevent overwriting an object that someone else has changed

I assume that the timestamp will be stored as a hidden field in the form you are trying to save.

 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()