dealing with concurrency issues

Panoy · #1 (**permalink**) 07-03-07, 11:55

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!

Grafixx01 · #2 (**permalink**) 07-03-07, 12:39

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.

Thrasymachus · #3 (**permalink**) 07-03-07, 13:47

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?

pootle flump · #4 (**permalink**) 07-03-07, 13:56

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).

Panoy · #5 (**permalink**) 07-03-07, 17:36

Quote:

]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!

pootle flump · #6 (**permalink**) 07-03-07, 17:59

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?

mike_bike_kite · #7 (**permalink**) 07-04-07, 09:03

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

pootle flump · #8 (**permalink**) 07-04-07, 09:21

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.

pootle flump · #9 (**permalink**) 07-04-07, 09:24

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.

Panoy · #10 (**permalink**) 07-04-07, 10:33

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.

mike_bike_kite · #11 (**permalink**) 07-05-07, 03:49

Quote:

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

pootle flump · #12 (**permalink**) 07-05-07, 04:40

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

Pat Phelan · #13 (**permalink**) 07-05-07, 09:52

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

Panoy · #14 (**permalink**) 07-05-07, 15:03

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".

Pat Phelan · #15 (**permalink**) 07-05-07, 17:17

Every time I build something that is "fool proof", mother nature up's the ante by creating an even more adroit fool.

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech