锁定机制（悲观/乐观）与数据库事务隔离级别有何关系？

问题描述：

我正在编写一个Web应用程序，其中两个不同的用户可以更新事物列表，例如执行任务列表。我已经意识到，乐观锁定机制最有效，因为我不希望出现激烈争用。

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.

我一直在查看事务隔离级别，现在有点困惑了。看起来不同的事务隔离级别也可以解决类似的问题。

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.

答

这两个都与数据一致性和并发访问有关，但是它们是两个

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.

事务隔离与读取一致性有关。

读取未提交级别允许会话查看其他会话的未提交更改

读取已提交级别允许会话仅查看其他会话的已提交更改

可序列化级别允许会话仅查看在事务开始之前所做的更改

看看下面的示例，我指出了查询结果在事务隔离级别之间有所不同。

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

有四个ANSI指定的事务隔离级别（其中一个未提及）上面的示例是可重复读取），除可序列化外，所有其他内容都存在某些异常。请注意，它与锁定无关。

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.

您可以在此处，这些概念非常通用。

You can take a look at Oracle documentation on this here, the concepts are quite universal.

最后，对于网络应用程序，使用乐观锁定的方法似乎很明智。您很可能会获取一个列表项，并在两个不同的HTTP请求中对其进行更新。在提取之后，要在记录上有明确的锁定的情况下使事务保持打开状态是不可能的（至少是不明智的做法）（您如何知道第二个请求是否将全部到达？）乐观锁定可以很好地处理此问题。

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.

锁定机制（悲观/乐观）与数据库事务隔离级别有何关系？

相关推荐