我有下表:
CREATE TABLE [Provider]
(
[Id] BIGINT IDENTITY(1, 1) NOT NULL,
[Name] CHARACTER VARYING(255) NOT NULL,
[Description] CHARACTER VARYING(MAX) NOT NULL,
[CreatedOn] DATETIMEOFFSET NOT NULL,
[DeletedOn] DATETIMEOFFSET NULL,
UNIQUE([Name], [DeletedOn]),
CHECK([DeletedOn] IS NULL OR ([CreatedOn] <= [DeletedOn])),
PRIMARY KEY([Id])
);
所有对Provider
的访问均由存储过程控制。所有存储的过程都必须遵循"失败快速"策略,首先验证参数然后执行操作。
可以创建,更新和删除Provider
记录(软)。
创建是标准的,仅包裹在存储过程中以保持一致性和参数验证。
删除(软)简单地更新Id
列指定的记录的DeletedOn
列。
更新是事情变得棘手的地方,也是我的问题的关注。为了维护应用于特定Provider
的更改历史记录,我们使用多个记录。Id
标识Provider
实体的特定实例。针对特定的Provider
实体进行了一系列更改,每个Provider
实体都是由其Name
唯一识别的。最新有效的Provider
实体版本由(Name, DeletedOn)
确定DeletedOn IS NULL
。
要执行上面的该模型,我们确保使用UNIQUE
约束,可以将两个Provider
记录视为最新记录。通过存储的过程,删除(软)的逻辑旧记录和插入新记录在交易中受到控制。当前,我们使用SET TRANSACTION LEVEL SERIALIZABLE
来确保在语句之间没有插入新记录,这将导致当前的交易失败,因为在约束检查过程中存在幻影记录。
问题在于,这会破坏并发。完整的表锁几乎是经常拍摄的,并且该表有大量读取,但插入或更新的内容不多。似乎SQL Server锁定在Name
上,然后升级到表锁定,以防止插入任何新记录,就像SERIALZABLE
交易级别一样。
这似乎是更颗粒的优化的好候选者。
我可以对此表进行哪些提示或其他更改,以确保仍然满足上述要求,这将允许冲突的插入物相互阻止,但显然允许采取非冲突的措施?
我最终使用了独立于主表的历史表。为了完成对原子性的需求,我只是将更新包裹在实时表中,然后将插入插入到历史表中。