If I have a code that ExecuteNonQuery or ExecuteReader or ExecuteScalar they work just ok and the performance is... well, quite ok. Now, if I wan't to not eat up not so much resources, I run the async versions, ExecuteNonQueryAsync or ExecuteReaderAsync or ExecuteScalarAsync and I expect the business logic thread to be non-blocked while SQL is executing. For technical background details see:
http://fsprojects.github.io/SQLProvider/core/async.html
Anyways, this is the expected result. Actual result is that if I run this with 10 of threads (like web servers usually do), not only the threads are not locked (
http://stackoverflow.com/questions/30551459/mysql-c-sharp-async-methods-doesnt-work ), but the whole server is jammed and I got SQL-deadlocks and everything.
Here is the sample source code in F#:
let cstr = "server = localhost; database = TestDB; uid = root;pwd = 12345"
let ExecuteSqlOld (query : string) parameters =
use rawSqlConnection = new MySqlConnection(cstr)
rawSqlConnection.Open()
use command = new MySqlCommand(query, rawSqlConnection)
parameters |> List.iter(fun (par:string*string) -> command.Parameters.AddWithValue(par) |> ignore)
let affectedRows = command.ExecuteNonQuery()
()
let ExecuteSql (query : string) parameters =
async {
use rawSqlConnection = new MySqlConnection(cstr)
do! rawSqlConnection.OpenAsync() |> Async.AwaitTask
use command = new MySqlCommand(query, rawSqlConnection)
parameters |> List.iter(fun (par:string*string) -> command.Parameters.AddWithValue(par) |> ignore)
let! affectedRows = command.ExecuteNonQueryAsync() |> Async.AwaitTask
()
}
The problem is also reproducable with the source code in this repo, under Thread test, changing the last method to use SubmitUpdatesAsync:
https://github.com/fsprojects/SQLProvider/blob/master/tests/SqlProvider.Tests/scripts/MySqlTests.fsx