Skip to main content
A
Anonymous
June 14, 2007
Answered

Prevent multiple edit sessions

  • June 14, 2007
  • 4 replies
  • 816 views
I've built an ad campaign managing tool with Coldfusion and mySQL which allows multiple users in diverse locations to monitor, edit and communicate about ads which are scheduled for publication as part of a campaign. Recently, it came to my attention (duh!) that it is important to prevent multiple users from opening the same ad for editing. I could use CFLOCK on my INSERT or SELECT statements, but I think I might rather CFLOCATION to a page explaining that another user already has the ad open for editing. I've been pondering setting application scope variables whenever an ad table row gets opened for editing, then testing for such variable and delivering a message notifying the user that the record is already being modified. Am I on the right track here? Or am I missing something obvious?

I've contemplated the potential for a user to open an ad for edit, then never submit or otherwise close the session, but a timout on the application variable could handle that, couldn't it?

I've built numerous CMS and dynamic sites, but this is the first one with realistic potential for multiple simultaneous administrative connections. Any help is much appreciated.

max
    This topic has been closed for replies.
    Correct answer Amiable_Penguin15D5
    Add a column named "edit_lock" to that table, default it to 0. Add a column named "last_edited".

    When a user requests to edit any record, check if the value is 0. If it is, then update it to 1, update LAST_EDITED to #now()# and allow the user to edit the record. Add that record ID to an array that's stored in the applicaiton scope. When the user submits a change, update the record ( UPDATE someTable SET foo = "wibble", EDIT_LOCK = 0 WHERE ID = #x# and EDIT_LOCK = 1 ), set the lock value to 0 and remove that element from the application variable.

    Case 1:
    UserA edits a record.
    UserB attempts to edit the same record.
    UserB gets a message that the record is being updated by another user.
    User A submits the update.
    UserB attempts to edit the same record.
    UserB edits the record, seeing the changes made by UserA.

    Case 2:
    UserA edits a record.
    UserB attempts to edit the same record.
    UserB gets a message that the record is being updated by another user.
    UserA goes to lunch without submitting any changes.

    Programmer has setup a Scheduled Task to check the database table for uncompleted edits. It looks for any records where the ID is in that applicaiton variable & EDIT_LOCK = 1 and the difference between LAST_EDIT and now() is greater than the session timeout value. Update those records to EDIT_LOCK = 0.

    UserB attempts to edit the same record and eventually is allowed access since UserA's session has timed out..
    UserA comes back from lunch and clicks "submit" on his still open edit form.

    UserA should have been kicked out by the application checking for an active session before processing anything else.
    Just in case, since EDIT_LOCK will have been reset to 0, the UPDATE statement will still fail since its WHERE clause is only updating records where EDIT_LOCK = 1.

    Case 3:
    UserA edits a record.
    UserB attempts to edit the same record.
    UserB gets a message that the record is being updated by another user.
    UserA goes to lunch without submitting any changes.
    UserB catches UserA at the door and tells him, "hey I need to update that record".
    UserA goes back to his desk and clicks "Cancel", which instead of going back to the Record List page, it goes forward to a cancel_edit.cfm?recordID=X page, so that EDIT_LOCK for that record can be cleared. (Getting UserA to always click "Cancel" is a matter of training.)

    On top of all this, you can also build an Admin section where someone can override any in-progess edits by setting the EDIT_LOCK to 0 at any time.

    4 replies

    A
    Anonymous
    June 25, 2007
    Just wanted to post a followup to this. Thanks cf_menace for the direction. I implemented your solution exactly, with one exception. I didn't set up the application scope array storing the locked record ids, because it seemed unnecessary once I got into implemented the record locking system. I was already storing the status of the record in the table itself ("edit_lock" column), so I simply check against that column value when a user attempts to open a record for editing. If the status in "edit_lock" is '1', then I prevent opening and redirect to a page notifying them that the record is already open for editing, and by whom. My CRON job just checks the same db column for status of '1' and compares the "last_edit" time with the current server time, resetting to '0' if the time lapse is greater than 30 minutes. I prevent any updates of records where the "edit_lock" status is '0', and voila: Done.

    Very nice method, and I again thank you. Let me know if you think there's a big problem using the stored db status versus an application scope status.
    E
    efecto747
    Participating Frequently
    June 14, 2007
    Hi,

    Have you considered using a Wiki to manage the communication between users? The situation you've described is sounds perfect for a Wiki - might be easier than re-inventing the wheel..

    If you do a Google on wiki you'll get a ton of links to various tools and add-ons - here's just one example: http://twiki.org/

    cheers.
    D
    Dan_Bracuk
    Inspiring
    June 14, 2007
    quote:
    Originally posted by: denvermax
    I've built an ad campaign managing tool with Coldfusion and mySQL which allows multiple users in diverse locations to monitor, edit and communicate about ads which are scheduled for publication as part of a campaign. Recently, it came to my attention (duh!) that it is important to prevent multiple users from opening the same ad for editing. I could use CFLOCK on my INSERT or SELECT statements, but I think I might rather CFLOCATION to a page explaining that another user already has the ad open for editing. I've been pondering setting application scope variables whenever an ad table row gets opened for editing, then testing for such variable and delivering a message notifying the user that the record is already being modified. Am I on the right track here? Or am I missing something obvious?

    I've contemplated the potential for a user to open an ad for edit, then never submit or otherwise close the session, but a timout on the application variable could handle that, couldn't it?
    max

    This is not a programming issue, it's a business issue your users have to resolve. Get them to tell you who is allowed to do what, and then write code for that.

    Otherwise, store and display who did what when, and let your client decide what the ad should say.
    A
    Amiable_Penguin15D5Correct answer
    Participating Frequently
    June 14, 2007
    Add a column named "edit_lock" to that table, default it to 0. Add a column named "last_edited".

    When a user requests to edit any record, check if the value is 0. If it is, then update it to 1, update LAST_EDITED to #now()# and allow the user to edit the record. Add that record ID to an array that's stored in the applicaiton scope. When the user submits a change, update the record ( UPDATE someTable SET foo = "wibble", EDIT_LOCK = 0 WHERE ID = #x# and EDIT_LOCK = 1 ), set the lock value to 0 and remove that element from the application variable.

    Case 1:
    UserA edits a record.
    UserB attempts to edit the same record.
    UserB gets a message that the record is being updated by another user.
    User A submits the update.
    UserB attempts to edit the same record.
    UserB edits the record, seeing the changes made by UserA.

    Case 2:
    UserA edits a record.
    UserB attempts to edit the same record.
    UserB gets a message that the record is being updated by another user.
    UserA goes to lunch without submitting any changes.

    Programmer has setup a Scheduled Task to check the database table for uncompleted edits. It looks for any records where the ID is in that applicaiton variable & EDIT_LOCK = 1 and the difference between LAST_EDIT and now() is greater than the session timeout value. Update those records to EDIT_LOCK = 0.

    UserB attempts to edit the same record and eventually is allowed access since UserA's session has timed out..
    UserA comes back from lunch and clicks "submit" on his still open edit form.

    UserA should have been kicked out by the application checking for an active session before processing anything else.
    Just in case, since EDIT_LOCK will have been reset to 0, the UPDATE statement will still fail since its WHERE clause is only updating records where EDIT_LOCK = 1.

    Case 3:
    UserA edits a record.
    UserB attempts to edit the same record.
    UserB gets a message that the record is being updated by another user.
    UserA goes to lunch without submitting any changes.
    UserB catches UserA at the door and tells him, "hey I need to update that record".
    UserA goes back to his desk and clicks "Cancel", which instead of going back to the Record List page, it goes forward to a cancel_edit.cfm?recordID=X page, so that EDIT_LOCK for that record can be cleared. (Getting UserA to always click "Cancel" is a matter of training.)

    On top of all this, you can also build an Admin section where someone can override any in-progess edits by setting the EDIT_LOCK to 0 at any time.
    A
    Anonymous
    June 15, 2007
    What an outstandingly thorough answer, cf_menace. I will begin implementing this as a solution.

    Thanks so much!

    max
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices