Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Can I lock up partial set of records in a table?
Chris Weiss wrote:
>
> Within a single user session, you can lock a set of records using a SELECT
> ... FOR UPDATE WHERE .... This is well documented on technet.oracle.com.
> This will row lock all related records and prevent modifications of the
> records while you are working on them. The documentation also shows how to
> implement broader and more restrictive locks.
>
> If you need the data locked while you use SQLLDR, I believe you will need
> two processes:
>
> 1) Process A lock the records from something like SQL*PLUS, keeping the
> connection open so the lock remains.
> 2) Use sqlldr to load the records into a staging area.
> 3) When the load is complete, use process A to move the records and release
> the locks with a commit.
>
> You need to be aware that you cannot "lock an area" as I think you are
> trying to do. You may need to provide some application locking scheme to
> achieve the fine grained control you might be looking for. You could
> implement a "soft locking" scheme where the user request registers a lock in
> a table and every other process then queries this lock table before
> operating on other records. This is non-trivial, but I have used it when I
> could not get the fine-grained control from traditional locking mechanisms
> that I needed.
>
> Good Luck!
>
> --
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Chris Weiss
> mailto:chris_at_hpdbe.com
> www.hpdbe.com
> High Performance Database Engineering
> Available for long and short term contracts
>
> "C Chang" <cschang_at_maxinter.net> wrote in message
> news:3CDB495D.6DF_at_maxinter.net...
> > I have a procedure to update part of records that belong to certain site
> > ( such as reginal bank). Can I lock up that part of records, so no other
> > user can enter new records belonging to that location? The new records
> > are entered through sqlldr process? I have no clue to this. Do i need a
> > trigger or? I will appreciate anyone for a learning tip.
> >
> > C Chang
Thanks Chris. The problem that I had was that there were some of the
records with the same ID, when I trid to update the status of those
records, some users were entering some new records, among them few of
match the condition that granted them with the same ID as updated ones.
So it ended up, under the same ID (group by), I have a record set with
two different kind of status. I am totally unware about this "soft
locking" scheme. Can you provide some documents or website toturing
this. I really appreciate this.
C Chang Received on Fri May 10 2002 - 19:35:31 CDT
![]() |
![]() |