Update a newly created row before final commit [Oracle]

Question

Hi,

insert into XYZ(col1, col2) values (1,2)
update XYZ set ... where col1 = 1
COMMIT

As in can see in the above code, we havent yet commited our insert statement, and we performed an update operation on the same row, and finally we commit the whole batch.

What exactly would happen in this case? Are there any chances of loosing data in this scenario?

Answer 1

the newly inserted row would by updated.

The only way you can "lose data" would be an interruption before the commit, in which case no operations would happen at all

Answer 2

Hi Bhaskardeep,

your session is always able to see its own modifications, even before you issue a commit.

Answer 3

The important words in Vincent's response are "your session".

A separate session will only see the unmodified data until you commit. That's part of read consistency means.

Depending on the frameworks and tools you're using, your session may get a lock on the record when you perform the update, preventing other sessions from updating it until you commit or rollback.

Answer 4

For further reading, here is a link to the "Data Concurrency and Consistency" section of the excellent Oracle Concepts Guide 10gR2

http://download.oracle.com/docs/cd/B19306%5F01/server.102/b14220/consist.htm

Answer 5

Infact All transactions are stored in Rollback Segmant with in Table space memory of that particular instance.. A Rollback segment is a storage space within a table space that holds transaction information used to guarantee data integrity during a ROLLBACK and used to provide read consistency across multiple transactions.