Thread: Row Lock

Row Lock

From
"Waldomiro"
Date:
Hello,
 
I have an aplication which ask a code to bring up the data on screen, then the user can change the data and save it.
 
My problem is :
 
2 users at same time can put the same code and make diferent changes.
 
Are there any way to lock this record in postgres when the first user is changing the data on screen ?
 
What shoud I do ?
 
Thanks
 
Waldomiro C Neto.
Shx Informática Ltda.
+55 (11) 5581-1551
www.shx.com.br
wmiro@shx.com.br
 

Re: Row Lock

From
Dave Tenny
Date:
-- begin transaction
SELECT rowstuff FROM table WHERE record-of-interest = somekey FOR UPDATE
... get new value from the user
UPDATE rowstuff WHERE record-of-interest = somekey
COMMIT

-----------------------

However, it is considered exceedly poor application design to have any interactive
dialogs in the midst of a transaction.  (Really bad, don't do it!  You'll block potentially all users
for potentially unlimited amounts of time).

Suggest you get the row value, remember it, present to the user for update OUTSIDE a transaction,
then start a transaction to update when you have the new value.  If the row value has changed to something incompatible
with the user's value, abort and complain, otherwise do the update.


Waldomiro wrote:
Hello,
 
I have an aplication which ask a code to bring up the data on screen, then the user can change the data and save it.
 
My problem is :
 
2 users at same time can put the same code and make diferent changes.
 
Are there any way to lock this record in postgres when the first user is changing the data on screen ?
 
What shoud I do ?
 
Thanks
 
Waldomiro C Neto.
Shx Informática Ltda.
+55 (11) 5581-1551
www.shx.com.br
wmiro@shx.com.br
 

Re: Row Lock

From
Alessandro Polverini
Date:
On Fri, 2003-10-03 at 15:34, Dave Tenny wrote:
> -- begin transaction
> SELECT rowstuff FROM table WHERE record-of-interest = somekey FOR
> UPDATE
> ... get new value from the user
> UPDATE rowstuff WHERE record-of-interest = somekey
> COMMIT
>
> -----------------------
>
> However, it is considered exceedly poor application design to have any
> interactive
> dialogs in the midst of a transaction.  (Really bad, don't do it!
> You'll block potentially all users
> for potentially unlimited amounts of time).
>
> Suggest you get the row value, remember it, present to the user for
> update OUTSIDE a transaction,
> then start a transaction to update when you have the new value.  If
> the row value has changed to something incompatible
> with the user's value, abort and complain, otherwise do the update.

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.

Thanks,
Alex


> Waldomiro wrote:
> > Hello,
> >
> > I have an aplication which ask a code to bring up the data on
> > screen, then the user can change the data and save it.
> >
> > My problem is :
> >
> > 2 users at same time can put the same code and make diferent
> > changes.
> >
> > Are there any way to lock this record in postgres when the first
> > user is changing the data on screen ?
> >
> > What shoud I do ?
> >
> > Thanks
> >
> > Waldomiro C Neto.
> > Shx Informática Ltda.
> > +55 (11) 5581-1551
> > www.shx.com.br
> > wmiro@shx.com.br
> >