a problem with concurrency

[Michele Simionato]

At work we have a Web application acting as a front-end to a
database (think of a table-oriented interface, similar to
an Excel sheet). The application is accessed simultaneously by
N people (N < 10).

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?").

Looks like a lot of work for an application which is very low
priority for us. Also, I do not feel too confident with managing
concurrency directly. However, just for the sake of it I have
written a prototype with the basic functionality and I am asking
here for some advice, since I am sure lots of you have already
solved this problem.

My constraint are: the solution must work with threads (the
web app uses the Paste multithreaded server) but also with processes
(while the server is running a batch script could run
and set a few rows). It also must be portable across
databases, since we use both PostgreSQL and MS SQLServer.

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.

This morning I had a spare hour, so I wrote a 98 lines prototype
which has no web interface and does not use an ORM, but has the
advantage
of being easy enough to follow; you can see the code here:
http://pastebin.com/d1376ba05

The prototype uses SQLite and works in autocommit mode (the real
application works in autocommit mode too, even if with different
databases). I have modelled the real tables with a simple table
like this:

CREATE TABLE editable_data (
rowid INTEGER PRIMARY KEY,
text VARCHAR(256),
lockedby VARCHAR(16))

There is thread for each user. The test uses 5 threads; there is
no issue of scalability, since I will never have more than 10 users.
The basic idea is to use a RowLock object with signature
RowLock(connection, username, tablename, primarykeydict) with
__enter__ and __exit__ methods setting and resetting the lockedby
field of the database table respectively.

It took me more time to write this email than to write the prototype,
so I do not feel confident with it. Will it really work
for multiple threads and multiple processes? I have always managed to
stay
away from concurrency in my career ;-)


Michele Simionato

 

[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

 

[Michele Simionato]

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

This is much easier to implement than the locking mechanism since I already have
the fields $EXPECTED_TIMESTAMP and $EXPECTED_USERID in the db!
It looks quite sufficient for my use case.