Thread: Row Lock
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
-- 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:
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
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 > >