Is oracle 10g insert with select atomic?

We have a table T with columns A and B with a composite unique constraint on the columns A and B.

Now I insert records into the table T using the following single statement in one session -

insert into T values (
(select a, b from dual)
union
(select c, d from dual)
union
(select e, f from dual))

and in another session I fire -

insert into T values (
(select e, f from dual)
union
(select a, b from dual)
)

My expectation is since there is a composite unique constraint, insert statement in one session will wait
since there is a common subset.

There is clearly a chance of deadlock, in the case, the multiple row inserts using a single insert statement is non atomic.
By atomic, I mean all rows get inserted by this single insert statement at one shot.

What I observe is, with the real dataset, a deadlock "is" occuring -
question is whether that is the expected behavior from Oracle 10G or a bug?

Is it actually a deadlock - ie ORA-00060: deadlock detected while waiting for resource ?
If Transaction A wants to insert a row ['H'], but it already exists in an uncommitted state (from an insert by Transaction B), it will wait. Then Transaction B finds it wants to insert a row ['G'] but it also finds that row exists in an uncommited state from an insert from Transaction A.
We get a deadlock, and Oracle throws an error for one of the sessions and will rollback that statement (not the transaction). The other will then be able to complete and eventually commit (or rollback).
It is the expected behaviour since there's nothing else it can do.

The insert is atomic in the ACID definition (the insert succeeds or fails as a whole - all or none of the rows are inserted).

The way around it is to generally to try to get the inserts happening in the same order (ie if the inserts are in ascending order, then they'll both try to insert 'G' first, then 'H' so there's no risk of a deadlock).

A UNION (as opposed to a UNION ALL) implies a DISTINCT to remove duplicates, which used to imply a full SORT and an ordered result set. 10GR2 (possibly R1) has some smarter algorithms, and DISTINCTs and UNIONs won't necessarily do the same full sort, so won't necessarilly give an ordered result set.
So if you are suddenly experiencing this problem after a database upgrade, I'm not surprised. Add an explicit ORDER BY to the select, and it will probably go away.

Thanks for your reply and you are correct, I am getting ORA-00060.

I am using 10GR1 and union, as I can see in Toad, is returning an ordered result set. Since the selection is from dual, is there any way, I can verify that union may not return me an ordered result set.

Can you give me further references where it can be substantiated that "10GR2 (possibly R1) has some smarter algorithms, and DISTINCTs and UNIONs won't necessarily do the same full sort, so won't necessarilly give an ordered result set."

http://tkyte.blogspot.com/2005/08/order-in-court.html
has some discussion on sorts and order bys. Basically, if you don't have an ORDER BY, you should not assume that the result set is ordered at all.

You basically want both sessions to do their inserts like :

insert into T (col_1, col_2)
select a col_1, b col_2 from dual
union
select c, d from dual
union
select e, f from dual
order by col_1,col_2

That explains!
Thank you, Sir.