Locking a table row while web form is open

diego_de_lima has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

My application: I have a web application using Linux/Apache/Postgres/Perl. This application makes CRUD operations on many DB tables, but some o them are very large tables (50, 100 columns) and are often updated by many people (the same row, updated many times).

My problem: when more than one user open a form for Update on the same row, they don't know that someone else is also updating the row. So, when the first one submits the form, the second still have the old data on screen, and when he submits it, the data the first one submited is completely lost! And this is very, very bad.

It's not a problem of table or row LOCKING, but simply signaling the second user that the row is open for update and he's gonna have to wait some minutes. Just like opening the same file in a network share: the second user can only read the file.

Some solutions:

1. At the end of the page, create a loop while the page is open, keeping a lock file somewhere that the second process trying to open the row can see and alert. Pros: no daemons needed. Cons: I don't want to have a big Apache/mod_perl process looping and dedicated for minutes...

2. Use AJAX to call a CGI every 10 seconds to keep the lock file fresh. Pros: no daemon needed. Cons: too many requests and the 10 seconds thing is gonna be a pain to keep working.

3. Create a simple/light Perl HTTP daemon. The html form has a hidden IFRAME that connect to it like a web/chat application, touching a lock file and deleting it at the end. Pros: a simple http daemon is fast and very lightweight. Cons: a simple HTTP daemon may be very insecure, and needs another port to run, wich some firewalls can block.

So, finally, what are you people using to do this kind of thing? I'm thinking seriously of staying with the third option...

Diego de Lima

Comment on Locking a table row while web form is open

Replies are listed 'Best First'.
Re: Locking a table row while web form is open by eric256 (Parson) on Dec 22, 2005 at 05:08 UTC
An easier approach is to add two columns. LockedBy and LockedOn. When a user begins an edit, lock the record recording the ID and the Time. When they commit remove the lock. Don't let anyone edit the record if it has a lock, or let them see who locked it when and force remove the lock. This way you can also expire locks that are X minutes old, or when a users session ends, remove all there locks. I've used this approach before and it works rather well. ___________ Eric Hodges $_='y==QAe=e?y==QG@>@?iy==QVq?f?=a@iG?=QQ=Q?9'; s/(.)/ord($1)-50/eigs;tr/6123457/- \/\|\\\_\n/;print;	[reply]
Re^2: Locking a table row while web form is open by graq (Curate) on Dec 22, 2005 at 08:55 UTC
Do you know what the management loves? They love a little javascript ticker that shows how long until the lock times out - gives the person editing a sense of urgency and when it clicks down to 0 seconds they know they will have to start again... Now I find it a naff idea - but I don't pay my wages. Graq	[reply]
Re: Locking a table row while web form is open by perrin (Chancellor) on Dec 22, 2005 at 03:30 UTC
Why are you looking at these complex solutions? Is there a problem with a simple pessimistic locking approach, like creating a lock file when someone goes to the edit page and removing it when they save or adding a "signed_out_by" column in the database table? There are issues with people leaving that screen open and walking away, but you can use javascript and timeouts to help with that.	[reply]
Re: Locking a table row while web form is open by kulls (Hermit) on Dec 22, 2005 at 03:34 UTC
hi, Across consistency in web pages, you can go with explicit locking and don't allow more that one user to transact with table at-a-time, rather handling it with in CGI or front-end. -kulls	[reply]
Re: Locking a table row while web form is open by astroboy (Chaplain) on Dec 23, 2005 at 19:13 UTC
Optimistic locking may work for your application, but for some apps it's not an acceptable solution: When the user queries a row for editing generate a hash of said row When the user requests that the row is saved, regenerate the hash of the existing row. If it matches the original hash, then complete the save - otherwise let the user now that the row was changed by someone else The problem that I've encountered with locking web apps, is that rows get checked out/locked, and then the user gets distracted, called into a meeting etc and they never remember to unlock the record. Optimistic locking solves this problem, but it may not suit your business rules	[reply]