Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Note how no commit was done in between each statement. Could this cause a table lock or blocked session?

delete from tableblah;
insert into tableblah
(select * from othertable);
share|improve this question
    
You can read an explanation about locks with examples I provided here –  Florin Ghita Mar 3 '12 at 18:15
add comment

1 Answer

In Oracle, writers don't block readers, and it won't lock the table, just the affected rows.

Until you commit, all other sessions will continue to see the data as it was before your delete, and can continue to query.

The only blocking you'd see in another writer session is if there is a primary key or unique index on the table and it tries to insert a row which clashes; or it tries to update/delete a row that you deleted. That second session will wait until your first session issues a commit or rollback; the second session will then either get an error (if you committed) or complete the action (if you rolled back).

share|improve this answer
1  
Of course, the first statement will wait if any other session has any rows locked - assuming a unique constraint or primary key exists. –  Adam Musch Mar 6 '12 at 7:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.