We have multiple processes running in parallel accessing the same
database table in Oracle. The processes access the table thru a
trigger and a stored procedure written for the trigger.

We want only one process to pick a record and update it with a status.

Is it possible to lock a record in Oracle ? Can any one tell me how we
can achieve this?

Thanks,
Srini

On 12 Oct 2004 00:27:46 -0700, aone1504 (AT) yahoo (DOT) com (Srini) wrote:


Records are automatically locked.
You seem to need a table level lock, consequently severely limiting
the scalability of your application. You can lock a table by issuing
the lock table statement, in your case in exclusive mode.
Note: this will not prevent records to be read.


--
Sybrand Bakker, Senior Oracle DBA

Srini wrote:

Note that Oracle only locks one row. Readers don't block writers, writers
don't block readers.

Cursors, together with SELECT ... FOR UPDATE & UPDATE ... WHERE CURRENT OF
might help.

You can create brand new 'named' locks and interrogate those. Check out
DBMS_LOCK package and it's documentation in the SUpplied PL/SQL Packages
and Procedures.