I understand a little about Oracle blocking - how updates block other updates till the transaction completes, how writers don't block readers etc.
我对Oracle阻塞有一点了解 - 在事务完成之前，更新如何阻止其他更新，编写器如何阻止读取等。
I understand the concept of pessimistic and optimisic locking, and the typical banking textbook examples about losing lost updates etc.
我理解悲观和优化锁定的概念，以及关于丢失丢失更新的典型银行教科书示例等。
I also understand the JDBC transaction isolation levels where we might say, for instance, we are happy with seeing uncommitted data.
我也理解JDBC事务隔离级别，例如，我们很高兴看到未提交的数据。
I'm a bit fuzzy however about how these concepts are related and interact. For instance:
然而，我对这些概念如何相关和相互作用有点模糊。例如：
- Is Oracle providing pessimistic or optimistic locking by default (it just seems to block the seperate update based on experiments in two TOAD sessions.)
- Oracle是否默认提供悲观或乐观锁定（它似乎只是基于两个TOAD会话中的实验来阻止单独更新。）
- If, as I suspect, these are application level concepts, why would I go to the trouble of implementing a locking strategy when I can let the database synchronise transaction updates anyway?
- 如果我怀疑这些是应用程序级别的概念，那么当我可以让数据库同步事务更新时，为什么我会遇到实现锁定策略的麻烦？
- How do transaction isolation levels (which I set on the connection) alter the database behaviour when other clients besides my application be accessing with different isolation levels.
- 当我的应用程序以外的其他客户端使用不同的隔离级别访问时，事务隔离级别（我在连接上设置）如何改变数据库行为。
Any words to clarify these topics would be really appreciated!
任何澄清这些主题的话都会非常感激！
3 个解决方案
#1
3
-
Oracle allows for either type of locking - how you build your app dictates what is used. In retrospect, it's not really a database decision.
Oracle允许任何类型的锁定 - 您构建应用程序的方式决定了所使用的内容。回想起来，这不是一个真正的数据库决策。
-
Mostly, Oracle's locking is sufficient in a stateful connection to the database. In non-stateful apps, e.g., web apps, you cannot use it. You have to use application level locking in such situations because locking applies to a session.
大多数情况下，Oracle的锁定在与数据库的有状态连接中已足够。在非有状态的应用中，例如网络应用，您无法使用它。在这种情况下，您必须使用应用程序级别锁定，因为锁定适用于会话。
-
Usually you don't need to worry about it. In Oracle, readers never block writers, and writers never block readers. Oracle's behavior does not change with the various ANSI isolation levels. For example, there is no such thing as a "dirty read" in Oracle. Tom Kyte points out that the spirit of allowing dirty reads is to avoid blocking reads, which is not an issue in Oracle.
通常你不需要担心它。在Oracle中，读者永远不会阻止作者，作家也永远不会阻止读者。 Oracle的行为不会随着各种ANSI隔离级别而改变。例如，Oracle中没有“脏读”这样的东西。 Tom Kyte指出允许脏读的精神是避免阻塞读取，这在Oracle中不是问题。
I would strongly recommend reading Tom Kyte's excellent book "Expert Oracle Database Architecture", in which these and other topics are addressed quite clearly.
我强烈建议阅读Tom Kyte的优秀书籍“Expert Oracle Database Architecture”，其中非常清楚地阐述了这些和其他主题。
#2
2
Optimistic locking is basically "I'll only lock the data when I modify the data, not when I read it". The gotcha is that if you don't lock the data right away, someone else can change it before you do and you're looking at old news (and can blindly overwrite changes that have happened between when you read the data and updated it.)
乐观锁定基本上是“我只会在修改数据时锁定数据，而不是在我读取数据时”。问题是如果你不立即锁定数据，其他人可以在你做之前改变它并且你正在查看旧的新闻（并且可以盲目地覆盖在你读取数据和更新数据之间发生的变化。 ）
Pessimistic locking is locking the data when you read it so that you'll be sure that no one has changed it if you do decide to update it.
悲观锁定是在您读取数据时锁定数据，这样您就可以确定在您决定更新数据时没有人更改过它。
This is an application decision, not an Oracle decision as:
这是一个应用程序决策，而不是Oracle的决定：
SELECT x, y, z FROM table1 WHERE a = 2
SELECT x，y，z FROM table1 WHERE a = 2
will not lock the matching records but
不会锁定匹配的记录但是
SELECT x, y, z FROM table1 WHERE a = 2 FOR UPDATE
SELECT x，y，z FROM table1 WHERE a = 2 FOR UPDATE
will. So you have to decide if you're ok with optimistic locking
将。所以你必须决定你是否乐观锁定
SELECT x, y, z FROM table1 WHERE a = 2
...time passes...
...时间流逝...
UPDATE table1
SET x = 1, y = 2, z = 3
WHERE a = 2
(you could have overwrote a change someone else made in the meantime)
（你可能已经覆盖了其他人在此期间做出的改变）
or need to be pessimistic:
或者需要悲观：
SELECT x, y, z FROM table1 WHERE a = 2 FOR UPDATE
...time passes...
...时间流逝...
UPDATE table1
SET x = 1, y = 2, z = 3
WHERE a = 2
(you're sure that no one has changed the data since you queried it.)
（您确定自查询以来没有人更改过数据。）
Check here for isolation levels available in Oracle. http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#CNCPT621
在此处查看Oracle中可用的隔离级别。 http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#CNCPT621
#3
1
Oracle ALWAYS handles pessimistic locking. That is, it will lock a record when it is updated (and you can also hit locks for deletes and inserts if there is a key involved). You can use SELECT....FOR UPDATE to augment the pessimistic locking strategy.
Oracle ALWAYS处理悲观锁定。也就是说，它会在更新时锁定记录（如果涉及到密钥，您还可以在删除和插入时锁定）。您可以使用SELECT .... FOR UPDATE来增强悲观锁定策略。
Really any database/storage engine that works transactionally must do some form of locking.
实际上任何以事务方式工作的数据库/存储引擎都必须执行某种形式的锁定。
The SERIALIZABLE isolation level is much closer to a optimistic locking mechanism. It will throw an exception if the transaction tries to update a record that has been updated since the start of the transaction. However it relies on a one-to-one between the database session and the end user session.
SERIALIZABLE隔离级别更接近乐观锁定机制。如果事务尝试更新自事务开始以来已更新的记录，它将引发异常。但是，它依赖于数据库会话和最终用户会话之间的一对一。
As connection pooling/stateless applications become prevalent, especially in systems with heavy user activity, having a database session tied up for an extended period can be a poor strategy. Optimistic locking is preferred and later versions of Oracle support this with the ORA_ROWSCN and ROWDEPENDENCIES items. Basically they make it easier to see if a record has been changed since you initially/last looked at it.
随着连接池/无状态应用程序变得普遍，特别是在用户活动量很大的系统中，将数据库会话捆绑在一个较长时间内可能是一个糟糕的策略。乐观锁定是首选，后续版本的Oracle通过ORA_ROWSCN和ROWDEPENDENCIES项支持此功能。基本上，它们可以让您更容易看到自最初/最后一次查看以来记录是否已更改。
As that one-to-one relationship between a database session and a user-session has become legacy, the application layer has preserved more of the 'user-session' state and so become more responsible for checking that choices the user made five/ten minutes ago are still valid (eg is the book still in stock, or did someone else buy it).
由于数据库会话和用户会话之间的一对一关系已成为遗留问题，因此应用程序层保留了更多的“用户会话”状态，因此更加负责检查用户做出的选择5/10几分钟前仍然有效（例如，这本书还有库存，还是别人买了它）。
#1
3
-
Oracle allows for either type of locking - how you build your app dictates what is used. In retrospect, it's not really a database decision.
Oracle允许任何类型的锁定 - 您构建应用程序的方式决定了所使用的内容。回想起来，这不是一个真正的数据库决策。
-
Mostly, Oracle's locking is sufficient in a stateful connection to the database. In non-stateful apps, e.g., web apps, you cannot use it. You have to use application level locking in such situations because locking applies to a session.
大多数情况下，Oracle的锁定在与数据库的有状态连接中已足够。在非有状态的应用中，例如网络应用，您无法使用它。在这种情况下，您必须使用应用程序级别锁定，因为锁定适用于会话。
-
Usually you don't need to worry about it. In Oracle, readers never block writers, and writers never block readers. Oracle's behavior does not change with the various ANSI isolation levels. For example, there is no such thing as a "dirty read" in Oracle. Tom Kyte points out that the spirit of allowing dirty reads is to avoid blocking reads, which is not an issue in Oracle.
通常你不需要担心它。在Oracle中，读者永远不会阻止作者，作家也永远不会阻止读者。 Oracle的行为不会随着各种ANSI隔离级别而改变。例如，Oracle中没有“脏读”这样的东西。 Tom Kyte指出允许脏读的精神是避免阻塞读取，这在Oracle中不是问题。
I would strongly recommend reading Tom Kyte's excellent book "Expert Oracle Database Architecture", in which these and other topics are addressed quite clearly.
我强烈建议阅读Tom Kyte的优秀书籍“Expert Oracle Database Architecture”，其中非常清楚地阐述了这些和其他主题。
#2
2
Optimistic locking is basically "I'll only lock the data when I modify the data, not when I read it". The gotcha is that if you don't lock the data right away, someone else can change it before you do and you're looking at old news (and can blindly overwrite changes that have happened between when you read the data and updated it.)
乐观锁定基本上是“我只会在修改数据时锁定数据，而不是在我读取数据时”。问题是如果你不立即锁定数据，其他人可以在你做之前改变它并且你正在查看旧的新闻（并且可以盲目地覆盖在你读取数据和更新数据之间发生的变化。 ）
Pessimistic locking is locking the data when you read it so that you'll be sure that no one has changed it if you do decide to update it.
悲观锁定是在您读取数据时锁定数据，这样您就可以确定在您决定更新数据时没有人更改过它。
This is an application decision, not an Oracle decision as:
这是一个应用程序决策，而不是Oracle的决定：
SELECT x, y, z FROM table1 WHERE a = 2
SELECT x，y，z FROM table1 WHERE a = 2
will not lock the matching records but
不会锁定匹配的记录但是
SELECT x, y, z FROM table1 WHERE a = 2 FOR UPDATE
SELECT x，y，z FROM table1 WHERE a = 2 FOR UPDATE
will. So you have to decide if you're ok with optimistic locking
将。所以你必须决定你是否乐观锁定
SELECT x, y, z FROM table1 WHERE a = 2
...time passes...
...时间流逝...
UPDATE table1
SET x = 1, y = 2, z = 3
WHERE a = 2
(you could have overwrote a change someone else made in the meantime)
（你可能已经覆盖了其他人在此期间做出的改变）
or need to be pessimistic:
或者需要悲观：
SELECT x, y, z FROM table1 WHERE a = 2 FOR UPDATE
...time passes...
...时间流逝...
UPDATE table1
SET x = 1, y = 2, z = 3
WHERE a = 2
(you're sure that no one has changed the data since you queried it.)
（您确定自查询以来没有人更改过数据。）
Check here for isolation levels available in Oracle. http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#CNCPT621
在此处查看Oracle中可用的隔离级别。 http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#CNCPT621
#3
1
Oracle ALWAYS handles pessimistic locking. That is, it will lock a record when it is updated (and you can also hit locks for deletes and inserts if there is a key involved). You can use SELECT....FOR UPDATE to augment the pessimistic locking strategy.
Oracle ALWAYS处理悲观锁定。也就是说，它会在更新时锁定记录（如果涉及到密钥，您还可以在删除和插入时锁定）。您可以使用SELECT .... FOR UPDATE来增强悲观锁定策略。
Really any database/storage engine that works transactionally must do some form of locking.
实际上任何以事务方式工作的数据库/存储引擎都必须执行某种形式的锁定。
The SERIALIZABLE isolation level is much closer to a optimistic locking mechanism. It will throw an exception if the transaction tries to update a record that has been updated since the start of the transaction. However it relies on a one-to-one between the database session and the end user session.
SERIALIZABLE隔离级别更接近乐观锁定机制。如果事务尝试更新自事务开始以来已更新的记录，它将引发异常。但是，它依赖于数据库会话和最终用户会话之间的一对一。
As connection pooling/stateless applications become prevalent, especially in systems with heavy user activity, having a database session tied up for an extended period can be a poor strategy. Optimistic locking is preferred and later versions of Oracle support this with the ORA_ROWSCN and ROWDEPENDENCIES items. Basically they make it easier to see if a record has been changed since you initially/last looked at it.
随着连接池/无状态应用程序变得普遍，特别是在用户活动量很大的系统中，将数据库会话捆绑在一个较长时间内可能是一个糟糕的策略。乐观锁定是首选，后续版本的Oracle通过ORA_ROWSCN和ROWDEPENDENCIES项支持此功能。基本上，它们可以让您更容易看到自最初/最后一次查看以来记录是否已更改。
As that one-to-one relationship between a database session and a user-session has become legacy, the application layer has preserved more of the 'user-session' state and so become more responsible for checking that choices the user made five/ten minutes ago are still valid (eg is the book still in stock, or did someone else buy it).
由于数据库会话和用户会话之间的一对一关系已成为遗留问题，因此应用程序层保留了更多的“用户会话”状态，因此更加负责检查用户做出的选择5/10几分钟前仍然有效（例如，这本书还有库存，还是别人买了它）。