Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2004
    Posts
    3

    Unanswered: I Want to Lock One Record

    Please help! I have a VB application used by multiple users. MS SQL 2000 backend.

    When a user opens a record for editing, it want to mark the record as locked so that if other users try to open this record, it will not be available.

    Currently, I'm using a programmatic method that basically sets a field 'lock" to 1 if the record is in use and 0 if not in use. But this has the side affact of locking the record if the user reboots their workstation. In addition, I don't believe that this is the best method and approach to achieve my goal.

    Thanks very much for your help in advance. All ideas are welcome.

    crownlog

  2. #2
    Join Date
    Jan 2004
    Location
    Shanghai,China
    Posts
    76
    I think u can make use of SQL Server's locking mechanism.
    example:
    U can put a special feild as a semphemore.
    when a user opens a record,the program begin a transaction and update the field to 1 immdiately.When the user press save button in front-program,the transaction will commit.When the locker workstation reboots,the transaction will auto-released right now,and the other waiting program will get the control immdiately.

  3. #3
    Join Date
    Oct 2004
    Posts
    3

    Cool More data please:)

    enhydraboy,

    Thanks very much. Might you have more details or an example in using MSSQL2K locking mech.

    Regards
    crownlog

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nobody?

    This is a bad design. It's the path that leads to....the dark side..

    You should desgin the table so that it has an update datetime column.

    Every time a row is modified, update that column.

    If you need to know if the row was modified, check that column again and compare the datetimes...if they are the same, good to go....if they're different, show the user the changes...you could even build something to allow them to merge the 2...then apply the update...

    The my own opinion (MOO)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    crownlog ... listen to Brett's advice. What if your user, after locking the row, goes to lunch or home for the weekend or Aruba for the week or Australia after embezzling $1M? best to not lock the row until ready to update!

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    should read...

    "best to let sql server manage ALL of your locking"
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Oct 2004
    Posts
    3

    Unhappy

    Actually, the issue is that I need to open a record and keep it opened the entire time it is being edited by user1 - could be minutes. I need to keep all the other users from trying to open it while it is being edited by User1. I only need that record locked during the edit.

    What's the best way to do it?

  8. #8
    Join Date
    Jan 2004
    Location
    Shanghai,China
    Posts
    76
    Why is a bad design?
    Is A timestamp field good?
    What I can confirm is that is not what the crownlog requests.

  9. #9
    Join Date
    Jan 2004
    Location
    Shanghai,China
    Posts
    76
    Quote Originally Posted by crownlog
    Actually, the issue is that I need to open a record and keep it opened the entire time it is being edited by user1 - could be minutes. I need to keep all the other users from trying to open it while it is being edited by User1. I only need that record locked during the edit.

    What's the best way to do it?
    Let me tell my solution in detail,but u must suffer my bad english first.
    Everybody knows that RDBMS uses locking to keep consistency of updating and reading during cocurrent sessions.
    So when we program in VB/VC/Delphi,we needn't care anything because sqlserver engine will do it better.

    crownlog's case is that he want find a solution that will synchronize transaction between different clients(according to per application).
    So we must establish a synchronization rules,we need a center-locking control.
    There are many solution u can choose:
    1 Unique Application server,u must program by yourself
    2 Let SQL Server became a center-locking control server,because database locking control is very perfect.What u need do is understanding it and making use of it.

    About "best to let sql server manage ALL of your locking",I haven't read the book.But I know if I unstand sql server well,I am not fraid to using it and I well let sql server serve for me saftly.

  10. #10
    Join Date
    Jan 2004
    Location
    Shanghai,China
    Posts
    76
    Quote Originally Posted by tomh53
    crownlog ... listen to Brett's advice. What if your user, after locking the row, goes to lunch or home for the weekend or Aruba for the week or Australia after embezzling $1M? best to not lock the row until ready to update!
    It's the problem crownlog must think it over.
    How to realize his requests in UI function.
    Using locking or using timestamp,U will not avoid the problem that when one body go home for sleeping without quit his appliction,the others must wait he come back to finish his work.
    Maybe u must need administrator to force one to go out.So It's not the problem of techniques.

  11. #11
    Join Date
    Nov 2004
    Posts
    4
    Maybe a combination of the two. A binary field to determine whether the record is being currently used, and a time limit of say 10 minutes, so another field with a timestamp. Assuming all the systems are using a level 2 time clock server. You could have a script running in the background to clear out any locked fields and set it to 0 if the time has lapsed comparative to the timestamp. Also setting a timer on the page of your application, and maybe a timestamp refresh button. It would force the user to pay attention to what they are doing. If I'm not getting my idea across, just drop me an e-mail, and I'll try to get some sort of flowchart together to explain it better.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •