OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Lock

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Lock [message #57106]

Tue, 20 May 2003 22:29

Narren
Messages: 3
Registered: May 2003

Junior Member

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 ?

Report message to a moderator

Re: Lock [message #57111 is a reply to message #57106]

Wed, 21 May 2003 01:18

Maaher
Messages: 7065
Registered: December 2001

Senior Member

At first glance:
What you could do is lock the entire table B during the load, insert into C through a PL/SQL stored procedure which first attempts to select for update. Normally this will wait until all locks are released.

1. In Load: lock table B in exclusive mode;
2. In Load: Commit/Rollback in the end
3. In the other process: Select * From B for update
or lock table B in exclusive mode

Step 3 will wait for step 2.

It is perfectly possible that there are more user-friendly ways, but I'm confident this approach can work.

I just though of something else: why can't the load procedure call your other procedure in the end? That way you don't have to be concerned about locking.

As I already mentioned, this is a quick reply. Maybe there are better solutions for this.
MHE

Report message to a moderator

Re: Lock [message #57114 is a reply to message #57111]

Wed, 21 May 2003 08:26

Narren
Messages: 3
Registered: May 2003

Junior Member

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"
<DO MY PROCESS>
Delete from "table B"
END

Report message to a moderator

Sorry Re: Lock [message #57115 is a reply to message #57111]

Wed, 21 May 2003 08:29

Narren
Messages: 3
Registered: May 2003

Junior Member

I used < > in my message so it got messed...

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

Report message to a moderator

Previous Topic:	Alter tablespace
Next Topic:	HASH-related parameters

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 11:45:00 CDT 2024