订单抬头-订单行交易的正确隔离级别是多少? [英] What is the correct isolation level for Order header - Order lines transactions?
问题描述
可以说，我们通常使用Order_Header和Order_LineItems表. 另外，假设我们有用于创建，更新和选择订单的交易.喜欢:
Lets say we have a usual situation with Order_Header and Order_LineItems tables. Also, lets say we have transactions for creating, updating and selecting Orders. Like:
创建:
BEGIN TRANSACTION
INSERT INTO Order_Headers...
SET @Id = SCOPE_IDENTITY()
INSERT INTO Order_LineItems...(using @Id)
DECLARE @SomeVar INT
--just example to show update dependant on select
SELECT @SomeVar = COUNT(*) Order_Headers
WHERE OrderDate > '1-1-2017'
UPDATE Order_Headers
SET SomeValue = @SomeVar
WHERE Id = @Id
COMMIT
END TRANSACTION
另一方面，为了简单起见，我们可以根据一些条件获取订单，比如说最后10个:
On the other hand, we have transaction for getting Orders base on some criteria, for simplicity, lets say last 10:
SELECT TOP 10 * FROM Order_Headers
ORDER BY Id DESC
有人可以说一下每笔交易的正确隔离级别是什么，然后简短地解释原因吗?
Could someone please say what would be correct isolation level for each transaction and shortly explain why?
[UPDATE]
-
我想确保没有其他会话可以插入匹配的行 WHERE OrderDate>'1-1-2017'
I want to make sure that no other session can insert rows matching WHERE OrderDate > '1-1-2017'
我还想确保第二笔交易(纯选择订单)永远不会拿走在第一笔交易中未完全完成"的行.表示那些是在事务INSERT部分中创建但尚未在UPDATE部分中更新的那些. (我猜这是默认的READ COMMITED所涵盖的，对吧?)
I also want to make sure that second transaction (pure selecting orders) never pick up rows that are not fully 'done' in first transaction. Meaning those that are created in transactions INSERT part but not yet updated in UPDATE part. (i guess that is covered by READ COMMITED being default, right?)
[UPDATE 2]
我要
WHERE OrderDate > '1-1-2017'
成为交易开始时的价值.
to be the value at the begining of the transaction.
推荐答案
首先请确保您的数据库已启用快照隔离
First make sure your database has enabled snapshot isolation
ALTER DATABASE YOURDB
SET ALLOW_SNAPSHOT_ISOLATION ON
第一笔交易需要快照隔离
First transaction requires SNAPSHOT isolation
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
第二笔交易需要READ COMMITTED隔离
Second Transaction requires READ COMMITTED isolation
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
这篇关于订单抬头-订单行交易的正确隔离级别是多少?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
