Best Method for Updating a record 
Author Message
 Best Method for Updating a record

Well, guys, I'm thinking of a methodological problem:

I'm working with Informix just for some weeks (SYBASE before).

How to do updates to a table in respect of concurrency in the best way?

Assume in a multi-user environment an application reads and eventually
updates a table A. The sequence is simply to read the record, let the user
edit it and, after confirmation by the user write it back or not.
The record shall not be rewritten if the record changed in between!

There are some solutions to do this:

1.) Lock the record from the time it is read until it is rewritten.
    This has as I think some problems:
        a.) The first is that the record is locked for a long time if the
            user takes a coffie break or something else without unlocking
            the record. So in this case other applications can not access
            the record may be for a long time.
        b.) On the other hand, what happens if the user`s application fails
            before unlocking the record?
        c.) The application probably already at reading time has to know if
            writing has to be done (maybe records are locked that will not
            be rewritten).

2.) Do not lock the record!
    When rewriting the record, do a "full specification" of the old one.
    I mean, to be sure that noone else has modified the record in between,
    add a WHERE-clause to the Update that will fully specify the old record
    and so will not do the update if any value was modified.
    Here we have the overhead of remembering all old values in the application.
    It is the optimistic method that normally only 1 user works updating
    with 1 record at a time.

3.) Is there any easier method to 2.) without remembering the
    "full specification" of the old record like the usage of a
    timestamp in SYBASE? Has Informix a datatype like SYBASE's TIMESTAMP that
    changes on any update of a record?

Probably there are some different methods varying from the basic ones.
Of course, the whole environment in respect of performance, safety and the kind
of data has to be regarded for a decision of the method.

I would be pleased to hear anything about *your* experience and ideas
to this problem here in the newsgroup or by E-Mail.

Thanks,

Achim

--
  ==========================================================================
 / Achim Reiners                    Software Engineering                    \
|  /M/A/I Deutschland GmbH                                                   |
|  Softwarezentrum Koeln            Phone:  +49 221 956400-40                |
|  Mathias-Brueggen-Str. 85         Fax:    +49 221 956400-69                |

 \_________________________________________________________________________/  



Mon, 22 Apr 1996 00:26:30 GMT
 Best Method for Updating a record

Achim,

Comments inline.

Quote:
> There are some solutions to do this:

> 1.) Lock the record from the time it is read until it is rewritten.
>     This has as I think some problems:
>       a.) The first is that the record is locked for a long time if the
>           user takes a coffie break or something else without unlocking
>           the record. So in this case other applications can not access
>           the record may be for a long time.

This is the major issue with the pessimistic approach.  Depending on
the application this may or may not be possible choice.

Quote:
>       b.) On the other hand, what happens if the user`s application fails
>           before unlocking the record?

Online will recognise that the application program has died and will
automatically rollback the transaction that was in progress.

Quote:
>       c.) The application probably already at reading time has to know if
>           writing has to be done (maybe records are locked that will not
>           be rewritten).

This is controlled by the application through two things.  First the
"set isolation level" currently in force which can be dirty read,
committed read, cursor stability or repeatable read.  These define how
many and how long the rows selected within a transaction remain
locked.  Also rows can be deliberately given shared locks by the use
of the "select for update" syntax.  How long these locks last is also
dependant on the isolation level unless an update/delete is done to
the row.  In that case the lock is always held until commit.

Quote:
> 2.) Do not lock the record!
>     When rewriting the record, do a "full specification" of the old one.
>     I mean, to be sure that noone else has modified the record in between,
>     add a WHERE-clause to the Update that will fully specify the old record
>     and so will not do the update if any value was modified.
>     Here we have the overhead of remembering all old values in the application.
>     It is the optimistic method that normally only 1 user works updating
>     with 1 record at a time.

> 3.) Is there any easier method to 2.) without remembering the
>     "full specification" of the old record like the usage of a
>     timestamp in SYBASE? Has Informix a datatype like SYBASE's TIMESTAMP that
>     changes on any update of a record?

No, there is no TIMESTAMP.  But you can ensure that the same is
possible in Informix by creating a datetime field on each table
definition and then setting it to "Current" in any "insert" or
"update" statement.  Not as automatic as Sybase but it also allows for
giving all rows in a transaction a single time or the time when the
row is written as you prefer.  Using this datetime field as a
transaction timestamp means that you do not need to pass in the "full
spec' of the old record.

Quote:
> Probably there are some different methods varying from the basic ones.
> Of course, the whole environment in respect of performance, safety and the kind
> of data has to be regarded for a decision of the method.

This last is really the key.  I have used both for different
applications and find them just as easy to do once you have set up the
standards and development environment.  Though pessimistic is easier
for people to get used to, optimistic has the advantage of better
throughput generally and is essential if you are going to make the
application true client/server.

Cheers - Jim
My opinions are my own.  They may vary with time but they remain MINE!
----------------------------------------------------------------------

Company: DHL Systems Inc             Phone:    (415) 375-5222 (Work)
Address: 700 Airport Blvd. #300                (415) 882-9728 (Home)
         Burlingame, CA 94010-1937   Fax:      (415) 375-5019
----------------------------------------------------------------------



Tue, 23 Apr 1996 08:13:04 GMT
 Best Method for Updating a record
One way to achieve what you want is to add an Informix time-stamp field to the
relevant tables. A resolution of frac(3) should be more than enough.

Of course this method is cumbersome in that you have to update the time-stamp
manually but it is fairly cheap and reliable.

: Well, guys, I'm thinking of a methodological problem:

: I'm working with Informix just for some weeks (SYBASE before).

: How to do updates to a table in respect of concurrency in the best way?

: Assume in a multi-user environment an application reads and eventually
: updates a table A. The sequence is simply to read the record, let the user
: edit it and, after confirmation by the user write it back or not.
: The record shall not be rewritten if the record changed in between!

: There are some solutions to do this:

: 1.) Lock the record from the time it is read until it is rewritten.
:     This has as I think some problems:
:       a.) The first is that the record is locked for a long time if the
:           user takes a coffie break or something else without unlocking
:           the record. So in this case other applications can not access
:           the record may be for a long time.
:       b.) On the other hand, what happens if the user`s application fails
:           before unlocking the record?
:       c.) The application probably already at reading time has to know if
:           writing has to be done (maybe records are locked that will not
:           be rewritten).

: 2.) Do not lock the record!
:     When rewriting the record, do a "full specification" of the old one.
:     I mean, to be sure that noone else has modified the record in between,
:     add a WHERE-clause to the Update that will fully specify the old record
:     and so will not do the update if any value was modified.
:     Here we have the overhead of remembering all old values in the application.
:     It is the optimistic method that normally only 1 user works updating
:     with 1 record at a time.

: 3.) Is there any easier method to 2.) without remembering the
:     "full specification" of the old record like the usage of a
:     timestamp in SYBASE? Has Informix a datatype like SYBASE's TIMESTAMP that
:     changes on any update of a record?

: Probably there are some different methods varying from the basic ones.
: Of course, the whole environment in respect of performance, safety and the kind
: of data has to be regarded for a decision of the method.

: I would be pleased to hear anything about *your* experience and ideas
: to this problem here in the newsgroup or by E-Mail.

: Thanks,

: Achim

: --
:   ==========================================================================
:  / Achim Reiners                    Software Engineering                    \
: |  /M/A/I Deutschland GmbH                                                   |
: |  Softwarezentrum Koeln            Phone:  +49 221 956400-40                |
: |  Mathias-Brueggen-Str. 85         Fax:    +49 221 956400-69                |

:  \_________________________________________________________________________/  



Sat, 04 May 1996 22:10:34 GMT
 Best Method for Updating a record

Quote:

>: Well, guys, I'm thinking of a methodological problem:
>: I'm working with Informix just for some weeks (SYBASE before).
>: How to do updates to a table in respect of concurrency in the best way?
>: There are some solutions to do this:
>: 1.) Lock the record from the time it is read until it is rewritten.

I am not a very sophisticated 4GL programmer but i think that simply locking
the record is the way to go.

Quote:
>:     This has as I think some problems:
>:   a.) The first is that the record is locked for a long time if the
>:       user takes a coffie break or something else without unlocking
>:       the record. So in this case other applications can not access
>:               the record may be for a long time.

A user is responsible to terminate the programm before he goes home!

Let me put it that way: you don't integrate an automatic that turns the motor
of you car off after 10 minutes of standing still?!

And thats just because the effort to integrate such an automatic wouldn't pay
off.

Quote:
>:   b.) On the other hand, what happens if the user`s application fails
>:       before unlocking the record?

You lock a record within a transaction.  When your program is terminated the
sqlexec process will remove the lock.

Quote:
>:   c.) The application probably already at reading time has to know if
>:       writing has to be done (maybe records are locked that will not
>:       be rewritten).

That's trivial if you design your programs that way. For example make
a distinction between viewing data and editing data.

  A r n d

PS.: Seit wann hat den MAI einen Internet - Anschluss?



Sun, 05 May 1996 23:38:07 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Update Method fails to update record

2. What is best method to get recursive records?

3. Best Method to detect duplicate record on .ADDNEW?

4. Best method - selection list of ALL records

5. Best method to update table structures

6. Best Population method for instantaneous update?

7. best method of db updates in ADO

8. Best method: Update all fields or only those that have changed

9. Best method for updating tables

10. Best method of updating a dataset - ESQL

11. fast method to update records ?


 
Powered by phpBB® Forum Software