Re: Can I lock up partial set of records in a table?

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