Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Databases
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 2nd, 2004, 11:37 AM
Authorized User
 
Join Date: Jan 2004
Location: , , .
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default Record locking in an ASP application

I am writing an ASP application that uses forms to edit an SQL Server 2000 database.

Each form edits one record.

I want the app to be used simultaneously by several people. I want to create a "locking" mechanism so that if person A has a form open (to edit a record) person B cannot open that form for that record, to edit it (possibly I would allow them to read it but disable the form elements, including the submit button).

What is the best way to achieve this?

One method I thought of is to have an "isLocked" field in each record. This is set to "true" when someone opens the record to edit it, then set to "false" when the edit is submitted.

However, this method is no good because there is no guarantee that the edit will be submitted by the user (e.g. he may open the record and view its contents in the form, and then close his browser before doing anything else. This would leave an incorrect "islocked" state in the record of the database).

This is a different issue from "record locking" in SQL. I need to create multi-user restrictions within the app itself. I am sure you know what I mean.

So... how do I do this? Is there a good way?

Thanks.

James

//##

Reply With Quote
  #2 (permalink)  
Old February 2nd, 2004, 11:56 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Instead of a flag to indicate locked state, save a lock time.

When another use tries to edit the record, if lock time has expired they can edit it. You'll need to make in obvious to an editor that they must complete changes within the lock expiration time. You could also put a timed refresh on the page that is the same as the lock expiration time. Maybe even show a timer on the page. This way, the user has some feedback about how long they have. In they leave the browser for long enough, refresh the page away from the edit state so they don't try to save changes and then fail.

Alternatively, use a flag AND a timestamp. The flag indicates the lock state, and the time is the lock time. Then you could have a SQL job that runs and checks the lock times and automatically unlocks records that are past expiration. This would potentially simplify the code you need in the ASP pages for checking the state/time of the locks. You only need to check the state instead of dealing with time checkes. It's also then very easy to change the time of lock expirations, because you only need to change the comparison in the SQL run be the "auto unlock" job.

Peter
------------------------------------------------------
Work smarter, not harder.
Reply With Quote
  #3 (permalink)  
Old February 2nd, 2004, 12:35 PM
Authorized User
 
Join Date: Jan 2004
Location: , , .
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks.

Are these two methods the "standard" ones for ASP applications? (I don't really want to impose a time limit on a user, which would rule out the first approach you describe, and maybe also the second). I want to implement a solution that is generally accepted by professional programmers as the best.

What do people think?



Reply With Quote
  #4 (permalink)  
Old February 2nd, 2004, 01:37 PM
Registered User
 
Join Date: Jan 2004
Location: Gainesville, fl, .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to tlbacon527 Send a message via MSN to tlbacon527
Default

The one solution I was given was not to lock the record but keep a snapshot of the value that is editable and when the user submits it will compare the old snapshot with what is in the record and if it does not match, the new record would not be saved and send an alert to the user that the data is old and needs to be refreshed for them to submit their work.

  I personally haven't implemented it yet but another developer gave me this solution he uses as he felt locking doesn't always work well. Just another option.

Good luck,
Tamara
Reply With Quote
  #5 (permalink)  
Old February 2nd, 2004, 04:24 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Tamara's suggestion is very good. Locking can certainly be problematic if you don't want to impose time limits. That's more like a passive protection approach. Instead of blocking a second user, check that nothing's changed since you loaded the record before you update.

For a web based application, a timed lockout is probably the best (if not only) form because of the stateless nature of the environment.

Peter
------------------------------------------------------
Work smarter, not harder.
Reply With Quote
  #6 (permalink)  
Old February 3rd, 2004, 05:13 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

The 'locked' flag is not a good idea, it'll cause you more problems than it will solve.

A standard way (similar to Tamara's but simpler) is to have a timestamp field which is updated every time the record is, umm, updated.

Instead of comparing snapshots of the whole record just compare the timestamp values before updating - if the record's current timestamp is later than the snapshot timestamp then don't do the update because someone else has changed the data since it was acquired.

As you're using SQL Server this is v easy to implement - just do all your updates via stored procedures and incorporate the checking and timestamp updating in the stored proc.
Reply With Quote
  #7 (permalink)  
Old February 3rd, 2004, 07:58 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Phil, what are the "more problems" that a lock flag will cause? I'm curious to know why you think that's not a good idea.

I do like your idea better, certainly makes more sense.

Also, out of curiousity, is there any way of generating a checksum of sorts for a row?

Peter
------------------------------------------------------
Work smarter, not harder.
Reply With Quote
  #8 (permalink)  
Old February 3rd, 2004, 08:16 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Mainly I think that you'll spend a lot of time trying to sort out flags that indicate a record is locked when the user has long ago left the page. IMO trying to code record locking yourself is just a waste of time -leave it to the db.
Reply With Quote
  #9 (permalink)  
Old February 3rd, 2004, 08:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Peter, sorry I didn't answer your checksum question. SQL Server has BINARY_CHECKSUM and CHECKSUM T-SQL functions so I guess you could use those instead - never used them myself though.

rgds
Phil
Reply With Quote
  #10 (permalink)  
Old February 3rd, 2004, 10:35 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Provided that one could work around the shortfalls of BINARY_CHECKSUM, it would almost seem this would be a better method for this problem. Instead of worrying about the time a record was last updated, why not save the retrieved data with its checksum, then compare that with a new checksum before you attempt an update. This would seem to me to be an even more robust solution.

Peter
------------------------------------------------------
Work smarter, not harder.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Record Locking & Transactions in Strongly Typed DS Kia Visual Basic 2005 Basics 4 July 23rd, 2007 06:23 AM
ADO Record Locking pjohanne VB Databases Basics 3 March 9th, 2006 05:42 AM
Record locking Stanny Access 1 February 11th, 2006 12:57 PM
Record locking - user needs the next queued record cbtoolkit SQL Server 2000 0 December 6th, 2004 08:29 AM
Locking a record Bune SQL Server 2000 3 August 26th, 2003 09:50 AM



All times are GMT -4. The time now is 10:12 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.