Is oracle 10g insert with select atomic?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Is oracle 10g insert with select atomic?

Hybrid View

  1. #1
    Join Date
    Feb 2006
    Posts
    3

    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?

  2. #2
    Join Date
    Feb 2005
    Posts
    158
    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.

  3. #3
    Join Date
    Feb 2006
    Posts
    3
    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."

  4. #4
    Join Date
    Feb 2005
    Posts
    158
    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

  5. #5
    Join Date
    Feb 2006
    Posts
    3
    That explains!
    Thank you, Sir.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width