如何理解在SQLServer 2005上的INSERT INSERT INSERT(*)=0的唯一键约束?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (101)

将从多个进程插入SQL数据库。进程可能有时会尝试将重复的数据插入到表中:

System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'UK1_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'.
The statement has been terminated.

查询类似于:

INSERT INTO MyTable (FieldA, FieldB, FieldC)
SELECT FieldA='AValue', FieldB='BValue', FieldC='CValue'
WHERE (SELECT COUNT(*) FROM MyTable WHERE FieldA='AValue' AND FieldB='BValue' AND FieldC='CValue' ) = 0

用于创建表的SQL:

CREATE TABLE [dbo].[MyTable](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [FieldA] [bigint] NOT NULL,
  [FieldB] [int] NOT NULL,
  [FieldC] [char](3) NULL,
  [FieldD] [float] NULL,
  CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED 
  (
    [Id] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON),
  CONSTRAINT [UK1_MyTable] UNIQUE NONCLUSTERED 
  (
    [FieldA] ASC,
    [FieldB] ASC,
    [FieldC] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
提问于
用户回答回答于

SQL Server的默认在不再需要的时候释放共享锁。子查询将导致表上的短暂共享,只要子查询完成,该锁就会被释放。

用户回答回答于

可以尝试性爱

Tran A                                Tran B
---------------------------------------------
SELECT COUNT(*)...
                                  SELECT COUNT(*)...
INSERT ....
                                  INSERT... (duplicate key violation).

在SQLProfiler中

创建表脚本

create table MyTable
(
FieldA int NOT NULL, 
FieldB int NOT NULL, 
FieldC int NOT NULL
)
create unique nonclustered index ix on  MyTable(FieldA, FieldB, FieldC)

然后将下面的内容粘贴到两个不同的SSMS窗口中

插入脚本

DECLARE @FieldA INT, @FieldB INT, @FieldC INT
SET NOCOUNT ON
SET CONTEXT_INFO 0x696E736572742074657374

BEGIN TRY
WHILE 1=1
    BEGIN

        SET @FieldA=( (CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT))) * 24 * 60 * 60 * 300)
        SET @FieldB = @FieldA
        SET @FieldC = @FieldA

        RAISERROR('beginning insert',0,1) WITH NOWAIT
        INSERT INTO MyTable (FieldA, FieldB, FieldC)
        SELECT FieldA=@FieldA, FieldB=@FieldB, FieldC=@FieldC
        WHERE (SELECT COUNT(*) FROM MyTable WHERE FieldA=@FieldA AND FieldB=@FieldB AND FieldC=@FieldC ) = 0
    END
END TRY
BEGIN CATCH
    DECLARE @message VARCHAR(500)
    SELECT @message = 'in catch block ' + ERROR_MESSAGE()
    RAISERROR(@message,0,1) WITH NOWAIT
    DECLARE @killspid VARCHAR(10) 
    SELECT @killspid = 'kill ' +CAST(SPID AS VARCHAR(4)) FROM sys.sysprocesses WHERE SPID!=@@SPID AND CONTEXT_INFO = (SELECT CONTEXT_INFO FROM sys.sysprocesses WHERE SPID=@@SPID)
    EXEC ( @killspid )
END CATCH

扫码关注云+社区

领取腾讯云代金券