Commit without freeing locks

Hi to all oracle users,

I have a problem in understanding the commit logic of the oracle workgroup server version 7.3.

My application uses a select for update in order to lock a specific row when this row needs to be modified by the user. After the user confirms its changes with the OK button i use the regular oracle commit command to commit all the changes and to free all the locked rows.
If the user need to switch to other information that resides in another table and decides to modify this information i have the following problem: the commit of this nested transaction also frees the original locked row.

Example:

1. User want's to modify a client in table CLIENT.
2. SELECT ... FROM CLIENT FOR UPDATE.
3. User needs to select the city of the client and changes to the city maintenance.
4. User entries a new city row in the table CITY.
5. User commits its new CITY entry. (COMMIT)
6. This commit also frees the locked CLIENT what was not wanted.

Especially in a GUI environment, where it is usual to be able to switch to other program modules, it is almost impossible to check all the commits a user cound generate while the original locked row should remain locked.

Is the any hidden oracle secret i need to know?

I used to work with a database system that allowed me to commit without freeing locks for some specified tables.

Is there any similar method i can use when working with oracle?

I was also thinking of using several user sessions in order to be able to commit each session separately.

Any help is appreciated.

Dieter Oberkofler
Director of Engineering

LEADING BITS GmbH.               Tel             (+43-1) 586 76 11
Schleifmuehlgasse 5/17           Fax             (+43-1) 587 76 15
A-1040 Vienna                    E-mail     dtr_at_leadingbits.via.at
Austria                          Compuserve            100141.1314


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