0

I have this code in a method:

Task<int> linksCount = Task<int>.Factory.StartNew(() => { return DatabaseIn.GetUrlsCount(); });
Task<int> imagesCount = Task<int>.Factory.StartNew(() => { return DatabaseIn.GetImagesCount(); });

Later the linksCount and imagesCount.Result is displayed. However, when I run this code the first time. I get the following exception:

There is already an open DataReader associated with this Connection which must be closed first.

The code where the exception is thrown is:

MySqlCommand comm = null;
string cmdString = "SELECT COUNT(*) FROM damocles.imagestoassess;";

comm = new MySqlCommand(cmdString, conn);

if (comm.ExecuteScalar() == null)
{
    comm.Dispose();
    conn.Close();
    return 0;
}

int res = Convert.ToInt32(comm.ExecuteScalar());

comm.Dispose();
conn.Close();
return res;

The exception is thrown on the first call to ExecuteScalar. If I enclose the code in a try..catch clause the code continues but it throws another error in another part of the code.

Question:

  1. Why does it throw the exception? It doesn't throw any exception when run synchronously.
  2. How do I fix the code to work Asynchronously?

Thanks.

Theodor Zoulias
  • 34,835
  • 7
  • 69
  • 104
Dave Gordon
  • 29
  • 7
  • Is there any specific reason why you are [using `StartNew`](https://blog.stephencleary.com/2013/08/startnew-is-dangerous.html)? – GSerg May 09 '20 at 14:41
  • Does this answer your question? [Exception: There is already an open DataReader associated with this Connection which must be closed first](https://stackoverflow.com/questions/5440168/exception-there-is-already-an-open-datareader-associated-with-this-connection-w) – devNull May 09 '20 at 14:42
  • 2
    It would appear you are using "async" and "parallel" interchangeably. You are launching two threads (in a wrong way too) that try to use the same connection object. In an async scenario, you would stay on the same thread, and the second query would start only after the first one completes (which would be perfectly async, but not in parallel). Connection objects do not like several async commands being in flight against them at the same time, this is not specific to mysql. If you absolutely want to run two queries in parallel, give each a separate connection object. – GSerg May 09 '20 at 14:49
  • @GSerg No there is not. – Dave Gordon May 09 '20 at 14:49
  • 1
    As a side note, you call `comm.ExecuteScalar()` twice, so you hit the database twice. You could optimize this by storing the result of the first call in a local variable. – Theodor Zoulias May 09 '20 at 14:49
  • Ok this seems to be beyond me. I am going to go back to incredibly slow synchronous coding for now. Thank you all. – Dave Gordon May 09 '20 at 15:11
  • 1
    Also please see https://stackoverflow.com/a/40065501/11683 for why `async` it's not going to work anyway even if you were to properly implement `Task GetUrlsCountAsync()` and do `int linksCount = await GetUrlsCountAsync()`. – GSerg May 09 '20 at 15:11
  • @GSerg the StartNew is correct the blog states: "As a side note, the context of this discussion is async code. If you’re writing parallel code (e.g., dynamic task-based parallelism), then StartNew is the tool you want to use." Which is what I was aiming for. – Dave Gordon May 09 '20 at 15:46

1 Answers1

1

A MySqlConnection can only be used by one thread at a time. Your exception is happening because both Tasks are using the same conn object. For more details, see https://mysqlconnector.net/troubleshooting/connection-reuse/.

Your code will work if you create and use a new MySqlConnection inside each Task.

Additionally, it's a longstanding bug that async operations in MySql.Data aren't actually asynchronous. You'll need to switch to MySqlConnector to get asynchronous I/O for MySQL operations.

Bradley Grainger
  • 27,458
  • 4
  • 91
  • 108