In my c# code I have the following method that creates a document in the database, adds metadata regarding the document to the database and then updates some information regarding the date the repository was last updated. This method is often called numerous times in quick succession as multiple file uploads are common. However I am having problems with the code failing due to deadlock in sql server.
private IEnumerable<DocumentMetadata> CreateDoc(int? jobId, int?repositoryId, int? folderId, string documentTypeString, IEnumerable<DocumentModel> files)
{
if ((jobId == null && repositoryId == null) || (jobId != null && repositoryId != null))
{
throw new InvalidOperationException("Either job id or repository id must be specified");
}
using (var tran = new TransactionScope())
{
List<DocumentMetadata> newDocuments = new List<DocumentMetadata>();
var documentType = GetDocumentTypeByPrefix(documentTypeString);
if (folderId == null)
{
// Find the root folder
var job = getJob(jobId);
var rootFolder = getRootFolder(job);
// If we can't find a root folder, create one
if (rootFolder == null)
{
rootFolder = CreateRootDirectory(job);
}
folderId = rootFolder.FolderId;
}
User currentUser = _userService.GetCurrentUser();
foreach (var file in files)
{
var document = new Document() { Document1 = file.Data };
var documentMetadata = new DocumentMetadata
{
Document = document,
CreatedDate = file.CreatedDate,
FileName = file.Filename,
FileSize = file.Data.Length,
FolderId = folderId,
DocumentType = documentType,
JobId = jobId,
RepositoryId = repositoryId,
User = currentUser
};
_unitOfWork.DocumentMetadata.Add(documentMetadata);
newDocuments.Add(documentMetadata);
}
// set repository updated date
if (repositoryId != null)
{
DocumentRepository repo = GetDocumentRepository(repositoryId);
if (repo != null)
{
repo.UpdatedDate = new DateTimeOffset(DateTime.Now);
}
}
_unitOfWork.SaveChanges();
tran.Complete();
return newDocuments;
}
}
After some debugging it would appear that the updating of the repository id is causing the deadlock problem. If I remove this code block outside of the transaction all files are saved with no errors.
Why would this code block
if (repositoryId != null)
{
DocumentRepository repo = GetDocumentRepository(repositoryId);
if (repo != null)
{
repo.UpdatedDate = new DateTimeOffset(DateTime.Now);
}
}
cause the deadlock? No other access is being made to the DocumentRepository table apart from in this method - as the locks are obtained in the same order surely there should be no deadlock?
What is it about this code that is leading to deadlock?
Updated: The code for GetDocumentRepository is:
public DocumentRepository GetDocumentRepository(int repositoryId)
{
var result = DocumentRepositories.SingleOrDefault(x => x.RepositoryId == repositoryId); return result;
}
Have you checked the code without defining a transaction explicitly? Based on your code I would say that you are trying to read something that has been modified but not commited. Another test you could do is to try to add a breakpoint in your code and try to get the DocumentRepository using READ UNCOMMITTED.