Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    2

    Question Unanswered: Problem when select and insert together

    My Problem is as follows

    I have a process ( daemon process ) [ say proc 1 ] which keeps on inserting data in to a table ( a huge table.having partitions ). as an when it gets input.

    I have another process [ say proc2 ] needs to select from this table data based on certains things...This query has quite a many order by fileds..also in it.

    The question i have is , when my proc2 is doing select ( generally takes 1 hour ). If at the same time is proc1 is inserting data say in the same block that proc2 was selecting. Does Oracle rollbacks what it has selected and starts selecting again ??..

  2. #2
    Join Date
    May 2002
    Location
    Thailand
    Posts
    12

    Talking

    Hi,

    The answer to your question depends upon several factors.. Generally, when u do a insert or a select a table/row level is put on the object (depending upon the type of DML you use), so there is no reason why Oracle should rollback..

    Rama

  3. #3
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204

    Re: Problem when select and insert together

    Originally posted by newtooracle
    My Problem is as follows

    I have a process ( daemon process ) [ say proc 1 ] which keeps on inserting data in to a table ( a huge table.having partitions ). as an when it gets input.

    I have another process [ say proc2 ] needs to select from this table data based on certains things...This query has quite a many order by fileds..also in it.

    The question i have is , when my proc2 is doing select ( generally takes 1 hour ). If at the same time is proc1 is inserting data say in the same block that proc2 was selecting. Does Oracle rollbacks what it has selected and starts selecting again ??..

    No, Oracle does not 'Rollback' on a select statement. Oracle gaurantees read consistency, which means the select statement will return the data as it looked the instance the select started. The result is, if any new rows were inserted after the select statement started, but before it completed, they would not be returned in the select statements result set.

    HTH,
    Patrick

  4. #4
    Join Date
    Mar 2003
    Posts
    2

    Red face still a question !

    One more question !!.

    According to what i read in oracle documents and books. Oracle fetches data in blocks. Now when it starts the select if must certainly have a time stamp of that instance with it. Say it was selecting from block 1. In the mean time some proc1 inserted in block 2.
    Now when it tries to select from block2 it sees that the timestamp is updated hence it rollbacks the selected block and starts selecting. If it doesn't do so then it get data from block1 from another time and block2 for another time..what say !!!????????????

  5. #5
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    Oracle captures the SCN (System Change Number) when the select statement starts. Based on the SCN, Oracle knows what data is consistent with your query and what is "newer".

    See http://download-west.oracle.com/docs...ntro.htm#44618 for details.

Posting Permissions

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