topic Lock the Table in Operating System - HP-UX

Lock the Table

Prabhu_7 — Wed, 21 May 2003 20:13:13 GMT

i have "table A", whenever i insert records in
"table A" similar record will be inserted into "table B" (Insert trigger in table A)
One of our process does ,

Insert into table c
Select (some calculations) from table B

then ,

delete from table B

I'm not sure when table A will be loaded.
It can be loaded anytime (means table B too)..

But our process starts at 10AM everyday, so it fetches from table B arround 10AM, If during that time table B is getting loaded , then it takes only records loaded at that time. How to make my select statement to wait until all records get loaded into table B ?

Load process is done by someother group, so we cant
change their process.Its impossible.
Our process starts when we start pulling data from
"table B". I can alter the process from this point only.

Is that possible to check whether is there any lock in
table and then start fetching data.

For eg ,

If [ ANY LOCK IN TABLE B ] then
Wait [ DONT FETCH ]
Else
[ LOCK TABLE B , SO THAT NO NEW RECORDS ARE INSERTED ]
Select from "table B"
Delete from "table B"
End

Re: Lock the Table

James A. Donovan — Wed, 21 May 2003 20:34:56 GMT

simple....

insert table D
as select * from table B;

insert table C
as select * from table D;

delete from table B
where D.record = B.record;

delete * from table D;

repeat.....

Re: Lock the Table

Brian Crabtree — Wed, 21 May 2003 21:29:05 GMT

This should be cleaned up quite a bit (if statements, etc) but this is a basic concept for it:
-------
declare
numcount number := '1';
begin
while numcount > 0 loop
-- xxxxx should be the object id for the
select count(*) into numcount from
v$locked_object where object_id = 'xxxxx';
dbms_lock.sleep('10');
end loop;
end;
/
--------

Hope this helps,

Brian