CODEWITHSUNDEEP
databaseoracle-databaseconcurrencytransactions
Ron Garrity
Ron Garrity

Reputation: 1463

Concurrent Updates in Oracle: Locking or not?

I'm confused. I'm reading about MVCC in Oracle. I thought MVCC meant no locks. But, I read somewhere else that all UPDATEs do automatic locking, regardless of the isolation level. Can someone explain what happens during an Oracle update? And what happens when multiple read committed transactions try to do a concurrent update t set c = c + 1 where id = 3. What's the result, given c = 1 before either of the transactions, and what's going on with 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

Upvotes: 6

Views: 5686

Answers (1)

NullUserException
NullUserException

Reputation: 85458

You're right, this will lock the row regardless of the isolation level. With MVCC you can get consistent reads with no locks, but you still need locks when writing.

The second transaction will wait for the first one to finish (eg: COMMIT or ROLLBACK) before attempting to do anything. So in this case the cursor on T2 would "hang" on the update, waiting for T1 to finish.

You'll get a new SCN after T1 commits and another after T2 commits.

Upvotes: 8

Related Questions