Optimistic Locking

[Tim Sawyer]

Hi Folks,

I'm just evaluating django for use on a project. I have a multiuser
application where many users can be changing data at once.

What's the status of hibernate style optimistic locking, where each object has
a version and update/deletes are prevented if the last saved version of that
object is newer than the one being saved?

Thanks,

Tim.

[Thomas Guettler]

Hi,

I use this solution:

The model class has an field mtime which get's updated automatically:

class MyObject(models.Model):
mtime=models.DateTimeField(verbose_name=u'Letzte Änderung am',
editable=False, auto_now=True)

def mtime_has_changed(self, request):
mtime=request.POST['mtime']
mtime=datetime.datetime(*[int(i) for i in re.findall(r'[\d]+',
mtime)])
return self.mtime!=mtime

Comments welcome

HTH,
Thomas

[Alistair Lattimore]

Thomas,

Do you use a custom manager to select out the row before issuing the
save to make sure that the in memory timestamp matches that of the
database?

Al.

[Thomas Guettler]

Am Montag, 28. Januar 2008 10:16 schrieb Alistair Lattimore:
> Thomas,
>
> Do you use a custom manager to select out the row before issuing the
> save to make sure that the in memory timestamp matches that of the
> database?
>

No, since I don't use caching the mtime should be 'fresh'. Since
I use one transaction for one request, I should get a database error,
if the mtime was changed during reading and writing it back.

I have not tested this, but I think it should be like that.

Thomas

[Michael Hipp]

Could you explain how this works, please?

It looks - to my uneducated eyes - if this leaves open a potential race
condition where the mtime field could yet be changed in the database by
another process within the "decision time" of this method. Do I
misunderstand? It seems to me that this can only be reliably caught by
the database itself (probably using a rule or trigger).

Thanks,
Michael

[Thomas Guettler]

> Could you explain how this works, please?
>
> It looks - to my uneducated eyes - if this leaves open a potential race
> condition where the mtime field could yet be changed in the database by
> another process within the "decision time" of this method. Do I
> misunderstand? It seems to me that this can only be reliably caught by
> the database itself (probably using a rule or trigger).
>
> Thanks,
> Michael

t=time p=process

t1 p1 read mtime
t2 p2 read mtime
t3 p2 write mtime
t4 p1 write mtime

AFAIK the database should raise an exception.

Process1 does a 'dirty read':

http://www.postgresql.org/docs/8.2/static/transaction-iso.html

But you need to use transaction management. The read and write
must be in one transaction.

The edit form has hidden value which contains the current mtime of the model
instance. If the form is valid, check if the mtime has not changed. If it
changed, you need reload the form (All previous form input must be reset,
otherwise the user can't see what the other person has changed).

HTH,
Thomas

[code_berzerker]

How about rewriting save method complately and make additional
condition in WHERE clausule like this:
UPDATE ....... WHERE id=666 AND mtime=object_mtime
Checking number of updated rows would give you information about
success and would guarantee that there is no data manipulation between
mtime check and save.

[Tim Sawyer]

If you're going to do that, then couldn't we change the framework to add a new
VersionField. If there is a VersionField defined on the object, then the
code on save could automatically be added. This VersionField would simply
hold a version number for the record, that is incremented at save. This is a
similar idea to the date thing, but slightly more robust - in a high traffic
environment there is a theoretical (albeit small) possibility of two objects
picking up the same time.

This is how hibernate recommends that you do it. I saw someone suggesting
this method on this mailing list (found it with a web search I did when I
first started looking at django), this sounds like the optimum solution for
optimistic locking to me, and it would open up more use cases for django, at
least for me.

Would this be straightforward to add?

Tim.

[Michael Hipp]

I'd love to see something like this added.

Can it be done entirely in SQL or does it require some stored triggers
or rules in the database?

Thanks,
Michael

[Tim Sawyer]

On Monday 04 Feb 2008, Michael Hipp wrote:

> Tim Sawyer wrote:
> > If you're going to do that, then couldn't we change the framework to add
> > a new VersionField. If there is a VersionField defined on the object,
> > then the code on save could automatically be added. This VersionField
> > would simply hold a version number for the record, that is incremented at
> > save. This is a similar idea to the date thing, but slightly more robust
> > - in a high traffic environment there is a theoretical (albeit small)
> > possibility of two objects picking up the same time.
> >
> > This is how hibernate recommends that you do it. I saw someone
> > suggesting this method on this mailing list (found it with a web search I
> > did when I first started looking at django), this sounds like the optimum
> > solution for optimistic locking to me, and it would open up more use
> > cases for django, at least for me.
> >
> > Would this be straightforward to add?
>
> I'd love to see something like this added.
>
> Can it be done entirely in SQL or does it require some stored triggers
> or rules in the database?

I believe it can be done entirely in SQL.

* Select Object (for example ID, Version, Surname, Forename) - version column
defaults to 0 for new inserts

* When object is updated, do an UPDATE blah WHERE id = :id and version
= :version. If your update updated no rows, raise a locking error

There would also have to be code added into the admin front end to deal with
locking errors.

Tim.