I am writing a web application where two different users can update a list of things, to do list, for example. I have come to realize that, optimistic locking mechanism works best since I don't expect high contention.
我正在编写一个Web应用程序，例如，两个不同的用户可以更新列表中的事情列表。我已经意识到，乐观锁定机制最有效，因为我不期望高争用。
I was looking at transaction isolation levels and now I am a little confused. Looks like different transaction isolation levels also solve similar problems.
我在看事务隔离级别，现在我有点困惑。看起来不同的事务隔离级别也解决了类似的问题。
How are these two different concepts related to each other? If possible, with a simple example.
这两个不同的概念如何相互关联？如果可能，举个简单的例子。
2 个解决方案
#1
15
Both of these things are related to data consistency and concurrent access, but they are two different mechanisms.
这两件事都与数据一致性和并发访问有关，但它们是两种不同的机制。
Locking prevents concurrent access to some object. For example when you attempt to update a todo list item, with pessimistic locking database places a row lock on the record until you either commit or rollback the transaction, so that no other transaction is allowed to update the same record. Optimistic locking is application-side check whether the timestamp/version of a record has changed between fetching and attempting to update it. This is regardless of transaction isolation level.
锁定可防止对某些对象的并发访问。例如，当您尝试更新待办事项列表项时，使用悲观锁定数据库会在记录上放置行锁，直到您提交或回滚事务为止，以便不允许其他事务更新同一记录。乐观锁定是应用程序端检查记录的时间戳/版本是否在获取和尝试更新之间发生了变化。这与事务隔离级别无关。
Transaction isolation is about read consistency.
事务隔离与读取一致性有关。
- Read uncommitted level allows session to see other session's uncommitted changes
- 读取未提交级别允许会话查看其他会话的未提交更改
- Read committed level allows session to see other session's committed changes only
- 读取已提交级别允许会话仅查看其他会话的已提交更改
- Serializable level allows session to see only changes committed before the transaction began
- 可序列化级别允许会话仅查看事务开始之前提交的更改
Take a look at below example, I indicated the query results that differ between transaction isolation levels.
看一下下面的例子，我指出了事务隔离级别之间不同的查询结果。
SESSION 1 SESSION 2
-------------------------------- --------------------------------------
SELECT count(*) FROM test;
=> 10
INSERT INTO test VALUES ('x');
SELECT count(*) FROM test;
=> 10 with read committed/serializable
=> 11 with read uncommited (dirty read)
COMMIT;
SELECT count(*) FROM test;
=> 10 with serializable
=> 11 with read uncommitted/read committed
There are four ANSI specified transaction isolation levels (one not mentioned in the example above is "repeatable read"), all of them except serializable are subjects to some anomalies. Note it has nothing to do with locking.
有四个ANSI指定的事务隔离级别（上面的示例中未提及的是“可重复读取”），除序列化之外的所有这些都是一些异常的主题。请注意，它与锁定无关。
You can take a look at Oracle documentation on this here, the concepts are quite universal.
您可以在此处查看Oracle文档，这些概念非常普遍。
Finally, your approach to use optimistic locking seems sensible for a web application. Most probably you fetch a list item and update it in two different HTTP requests. It is impossible (or unwise at least) to keep transaction open with explicit lock on the record after the fetch (how do you know whether the second request will arrive at all?) Optimistic locking handles this gracefully.
最后，使用乐观锁定的方法对于Web应用程序似乎是明智的。最有可能的是，您获取一个列表项并在两个不同的HTTP请求中更新它。在获取之后，不可能（或至少是不明智的）保持事务打开并在记录上显式锁定（你怎么知道第二个请求是否会到达？）乐观锁定处理这个优雅。
#2
0
Locking mechanisms are usually used to implement transaction isolation levels. So transaction isolation levels define contract how your transactions have to behave in concurrent execution. Locking mechanisms are implementation details.
锁定机制通常用于实现事务隔离级别。因此，事务隔离级别定义了事务在并发执行中的行为方式。锁定机制是实现细节。
From application writing perspective you should focus on setting appropriate transaction isolation level. Of course setting specific isolation level implicates locking, but as long as you don't have your application under heavy load, you don't need to take care of it much.
从应用程序编写角度来看，您应该专注于设置适当的事务隔离级当然，设置特定的隔离级别意味着锁定，但只要您没有负载繁重的应用程序，就不需要太多处理它。
Import thing is that locking mechanisms differ between database engines. If you write application for one database and after some time you would change db engine, your application may behave differently or some part of it may require rewriting.
导入的东西是数据库引擎之间的锁定机制不同。如果您为一个数据库编写应用程序，并且在一段时间后您将更改数据库引擎，您的应用程序可能会有不同的行为，或者某些部分可能需要重写。
My advice from fifteen years of business application development is not to rely on explicit locking.
我十五年的业务应用程序开发的建议不是依赖显式锁定。
#1
15
Both of these things are related to data consistency and concurrent access, but they are two different mechanisms.
这两件事都与数据一致性和并发访问有关，但它们是两种不同的机制。
Locking prevents concurrent access to some object. For example when you attempt to update a todo list item, with pessimistic locking database places a row lock on the record until you either commit or rollback the transaction, so that no other transaction is allowed to update the same record. Optimistic locking is application-side check whether the timestamp/version of a record has changed between fetching and attempting to update it. This is regardless of transaction isolation level.
锁定可防止对某些对象的并发访问。例如，当您尝试更新待办事项列表项时，使用悲观锁定数据库会在记录上放置行锁，直到您提交或回滚事务为止，以便不允许其他事务更新同一记录。乐观锁定是应用程序端检查记录的时间戳/版本是否在获取和尝试更新之间发生了变化。这与事务隔离级别无关。
Transaction isolation is about read consistency.
事务隔离与读取一致性有关。
- Read uncommitted level allows session to see other session's uncommitted changes
- 读取未提交级别允许会话查看其他会话的未提交更改
- Read committed level allows session to see other session's committed changes only
- 读取已提交级别允许会话仅查看其他会话的已提交更改
- Serializable level allows session to see only changes committed before the transaction began
- 可序列化级别允许会话仅查看事务开始之前提交的更改
Take a look at below example, I indicated the query results that differ between transaction isolation levels.
看一下下面的例子，我指出了事务隔离级别之间不同的查询结果。
SESSION 1 SESSION 2
-------------------------------- --------------------------------------
SELECT count(*) FROM test;
=> 10
INSERT INTO test VALUES ('x');
SELECT count(*) FROM test;
=> 10 with read committed/serializable
=> 11 with read uncommited (dirty read)
COMMIT;
SELECT count(*) FROM test;
=> 10 with serializable
=> 11 with read uncommitted/read committed
There are four ANSI specified transaction isolation levels (one not mentioned in the example above is "repeatable read"), all of them except serializable are subjects to some anomalies. Note it has nothing to do with locking.
有四个ANSI指定的事务隔离级别（上面的示例中未提及的是“可重复读取”），除序列化之外的所有这些都是一些异常的主题。请注意，它与锁定无关。
You can take a look at Oracle documentation on this here, the concepts are quite universal.
您可以在此处查看Oracle文档，这些概念非常普遍。
Finally, your approach to use optimistic locking seems sensible for a web application. Most probably you fetch a list item and update it in two different HTTP requests. It is impossible (or unwise at least) to keep transaction open with explicit lock on the record after the fetch (how do you know whether the second request will arrive at all?) Optimistic locking handles this gracefully.
最后，使用乐观锁定的方法对于Web应用程序似乎是明智的。最有可能的是，您获取一个列表项并在两个不同的HTTP请求中更新它。在获取之后，不可能（或至少是不明智的）保持事务打开并在记录上显式锁定（你怎么知道第二个请求是否会到达？）乐观锁定处理这个优雅。
#2
0
Locking mechanisms are usually used to implement transaction isolation levels. So transaction isolation levels define contract how your transactions have to behave in concurrent execution. Locking mechanisms are implementation details.
锁定机制通常用于实现事务隔离级别。因此，事务隔离级别定义了事务在并发执行中的行为方式。锁定机制是实现细节。
From application writing perspective you should focus on setting appropriate transaction isolation level. Of course setting specific isolation level implicates locking, but as long as you don't have your application under heavy load, you don't need to take care of it much.
从应用程序编写角度来看，您应该专注于设置适当的事务隔离级当然，设置特定的隔离级别意味着锁定，但只要您没有负载繁重的应用程序，就不需要太多处理它。
Import thing is that locking mechanisms differ between database engines. If you write application for one database and after some time you would change db engine, your application may behave differently or some part of it may require rewriting.
导入的东西是数据库引擎之间的锁定机制不同。如果您为一个数据库编写应用程序，并且在一段时间后您将更改数据库引擎，您的应用程序可能会有不同的行为，或者某些部分可能需要重写。
My advice from fifteen years of business application development is not to rely on explicit locking.
我十五年的业务应用程序开发的建议不是依赖显式锁定。