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