sql - 如果存在，则选择其他插入然后选择

Question

在 Microsoft SQL Server 2005 中如何表达以下内容:

IF EXISTS (SELECT * FROM Table WHERE FieldValue='') THEN
   SELECT TableID FROM Table WHERE FieldValue=''
ELSE
   INSERT INTO TABLE(FieldValue) VALUES('')
   SELECT TableID FROM Table WHERE TableID=SCOPE_IDENTITY()
END IF

我想要做的是查看是否已经存在空白字段值，如果存在则返回该 TableID，否则插入空白字段值并返回相应的主键。

Answer 1

您需要在事务中执行此操作，以确保两个同时的客户端不会两次插入相同的 fieldValue:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
    DECLARE @id AS INT
    SELECT @id = tableId FROM table WHERE fieldValue=@newValue
    IF @id IS NULL
    BEGIN
       INSERT INTO table (fieldValue) VALUES (@newValue)
       SELECT @id = SCOPE_IDENTITY()
    END
    SELECT @id
COMMIT TRANSACTION

您还可以使用Double-checked locking减少锁定开销

DECLARE @id AS INT
SELECT @id = tableID FROM table (NOLOCK) WHERE fieldValue=@newValue
IF @id IS NULL
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRANSACTION
        SELECT @id = tableID FROM table WHERE fieldValue=@newValue
        IF @id IS NULL
        BEGIN
           INSERT INTO table (fieldValue) VALUES (@newValue)
           SELECT @id = SCOPE_IDENTITY()
        END
    COMMIT TRANSACTION
END
SELECT @id

至于为什么 ISOLATION LEVEL SERIALIZABLE 是必要的，当您处于可序列化事务中时，命中表的第一个 SELECT 会创建一个范围锁，覆盖记录应该所在的位置，因此其他人都无法插入相同的记录，直到此为止交易结束。

如果没有 ISOLATION LEVEL SERIALIZABLE，默认隔离级别 (READ COMMITTED) 不会在读取时锁定表，因此在 SELECT 和 UPDATE 之间，有人仍然能够插入。具有 READ COMMITTED 隔离级别的事务不会导致 SELECT 锁定。具有 REPEATABLE READS 的事务会锁定记录(如果找到)，但不会锁定间隙。