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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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
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

Your Answer


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.