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?
Who is Participating?
johnsoneConnect With a Mentor Senior 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.
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).
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?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.