Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

Locking a table row while web form is open

by diego_de_lima (Beadle)
on Dec 22, 2005 at 02:18 UTC ( #518463=perlquestion: print w/ replies, xml ) Need Help??
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
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.
Re: Locking a table row while web form is open
by kulls (Hermit) on Dec 22, 2005 at 03:34 UTC
    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.
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;

      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.

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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://518463]
Approved by neversaint
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (11)
As of 2014-09-19 11:17 GMT
Find Nodes?
    Voting Booth?

    How do you remember the number of days in each month?

    Results (135 votes), past polls