I am trying to understand Oracle 11g a little more closely. My question is simple: how does Oracle handle two sessions that are attempting to insert records into a single table at the same time.
For example, INSERT A runs at 1:00PM against Table 1. It will take 5 minutes to complete.
INSERT B is executed at 1:02 PM against Table 1. It will take 1 minute to complete.
What will happen? Will Oracle queue INSERT B to be executed after INSERT A is finished? Will INSERT B run simultaneously with INSERT A?
Thanks!
Best Answer
The inserts don't affect each other unless they would have a conflict with unique or primary key constraints. They should be independent. I believe this concurrency issue is one of the reasons why Oracle's sequence is a separate object (cached, surrogate PK generation separated from insert).
In that respect, they operate on the uncommitted table as it is seen to their session. This behavior is described in the stackoverflow post below.
https://stackoverflow.com/questions/3194999/dml-by-multiple-users-commit-scenarios-in-oracle