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
--
http://mail.python.org/mailman/listinfo/python-list
