快照隔离是好的吗？

Question

快照隔离是好的吗？

sql-serverisolation-level

10

我的情况：

表格：User { Id, Name, Stone, Gold, Wood }

我有以下“写入”线程：

miningThread（每分钟执行一次）

UPDATE User SET Stone = @calculatedValue WHERE Id=@id UPDATE User SET Wood = @calculatedValue WHERE Id=@id

tradingThread（每分钟执行一次）

UPDATE User SET gold = @calculatedValue WHERE Id=@id

constructionThread（每分钟执行一次）

UPDATE User SET Wood = @calculatedValue WHERE Id=@id UPDATE User SET Stone= @calculatedValue WHERE Id=@id

并且我收到用户的以下“写入”请求：

SellResource

UPDATE User SET Stone(Wood,Gold) = @calculatedValue WHERE Id=@id

(calculatedValue是由C#业务逻辑代码计算得出的)

在这种情况下，如果我设置为“读取提交的快照”隔离级别，我会遇到很多“丢失更新”的问题。但是，如果我将隔离级别设置为“可串行化”或“快照”，一切都正常。问题：我看了一个“隔离级别比较”表格，发现可串行化和快照隔离可以解决并发事务的所有问题。然而，可串行化非常慢。 1. 我可以将快照隔离用于所有写事务吗？我不想让我的表变得混乱。我的业务逻辑很复杂，而且经常变化。 2. 快照隔离有缺陷吗？ 3. 对于只读事务，哪个隔离级别更好？

- Glebka

2这看起来好多了。我提名重新开放并投了票。希望其他人也能做同样的事情。Craig Freedman在他的博客文章《Serializable vs. Snapshot Isolation Level》中对这种情况做了很好的解释。当这个问题重新开放时，我可以给出一个详细的答案，说明我如何处理它（例如，对于读取查询明确使用SNAPSHOT，并对更新/删除使用Read Committed以避免这种情况），但在那之前，希望这篇轻松阅读能帮助你更好地理解这种情况。 - John Eisbrener

@GLeBaTi 根据你提出的问题，我认为在使用快照隔离时，你很可能会遇到更新冲突错误。我不认为快照是你最好的选择。 - Shanky

@Shanky 如果我遇到更新冲突，我可以重新启动操作。对我来说最好的情况是没有逻辑错误。 - Glebka

1那好吧，我猜你准备好了，坦白说，我没有看到任何其他的大问题。它运行得很好。另外，请查看约翰分享的文章“Serializable vs Snapshot”中的大理石查询演示。 - Shanky

1个回答

- Jimmy Stenke · Accepted Answer

摘要：

所有写事务都可以使用快照隔离吗？

是的，但根据使用情况，读提交的快照可能更好。

快照隔离有缺陷吗？

是的。它需要存储所有活动事务的行版本，这需要磁盘/内存。

对于只读事务来说，哪种隔离级别更好？

快照隔离或者读提交的快照，取决于是否有多个相互依赖的语句和事务的大小。如果有许多更新操作并且 tempdb 的磁盘空间是一个问题，则使用读提交。

更深入的信息

使用哪种隔离级别取决于您的用例以及在事务中如何使用数据。因此，让我们从更深入的级别比较开始。

SQL Server通过使用两种不同的技术来维护一致性：锁定和行版本控制。

锁定隔离级别

锁定是通过SQL Server在读取的表/行上发出共享锁来实现的，阻止其他事务更新数据。对于更新操作也是如此，它们会发出独占锁，阻止其他事务读取数据。锁定发生在数据库的不同部分，我不会详细介绍，但例如表、行、索引。

READ COMMITTED 使用锁定来确保它只读取已提交的数据，并且在读取数据时没有其他事务正在更新数据。这意味着在另一个事务当前选择的行上执行更新语句将被阻塞。而在另一个事务正在更新的行上执行选择语句将被阻塞，直到该数据被提交。

READ UNCOMMITTED 忽略一切，不进行任何锁定。这意味着另一个事务可以在当前事务正在读取的行上执行更新操作而不被阻塞，但同时也会导致事务接收到其他事务可能尚未提交的数据。

SERIALIZABLE 做相反的事情，它锁定所有内容。当 READ COMMITTED 读取行或完成语句时释放锁定，或根据锁定方式释放锁定。

SERIALIZABLE 在事务提交后释放锁定。这意味着，如果另一个事务想要更新事务至少读取过一次的数据，或者另一个事务想要读取事务已更新的数据，则将被阻止，直到该事务提交。

行版本隔离级别

READ COMMITTED SNAPSHOT 和 SNAPSHOT ISOLATION 使用行版本控制。行版本控制意味着每次修改行时，SQL Server 都会存储该行的版本，确保在另一个事务读取时，它保持不变。

SNAPSHOT ISOLATION 的工作方式是，在对表进行读取时，它检索在事务启动时提交的行的最新版本。这为事务内的数据提供了一致的快照。在事务启动后修改的数据将不可见，但同时该事务也不会被阻止。为防止丢失更新，如果事务想要更新已被其他事务更改的某些行，则会终止由于冲突数据而产生的事务。

读提交的快照与快照隔离方式相同，但它不会在整个事务期间保留快照，而是仅在语句执行期间保留。这意味着在一个事务内的两个读取语句可能会得到不同的结果。然而，当事务执行更新操作时，它使用的是实际行而不是前一个行版本，并且不跟踪该行是否已更改。

由于SQL Server需要保持每个能被活动事务使用的修改后的行可用，它将它们存储在tempdb中。因此，tempdb需要足够大以容纳所有的更改。有一个后台线程会检查哪些行仍然需要，并删除其余的行，但如果有一个长时间运行的事务，它将阻止这些行被删除。如果tempdb的空间耗尽，将不会创建新的行版本，并且任何试图访问那些（不存在的）行的事务将终止。

快照隔离是允许的，并且比读提交的快照保持更好的一致性，但由于冲突解决可能在更新时失败。同一事务中的多个语句之间保证相互一致。
读提交对于并发读取是允许的，但对于更新不太适用。由于锁定，它也比行版本化级别慢。
读未提交不建议使用，因为它会读取脏数据，如果这不是问题，则没有锁定和不需要保留旧的行版本。
可串行化不建议使用，因为它会对所有内容进行锁定，因此速度慢且不适用于并发。

快照隔离是好的吗？

摘要：

更深入的信息

锁定隔离级别

行版本隔离级别

评论