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.