事务隔离级别——选择正确的隔离级别

Question

我是一名SQL初学者，关于事务的隔离级别，我需要帮助。我需要知道以下情况哪种隔离级别最好以及为什么：

数据库中有3个表：

- Animals（通过将芯片插入其中注册的动物）KEY - ID_CHIP REF CHIPS - Chips（可以但不必将其插入动物中的芯片）KEY - ID_CHIP。其中一个属性是“INSERTED_BY”，它引用第三个表PEOPLE（提供插入芯片的人的ID，如果尚未插入，则为NULL） - People - KEY：ID

现在考虑以下事务：已向动物插入新芯片。更新数据库的人必须更改两件事：

- 向ANIMALS添加新实体 - 更新插入的芯片记录（将INSERTED_BY属性从NULL更改为插入芯片的人的ID）

第二个事务是控制器事务，检查ANIMALS中的实体数是否等于具有INSERTED_BY属性不等于NULL的CHIPS数量。

下图显示了一种情况： 请问有谁能告诉我哪种隔离级别最好，为什么？我陷入困境..感激不尽。

- Simon

---

你能解释一下为什么需要第二个事务吗？在索引/表堆中读取所有记录并不是一个好的/可扩展的解决方案。 - Bogdan Sahlean

@BogdanSahlean 这是一个学术项目，我必须模拟隔离级别，这就是我想出的例子。 - Simon

@Simon 很高兴知道这一点，因为这解释了为什么你对快照隔离不感兴趣。换句话说，你对生产级解决方案不感兴趣。你想要“模拟”什么？ - usr

Answer 1

您的情况很容易，因为其中一个事务是纯读取事务。请考虑快照隔离级别。在SNAPSHOT隔离级别下运行读取器将使其获得整个数据库的一致性时间点。不会有锁定或等待。

这意味着在t2时，插入操作对C2不可见。

这非常容易实现并完全解决了问题。

没有SNAPSHOT隔离级别，您需要SERIALIZABLE隔离级别，而且您经常会发生死锁。现在您需要调查锁定提示。更加复杂，但不是必需的。

- usr

---

首先感谢您的时间！很抱歉，我不明白。我可以选择：READ UNCOMMITED、READ COMMITED、REPEATABLE READ和SERIALIZABLE。哪个事务应该是SERIALIZABLE，第二个呢？为什么？ - Simon

首先，阅读有关快照隔离的内容，以认识到它将解决您的问题。这是一个非常好的工具。https://www.google.com/webhp?complete=1&hl=en#complete=1&hl=en&q=sql+server+snapshot+isolation和http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx 您可以使用ADO.NET和SET TRANSACTION ISOLATION LEVEL SNAPSHOT来使用它；C2必须是可序列化的，以便事务的读取集被锁定（这将在您给出的时间表中死锁，但至少不会静默返回错误结果）。 - usr

谢谢，第二笔交易怎么样？ - Simon

无论隔离级别和锁提示是什么，写入操作都会进行X锁定。我建议使用SERIALIZABLE，因为您似乎更关心安全性。请注意可能出现的阻塞和死锁问题。另外，我要再次强调：您的工作负载将以一定的概率发生死锁。 - usr

@usr：你能告诉我仅有的一种情景，这两个事务会相互阻塞（死锁）吗？ - Bogdan Sahlean

@BogdanSahlean 我现在意识到这是不可能的。这永远不会死锁。Serializable 可以工作。我仍然建议使用 SNAPSHOT 隔离，因为它使得结论一切正常变得非常容易。我必须在脑海中经历所有可能的时间表，才能得出没有死锁的结论。这是糟糕的代码，因为它让我思考。而且它对未来的更改是脆弱的。 - usr