Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 524
Ron Garrity Avatar asked Sep 23 '11 01:09

Ron Garrity


People also ask

What is concurrency control locking?

A lock is a variable associated with a data item that describes the status of the item with respect to possible operations that can be applied to it. It prevents access to a database record by a second transaction until the first transaction has completed all of its actions.

What types of locking are available in Oracle?

Oracle locks fall into one of three general categories. DML locks protect data. For example, table locks lock entire tables, row locks lock selected rows. DDL locks protect the structure of schema objects—for example, the definitions of tables and views.

What is concurrency control without locking in DBMS?

In Computer Science, in the field of databases, non-lock concurrency control is a concurrency control method used in relational databases without using locking.

Does Oracle support concurrency control?

Oracle provides data concurrency and integrity between transactions using its locking mechanisms. Because the locking mechanisms of Oracle are tied closely to transaction control, application designers need only define transactions properly, and Oracle automatically manages locking.


1 Answers

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.

like image 90
NullUserException Avatar answered Sep 20 '22 22:09

NullUserException