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

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

Possible duplicate of [How to avoid mysql 'Deadlock found when trying to get lock; try restarting transaction'](https://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans) — Progman, Jan 13 '19 at 00:50

score 2 · Accepted Answer · answered Jan 13 '19 at 00:52

2

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  
}

answered Jan 13 '19 at 00:52

Talha Akber

56
8

thanks. Now I'm getting a different error A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond\r\n at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)} Message [string]:"Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond." – Abdulkarim Kanaan Jan 13 '19 at 01:00
a question please, why isn't enough having a default TransactionScope? – Abdulkarim Kanaan Jan 13 '19 at 01:41
Thanks @mjwills I think now I know that Serializable is the default Isolation Level which is the safest level but with minimum concurrency ability. but any way, why is it the case of DeadLock in my query? – Abdulkarim Kanaan Jan 13 '19 at 04:31
Likely `Serializable` isolation level is the cause of your deadlock. – mjwills Jan 13 '19 at 04:33
Maybe because you are missing the finally scope rollback part. and you are throwing exception from the middle. Cover whole body with try and add finally – Talha Akber Jan 13 '19 at 04:39
thanks @TalhaAkber Your solution works. One thing, regarding the error "Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond." after restarting the `MySQL service`, it's gone – Abdulkarim Kanaan Jan 13 '19 at 05:01
but one thing, I couldn't understand is that how the deadlock happens in the serializable mode as I have a single key, don't I? so if one thread captures the key, then the second one just has to wait until the first transaction is completed! Am I correct? or did I miss something? or is it becuase of Include...more than one key? thanks – Abdulkarim Kanaan Jan 13 '19 at 05:01

score 1 · Answer 2 · answered Jan 13 '19 at 03:50

1

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");
}

answered Jan 13 '19 at 03:50

Talha Akber

56
8

thanks @TalhaAkber. I will add that. By the way, after restarting MySQL service everything seems fine. I'm not sure what the porblem was. anyway, I will keep checking. – Abdulkarim Kanaan Jan 13 '19 at 04:33
with the default IsolationLevel or did you change TransactionScope ? The main reason was you started the transaction and never finished because you threw exception from the middle. try consider finally tag after try catch and rollback your scope – Talha Akber Jan 13 '19 at 04:35
with the default IsolocationLevel the exception is thrown. – Abdulkarim Kanaan Jan 13 '19 at 04:48

score 0 · Answer 3 · answered Jan 14 '19 at 03:57

Try to put these before use Transaction Scope :

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

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

3 Answers3