使用直接查询和存储过程对同一个表进行多次插入请求会导致冲突吗？

Question

Multiply用户可以调用存储过程(SP)，这将对SQL Server中的mytable进行一些更改。此 SP 应将一些行插入到通过 parentid 列引用自身的 mytable 中。

TABLE mytable(
   id int identity(1,1) primary key,
   name varchar(20) not null,
   parentId int not null foreign key references mytable(id)
)  

为了将行插入到此类表中，根据其他帖子，我有两种方法：

1. 通过ALTER TABLE mytable alter column Parentid int null;允许parentid列为空，插入该行，更新 parentid 并禁用 null 为 parentid
2. 通过 set Identity_insert maytable on 允许 IDENTITY，插入虚拟行使用 id=-1 和 parentid=-1，插入引用 -1 的正确行，更新 parentid 到 SCOPE_IDENTITY() 并最终将 IDENTITY 设置为关闭

案例：

假设我采用第二种方式。 SP 成功将identity_insert mytable 设置为 但是 尚未完成其余SP 的执行。此时，还有其他 INSERT 请求（不通过 SP）发送到 mytable 表，例如 INSERT INTO mytable(name,parentid) VALUES('theateist', -1）。没有指定 id，因为他们假设 IDENTITY 已关闭，因此 id 是自动递增的。

问题：

插入时会不会出现错误，因为IDENTITY在这段时间内是ON的，不再自动递增，因此需要id规范？如果是的话，使用第一种方式会更好，不是吗？

谢谢

Answer 1

identity_insert 是每个连接的设置 - 您不会影响针对此表运行的其他连接/语句。

如果可以避免的话，我绝对不建议采用第一种方式，因为它可能会影响表的其他用户 - 例如，其他一些连接可能会执行损坏的插入（parentid=null），而列定义允许这样做，然后您的存储过程将中断。此外，设置列not null 会强制进行全表扫描，因此随着表的增长，这将无法正常工作。

如果您确实坚持使用方法 2，那么您仍然会遇到两个连接同时运行此存储过程时会发生什么的问题 - 它们都希望在不同时间插入 -1 行，并将其也删除。你们会有冲突。

我猜你遇到的问题是插入树的“根”，因为它们没有父级，所以你试图让它们自我引用。相反，我可能会让根永久具有空的parentid。如果还有其他一些键列，则可以在筛选索引或索引视图中使用这些键列，以确保每个键仅存在一个根。

---

想象一下，我们正在构建某种形式的家谱，并忽略了此类野兽的大部分现实（例如大多数要求孩子有两个父母的家庭）：

CREATE TABLE People (
    PersonID int IDENTITY(1,1) not null,
    Surname varchar(30) not null,
    Forename varchar(30) not null,
    ParentID int null,
    constraint PK_People PRIMARY KEY (PersonID),
    constraint FK_People_Parents FOREIGN KEY (ParentID) references People (PersonID)
)

CREATE UNIQUE INDEX IX_SoleFamilyRoot ON People (Surname) WHERE (ParentID is null)

这确保了在每个家庭（按姓氏标识）内，准确地一个人的 ParentID 为空。希望您可以修改此示例以适合您的模型。

在 SQL Server 2005 及更早版本上，您必须改用索引视图。