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