SQL Server : using stored procedure in parallel, prevent inserting same key-CodePudding

I have a SQL Server stored procedure, and I want to insert a lot of data in parallel from Spring boot. I have to ensure that the compound values of three columns must be unique.

I am not a SQL Server expert, but AFAIK there is key lock, which is should be good for this. There is an index for these three columns.

The procedure working like this, try to find a record which exactly contains these three columns. If yes the recordid will be the return value. If not exists, it inserts this new record and returns with its id.

Can I find an example about this?

CodePudding user response：

The first step would be (as marc suggests above) to create a unique index on the combination of those 3 columns - that will not only provide the integrity structure needed to ensure uniqueness, but also allow you to efficiently lookup/seek for records with that combination of values. I won't get into the various optimizations you'd need to consider when creating that index, but there's plenty of other q/a that covers it.

Once you have that, you have a few options in terms of how to go about coding up the procedure that performs the logic you described. 2 approaches you could use are included below - one that optimizes a bit more for the situation where the values being inserted are more likely to be unique/new records, and another that optimizes for the scenario where the values being inserted are more likely to be duplicate/existing records. There are certainly other valid approaches you could take as well, these are just 2.

-- Test example table with id and 3 other columns
create table test3 (id int not null primary key clustered identity(1,1), col1 int not null, col2 int not null, col3 int not null);

-- Create a unique compound key on the 3 columns
create unique nonclustered index iux_test3_col1_col2_col3
    on dbo.test3 (col1, col2, col3);

-- Proc that favors/optimizes for the situation where the majority of records are likely to be new/unique records
create procedure dbo.test3_insert_favored
    @col1 int,
    @col2 int,
    @col3 int
as

declare @id int = null;

begin try;
    insert  dbo.test3
            (col1, col2, col3)
    select  @col1, @col2, @col3
    where   not exists
            (
            select  null
            from    dbo.test3 t
            where   t.col1 = @col1
                    and t.col2 = @col2
                    and t.col3 = @col3
            );

    select  @id = scope_identity();
end try
begin catch;
    if error_number() not in(2601, 2627)
    begin;
        throw;
    end;
end catch;

if @id is null
begin;
    select  @id = t.id
    from    dbo.test3 t
    where   t.col1 = @col1
            and t.col2 = @col2
            and t.col3 = @col3;
end;

select  @id as record_id;

go

-- Proc that favors/optimizes for the situation where the majority of records are likely to be repeated/existing records
create procedure dbo.test3_select_favored
@col1 int,
@col2 int,
@col3 int
as

declare @id int = null;

select  @id = t.id
from    dbo.test3 t
where   t.col1 = @col1
        and t.col2 = @col2
        and t.col3 = @col3;

if @id is null
begin;
    begin try;
        insert  dbo.test3
                (col1, col2, col3)
        select  @col1, @col2, @col3;

        select  @id = scope_identity();
    end try
    begin catch;
        if error_number() not in(2601, 2627)
        begin;
            throw;
        end;
    end catch;

end;

if @id is null
begin;
    select  @id = t.id
    from    dbo.test3 t
    where   t.col1 = @col1
            and t.col2 = @col2
            and t.col3 = @col3;
end;

select  @id as record_id;

go