Re: Commit without freeing locks

Dieter Oberkofler wrote:
>
> 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
> ------------------------------------------------------------------You're allowed one active transaction per conection. If you need to
maintain more than one concurrently active transaction, create another connection.

-- 
Rich Woods, Field Support Analyst 
Oracle Corporation, USA
The comments and opinions expressed herein are mine and 
do not necessarily represent those of Oracle Corporation.