Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2004
    Posts
    91

    Unanswered: What is this methodology called

    Hi everyone -

    I'm stumped on what to cal this, there might even be
    a method or pattern named for what i am trying to accomplish...

    In the database, a number field is included on each table

    When the DAL reads the record from the database, it is passed to
    the client - work is possibly done to the record and is sent
    back to the DAL for update.

    A query is done against the table to retrieve the record again,
    the numbers are compared - if they don't match, it is assumed the record
    been modified by another user/thread/activity. An error is returned to the client stating the data has been changed.

    if the numbers match, the record is updated with the number field being incremented by one.

    what is this methodology called (beside crap :-) )


    thanks
    tony

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    it sounds like someone has coded there own locking scheme. it could be more graceful if user A was not allowed to "checkout" a record if user B has not "checked in" the record.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I was going for crap

    It seems they are looking to avoid data collisions in a most unique way

    They also seem to be wanting to number of row modification that are made

    I'm more concerened with what has changed.

    I do this

    1. Every table as adddt and upd_dt
    2. Every Update statement has in it's predicate the upd_dt (I hack upd_dt on insert so the 2 dates are identical so I don't have to deal with nulls here)
    3. If @@rowcount is 0, I then check to see if the rows blown away, or if it is truly a data collision. The calling app must pass the datetime value it received from the original select. The check is done by doing an existance check, and setting return code output variable to tell the app what happened
    4. If the update is successful, a trigger fires and move the entire before image (in the virtual deleted table) to history, with the date of the modification and the type of operation ('D' or 'U'), the application user and the system user (usually a connection pooling id for my stuff)

    With this, I can know everything that is going on in my databases

    I'd be curious to see a code snipet of their updates...I assume they're using sproc access only to the database
    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.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Thrasymachus
    it sounds like someone has coded there own locking scheme. it could be more graceful if user A was not allowed to "checkout" a record if user B has not "checked in" the record.

    Are you serious?
    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 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think it's perfectly acceptable to wait 15 minutes for user B to go get a copy before checking it back in
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What's crap with that? It is just a standard disconnected check using row versioning. And I don't see how Brett's is substantially better -yes it is better but it is still essentially the same methodology.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2007
    Posts
    62
    Sounds like standard optimistic locking to me.

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by alt-088
    A query is done against the table to retrieve the record again,
    the numbers are compared - if they don't match, it is assumed the record
    been modified by another user/thread/activity. An error is returned to the client stating the data has been changed.

    if the numbers match, the record is updated with the number field being incremented by one.
    Quote Originally Posted by Brett Kaiser
    1. Every table as adddt and upd_dt
    2. Every Update statement has in it's predicate the upd_dt (I hack upd_dt on insert so the 2 dates are identical so I don't have to deal with nulls here)
    3. If @@rowcount is 0, I then check to see if the rows blown away, or if it is truly a data collision. The calling app must pass the datetime value it received from the original select. The check is done by doing an existance check, and setting return code output variable to tell the app what happened
    4. If the update is successful, a trigger fires and move the entire before image (in the virtual deleted table) to history, with the date of the modification and the type of operation ('D' or 'U'), the application user and the system user (usually a connection pooling id for my stuff)
    Both methods can be improved upon by using the timestamp data type in a column on the table (instead of a programmatically incremented number or a datestamp).

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

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