Home » SQL & PL/SQL » SQL & PL/SQL » Prevent inserts during a process - is locking a feasible option? (Oracle 10g)
Prevent inserts during a process - is locking a feasible option? [message #347831] Sun, 14 September 2008 08:40 Go to next message
sriramkanala
Messages: 9
Registered: December 2005
Junior Member
Hi

I am working on a booking order creation application. This uses foreign exchange rate information. This process normally takes 3 to 4 hours.
While this process is in progress, no new foreign exchange rates should be taken into the system.
That means I should not allow INSERTs to the table containing the forex rates.

What is the best way to prevent inserts while booking is in progress?
Is locking the table that contains exchange rates is a viable option?
Locking a table for few hours is a good practise?
Also if the table is locked, what happens to the new insert that comes in? Does it remain in database cache till the lock is released and then the data gets inserted automatically after the lock is released?

Can someone please clarify and suggest an approach?

Thanks
Sriram
Re: Prevent inserts during a process - is locking a feasible option? [message #347833 is a reply to message #347831] Sun, 14 September 2008 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is locking the table that contains exchange rates is a viable option?

If you don't want insert it is your only option.

Quote:
Locking a table for few hours is a good practise?

If you need it then it is a good practice.
Now do you really need it? It depends on why you don't want new insert.

Quote:
Also if the table is locked, what happens to the new insert that comes in?

They wait.

Quote:
suggest an approach?

It depends on your business need, model, concurrent workload...

Regards
Michel
Re: Prevent inserts during a process - is locking a feasible option? [message #347836 is a reply to message #347831] Sun, 14 September 2008 08:58 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>While this process is in progress, no new foreign exchange rates should be taken into the system.
>That means I should not allow INSERTs to the table containing the forex rates.

Crazy requirements?!
What about UPDATE to existing exchange rates?

Re: Prevent inserts during a process - is locking a feasible option? [message #347838 is a reply to message #347831] Sun, 14 September 2008 09:05 Go to previous messageGo to next message
sriramkanala
Messages: 9
Registered: December 2005
Junior Member
UPDATES also should be blocked. No data manipulation at all while booking is in progress.
Re: Prevent inserts during a process - is locking a feasible option? [message #347843 is a reply to message #347838] Sun, 14 September 2008 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sriramkanala wrote on Sun, 14 September 2008 16:05
UPDATES also should be blocked. No data manipulation at all while booking is in progress.

Why?

Regards
Michel

Re: Prevent inserts during a process - is locking a feasible option? [message #347844 is a reply to message #347831] Sun, 14 September 2008 09:30 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
No (B)locking is required as long as the process doing the DML against exchange rates does only a single COMMIT at the end of it.


Re: Prevent inserts during a process - is locking a feasible option? [message #347855 is a reply to message #347831] Sun, 14 September 2008 11:21 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If the (b)lock really is a problem, you could
- read the contents of your table into memory at the start of your process (or copy it to a temporary table)
or
- add startdate/time and enddate/time columns and use those to fetch the valid value
Previous Topic: Insert date on a table
Next Topic: Getting completed status of concurrent request
Goto Forum:
  


Current Time: Sun Dec 11 08:06:53 CST 2016

Total time taken to generate the page: 0.05424 seconds