Table Access while updating - should I lock?

I have a stored procedure that is triggered to reload a table of data every 5 minutes. It takes approx 2-3 seconds to execute.

I was just wondering what happens if an end user tries to view whats in the table (excel file that selects from table) at exactly the point the script is running.  i.e. after deleting from the table and before finishing the reload.

What will they see?  I guess the scenarios are:
1)  The select from table statement executes and retrieves whatever is in the table at that exact point.... a mess of data.
2) It will wait and execute once the procedure is finished.
3) It will take the data that was in the table right before the procedure started.

I would like it to wait for the procedure to finish.  What can I do to ensure that is what happens?  I have been reading up about the LOCK statement, but not sure if I should need to use that.
Can someone explain what will actually happen, and how I can assure it will wait?
Thanks.
obrienjimmyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
Locks do not block reads, so a lock will not prevent someone from selecting the data.

Assuming you do a delete and not a truncate, I would continue on that route.  Make sure the deletes to get rid of the old data and the inserts to put the new data in are all in the same transaction.  This way, while the load is running users will see the old data.  Then once the load is complete and is committed, they will see the new data.  They can only see a mix of old and new if you do it in multiple transactions.

If you must do the process in multiple transactions, then I suggest a staging table.  Get all the data into a staging table and then your last step is to delete and insert into the real table in a single transaction.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alexander Eßer [Alex140181]Software DeveloperCommented:
You could "revoke select on..."
....
....
...
nope, I didn't say that ;-) That would be more than dirty...

Stick to johnsone's suggestion(s).
0
obrienjimmyAuthor Commented:
Okay, that sounds good.  I'm pretty sure I'm good so, but just to confirm. A transaction doesn't end until there is a commit or rollback right?

So I have a trigger that calls a procedure and commits it.  The procedure doesn't contain any COMMIT statement.  So this means the user will see the old data until the commit statement goes through right?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.