Parallel updates in Oracle: blocking or not?

Question

I'm confused. I am reading about MVCC in Oracle. I thought MVCC did not mean blocking. But I read somewhere else that all UPDATE perform automatic locking, regardless of isolation level. Can someone explain what happens during the upgrade of Oracle? And what happens when several read transactions try to execute parallel update t set c = c + 1 where id = 3 . What is the result given c = 1 before any of the transactions, and what happens to locks and SCN?

 Begin T1 Begin T2 T1: update t set c = c + 1 where id = 3 T2: update t set c = c + 1 where id = 3 Commit T1 Commit T2 

Answer 1

You are right, this will block the row regardless of isolation level. With MVCC, you can get consistent reads without locks, but you need locks when writing.

The second transaction will wait for the first to complete (for example: COMMIT or ROLLBACK ) before trying to do anything. Therefore, in this case, the cursor on T2 “freezes” in the update, waiting for the completion of T1.

You will receive a new SCN after completing T1, and another after completing T2.