sql server 同步表若干数据原子性 重复并发问题

语句为

Insert  dbo.XXX(name) Select 'xxx' where not exists(Select 1 from  XXX where name='xxx')";

咋一看不可能重复，实际重复了，那肯定是并发问题了

解决思路有3种：
1.约束

2. 开启事务 从而锁定

3. 把同步语句改为merge更好

SET XACT_ABORT ON;

BEGIN TRANSACTION;

BEGIN TRY
    -- 检查并插入
    IF NOT EXISTS (
        SELECT 1 
        FROM xxx
        WHERE x= xxx
    )
    BEGIN
        INSERT INTO xxx

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;

SET XACT_ABORT OFF;

try catch TRANSACTION

BEGIN TRY
    BEGIN TRANSACTION;

    -- 检查是否存在相同的 Name，如果不存在则插入
    IF NOT EXISTS (
        SELECT 1 
        FROM Axxx WITH (UPDLOCK, HOLDLOCK) 
        WHERE x= xxx
    )
    BEGIN
        INSERT INTO Axxx
        (xx)
        VALUES 
        (xx);
    END

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- 出现错误时回滚事务
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- 捕获错误信息
    DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- 返回错误信息
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;

merge

MERGE INTO Axx AS target
USING (SELECT x) AS source
ON target.x= source.x
WHEN NOT MATCHED THEN
    INSERT (name)
    VALUES (@name);


xxx