Asynchronous database inserts from C# always result in a deadlock

Question

I'm hoping this doesn't come off as too amateurish, but I'm converting a synchronous program to run asynchronously. So far, I've gotten everything running asynchronously except database inserts. Essentially, where it fails is that two of the tasks are trying to insert into the same table at the same time, judging by the fact that the exception returned references the line of my stored procedure with the insert statement.

Here is the relevant C# code:

I attempt to spin the input loading function into a different context, which allows the program to continue processing (and which will eventually lead to calling this same function on another list of files, which almost always occurs before the first document is finished loading into the DB)

await Task.Run(() => context.LoadInput(fileList))

My input files are CSV files. I load them into the database using stored procedures that take a user-defined table type as a parameter. I assemble the UDT on the C# side and send it to the database. In the database, the following stored procedure is called:

CREATE PROCEDURE ImportDocument @documentData dbo.DocumentImportStructure READONLY, @newDocumentId INT OUT AS SET NOCOUNT ON;
BEGIN
    BEGIN TRY
    -- Detect duplicate file
    DECLARE @duplicateId AS INT = (SELECT Id FROM dbo.Documents AS d WHERE EXISTS (SELECT Id FROM @documentData as i WHERE i.[File_Name] = d.[File_Name] AND i.File_Size = d.File_Size));
    If(@duplicateId IS NOT NULL)        
        If((SELECT [Status] FROM dbo.Documents WHERE Id = @duplicateId) < 2)
            SET @newDocumentId = @duplicateId
        ELSE
            BEGIN
                DECLARE @ErrorMsg AS NVARCHAR(2048) = 'The document "' + (SELECT [File_Name] FROM @documentData) + '" created ' + CAST((SELECT File_CreationDate FROM @documentData) AS NVARCHAR) + ' already exists in the database under file ID ' + CAST(@duplicateId AS nvarchar) + '.';
                THROW 60001, @ErrorMsg, 1;
            END;        
    ELSE
        BEGIN
        -- Add new file
        BEGIN TRANSACTION
            INSERT INTO dbo.Documents([File_Name], File_CreationDate, File_Size, MailingType, StatementDate, DueDate, RecordCount, [Status])
                SELECT [File_Name], File_CreationDate, File_Size, MailingType, StatementDate, DueDate, RecordCount, [Status] FROM @documentData;
        SET @newDocumentId = @@identity
        COMMIT      
        END;
    RETURN @newDocumentId
    END TRY
    BEGIN CATCH
        DECLARE @state AS INT = XACT_STATE()
        IF(@state = -1)
        BEGIN
            PRINT N'The transaction cannot be committed in its current state. Rolling back transaction.'
            ROLLBACK TRANSACTION;
        END;
        ELSE IF (@state = 1)
        BEGIN
            PRINT N'The the transaction can be committed in its current state. Committing transaction.'
            COMMIT TRANSACTION;
        END;
        THROW;
    END CATCH
END

Line 18 contains the INSERT statement, and it's also where the following exception is thrown soon after all of the documents start simultaneously being written to the database:

$exception  {"Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The transaction cannot be committed in its current state. Rolling back transaction."}   System.Data.SqlClient.SqlException

I could load the data synchronously and just have everything else be asynchronous, but I feel like there's a way to do this that I'm missing, or an issue I'm overlooking.

Answer 1

Seems like you have issues with your read statement. What is the isolation level on a database?

You can try to get rid of the second read by adding StatusId <2 to the first read statement

(SELECT Id
   FROM dbo.Documents AS d
  WHERE StatusID < 2
   AND EXISTS (SELECT Id FROM @documentData as I
                WHERE i.[File_Name] = d.[File_Name]
                  AND i.File_Size = d.File_Size)
)