How to synchronize multiple instances of a stored procedure executing at the same time in SQL Server 2012?

Question

Overview: In my scenario a user uploads an excel file that needs to be written into database, I am allocating each uploaded file a batch_id that batch_id is written in last column of each row of my table as shown in these pictures

With the help of this batch_id column I will be able to segregate that which rows belong to which document.(for example: if batch_id=1 than these 1000 rows belongs to document 1)

Problem: Consider two users user1 and user2 executing this stored procedure at the same time while Stored Procedure is writing batch_id=1 in all the rows which belong to document1,at the same time rows belonging to document2 will gets inserted thus causing a conflict wether to write batch_id=1 or batch_id=2 in the rest of remaining rows.

Imperfect Solution: To Execute Stored Procedure on one document at a time,when its done execute it on document 2 but this will let the user to wait until the current execution finishes which i don't want to happen.

UPDATE:::::::::::::::

The batch_id value is coming from another table MASTER_CDT in this table batch_id is primary key and auto incremented, when a document gets uploaded first a batch_id is generated this batch_id is then inserted into rows corresponding to that batch, the problem is while batch_id(consider batch_id=1) is getting written into current batch rows if another user uploads document then batch_id is changed to value 2 while rows regarding batch 1 are still missing batch_id value but now batch_id=2 will be written to them therefore causing trouble.

Answer 1

You have not posted any code so I'll make some assumptions based on the additional information you added to your question. Once a batch_id is assigned for the upload, the application should use that value when inserting related rows rather than retrieve a batch_id again, which may be different due to concurrency. It seems you may be inserting rows individually and retrieving the batch_id repeatedly, either in code or the proc.

There are a couple of ways to fix the problem. One is to add a @batch_id parameter to the proc and change the code to pass the value from the related MASTER_CDT insert (i.e. returned with SCOPE_IDEINITY()) for each spreadsheet row insert.

A more elegant solution is to pass the spreadsheet contents as a table-valued parameter like the example below. Not only will this address the concurrency problem, it will be much more efficient than individual inserts. Whether or not this is feasible depends on the language/API you are using.

--create table type for TVP
CREATE TYPE dbo.EmployeeSpreadsheetRows AS TABLE(
      employee_id int
    , name nvarchar(100)
    , email varchar(255)
    , phone_number varchar(20)
    , date_of_birth date
    , address nvarchar(100)
    , department nvarchar(100)
    , manager nvarchar(100)
    , validation_message nvarchar(1000)
    );
GO


CREATE PROC dbo.InsertSpreadsheet
      @FileName varchar(255) --values needed for MASTER_CDT table insert
    , @EmployeeSpreadsheetRows dbo.EmployeeSpreadsheetRows READONLY --spreadsheet rows
AS
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRAN;
    INSERT INTO dbo.MASTER_CDT(FileName) VALUES(@FileName);
    DECLARE @batch_id int = SCOPE_IDENTITY();
    INSERT INTO EMPLOYEE_SPREADSHEET(
          employee_id
        , name
        , email
        , phone_number
        , date_of_birth
        , address
        , department
        , manager
        , validation_message
        , batch_id
        )
    SELECT
          employee_id
        , name
        , email
        , phone_number
        , date_of_birth
        , address
        , department
        , manager
        , validation_message
        , @batch_id
    FROM @EmployeeSpreadsheetRows;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 1 ROLLBACK;
    THROW;
END CATCH;
GO

Answer 2

If I'm hearing what you're saying correctly, consider making your batch_id "match" an uploaded document by adding in your procedure something like:

declare @batch_id uniqueidentifier 
select @batch_id = newid()
insert into main_table (employee_id, name, email, ... batch_id) values (101,'Max Chan','[email protected]', @batch_id)
insert into main_table (employee_id, name, email, ... batch_id) values (102,'Next User','[email protected]', @batch_id)

That way, the batch_id will match the uploaded document. And, no batch_ids will be the same using the GUID approach.