On Mon, 2004-12-20 at 19:16, gregatacd wrote:

>
>
> DB: Firebird 1.5
>
> I'm looking for a way to mark a record as being updated. For
> example, I read a record with a timestamp or other mark, then when I
> update it (after the user enters some changes etc.), I find that the
> mark has changed somehow (another user updating the record
> underneath me).
>
> What would be the best way to mark a record as updated or added?

I use a UUID (GUID on windoze), a sequence number, creation timestamp,
and end timestamp on each row (plus other audit data). Application data
in rows is never deleted or updated.

Instead of a delete, I update the the END_TS on the row to NOW. (END_TS
is not aprt of the application data) instead of an update,, I update
the END_TS on the current record to now and insert a record with the
next sequence number. As a part of the update function, I include
"where SID = ? and END_TS is null", so it is impossible for me to (A)
inactivate an already inactive row, or (b) inactivate the wrong row as a
result of other concurrent work.

In this paradigm, no one ever edits a record that has been recorded.
The actual operation is invalidate and add a new (replacement) record.
With careful application of design, you can often limit the number of
places where changes to data need to be made on a regular basis.

>
> One potential solution would be to create a timestamp column and
> have an update/insert trigger update the timestamp column each time.
> Is there a performance issue with this (since getting the current
> time may not be overly quick, and it must be done once per
> update/insert).

The system hardware clock is addressable on most systems, but how it is
addressed varies widely. Its overheads are far lower than the more
common BIOS clock. On a 2.4 GHz intel platform I measured the overheads
of calling the hardware clock at approximately .002 milliseconds per
call, including all floating point math to establish this measure. For
my purposes, it was not worthwhile to measure any further than this.

Other enterprise scale DBMS' support microsecond precision when
recording timestamps. Firebird's internal data structures support at
least millisecond precision, but the decision was made at some point to
limit timestamps to 1 second precision in the interfaces to the outside
world. While I can live with and work around it, I personally find that
this design choice unnecessarily hampers an otherwise superior product.

>
> Or, add a 64bit integer column and have the triggers do a +1 on this
> column... would this be 'atomic' enough given that multiple users
> may be editing a given record...


>
> Any advice here on best practices would be great!
>
> Greg
>
>
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>