MySQL - DBUpdateException ('Deadlock found when trying to get lock; try restarting transaction')

c# entity-framework-core mysql transactionscope

Question

I have an mvc app in .NET Core uses MySQL. I have a download function where id is passed to download a file. However, errors keep happening and seems for somehow the app becomes less responsive on the server as if it is being recycled. the following code shows the part responsible to download the file. I'm using TransactionScope to ensure that the download counter has been updated correctly. the error I'm getting is related to deadlock. Is it possible if someone could point out where the issue is?

using (var context = new MyDbContext())
using (TransactionScope scope = new TransactionScope())
{
    ProjectDownload download = context.ProjectDownload.Include(pd => pd.Project).Where(d => d.Id == downloadId).FirstOrDefault();

    if (download == null) throw new InvalidOperationException("Cannot find ProjectDownload.Id");

   download.DownloadCounter++;
   download.Project.DownloadCounter++;

   try
   {
       context.SaveChanges();
       scope.Complete();
       return (ProjectDownloadModel)download;
   }
   catch (DbUpdateException ex){
       throw;
   }
}

also tried this way changing the previous code to:

using (var context = new MyDbContext())
using (TransactionScope scope = new TransactionScope())
{
    ProjectDownload download = context.ProjectDownload.Where(d => d.Id == downloadId).FirstOrDefault();

   download.DownloadCounter++;

   Project proj = context.Project.Where(p => p.ProjectDownload.Any(pd => pd.Id == downloadId)).FirstOrDefault();
   proj.DownloadCounter++;
   ...

the following error occures:

Exception has occurred: CLR/MySql.Data.MySqlClient.MySqlException
An exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in Microsoft.EntityFrameworkCore.dll but was not handled in user code: 'Deadlock found when trying to get lock; try restarting transaction'
  at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
  at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
  at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
  at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
  at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
  at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
  at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)    
  at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)    
  at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
  at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()    
  at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable`1 source, Boolean& found)    
  at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ResultEnumerable`1.GetEnumerator()
  at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__17`2.MoveNext()
  at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
  at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable`1 source, Boolean& found)    
  at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)    
  at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass15_1`1.<CompileQueryCore>b__0(QueryContext qc)    
  at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)    
  at ProjectProcess.DownloadProject(Int32 downloadId) in C:\Users\Projects\ProjectWebsite\BusinessLogic\ProjectProcess.cs:line 124    
  at ProjectController.DownloadProject(Int32 downloadID) in C:\Users\Projects\ProjectWebsite\Controllers\ProjectController.cs:line 33    
  at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)    
  at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__12.MoveNext()

I don't understand how the deadlock happens and how to solve it

1
0
1/15/2019 8:27:00 PM

Accepted Answer

The problem is you are not locking certain record. In mysql you can lock rows like this

SELECT * FROM users WHERE name = 'name' FOR UPDATE;

But for your problem you can try this. I hope that solves your problem

using (var scope = new TransactionScope(TransactionScopeOption.RequireNew,
new TransactionOptions { 
    IsolationLevel = IsolationLevel.ReadUncommitted 
}))
{
  // your select and increment should go here  
}
1
1/13/2019 12:52:51 AM

Popular Answer

Can you try one more thing. Just a quick fix

//replace your line with this
if (download == null) {
    context.SaveChanges();
    scope.Complete();
    throw new InvalidOperationException("Cannot find ProjectDownload.Id");
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow