a problem with concurrency

[Tim Chase]

> When a user posts a requests he changes the underlying database
> table. The issue is that if more users are editing the
> same set of rows the last user will override the editing of the
> first one. Since this is an in-house application with very few
> users, we did not worry to solve this issue, which happens
> very rarely. However, I had a request from the people using
> the application, saying that this issue indeed happens sometimes
> and that they really would like to be able to see if some other
> user is editing a row. In that case, the web interface should
> display the row as not editable, showing the name of the user
> which is editing it. Moreover, when posting a request involving
> non-editable rows, there should be a clear error message and
> the possibility to continue anyway (a message such as
> "do you really want to override the editing made by user XXX?").

The common way to do this is to not bother with the "somebody 
else is editing this record" because it's nearly impossible with 
the stateless web to determine when somebody has stopped browsing 
a web page.  Instead, each record simply has a "last modified on 
$TIMESTAMP by $USERID" pair of field.  When you read the record 
to display to the user, you stash these values into the page as 
$EXPECTED_TIMESTAMP and $EXPECTED_USERID.  If, when the user 
tries to save the record, your web-server app updates the record 
only if the timestamp+username+rowid match:

   cursor.execute("""
   UPDATE MyTable SET
    Field1=?,
    Field2=?,
    Field3=?
   WHERE id=? AND LastModified=? AND LastModifiedBy=?""",
   (field1, field2, field3,
    rowid, expected_lastmodified, expecteduserid)
   )
   if cursor.rowcount:
     cursor.commit()
     print "Yay!"
   else:
     cursor.execute("""
       SELECT u.name, t.lastmodified
       FROM MyTable t
         INNER JOIN MyUsers u
         ON u.id = t.LastModifiedBy
       WHERE t.id = ?""", (rowid,))
     # maybe a little try/except around this in case
     # the record was deleted instead of modified?
     name, when = cursor.fetchone()
     print "This information has been modified " \
       "(by %s at %s) since you last viewed it (at %s)"  % (
       name, when, expected_lastmodified)

If you wanted to be really snazzy, you could pull up the existing 
new record alongside the data they tried to submit, and allow 
them to choose the correct value for each differing field.

This also encourages awareness of conflicting edits and hopefully 
increases communication between your users ("Why is Pat currently 
editing this record...I'm working on it?!" [calls/IMs/emails Pat 
to get matters straight])

> The first idea that comes to my mind is to add a field 'lockedby'
> to the database table, containing the name of the user which is
> editing that row. If the content of 'lockedby' is NULL, then the
> row is editable. The field is set at the beginning (the user will
> click a check button to signal - via Ajax - that he is going
> to edit that row) to the username and reset to NULL after the
> editing has been performed.

Locking is the easy part -- it's knowing when to *unlock* that it 
becomes a problem.  What happens if a user locks a record at 
4:59pm on Friday afternoon and then goes on vacation for a week 
preventing folks from editing this record?   If the locks are 
scoped to a single request, they do no good.  The locks have to 
span multiple requests. I'd just ignore locking.

-tkc