Re: Commit without freeing locks

gwn_at_servtech.com wrote:
>
> Why do you need to commit every little database change?
> Why not perform all the database updates necessary
> for the original transaction, then commit (and unlock) them all at once?
>
> If the problem is that the update code doesn't know whether to commit or not,
> pass it a flag as a parameter (commit or not). Then just the highest-level
> procedure would do the COMMIT.
>
> If you REALLY need to keep the original table locked over several
> transactions, I suggest you create a "locked" field in the table,
> and then lock the record; update the field; and unlock it.
> Now you can commit all you want, and as long as your other queries
> watch the "locked" field, no one else will update your record.
> Just watch for records that get "locked" without being "unlocked".
> --
> ------------------------------------------------------------------------------
> Computer General Rochester, NY (716) 436-6372
> "... providing general computer solutions to specific business problems"
> gwn_at_servtech.com

Thank you for your information!

The trick with the lock field seems ok but also needs a lot of changes in an application and has one big problem:
If the application for some reason is not able to free this "locked" field the database keeps this row as locked without any chance to recognize that the session that originally locked the row does no longer exist.

I really thing that this problem should be quite common in an event driven environments where the user can decide at any time to interrupt a change and to spawn to another branch of the application.

Just immagine a simple application that has a menu for all the basic information like clients, supplier, currencies etc. where while the user currently works on the client maintenance he can just invoke the maintenance of supplier by selecting the menu item.

I just can't believe that oracle does not provide an easy and save way to deal with this kind of problem.

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
------------------------------------------------------------------