1

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.

2

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)
)
  • 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 Apr 14 '17 at 17:03
  • Can you show how you set it up? – Natalia Apr 14 '17 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 Apr 14 '17 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 Apr 14 '17 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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