事务隔离-插入取决于先前的记录值 [英] Transaction isolation - INSERTS dependant on previous records values
问题描述
这个问题与讨论另一件事有关/来自: 什么是订单的正确隔离级别标头-订单行交易?
This question is related/came from discussion about another thing: What is the correct isolation level for Order header - Order lines transactions?
想象一下我们有常规的Orders_Headers和Orders_LineItems表的情况.还可以说我们有一条特殊的业务规则，内容为:
Imagine scenario where we have usual Orders_Headers and Orders_LineItems tables. Lets say also that we have a special business rules that say:
-
每个订单都有折扣"字段，该字段是根据上次输入的订单经过的时间计算的
Each order has Discount field which is calculated based on time passed from last order entered
如果最近Y小时内有超过X个订单，则将特别计算每个下一个订单折扣字段.
Each next order Discount field is calculated specially if there has been more than X order in last Y hours.
如果最近10个订单的平均频率高于每分钟x，则特别计算每个下一个订单折扣字段.
Each next order Discount field is calculated specially if average frequency of last 10 orders was higher than x per minute.
每个下一个订单折扣字段都是专门计算的
Each next order Discount field is calculated specially
这里要说明的是，每个订单都依赖于先前的订单，隔离级别至关重要.
Point here is to show that every Order is dependant on previous ones and isolation level is crucial.
我们有一笔交易(只是所示代码的逻辑):
We have a transaction (just logic of the code shown):
BEGIN TRANSACTION
INSERT INTO Order_Headers...
SET @Id = SCOPE_IDENTITY()
INSERT INTO Order_LineItems...(using @Id)
DECLARE @SomeVar INT
--just example to show selecting previous x orders
--needed to calculate Discount value for new Order
SELECT @SomeVar = COUNT(*) Order_Headers
WHERE ArbitraryCriteria
UPDATE Order_Headers
SET Discount= UDF(@SomeVar)
WHERE Id = @Id
COMMIT
END TRANSACTION
我们还有另一笔交易来读取订单:
We also have another transaction to read orders:
SELECT TOP 10 * FROM Order_Headers
ORDER BY Id DESC
问题
-
SNAPSHOT隔离级别是否适合第一个事务，而READ COMMITED是否适合第二个合适级别?
Is SNAPSHOT isolation level for first transaction and READ COMMITED for second appropriate levels?
是否有更好的方法来处理CREATE/UPDATE事务，或者这是做到这一点的方法?
Is there a better way of approaching CREATE/UPDATE transaction or is this the way to do it?
推荐答案
serializable选项:
通过updlock和serializable表提示使用悲观锁定策略，以获取由where标准指定的键范围锁定(由支持索引支持，仅锁定查询所需的范围) :
Using a pessimistic locking strategy by way of the updlock and serializable table hints to acquire a key range lock specified by the where criteria (backed by a supporting index to lock only the range necessary for the query):
declare @Id int, @SomeVar int;
begin tran;
select @SomeVar = count(OrderDate)
from Order_Headers with (updlock,serializable)
where OrderDate >= '20170101';
insert into Order_Headers (OrderDate, SomeVar)
select sysdatetime(), @SomeVar;
set @Id = scope_identity();
insert into Order_LineItems (id,cols)
select @Id, cols
from @TableValuedParameter;
commit tran;
- Documentation on serializable and other Table Hints - MSDN
- Key-Range Locking - MSDN
- SQL Server Isolation Levels: A Series - Paul White
- Questions About T-SQL Transaction Isolation Levels You Were Too Shy to Ask - Robert Sheldon
- Isolation Level references curated by Brent Ozar
这篇关于事务隔离-插入取决于先前的记录值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
