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.

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!

share|improve this question
5  
nothing beats trying it yourself to see :-) –  Jack Douglas Apr 5 '12 at 16:21
    
Absolutely. However, I'm not sure how to create a query that will intentionally take 5 minutes to complete. –  Dustin Apr 5 '12 at 17:36
3  
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) –  Jack Douglas Apr 5 '12 at 19:32
add comment

2 Answers

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.

http://stackoverflow.com/questions/3194999/dml-by-multiple-users-commit-scenarios-in-oracle

share|improve this answer
add comment

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.

share|improve this answer
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.