Aman.... wrote:That is what I think, but not sure.
It would be 1 if I am reading it correctly.
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.
namman wrote: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.
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