Oracle中的并发更新:是否锁定? [英] Concurrent Updates in Oracle: Locking or not?

问题描述

我很困惑.我正在阅读有关Oracle中的MVCC的信息.我以为MVCC意味着没有锁.但是，我在其他地方看到了所有UPDATE都执行自动锁定，而与隔离级别无关.有人可以解释在Oracle更新期间会发生什么情况吗?当多个读取已提交的事务尝试执行并发update t set c = c + 1 where id = 3时，会发生什么.如果在任何一次事务处理前c = 1，结果如何?锁和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

解决方案

您是对的，无论隔离级别如何，这都会锁定行.使用MVCC，您可以获得无锁的一致读取，但是在编写时仍然需要锁.

第二个事务将在尝试执行任何操作之前等待第一个事务完成(例如:COMMIT或ROLLBACK).因此，在这种情况下，T2上的光标将在更新时挂起"，等待T1完成.

在T1提交后，您将获得一个新的SCN，在T2提交后，您将获得另一个.

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

解决方案

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.

这篇关于Oracle中的并发更新:是否锁定?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！