Re: Row Lock

On Tue, 2003-10-07 at 10:27, Andreas Prohaska wrote:
> I don't think that there is something similar in postgres. You would have
> to do this on your own, using an "int" or "postgres timestamp". What I
> meant was something like:
>
>     1. Read the row from the database together with the optimistic
> control
>          column (occ). Let's assume it's an int with a current value of 1.
>
>     2. Remember this value (1) and let the user edit the record.
>
>     3. Now before writing the changes of the user into the database:
>
>     3.a  SELECT the row FOR UPDATE and compare the current value
>            of the occ with the value you remembered. If they are
>            not equal -> rollback, otherwise ...
>
>      3.b  increment the value of the occ to 2
>
>     3.c  UPDATE the record (releasing the update lock)
>
>
> As Dave Cramer suggested you may also use a timestamp with a
> trigger to do this automatically. Although I don't know if there
> wouldn't be a theoretical possibility that someone updates the
> record again before the trigger runs (I really don't know!).
No, this isn't possible. The timestamp column with an update/insert
trigger will work correctly.
>
> > > The easiest way to come around this sort of problem is to use an
> > > optimistic control column (like an int) that is incremented
> > > with every row update. This allows you to detect changes by other
> > > users before making your own.
> >
> > I'm not really sure what do you mean with the use of
> > optimistic control
> > column, can you please give me some detail?
> >
> > The problem (for me) is to find a solution that is functional also for
> > clients that uses the db other the mine.
> >
> > In mysql there is the column type "timestamp" that does exactly this:
> > records the time of the last update of the row, so I need to
> > check only
> > this value.
> >
> > Is there something similar in postgresql?
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
Dave Cramer <Dave@micro-automation.net>