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