none
EF async: if I use pesimistic concurrency solutoon I lost the advantage of the async feature? RRS feed

  • Question

  • In my solution i i use pesimistic solution for some cases. I am using SQL server.

    Ford that, I use a transaction, and do an update to block the record. That works but i have readed that if i use pesimistic concurrency, to keep the block, it is needed to keep the connection, how async free the connection while datadase is processing a requisa, my doubt is if i lost the advantage of the async if i use a transaction.

    Thank you so much.

    Tuesday, April 21, 2015 9:05 PM

Answers

  • Hi ComptonAlvaro,

    -->my doubt is if i lost the advantage of the async if i use a transaction.

    From this article in MSDN, we could see this: https://msdn.microsoft.com/en-us/data/dn456843.aspx

    But be aware that, depending on what you do within the asynchronous methods, this may result in long-running transactions – which can in turn cause deadlocks or blocking which is bad for the performance of the overall application.

    We could know if you use asynchronous methods, it may cause deadlocks or blocking which will have bad performance of the overall application.

    If you use the transaction with other features it's OK.

    If you have any other concern regarding this issue, please feel free to let me know.

    Best regards,
    Youjun Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by ComptonAlvaro Sunday, April 26, 2015 7:08 AM
    Wednesday, April 22, 2015 8:26 AM

All replies

  • Hi ComptonAlvaro,

    -->my doubt is if i lost the advantage of the async if i use a transaction.

    From this article in MSDN, we could see this: https://msdn.microsoft.com/en-us/data/dn456843.aspx

    But be aware that, depending on what you do within the asynchronous methods, this may result in long-running transactions – which can in turn cause deadlocks or blocking which is bad for the performance of the overall application.

    We could know if you use asynchronous methods, it may cause deadlocks or blocking which will have bad performance of the overall application.

    If you use the transaction with other features it's OK.

    If you have any other concern regarding this issue, please feel free to let me know.

    Best regards,
    Youjun Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by ComptonAlvaro Sunday, April 26, 2015 7:08 AM
    Wednesday, April 22, 2015 8:26 AM
  • Well, I am using pesimistic concurrence for ensure the coherence of some data, so I am doing the following:

    using (CMMSEntities dbContext = new CMMSEntities)
    {
    	using (DbContextTransaction transaction = dbContext.Database.BeginTransaction())
    	{
    		//I do a fake update to block the row that ensures that only process has the access to the group of rows
    		//that I need to update.
    		string strTSQL = "update Mytable set Concurrency = Concurrency where ID = " +pamMyEntity.ID;
    		await dbContext.Database.ExecuteSqlCommandAsync(strTSQL);
    		
    		//My code
    		
    		await dbContext.SaveChangesAsync();
    	}
    }

    What I want is to block the row but to have the benefits of the async methods, but I don't know if it is possible because I am using a transaction.

    With the async method, if I am not wrong, the client make a request, the database receive the request, release the connection, process the request and stablish a new connection to return the result to the client. So the connection is used only when it's needed.

    My doubt is when I use a transaction and block a row. I don't know if in this case the database is able to release the connection while is processing the request and keep the block in the row to avoid another process to have access to the row, or need to keep the connection alive while preccess the request.

    Thanks so much.

    Wednesday, April 22, 2015 8:52 AM
  •  I don't know if in this case the database is able to release the connection while is processing the request and keep the block in the row to avoid another process to have access to the row, or need to keep the connection alive while preccess the request.

    As far as I know, you may not be able to release the connection of DataBase.

    You need to your transaction.Complete() to finish the database transaction and your data is saved. During this time, the database connection would not be able to released.

    Regards,
    Omar

    Sunday, April 26, 2015 6:53 AM
  • "With the async method, if I am not wrong, the client make a request, the database receive the request, release the connection, process the request and stablish a new connection to return the result to the client. So the connection is used only when it's needed."

    That's not at all how it works.  Async is _only_ about freeing client threads.  Everything on the server happens same as in the sync case.

    At each "await" the client thread is free to do additional work, instead of blocking while the server does its work.  But the SqlConnection will stay open until after all the code completes and the code exits the "using" block.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Sunday, April 26, 2015 4:27 PM