I have a huge problem. I'm running postgres on WinXP for testing / development reasons.
Threre is this little peace of software I'm currently developing... It's simply a customer management tool. A article management tool will follow along with a management tool for a whole business sale process (invoice and so on).
The System is based on Java and Postgre 7.1 is its development DB.
Now here is my problem.
When a user selects a customer (e.g. ID=1) for editing, edits some entrys and clicks "save and exit" the app does the following :
- SELECT * FROM customers WHERE CUS_ID=1;..... //show the current data
- after that it reads the changed values....
- UPDATE customers SET ..... WHERE CUS_ID=1;
everything works fine.
Now I guessed, that if I would like to run the app in a multi user environment it has to tell if the current record is edited by another user.
USER1 selects customer 1 for editing
before he hits "save and exit" another user,
USER2 intends to edit customer 1.
So my app has to tell user2 that the record is in use.
I tried many things:
- SELECT * FROM customers WHERE CUS_ID=1 FOR UPDATE;
-> everytime a user hits the edit button.
When I try to lock the record by "Select 4 UPDATE" again it will cause the DB to freeze until "COMMIT". The statement_timeout does not seem to work.
That is the problem.
Is there another way of telling which row is locked by any user accessing the db at the moment? Has anyone got an idea how to get around this?
Please I would appreciate your help very much.
I like the idea of using an incrementing "last_edited" field on records. When user one loads the record, lets say last_edited = x. User two then loads the record and again last_edited = x. User one saves the record:
update my_table set my_field = 'my_value', last_edited = (x+1) where id = 1 and last_edited = x;
One row is affected because the record hasn't been edited since user one loaded it. User two saves the record but because last_edited = (x+1) the where clause fails, zero rows are affected. Because zero rows are affected we can assume that the record has been changed since user two loaded the record. We can then load the changed record and show user two the differences, explaining that the record was change since she originally loaded it. User two can then have the option of accepting user one's changes, overwriting the changes, or a combination of both.
Of course this may not be suitable for what you're doing, and there is the issue of the record being changed again by user three while user two is making up her mind about user one's changes...