| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Opinions needed: Locking strategy
In article <01bc18da$ebaf4280$e6b47dc3_at_svoigt-wkstn>,
Stephan Voigt <svoigt_at_voigt-gmbh.de> wrote:
>I´am using Oracle workgroup server 7.2.2 on netware and Delphi 2.01 on NT
>4.0 as frontend.
>
>I develop several applications in a multiuser environment. Like all
>sql-servers oracle performs standard locking on rows which are updated
>during transactions. These rows are unlocked upon commit or rollback. If
>two users try to update the same record, oracle waits until the record can
>be locked. But in my point of view, this is not very fine for a
>well-designed user-interface, when a user has to wait several minutes or
>hours for a lock.
>
>In my application, i want to check for a lock by another user before I try
>to update any record in order to prevent that the user must wait too long.
>I have already found something about the DBMS_LOCK package of oracle.
>Has anyone experiences with it ? If yes, please tell me.
>Are thery other ways for a good locking strategy ?
Developer 2000 (Forms) handles such cases by notifying the second user immediately when they try to make a change to a row that is locked. They get a message that the row is locked, and to try later. This leaves the user free to work on something else.
You can emulate this in software by trying to lock a row before updating by doing a select for update nowait. If the row is locked, the server returns "ORA-00054 resource busy and acquire with NOWAIT specified" and rejects the select.
In situations like this, I rollback my transaction and tell the user to try again later.
I just wish Oracle had a way of automatically killing sessions where a user has locked a row and then gone to lunch, leaving the record tied up and preventing others from completing their work.
Steve Cosner
 |
 |