MySQL C#异步方法不起作用? [英] MySQL C# async methods doesn't work?
问题描述
我在服务器中有一个数据库，异步方法似乎不起作用.
这是我的代码:
static async void Example()
{
string connectionString =
"Server=mydomainname.com;" +
"Port=3306;" +
"Database=scratch;" +
"Uid=Assassinbeast;" +
"Password=mypass123;" +
"AllowUserVariables= true;";
MySql.Data.MySqlClient.MySqlConnection sqConnection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
await sqConnection.OpenAsync();
Console.Write("Opened. Now Click to close");
Console.ReadLine();
sqConnection.Close();
}
static void Main(string[] args)
{
Console.ReadLine();
Example();
Console.WriteLine("Done");
Console.ReadLine();
}
在"await"语句中，它实际上应该跳回到Main()函数并写出"Done".但是它没有这样做.它只是像不是异步方法一样同步运行，一旦功能完全完成，它将首先写入完成".
那我在做什么错呢?是虫子吗?
更新
好的，所以在我得到一些答案之后，我实际上仍然看不到OpenAsync()和Open()之间的任何区别.
我开始尝试测试更多的东西，我想我可以得出结论，异步方法不起作用
这是我的新代码:
static async Task Example()
{
string connectionString =
"Server=mydomainname.com;" +
"Port=3306;" +
"Database=scratch;" +
"Uid=Assassinbeast;" +
"Password=mypass123;" +
"AllowUserVariables= true;";
using (var sqConnection = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
{
Console.WriteLine("Opening");
await sqConnection.OpenAsync();
Console.WriteLine("Opened. Now Closing");
}
}
static async Task Example2()
{
//Lets pretend this is a database that my computer will try to connect to
Console.WriteLine("Opening");
await Task.Delay(1000); //Lets say it takes 1 second to open
Console.WriteLine("Opened. Now Closing");
}
static void Main(string[] args)
{
Console.ReadLine();
Task.Run(() => Example());
Task.Run(() => Example());
Task.Run(() => Example());
Task.Run(() => Example());
Task.Run(() => Example());
Task.Run(() => Console.WriteLine("Done"));
Console.ReadLine();
}
在这里，当我运行Example()5次时，它将输出如下:
最多需要3秒钟才能写出完成". 请注意，我的计算机根本无法在CPU上运行，因为它只是在等待并连接到数据库，而连接到数据库大约需要1秒钟.
因此它实际上阻止了我的计算机线程，并且没有运行多线程，否则它将立即写出完成".
因此，如果我调用Example2()而不是Example()，那么我得到的结果就是我想要的和我期望的:
在这里，这是一个真正的异步方法，因为我可以在只有2个内核的计算机上一次完成6件事. 但是第一个示例，一次只能做两件事，因为MySQL异步方法不起作用.
我还使用sqConnection.Open()测试了它，其结果与sqConnection.OpenAsync()完全相同
所以现在，我无法弄清楚如何同时连接5次数据库.
从一些旧代码(6.7.2)看，似乎mysql ADO.NET提供程序没有正确实现任何异步功能.这包括TAP模式和较早的样式Begin ...，End ...异步模式.在那个版本中，Db *异步方法似乎根本没有被编写.他们将使用.NET中的基类，这些基类是同步的，并且看起来都像这样:
public virtual Task<int> ExecuteNonQueryAsync(...) {
return Task.FromResult(ExecuteNonQuery(...));
}
(100％与将其包装在任务中的额外开销同步； 参考资源在这里)
如果Begin(开始)和End(结束)版本编写正确(不是)，则可以这样实现:
public override Task<int> ExecuteNonQueryAsync(...) {
return Task<int>.Factory.FromAsync(BeginExecuteNonQueryAsync, EndExecuteNonQueryAsync, null);
}
执行此操作取决于底层套接字的某种回调api，该回调api最终会以一种模式处理，即调用方通过套接字发送一些字节，然后在就绪时从底层网络堆栈中调用已注册的方法. .
但是，mysql连接器不会执行此操作(首先不会覆盖该方法；但是，如果这样做，相关的begin和end方法将不会在某些底层套接字api上异步). mysql连接器的作用代替在当前连接实例上构建内部方法的委托，并在单独的线程上同步调用它.例如，在此期间，您不能同时在同一连接上执行第二条命令，如下所示:
private static void Main() {
var sw = new Stopwatch();
sw.Start();
Task.WaitAll(
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync());
sw.Stop();
Console.WriteLine(sw.Elapsed.Seconds);
}
private static DbCommand GetDelayCommand() {
var connection = new MySqlConnection (...);
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = "SLEEP(5)";
cmd.CommandType = CommandType.Text;
return cmd;
}
(假设您正在连接池中，并且任务数超过了最大池大小；如果异步有效，则此代码将根据池中的连接数获得一个数字，而不是取决于该数量的和可同时运行的线程数)
这是因为代码具有 https://bugs.mysql.com/bug.php? id = 70111
2016年7月6日更新:GitHub上的一个有趣项目，该项目最终可能会在 https://github.com上解决. /mysql-net/MySqlConnector (可能会使用更多与成功有关的贡献者[我不再使用MySql做任何事情].)
i have a database in a server and it seems like the async method doesn't work.
Here is my code:
static async void Example()
{
string connectionString =
"Server=mydomainname.com;" +
"Port=3306;" +
"Database=scratch;" +
"Uid=Assassinbeast;" +
"Password=mypass123;" +
"AllowUserVariables= true;";
MySql.Data.MySqlClient.MySqlConnection sqConnection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
await sqConnection.OpenAsync();
Console.Write("Opened. Now Click to close");
Console.ReadLine();
sqConnection.Close();
}
static void Main(string[] args)
{
Console.ReadLine();
Example();
Console.WriteLine("Done");
Console.ReadLine();
}
At the "await" statement, its actually supposed to jump back to the Main() function and writeout "Done". But its not doing that. Its just running synchronously like it wasn't an async method and it will first write "Done" once the function is fully completed.
So what am i doing wrong? Is it a bug?
UPDATE
Okay, so after i got some answers, i actually still couldn't see any difference between the OpenAsync() and Open().
I began trying to test more things out and i think i can conclude that the async method DOES NOT WORK
Here is my new code:
static async Task Example()
{
string connectionString =
"Server=mydomainname.com;" +
"Port=3306;" +
"Database=scratch;" +
"Uid=Assassinbeast;" +
"Password=mypass123;" +
"AllowUserVariables= true;";
using (var sqConnection = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
{
Console.WriteLine("Opening");
await sqConnection.OpenAsync();
Console.WriteLine("Opened. Now Closing");
}
}
static async Task Example2()
{
//Lets pretend this is a database that my computer will try to connect to
Console.WriteLine("Opening");
await Task.Delay(1000); //Lets say it takes 1 second to open
Console.WriteLine("Opened. Now Closing");
}
static void Main(string[] args)
{
Console.ReadLine();
Task.Run(() => Example());
Task.Run(() => Example());
Task.Run(() => Example());
Task.Run(() => Example());
Task.Run(() => Example());
Task.Run(() => Console.WriteLine("Done"));
Console.ReadLine();
}
Here when i run the Example() 5 times, it will output like this:
It takes up to 3 seconds before it will write out "Done". Note that my computer is not working on the CPU at all, because it is only waiting and connecting to the database which takes around 1 second to connect to.
So its actually blocking my computers thread and not running multithreaded, otherwise it would write out "Done" immediedtly.
So if i called Example2() instead of Example(), then i get this result which is what i want and what i would expect:
Here, it is true async method, because i can do over 6 things at a time on my computer which has only 2 cores. But the first example, i could only do two things at a time because MySQL async method does not work.
I also tested it with sqConnection.Open(), which had the exact same result as sqConnection.OpenAsync()
So right now, i just cant figure out how to connect to the database 5 times at the same time.
Judging from some old code (6.7.2), it appears that the mysql ADO.NET provider does not implement any of the async functionality correctly. This includes the TAP pattern and the older style Begin..., End... async patterns. In that version, the Db* async methods appear to not be written at all; they would be using the base class ones in .NET which are synchronous and all look something like:
public virtual Task<int> ExecuteNonQueryAsync(...) {
return Task.FromResult(ExecuteNonQuery(...));
}
(100% synchronous with the added overhead of wrapping it in a task; reference source here)
If the Begin and End versions were written correctly (they aren't) it could be implemented something like this:
public override Task<int> ExecuteNonQueryAsync(...) {
return Task<int>.Factory.FromAsync(BeginExecuteNonQueryAsync, EndExecuteNonQueryAsync, null);
}
(reference source for that method for SqlCommand)
Doing this is dependent on some sort of callback api for the underlying socket to eventually deal with in a pattern where the caller sends some bytes over the socket and then a registered method gets called back from the underlying network stack when it is ready.
However, the mysql connector doesn't do this (it doesn't override that method in the first place; but if it did, the relevant begin and end methods aren't async on some underlying socket api). What the mysql connector does instead is build a delegate to an internal method on the current connection instance and invokes it synchronously on a separate thread. You cannot in the meantime for example execute a second command on the same connection, something like this:
private static void Main() {
var sw = new Stopwatch();
sw.Start();
Task.WaitAll(
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync(),
GetDelayCommand().ExecuteNonQueryAsync());
sw.Stop();
Console.WriteLine(sw.Elapsed.Seconds);
}
private static DbCommand GetDelayCommand() {
var connection = new MySqlConnection (...);
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = "SLEEP(5)";
cmd.CommandType = CommandType.Text;
return cmd;
}
(assuming you are connection pooling and the number of tasks there is more than the maximum pool size; if async worked this code would get a number depending on the number of connections in the pool instead of a number depending on both that and the number of threads that can run concurrently)
This is because the code has a lock on the driver (the actual thing that manages the network internals; *). And if it didn't (and the internals were otherwise thread safe and some other way was used to manage connection pools) it goes on to perform blocking calls on the underlying network stream.
So yeah, no async support in sight for this codebase. I could look at a newer driver if someone could point me to the code, but I suspect the internal NetworkStream based objects do not look significantly different and the async code is not looking much different either. An async supporting driver would have most of the internals written to depend on an asynchronous way of doing it and have a synchronous wrapper for the synchronous code; alternatively it would look a lot more like the SqlClient reference source and depend on some Task wrapping library to abstract away the differences between running synchronously or async.
* locking on driver doesn't mean it couldn't possibly be using non-blocking IO, just that the method couldn't have been written with a lock statement and use the non-blocking Begin/End IAsyncResult code that could have been written prior to TAP patterns.
Edit: downloaded 6.9.8; as suspected there is no functioning async code (non-blocking IO operations); there is a bug filed here: https://bugs.mysql.com/bug.php?id=70111
Update July 6 2016: interesting project on GitHub which may finally address this at https://github.com/mysql-net/MySqlConnector (could probably use more contributors that have a stake in its success [I am no longer working on anything with MySql]).
这篇关于MySQL C#异步方法不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
