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?
我很困惑。我正在阅读Oracle中的MVCC。我以为MVCC意味着没有锁。但是,我在其他地方读到所有UPDATE都进行自动锁定,而不管隔离级别如何。有人可以解释Oracle更新期间会发生什么吗?当多个读取提交的事务尝试进行并发更新时会发生什么?设置c = c + 1,其中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
1 个解决方案
#1
6
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.
你没错,无论隔离级别如何,都会锁定行。使用MVCC,您可以获得没有锁定的一致读取,但在写入时仍需要锁定。
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.
在尝试执行任何操作之前,第二个事务将等待第一个事务完成(例如:COMMIT或ROLLBACK)。所以在这种情况下,T2上的光标会“挂起”更新,等待T1完成。
You'll get a new SCN after T1 commits and another after T2 commits.
你会在T1提交后获得一个新的SCN,在T2提交后获得另一个SCN。
#1
6
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.
你没错,无论隔离级别如何,都会锁定行。使用MVCC,您可以获得没有锁定的一致读取,但在写入时仍需要锁定。
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.
在尝试执行任何操作之前,第二个事务将等待第一个事务完成(例如:COMMIT或ROLLBACK)。所以在这种情况下,T2上的光标会“挂起”更新,等待T1完成。
You'll get a new SCN after T1 commits and another after T2 commits.
你会在T1提交后获得一个新的SCN,在T2提交后获得另一个SCN。