如果Oracle ACID不能完全遵循“隔离”属性，该如何兼容？ [英] How is Oracle ACID compliant when it does not honour 'isolation' property fully?

问题描述

声明：Oracle不支持ACID属性中的隔离属性。
按照关于ACID的维基百科页面

Claim: Oracle does not honour isolation property in ACID properties. As per Wikipedia page on ACID

隔离确保并发执行事务使数据库保持与按顺序执行事务所获得的状态相同的状态。

"Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially."

仅当事务可序列化时，这种情况才会发生。是的，Oracle有一个称为Serializable的事务级别，但它不是真正的可序列化性，仅是快照隔离。

This can happen only if the transactions are serializable. Yes, Oracle has a transaction level called Serializable but it is not true serializability and is only snapshot isolation.

阅读> https://blog.dbi-services.com/oracle-serializable-is-not-serializable/
快照隔离的Wiki页面的摘录（ https://en.wikipedia.org/wiki/Snapshot_isolation ）

尽管快照隔离与可序列化有所区别，但Oracle有时还是将快照隔离称为可序列化。 p>

"In spite of its distinction from serializability, snapshot isolation is sometimes referred to as serializable by Oracle".

隔离度较弱，但不足以保证交易顺序会导致如果获得交易将获得的结果按顺序执行。为了保证它，必须具有可序列化性。

There are weaker isolation levels but they are not sufficient to guarantee that the sequence of transactions would lead to the outcome that would be obtained if they were executed sequentially. To guarantee it, serializability is a must.

Q1）由于Oracle不提供它（它的可序列化性不是真正的），所以它不能100％接受隔离。

Q1) Since Oracle does not provide it (its serializaibility is not a true one), it does not honor isolation 100 percent. How can then it be called ACID compliant?

Q2）看起来Oracle在隔离方面得到了宽大的对待。这样宽大处理是否也扩展到其他数据库？

Q2) Looks like Oracle was treated here leniently with regard to isolation. Is this leniency extended to other databases as well?

Q3）如果我们采取宽容的立场并说（隔离意味着100％隔离-不会接受更多的隔离），会不会'甲骨文声称符合ACID的说法破灭了吗？那么其他关系数据库呢？他们将能够像Oracle一样削减开支吗？

Q3) If we take an unforgiving stance and say (isolation means 100 percent isolation - no less is accepted), won't Oracle's claim of being ACID compliant fall to pieces? What about other relational databases? Will they be able to make the cut or will they fall short like Oracle?

推荐答案

除了SNAPSHOT，SQL Server还具有SERIALIZABLE。但是，至少在SQL Server中，SERIALIZABLE对于大多数实际用途来说是无用的，因为它太昂贵了，而且效果不佳。然后，您对实际上需要序列化的少数事务使用特殊的构造（即一次运行一次）。

SQL Server has SERIALIZABLE in addition to SNAPSHOT. But, at least in SQL Server, for most practical purposes SERIALIZABLE is useless, as it's too expensive, and not really effective. And you use special constructs for the few transactions that actually need to be serialized (ie run one-at-a-time).

SERIALIZABLE太昂贵了，因为事务排序是通过消除并发的某种组合以及生成运行时故障（死锁）来完成。两者都是非常昂贵且麻烦的。

SERIALIZABLE is to expensive because transaction ordering is accomplished by some combination eliminating concurrency, and by generating run-time failures (deadlocks). Both of which are very expensive and troublesome.

SERIALIZABLE并不是真正有效的，因为它实际上并未实现完全的事务隔离。为此，将要求每个事务排他地锁定它读取的所有数据，以防止两个事务读取相同的数据，然后再写入。

经典示例是在两个会话中运行

SERIALIZABLE is not really effective, because doesn't actually accomplish complete transaction isolation. To do so would require every transactions to exclusively lock all data it reads, to prevent two transactions from reading the same data, and then writing.
The classic example is where two sessions run

SELECT salary FROM emp where id = 1

，然后根据客户端中的现有值计算一个新值，然后

and then, compute a new value based on the existing in the client, and then

UPDATE emp set salary = :newSalary 

使这项工作正确的唯一方法是在第一次读取时设置排他锁，因此第二个会话也无法读取。

The only way to make this work right is to place an exclusive lock on the first read, so a second session can't read too.

在Oracle中，这是通过SELECT ... FOR UPDATE完成的，而在SQL Server中是通过UPDLOCK提示完成的。或使用显式的应用程序锁定，Oracle的DMBS_LOCK或SQL Server的sp_getapplock。

In Oracle this is accomplished with SELECT ... FOR UPDATE, and in SQL Server with an UPDLOCK hint. Or with an explicit "application lock", Oracle's DMBS_LOCK, or SQL Server's sp_getapplock.

这篇关于如果Oracle ACID不能完全遵循“隔离”属性，该如何兼容？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！