How to ensure only one process accesses a record in Oracle database table - 10-12-2004 , 02:27 AM
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?
Re: How to ensure only one process accesses a record in Oracle database table - 10-12-2004 , 04:27 AM
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
Re: How to ensure only one process accesses a record in Oracle database table - 10-12-2004 , 08:15 AM
don't block readers.
Cursors, together with SELECT ... FOR UPDATE & UPDATE ... WHERE CURRENT OF
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