This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Dec 5, 2012 8:32 PM by Aman.... RSS

Question about undo segment

namman Newbie
Currently Being Moderated
I have 3 statements as below. For my understanding, all 3 involve select data.
Question is : for each case, the select statement get data from where, undo segment or the myTable ?

1
update myTable set c1=1 where id=1 and c1=0

2
update myTable set c1=1 where id in (select id from myTable where id=1 and c1=0)

3
if exists (select * from myTable where id=1 and c1=0)
update myTable set c1=1 where id=1 and c1=0
else
insert myTable (id, c1) values(1,0)

Thanks
  • 1. Re: Question about undo segment
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    Whenever you post provide your 4 digit Oracle version.
    >
    I have 3 statements as below. For my understanding, all 3 involve select data.
    Question is : for each case, the select statement get data from where, undo segment or the myTable ?

    1
    update myTable set c1=1 where id=1 and c1=0

    2
    update myTable set c1=1 where id in (select id from myTable where id=1 and c1=0)

    3
    if exists (select * from myTable where id=1 and c1=0)
    update myTable set c1=1 where id=1 and c1=0
    else
    insert myTable (id, c1) values(1,0)
    >
    You forgot the buffer cache. Oracle will get the data for all three from either the buffer cache, the undo segments or the table depending on which version of each block it needs to maintain read-consistency.

    An unmodified block (i.e. a block which no session has modified could already be in the buffer cache if the same, or another, session has run a query that caused Oracle to retrieve that block.

    A modified block (i.e. a block which this, or another, session has modified) could be in the buffer cache, an undo segment or the table itself. Oracle has to determine which version of that modified block it needs and get it from whichever of those three possible locations it is at.
  • 2. Re: Question about undo segment
    namman Newbie
    Currently Being Moderated
    Thanks

    - I am using Oracle 11g

    I am still confused in 3 cases, but pick number 2 first.

    update myTable set c1=1 where id in (select id from myTable where id=1 and c1=0)

    There are only 3 processes accessing this table

    At 12:50
    - P0 update the table, set c1=0 where id=1 and c1=-1
    (I don't care about P0, just to provide example data on buffer cache and undo segment for P1)

    At 1:00
    - P1 run that query (update myTable set c1=1 where id in (select id from myTable where id=1 and c1=0).
    And suppose the table is very big, so that row has been read at 1:05. Then finish the update at 1:06

    At 1:01
    - P2 updates the row (id=1 and c1=0) set c1=2. The row is updated at 1:04


    A - If P1 read data from undo segment.
    it sees the row c1=0 in undo segment so the P1 will does the update, set c1=1. This overwrites the update of P2. As the result, at 1:06, the value of c1 = 1

    B - If P1 read data from buffer cache
    at 1:05, lock on that row already release, so it reads that row and see value c1=2, then do not update anything. As the result, at 1:06, the value of c1 = 2


    As you see, case A and B have completely different result.

    My question is : in this example, it works as case A or case B.

    I also want to have the answer for case 1 and 3 in the previous messages (apply the same time frame as this example)

    Thanks again
  • 3. Re: Question about undo segment
    rp0428 Guru
    Currently Being Moderated
    >
    - I am using Oracle 11g
    >
    11g is not a version. You can find the 4 digit version using SELECT * FROM V$VERSION.

    First, I suggest you review Chap 10 Data Concurrency and Consistency in the Database Concepts doc
    http://docs.oracle.com/cd/E14072_01/server.112/e10713/consist.htm

    There is a 'Read consistency' example a little ways down the page and that section, Multi-Versioning Read Consistency, talks about UNDO and the type of issue you are asking about.

    Back to your example. I have to make some assumptions here because you didn't provide some key information. One assumtion is that none of the three processes has issued a COMMIT or ROLLBACK at any point. Is that correct? The second assumption is that there is only ONE record in the database for the filter conditions you describe.

    So if you have reviewed the doc example I pointed you to you would see that the first thing you need to do is to add information to your example that shows what the data looks like for each of those processes at each point in time.
    >
    At 12:50
    - P0 update the table, set c1=0 where id=1 and c1=-1
    (I don't care about P0, just to provide example data on buffer cache and undo segment for P1)
    >
    How many records meet that condition? I am assuming just one; you need to provide the actual info for your example.

    That UPDATE statement will cause Oracle to lock that one row that is being updated. Other sessions can read the original row but no other session can update that same row.
    >
    At 1:00
    - P1 run that query (update myTable set c1=1 where id in (select id from myTable where id=1 and c1=0).
    And suppose the table is very big, so that row has been read at 1:05. Then finish the update at 1:06
    >
    Here is where the first problem with you example starts. You didn't say if there were any records to begin with with 'c1=0'. I will assume 'no' and that the only record had c1=-1 and is the record that P0 is updating.

    So the query above by P1 will NOT return any rows. The only record has 'c1=-1' but P1 subquery wants records with 'c1=0' and there aren't any. P1 CANNOT SEE the update that P0 is doing because P0 has not committed that update yet. This is part of read-consistency and is explained in the doc from above.

    Unfortunately until you provide more information about whether the assumptions I made are correct the fact that this query by P1 returns no rows breaks your examle; the remaininder of it relating to P2 won't apply because P2 can't see that row either.

    So I will assume that P0 does commit the update but I need you to tell me when that occurs. It it occurs after 1:00 query by P1 then P1 won't see the row; it's query will not return any rows as I explained above.

    P2 might see the row updated by P0 if the commit by P0 occured before the query by P2 got to that row. Otherwise P2 won't see a 'c1=0' row either.

    The first key thing to keep in mind is that any session can only see data that has been COMMITTED when the session began its transaction.

    The second key thing to remember is that only ONE session can update any given row. When the first session issues an UPDATE on a row that rows will have an exclusive lock on it until the session issues a COMMIT or ROLLBACK. If any other user tries to update the same row before a COMMIT or ROLLBACK has been issued Oracle will raise an exception.

    Let's keep sticking to this one example (#2) until it is completed to your satisfaction.
  • 4. Re: Question about undo segment
    namman Newbie
    Currently Being Moderated
    Thanks

    The version is : 11.1.0.7.0

    You are right. I miss a lot of thing in my example. In short, in the examples, all are committed right at the end of the statement

    I already read the document, and that's why I come here to ask. The document explains about read consistency for select and lock for update. The two case (select and update) are independent actions. Select will get data from undo segment if data has been changed after the select starts. It is clear for independent select, like : select c1 from myTable where id=1 and c1=0, I do not question on that simple select. The document also provides very clear examples about how read consistency work for select statement. But my examples and concern are complete different.

    My concern is : how the select, which is INSIDE an update, work? It means that the select is actually a part of update (example 1 and 2) or somehow relate to update (example 3). Those selects get data from sources like independent select?

    I will come back with clearer examples

    Thanks
  • 5. Re: Question about undo segment
    rp0428 Guru
    Currently Being Moderated
    >
    Select will get data from undo segment if data has been changed after the select starts.
    >
    NO! NO! NO! Reread my first reply.

    Select will get data from whereever the correct blocks are that have the correct data based on the need for read-consistency. Any given block could already be in the buffer cache, in the undo segment or even in the table's datafile on disk.

    It does NOT always get the blocks from undo.
  • 6. Re: Question about undo segment
    namman Newbie
    Currently Being Moderated
    Now, I make my question simple.

    The table myTable has 20 different columns (column id and c1 are ones I am concerned)

    + There are only 3 processes accessing this table
    + All Statements are committed right after the statement
    + There are milions rows in the tables
    + There is only 1 row where id=1
    + There is no index nor constraints


    At 12:50
    - P0 update the table, set c1=0 where id=1 and c1=-1
    (I don't care about P0, just to provide example data on buffer cache and undo segment for P1)
    COMMITTED

    At 1:00
    - P1 run the query - update myTable set c1=1 where id in (select id from myTable where id=1 and c1=0).
    The table is very big, so that row has been read at 1:05. Then finish the update at 1:06
    COMMITTED

    At 1:01
    - P2 updates the row (id=1 and c1=0) set c1=2. The row is updated at 1:04
    COMMITTED


    Question : the select part in P1 returns id=1 or returns nothing?

    Note: P2 finish update and committed at 1:04, and P1 read the row at 1:05

    Thanks
  • 7. Re: Question about undo segment
    namman Newbie
    Currently Being Moderated
    rp0428, my question is unclear ?
  • 8. Re: Question about undo segment
    Aman.... Oracle ACE
    Currently Being Moderated
    It would be 1 if I am reading it correctly.

    Aman....
  • 9. Re: Question about undo segment
    namman Newbie
    Currently Being Moderated
    Aman.... wrote:
    It would be 1 if I am reading it correctly.

    Aman....
    That is what I think, but not sure.

    If P1, with time frame mentioned in previous message, executes :

    1 - select id from myTable where id=1 and c1=0
    Clearly, it reads data in undo segment.

    or

    2 - update myTable set c1=1 where id=1 and c1=0
    Clearly, it reads data in buffer cache; in anther word, it collects data from buffer cache to update

    or

    3 - update myTable set c1=1 where id in (select id from myTable where id=1 and c1=0)
    This involves 2 selects : the first one is to get data for update's where clause conditions and the second one is to collect data to update.

    Question is : the first select get data from where, undo segment or buffer cache?

    (or there is only 1 select, that collects data in buffer cache to update ?)

    I am still not sure and really need an explanation.

    NOTE: in that update, the select is a part of the update or the select is INSIDE the update and more importantly, the select and update actually get/collect the same row (id=1)

    Books/documents mention how select (regular read, as number 1 above) work in Multi-Versioning Read Consistency, but none mentions the select which is a part of update or relate to update (as number 3)

    Hope to have explanation from gurus/experts

    Thanks
  • 10. Re: Question about undo segment
    Aman.... Oracle ACE
    Currently Being Moderated
    Well, neither I am a Guru nor an expert but would take my chances to answer your question.
    Question is : the first select get data from where, undo segment or buffer cache?
    You didn't seem to read the reply of Rp, the data is always going to come from buffer cache only, irrespective of whatever happens! If you read it from the disk then also it would be first brought into the buffer cache before it can be sent to you. If you are having a dirty buffer and looking for a read consistent image, it would be also created in the buffer cache only. Now, I may be wrong but IMO , the select for update would be looking for the read consistent image for the first time and after that means after teh update has occurred, it would be using the current image of the block as the update won't happen again on the most oldest copy of the data that you modified in the first place. For example, if you have modified the value 1 to 2 and then from 2 to 3 than for the next update the select would be reading the current copy of the data which is 3 and not 1 or 2 as they are already inconsistent images. If the data is already committed, for the next update within the same session or for the other sessions, that committed image would be the starting point.

    HTH
    Aman....
  • 11. Re: Question about undo segment
    namman Newbie
    Currently Being Moderated
    I am confident to say that the select get the data (id=1) from undo segment. However, the process is much more complicated than I thought. I have to do more test to confirm my conclusion.

    Thanks for help
  • 12. Re: Question about undo segment
    sb92075 Guru
    Currently Being Moderated
    why this obsession regarding UNDO?

    How will having answer change your behavior?
  • 13. Re: Question about undo segment
    rp0428 Guru
    Currently Being Moderated
    >
    Question is : the first select get data from where, undo segment or buffer cache?

    (or there is only 1 select, that collects data in buffer cache to update ?)

    I am still not sure and really need an explanation.
    >
    You actually have to try to read and understand it when you DO get an explanation.
    As I said above
    >
    Select will get data from whereever the correct blocks are that have the correct data based on the need for read-consistency. Any given block could already be in the buffer cache, in the undo segment or even in the table's datafile on disk.

    It does NOT always get the blocks from undo.
    >
    For ANY block Oracle might get it from the buffer cache, the undo segment or the datafile on disk. It all depends on exactly which VERSION of the block is needed and where that version is currently located.

    The VERSION of the block that is needed is determined when the query begins execution and the process is described in detail, with an example and diagram in the Oracle documentation.

    See Multiversion Concurrency Control in the Database Concepts doc
    http://docs.oracle.com/cd/B28359_01/server.111/b28318/consist.htm#sthref1510
    >
    As a query enters the execution stage, the current system change number (SCN) is determined. In Figure 13-1, this system change number is 10023. As data blocks are read on behalf of the query, only blocks written with the observed SCN are used. Blocks with changed data (more recent SCNs) are reconstructed from data in the rollback segments, and the reconstructed data is returned for the query. Therefore, each query returns all committed data with respect to the SCN recorded at the time that query execution began. Changes of other transactions that occur during a query's execution are not observed, guaranteeing that consistent data is returned for each query.
    >
    So all you have to ask yourself if what is the SCN at the time your query BEGAN.
    >
    As a query enters the execution stage, the current system change number (SCN) is determined.
    . . .
    Changes of other transactions that occur during a query's execution are not observed, guaranteeing that consistent data is returned for each query.
    >
    Even if that first query does the commit before Oracle actually tries to read the record you are talking about that record will have an SCN that is LATER THAN the SCN when your query BEGAN - so that version of the record WILL NOT be used.

    Take your example queries and construct a diagram like the one in the doc. For each query assign an SCN when data is COMMITTED and the SCN when the query begins executing and follow it through.
  • 14. Re: Question about undo segment
    Aman.... Oracle ACE
    Currently Being Moderated
    namman wrote:
    I am confident to say that the select get the data (id=1) from undo segment. However, the process is much more complicated than I thought. I have to do more test to confirm my conclusion.

    Thanks for help
    You should be confident but only when you understand the entire thing properly. The tests won't show anything as there would be simply things working. If the data is committed, there is no use to go to the Undo Segment as the transaction is over. If the data is not committed, only the old data would be there in the Undo Segment but you would need the current data for your on-going Update statement.

    Aman....
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points