If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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