什么时候比较喜欢悲观的交易隔离模式呢？ [英] when to prefer pessimistic model of transaction isolation over optimistic one?

问题描述

我明白了解表/行锁提示是否被用于并发的悲观事务（TX）隔离模型只有？

换句话说，什么时候可以表/ SQL Server（2005及更高版本）提供的乐观TX隔离参与期间使用行锁提示？如果以后提供内置乐观（又称为快照）版本控制）并发隔离，那么在SQL Server2005 +中需要悲观的TX隔离级别/提示？


$ b

$ b我已经读了悲观的选择是遗产，不再需要了，虽然我有疑问。另外，有乐观的（也称为快照aka版本）TX隔离级内置SQL Server2005 +，
当需要手动编码乐观并发功能时？

最后一个问题的灵感来自于：

• SQL Server中的乐观并发（2007年9月28日）

描述了在SQL Server中提供版本控制的自定义编码。

解决方案

乐观并发需要更多的资源，而在发生冲突时更为昂贵。

两个会话可以读取和修改值，只有当他们尝试同时应用它们的更改时才会发生冲突。这意味着在并发更新的情况下，两个值都应该存储在某处（当然需要资源）。

此外，当发生冲突时，通常整个事务应该

悲观并发模型使用锁定，从而降低并发性，但提高性能。

在两个并发任务的情况下，第二个任务等待释放可能比支出 CPU 时间和磁盘 I / O 在两个同时进行的作品，然后更多的回滚不幸的工作和重做它。

说，你有一个这样的查询：

 更新mytable 
 SET myvalue = very_complex_function（@range）
 WHERE rangeid = @range 
  

，与 very_complex_function 阅读一些数据来自 mytable 本身。换句话说，这个查询转换 mytable 的一个子集，分享范围的价值。

现在，当两个函数的工作范围相同时，可能有两种情况：

1. 悲观：第一个查询锁定，第二个查询等待它。第一个查询在 10 秒内完成，第二个也是。总计： 20 秒。




2. 乐观：两个查询独立工作（在相同的输入）。它们之间共享 CPU 之间的时间加上一些切换开销。他们应该将它们的中间数据保留在某处，所以数据被存储两次（这意味着两次 I / O 或内存）。让我们说几乎同时完成，在 15 秒。

   
   
   

   但是是时候提交工作，第二个查询将冲突，并且必须回滚其更改（比如，它需要相同的 15 秒）。然后需要重新读取数据，再次使用新的数据集（ 10 秒）。

   
   
   

   结果， 查询完成后比悲观锁定： 15 和 40 秒与 10 和 20 。


<如果后来提供内置乐观（又称为快照版本）并发隔离，则需要在SQL Server2005 +中使用悲观的TX隔离级别/提示，否则









乐观的隔离水平很好，乐观。当您希望对您的数据发生争议时，您不应该使用它们。





BTW，乐观隔离（对于读取查询） code> SQL Server 2000 。




Do I understand correctly that table/row lock hints are being used for pessimistic transaction (TX) isolation models of concurrency ONLY?
In other words, when can table/row lock hints be used during engagement of optimistic TX isolation provided by SQL Server (2005 and higher)?

When one would need pessimistic TX isolation levels/hints in SQL Server2005+ if the later provides built-in optimistic (aka snapshot aka versioning) concurrency isolation?
I did read that pessimistic options are legacy and are not needed anymore, though I am in doubt.

Also, having optimistic (aka snapshot aka versioning) TX isolation levels built-in SQL Server2005+, when one would need to manually code for optimistic concurrency features?

The last question is inspired by having read:

• "Optimistic Concurrency in SQL Server" (September 28, 2007)

describing custom coding to provide versioning in SQL Server.

解决方案

Optimistic concurrency requires more resources and is more expensive when the conflict occurs.

Two sessions can read and modify the values and the conflict only occurs when they try to apply their changes simultaneously. This means that in case of the concurrent update both values should be stored somewhere (which of course requires resources).

Also, when a conflict occurs, usually the whole transaction should be rolled back or the cursor refetched, which is expensive too.

Pessimistic concurrency model uses locking, thus downgrading concurrency but improving performance.

In case of two concurrent tasks, it may be cheaper for the second task to wait for a lock to release than spending CPU time and disk I/O on two simultaneous works and then yet more on rolling back the less fortunate work and redoing it.

Say, you have a query like this:

UPDATE  mytable
SET     myvalue = very_complex_function(@range)
WHERE   rangeid = @range

, with very_complex_function reading some data from mytable itself. In other words, this query transforms a subset of mytable sharing the value of range.

Now, when two functions work on the same range, there may be two scenarios:

1. Pessimistic: the first query locks, the second query waits for it. The first query completes in 10 seconds, the second one does too. Total: 20 seconds.

2. Optimistic: both queries work independently (on the same input). This shares CPU time between them plus some overhead on switching. They should keep their intermediate data somewhere, so the data is stored twice (which implies twice I/O or memory). Let's say both complete almost at the same time, in 15seconds.

   But when it's time to commit the work, the second query will conflict and will have to rollback its changes (say, it takes the same 15 seconds). Then it needs to reread the data again and do the work again, with the new set of data (10 seconds).

   As a result, both queries complete later than with a pessimistic locking: 15 and 40 seconds vs. 10 and 20.

When one would need pessimistic TX isolation levels/hints in SQL Server2005+ if the later provides built-in optimistic (aka snapshot aka versioning) concurrency isolation?

Optimistic isolation levels are, well, optimistic. You should not use them when you expect high contention on your data.

BTW, optimistic isolation (for the read queries) was available in SQL Server 2000 too.

这篇关于什么时候比较喜欢悲观的交易隔离模式呢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！