Oracle中的并发更新:是否锁定?

本文介绍了Oracle中的并发更新:是否锁定?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我很困惑.我正在阅读有关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.