Re: How to ensure only one process accesses a record in Oracle database table

From: Hans Forbrich <news.hans_at_telus.net>
Date: Tue, 12 Oct 2004 13:15:49 GMT
Message-ID: <9OQad.33788$663.20385@edtnps84>

Srini wrote:

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

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. Received on Tue Oct 12 2004 - 08:15:49 CDT