I am confused. I am reading through about MVCC in Oracle. I figured MVCC meant no locks. But, I just read elsewhere that
UPDATEs do automatic securing, no matter the isolation level. Can someone explain what goes on throughout an Oracle update? And what goes on when multiple read committed transactions attempt to perform a concurrent
update t set c = c + 1 where id = 3. What is the result, given c = 1 before either from the transactions, and what's happening using the 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
You are right, this can lock the row no matter the isolation level. With MVCC you will get consistent reads without any locks, however, you still need locks when writing.
The 2nd transaction will watch for the first ones to finish (eg:
ROLLBACK) before trying to complete anything. So within this situation the cursor on T2 would "hang" around the update, awaiting T1 to complete.
You'll customize the SCN after T1 commits and the other after T2 commits.