Thread: Row Lock

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.

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.

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

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?

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?

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

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>

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!).

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

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>