Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2007
    Posts
    77

    Question dealing with concurrency issues

    Hi to all,

    How does one deal with multi-user scenarios? About concurrency issues, when one record does not get modified by the other one. That "last one to update wins"?

    Is the timestamp technique the elegant solution. Are there more efficient techniques aside from timestamp? Locking the table? How about in transactional operations?

    Hoping to hear from your opinions. Thanks and god bless all!

  2. #2
    Join Date
    Aug 2006
    Posts
    559
    You know, I saw the solution to this in a book that I have but that book is at my house. I'll look for it tonight for you.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    the default locking scheme for an enterprise RDBMS should properly handle this.

    sometimes developers will cause problems by creating long running explicit transactions that will cause significant blocking but then you should really address the way your code is written and the way the DB is designed.

    are you having a particular problem?
    “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.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus
    the default locking scheme for an enterprise RDBMS should properly handle this.
    Depends on what model you are using though Sean. Anything based on a sproc API isn't going to use a default locking scheme. Nor anything that acts as a disconnected client. In these cases you need to work out your own. I assume this is what the OP means.

    I like timestamp. You need to be careful though that you only ever update rows you intend to update.

    Code:
    UPDATE MyTable 
    SET MyCol = 'Something'
    is a disaster if using timestamps. At a minimum it needs to be
    Code:
    UPDATE MyTable 
    SET MyCol = 'Something' 
    WHERE MyCol <> 'Something' OR MyCol IS NULL
    (and whatever additional conditions your business rule requires).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2007
    Posts
    77
    ]I like timestamp. You need to be careful though that you only ever update rows you intend to update.


    Code:
    UPDATE MyTable
    SET MyCol = 'Something'is a disaster if using timestamps.
    I thought using timestamps to update is specified by this sQL statement:
    Code:
       UPDATE table SET field = fieldvalue 
                  WHERE fieldID = fieldIDValue 
                  AND fieldTimeStamp = TimeStampValueSinceRead
    Hope you could elaborate more. I am just a beginner
    Thanks and god bless!

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Panoy
    I thought using timestamps to update is specified by this sQL statement:
    Code:
       UPDATE table SET field = fieldvalue 
                  WHERE fieldID = fieldIDValue 
                  AND fieldTimeStamp = TimeStampValueSinceRead
    Hope you could elaborate more. I am just a beginner
    No - you are quite right. Sorry - I didn't express what I meant properly.

    The above code you wrote is what your applications should always be using. I was thinking more about admin\ one off statements you might run on sets of data. Whereas in circumstances where there is no timestamp column you might not necessarily be careful to write a statement that affects only (and nothing but!) the records you absolutely have to touch,you must be careful if there is a timestamp.

    So in the example I showed you might decide to update a column for a set of data to one value or another. You would of course make sure that you affect no records that should not be updated. But would you always be careful that you do not ever touch any records you do not have to touch?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    You should also be setting the timestamp again in the update otherwise all the apps that read the record will still be able to update the record. You also need your code to check that a record has been updated otherwise the user will think he's done the update and later find it didn't happen.

    Code:
       -- do update
       UPDATE table 
       SET      field = fieldvalue,
                  fieldTimeStamp = now()
       WHERE  fieldID = fieldIDValue 
                 AND fieldTimeStamp = TimeStampValueSinceRead;
    
       -- test it happened
       if row_count() != 1 then
                 -- raise some sort of warning
       end if;
    Most of all you should really decide whether this is an issue for your application. Many apps only allow the user to change the data related to them so this wouldn't be an issue. Assuming this is a reasonably simple non financial application you could also just ignore the problem all together and state that "the last update wins" is the proper behaviour for your system If you want all the user updates to make an effect no matter what order they come in then store the changes in a separate transaction table.

    Mike

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mike_bike_kite
    You should also be setting the timestamp again in the update otherwise all the apps that read the record will still be able to update the record. You also need your code to check that a record has been updated otherwise the user will think he's done the update and later find it didn't happen.
    Hee hee - I think this might uncover another methodological distinction to further separate us

    I would put the update for the timestamp in a trigger - as close to the data as possible. Then no need to worry about going through the application\ sprocs or accounting for it in every update.

    You are of course totally correct though - if it is not handled automatically via a trigger then you will need to account for it in all your SQL.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A further "lighter" concurrency option is to compare the value of each column to be updated with a record of its previous value. Reject updates where the column value has changed. Essentially this is an attribute locking method rather than a row locking method.

    This may of course be totally inappropriate for your environment.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Mar 2007
    Posts
    77
    Yes, I was also putting that to mind, about that trigger. Updating the timestamp value of you table through triggers. In that way you would not worry about the updating of the timestamp value.

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally Posted by pootle flump
    Hee hee - I think this might uncover another methodological distinction to further separate us
    Not at all. It was just I didn't see how the timestamp was going to be set in the proposed solution. Triggers are the obvious choice but seeing as the chap mentioned he was a beginner I thought it easier to do it via the original update.

    Mike

    Must admit though I prefer databases where all the actions happen through sprocs as opposed to using embedded SQL and then relying on triggers to keep the RI - I just find it easier to follow the logic ... but I won't raise that subject! Glad to see the spirit of Salem / McCarthy is still riding high in the colonies though! Happy 4 July anyway

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mike_bike_kite
    Not at all. It was just I didn't see how the timestamp was going to be set in the proposed solution. Triggers are the obvious choice but seeing as the chap mentioned he was a beginner I thought it easier to do it via the original update.

    Mike
    Fair point

    Quote Originally Posted by mike_bike_kite
    Must admit though I prefer databases where all the actions happen through sprocs as opposed to using embedded SQL and then relying on triggers to keep the RI - I just find it easier to follow the logic ... but I won't raise that subject! Glad to see the spirit of Salem / McCarthy is still riding high in the colonies though! Happy 4 July anyway
    Well - this ain't an RI issue. For something like this I prefer triggers. The example I gave was for an adhoc update. As such, this is less likely to be done via a proc. You can't afford to not update the field so a trigger, though less transparent I will give you that, is at least fool-proof. And when I am involved this is paramount

    Not sure what you mean but I am a a Brit too
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by pootle flump
    Not sure what you mean but I am a a Brit too
    True, but nobody holds that against you around here!

    -PatP

  14. #14
    Join Date
    Mar 2007
    Posts
    77
    Instead of creating triggers to update timestamp values, I think a stored procedure which does the update of the timestamp field is also appropriate. But we have to restrict from updating in sql statements and just call those stored procs.

    The trigger technique is just what pootle flump said, which is "fool proof".

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Every time I build something that is "fool proof", mother nature up's the ante by creating an even more adroit fool.

    -PatP

Posting Permissions

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