简书链接:sqlserver同步表若干数据原子性重复并发问题
文章字数：138,阅读全文大约需要1分钟
语句为

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

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

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

  1. 开启事务 从而锁定

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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