MSSQL高并发时,如何避免插入重复数据+++++++++++ 避免插入重复数据,一般是先用exist判断是否存在,然后再插入,在高并发时,会有插入重复数据的可能 网上搜了一圈解决方案,有给表加唯一约束的(可行),有用触发器做判断的(持怀疑态度),还有insert和exist写在一条sql里的(持怀疑态度),例如下面的: create proc [dbo].[Name_Add] @Name varchar(50) as begin
begin tran insert Names (Name) select (@Name) where not exists (select NameId from Names with(HOLDLOCK) where Name = @Name) commit tran
select NameId,Name from Names with(nolock) where Name = @Name end
GO CREATE TABLE #Test (C1 INT); GO CREATE UNIQUE INDEX AK_Index ON #Test (C1) WITH (IGNORE_DUP_KEY = ON); GO INSERT INTO #Test VALUES (1); INSERT INTO #Test VALUES (1); --已忽略重复的键。
GO SELECT COUNT(*)AS [Number of rows] FROM #Test; GO DROP TABLE #Test; GO /*
GO CREATE TABLE #Test (C1 INT); GO CREATE UNIQUE INDEX AK_Index ON #Test (C1) WITH (IGNORE_DUP_KEY = ON); GO INSERT INTO #Test VALUES (1); INSERT INTO #Test VALUES (1); --已忽略重复的键。
GO SELECT COUNT(*)AS [Number of rows] FROM #Test; GO DROP TABLE #Test; GO /*