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;
    -- 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
                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;
        -- Add new file
            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
    RETURN @newDocumentId
        DECLARE @state AS INT = XACT_STATE()
        IF(@state = -1)
            PRINT N'The transaction cannot be committed in its current state. Rolling back transaction.'
        ELSE IF (@state = 1)
            PRINT N'The the transaction can be committed in its current state. Committing transaction.'

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.

1 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

   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)
  • Thank you for mentioning the isolation level! I never changed it and I don't know what the default is, but because you did I did some google searching. I just learned that bulk copy operations block queries running under SNAPSHOT, READ COMMITTED, and READ UNCOMMITTED with row versioning isolation levels. Something tells me that the issue was actually with the default being one of these. I changed it to REPEATABLE READ and it can load all 3 documents into the database at once now. I'm having issues with the transaction ignoring the timeout value I set now, though...
    – sonicbhoc
    Commented Apr 14, 2017 at 17:03
  • Can you show how you set it up?
    – Natalia
    Commented Apr 14, 2017 at 17:07
  • Sure. Although after looking at it again, I think it might be complicated enough to warrant its own question. After lunch, I'll ask it.
    – sonicbhoc
    Commented Apr 14, 2017 at 17:27
  • 1
    I actually figured it out. I made the same mistake twice, but the second time it was in the C# code. I fixed it, and everything is running smoothly now. Thank you!
    – sonicbhoc
    Commented Apr 14, 2017 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.