Re: Row Lock

On Tue, 2003-10-07 at 10:00, Alessandro Polverini wrote:
> On Tue, 2003-10-07 at 12:20, Andreas Prohaska wrote:
> > Even if this function would be implemented, your application would have
> > to keep the ResultSet/Transaction open while the data is presented to
> > the user in order to detect updates. So it would still be bad design.
>
> I agree. Anyway that kind of algorithm is used also for normal (batch)
> processing:
> - <loop> for every row of the result set
> - read the row
> - elaborate
> - check if the row has been modified, and, if not, store it. If it's
> modified, rollback everything
> - <loop>
>
> So, a method to know if a row has been modified from the extern, is
> needed.
>
> > After all I think that it is not possible to implement this function
> > because the backend itself handles updates/transactions in a way that
> > does not allow this kind of check (at least: doesn't directly support
> > it).
> >
> > 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?
an optimistic control column is exactly like the timestamp column below
>
> 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?
yes, just make it a timestamp and create a trigger for it which will be
updated everytime you update the row.
>
> Thanks,
> Alex
>
> > > Hello,
> > > I also have to do a similar program and I ended up implementing this
> > > exact scheme.
> > > But I had to use hashes of the record to check if it was
> > > changed because
> > > it seems like the proper function, expressely designed for that is not
> > > implementend: ResultSet.rowUpdated().
> > >
> > > Do you know if there are plans of supporting it?
> > >
> > > It would be a great boost for JDBC applications.
> > >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
--
Dave Cramer <Dave@micro-automation.net>