Re: LOCKING problems !!!!! HELP !!!!

esalk_at_alpha (Esther Salk) writes:

>Hi,
>
>We have some problems with explicit locking. We are building an
>MS-Access client application storing it's data on an ORACLE-server.
>When one user tries to update or delete a specific record on a table,
>and this record is currently in use by another user, one has to
>wait for this lock to be released. Now I can find in an ORACLE guide
>the following: "If the wait for a row lock is excessive, users
>might want to cancel the lock operation and retry at a later time;
>you might prefer to code such logic into applications."
>
>Yes indeed, I would like to do this, I only don't now how. How can I
>cancel a lock operation which is already waiting??? My application
>just hangs and doesn't proceed until the lock is released ????
>Can anybody help me with this, please !!
>
>Esther Salk
>
>--
Hi
You got to use the NOWAIT option of the select statement in Oracle SQL. Since you are using MS-ACCESS avoid the ACCESS-SQL and use SQL-passthrough to specify the nowait option.
Refer to the following manual "Oracle Server SQL Reference "page 4-396. I quote from the manual.
The NOWAIT option of SQL for select for update returns control to you if the SELECT statement attempts to lock a row that is locked by another user. If you omit this clause, Oracle7 waits until the row is available and then returns the result of the select statement. Note that the default is wait. That is the user waits to get the lock when it is locked by another user.
These problems are typically caused by poor application and table design. There is a script in $ORACLE_HOME/rdbms/admin called utllock.sql. Run this lock in conjunction with monitor lock facility to trace which users are locking data for update and ( going for a cup of coffee :) )

Mahesh Vallampati
Oracle Services
mvallamp_at_us.oracle.com
DBA and Unix admin.
713-750-5002
//In the beginning there was Codd.... Received on Wed Dec 20 1995 - 00:00:00 CET