How Does Oracle Handle Multiple Concurrent INSERTs Against One Table

Question

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!

• Jack Douglas: nothing beats trying it yourself to see :-)
• Dustin: Absolutely. However, I'm not sure how to create a query that will intentionally take 5 minutes to complete.
• Jack Douglas: insert a lot of data? insert into foo(id) select level from dual connect by level<1000000 (increasing the number of zeros one at a time until it is slow enough)

Answer 1

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

Answer 2

Perhaps it is useful to take a look at Oracle Transaction Management(Concepts) or SQL Processing for Application Developers(Advanced Application Developer's Guide)

You can investigate the behaviour of oracle by opening a session with sqlplus, settitng autocommit of by executing

set autocommit off

and executing an insert

insert into tableA (...) values (...);

Leave this session open and start a sqlplus session from another window, set autocommit to off and issue another insert. Both transaction will be active until you close them with commit (or rollback).

In most situations it is not really necessary to set autocommit off because this is the default for sqlplkus if you havent changed this.