C# で DM(MySQL) に複数同時にアクセスしたい

Question

前提・実現したいこと

C# からスレッドやタスクを利用して、同時に DB(MySQL) からデータの取得を行いたいと考えます。
DB へのアクセスには MySql.Data.MySqlClient.MySqlDataAdapter.Fill() を使用しています。

発生している問題・エラーメッセージ

上記のようなことをすると高い確率で、以下の例外が発生します。

MySql.Data.MySqlClient.MySqlException :
There is already an open DataReader associated with this Connection which must be closed first.

試したこと

例外の原因はひとつのコネクションで同時に複数のアクセスを行ったためと理解しました。
これはそのようなことを行うとデッドロックの発生等を招くこともあり危険であることも理解しました。
現状では .Fill() を行う箇所に各スレッドやタスクで共通のオブジェクトを利用した lock を掛けることによりこのメソッドを同時に実行しないようにしています。

ただ、今回同時アクセスを行うようにしたい箇所は多量の小さなデータの読み込みであり、直列に実行していると時間的にきついところがあります。
まとめて１回のアクセスで読み取れるようにしてもよいのですが、ここまで試した範囲では受け取った後のデータの整理に時間が掛かってしまい、(上記の) lock をつけて並列に実行した方が時間的に有利な状態となっています。

SQL Server では connectionString に MARS を許す設定を加えることで同時アクセスを許可できるようなのですが、MySQL にはそのような機能はないようです。

質問のまとめ

① MySQL でひとつのコネクション内で同時に DB へのアクセスを行う方法があれば教えてください。
② 「発生している問題」に書いた問題が発生するのは、同じコネクション内で複数のアクセスを行えないから。であるならばひとつのアプリケーション内で異なるコネクションを張ることが出来れば解決するように思えます。(根本的な問題の解決になるかは置いておきます)
ひとつのアプリケーション内で、かつ C# で、複数のコネクションを張る方法があれば教えてください。
③ それ以外に、同時にアクセスする方法があるようでしたら教えてください。

よろしくお願いします。
以上です。

以下、追記です。

当該部分のコードは以下のようにしています。

※そのまま書けないので、概要化しています。

// データを処理する部分
using( var connection = new MySqlConnection( connection_string ) )
{
    connection.Open();

    Parallel.For( 0, 3, id =>
    {
        while( !<終了条件> )
        {
            // <sql> を用意する
            // <param> を用意する
            var res = GetData( connection, <sql>, <param> );
            // res をいろいろする
        }
    });
}

// DB にアクセスする部分
public DataTable[] GetData( MySqlConnection connection, string sql, Hashtable param )
{
    var ret = new List<DataTable>();

    using( var command = new MySqlCommand( sql, connection ) )
    {
        foreach( DictionaryEntry item in <param> )
        {
            coomand.Parameters.Add( new MySqlParameter( item.Key.ToString(), item.Value ?? System.DBNull.Value ) );
        }

        using( var adapter = new MySqlDataAdapter( command ) )
        {
            using( var ds = new DataSet() )
            {
                lock( this )  adapter.Fill( ds );
                foreach( Data.DataTable item in ds.Tables ) ret.Add( item );
            }
        }
    }

    return ret.ToArray();
}

Comments

[hihijiji]

MySQLの排他制御については理解していますか？

[t.kusu]

一般的な DB の更新や参照の時の排他制御なら(程度の問題はありますが)分かっているつもりです。
MySQL 独特の制御があるのでしょうか。

[hihijiji]

排他制御について一般的な知識があるなら、別クライアントからのアクセスも別スレッドからのアクセスも同じように扱うことは可能かと思いますが…
それではダメな要因があるのでしょうか？

Answer 1

複数の MySqlConnection を作成して Open すれば、作成したぶんのコネクションが張られます。
(プーリングという仕掛けがあるので、かならずしも MySqlConnection の数 = コネクションの数にはなるわけではありません)
下のコードでは、SQL をパラレルに実行して DataTable を作成します。

using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace WindowsFormsApp1
{
    static class Program
    {
        [STAThread]
        static void Main() {
            var parallels = 5;
            var c = new MySqlConnectionStringBuilder();
            var builder = new MySqlConnectionStringBuilder {
                UserID = "....",
                Password = "....",
                Server = ".....",
                Database = "....",
                Pooling = true,
                MinimumPoolSize = (uint)parallels,
                MaximumPoolSize = (uint)parallels
            };

            var sqlCount = 5;
            var sqls = new string[sqlCount];
            var dataTables = new DataTable[sqlCount];
            // sqls[] に SQL を入れる
            sqls[0] = "select ......";
            sqls[1] = "select ......";
            sqls[2] = "select ......";
            sqls[3] = "select ......";
            sqls[4] = "select ......";

            ParallelFill(builder.ConnectionString, parallels, sqls, dataTables);
        }

        public static void ParallelFill(
                                string connectionString,
                                int parallels,
                                string[] sqls,
                                DataTable[] dataTables) {
            var option = new ParallelOptions();
            option.MaxDegreeOfParallelism = parallels;
            Parallel.For(0, sqls.Length, option,
                i => {
                    using (var connection = new MySqlConnection(connectionString)) {
                        connection.Open();
                        using (var command = new MySqlCommand(sqls[i], connection)) {
                            using (var adapter = new MySqlDataAdapter(command)) {
                                var dt = new DataTable();
                                adapter.Fill(dt);
                                dataTables[i] = dt;
                            }
                        }
                    }
                });
        }
    }
}

DataTable は高機能で便利な機能を持っていますが、速度だけを見ると遅いので、MySqlDataReader を使ったデータの取り出しを行うと速度改善が期待できます。

MySqlDataReader を使った例)

public static List<object[]> GetSQLValues(
                    MySqlConnection connection,
                    string sql,
                    params MySqlParameter[] parameters) {
    var result = new List<object[]>();
    using (MySqlCommand command = connection.CreateCommand()) {
        command.CommandText = sql;
        if (parameters != null && parameters.Length > 0) {
            command.Parameters.AddRange(parameters);
        }
        using (MySqlDataReader reader = command.ExecuteReader()) {
            int fieldCount = reader.FieldCount;
            while (reader.Read()) {
                var values = new object[fieldCount];
                reader.GetValues(values);
                result.Add(values);
            }
        }
    }
    return result;
}

ただ、このようにしてしまうと、項目にインデックスでアクセスことになるため、コードの可読性を著しく下げます。
Dapper あたりが落としどころかもしれません。

-- 「直列」「並列」の話 --
ネットワークの場合ですと、パケットを送って応答を「待つ」動作が入るので、単一のコネクションだと帯域を 100% 使い切ることはできません。
パラレル実行すると、待っている間に別のパケットを送れるのでメリットは充分あります。
ダウンローダーの中には複数のコネクションを張って、サーバーからパラレルにダウンロードし、高速化しているものがあるので使ってみると体感できるかと思います。
ディスクなんかもそうで、物理的にアームが動くからパラレル実行しても意味がないっていうのも誤った認識です。

Comments

[t.kusu]

遅くなりすみません。ご教示いただき感謝します。

・いただいたサンプル(ひとつめ)を見ると、自分の実装と意図としてそう相違はないようにも思えます。
　だとすると自分の書き方(質問本文を更新してコードを追記しました)に問題があるのかも知れません。
　最終的な結論を出せるのは連休明けになってしまいますが、確認してみたいと思います。

・MySqlDataReader の部分は、素直にDataTable を使用していました。
　仰る通り、可読性を考えました。ただスピードを求められていることもあるので、
　DataReader での実装も検証してみることにします。可読性は・・コメントで何とかなると考えます。

[KOZ6.0]

提示していただいたコードは、ひとつの MySqlConnection を Pararell.For の中で使っていますね。
私のコードは Paralell.For の中で MySqlConnection を作成し、新しい接続を開始しています。

ただし、スレッド起動やコネクションを作成するためのコストがマルチスレッドによる短縮効果を上回ってしまうと逆に遅くなってしまうのは SurferOnWww さんの回答にあるとおりです。
付け加えるなら、アプリケーション単体で速くなっても、複数端末で同時起動し、サーバー側の資源が枯渇してしまう事態になってしまうと、システム全体でのパフォーマンスを落としてしまいます。

そのあたりは、いろいろ調整が必要になってくるかと思います。

可読性についてはコメントもそうですが、定数を作成すると良いと思います。

[t.kusu]

重ねて遅くなりすみません。
先に２点お詫びを。
1) コメントの返信をいただいたことに気が付いていませんでした。無視した形になってしまいました。
2) 追記したコードに誤りがあります。connection の using ブロックと Parallel.For のブロックの前後が逆です。
そういえばタイトルも間違っていますね。。

環境下で確認してみました。
・現在のコードをいただいたサンプルのように修正してみましたが、例外発生を再現します。
　まだ対応が足りていないだけとも思えるのですが、現状としてご報告します。
・これも自分の誤りかも知れませんが、DataReader でもカラム名による参照が出来るんですね。
　本当にできるなら可読性の問題も解決できるかもしれません。
・可読性に関するご忠告をありがとうございました。胸にとどめておきます。

一応、今回で本質問を締めきろうと思います。ありがとうございました。

Answer 2

MySQL の Connector/NET の MySqlDataAdapter.Fill メソッドの中では MySqlDataReader を使っているはずです。

そして、MySQL のドキュメントの MySqlDataReader の説明には以下の記述があります。（ちなみにこれは SqlDataReader でも同じです）

MySqlDataReader Class
https://dev.mysql.com/doc/dev/connector-net/6.10/html/T_MySql_Data_MySqlClient_MySqlDataReader.htm

"While the MySqlDataReader is in use, the associated MySqlConnection is busy serving the MySqlDataReader, and no other operations can be performed on the MySqlConnection other than closing it. This is the case until the Close() method of the MySqlDataReader is called."

という訳で、上記の制限からの推測かつ未検証ですが、

① MySQL でひとつのコネクション内で同時に DB へのアクセスを行う方法があれば教えてください。

「同時」の意味にもよりますが、MySqlDataReader を Close しないのであれば方法はないということになるはずです。

② 「発生している問題」に書いた問題が発生するのは、同じコネクション内で複数のアクセスを行えないから。

そうではなくて、上に書いた "While the MySqlDataReader is in use, the associated MySqlConnection is busy serving the MySqlDataReader, and no other operations can be performed on the MySqlConnection" の通りだと思います。

質問者さんが質問に書いたエラーメッセージがそれを裏付けてます。

であるならばひとつのアプリケーション内で異なるコネクションを張ることが出来れば解決するように思えます。(根本的な問題の解決になるかは置いておきます) ひとつのアプリケーション内で、かつ C# で、複数のコネクションを張る方法があれば教えてください。

そうですね。MySQL のドキュメントの説明ではそれで解決しそうな気がします・・・が、今どのようなコードを書いているのですか？

今でも各 MySqlDataAdapter に異なる MySqlConnection をコネクションプールから取得しているようなコードだとすると、すでにそのようにしているが解決できてないということになりますけど。

③ それ以外に、同時にアクセスする方法があるようでしたら教えてください。

上に書いたように「同時」の意味にもよりますが、質問に「スレッドやタスクを利用して」と書いてあったということはマルチスレッドアプリで「同時」に処理して時間短縮することを期待しているのでしょうか？

でも、マルチスレッドアプリは実際には同時ではなくて、OS が高速でスレッドを切り替えながら実行していくということで、結局は質問に書いてあった、

直列に実行していると時間的にきついところがあります。

の「直列」と同じ（むしろ、スレッドの切り替えの分オーバーヘッドが増えて逆に遅くなる）と思うのですが。

アプリ側で何とかする手段があるとしても、リモートの DB サバ―へ tcp 接続しているとすると、そこは「直列」とならざるを得ませんけど？

【追記】

一つ思い出したことがあるので追記します。

Microsoft のドキュメントの「DbDataAdapter.Fill メソッド (DataSet)」の説明に以下の記述があります。

"指定したクエリが複数の結果を返す場合は、クエリを返す各列の結果セットが個別のテーブルに格納されます。2 番目以降の結果セットには、指定されたテーブル名に整数値を追加した名前が付けられます。たとえば、Table、Table1、Table2 のようになります。"

これは MySQL の Connector/NET の MySqlDataAdapter.Fill (DataSet) メソッドでも同じようです。

検証してみましたが、以下のコードのように複数の SELECT クエリ（以下の例では 3 つ）をセットすれば、DataSet に 3 つの DataTable を自動的に生成してくれます。

string connString = "接続文字列";
string queryString = "SELECT * FROM city; SELECT * FROM country; SELECT * FROM countrylanguage";
DataSet dataset = new DataSet();

using (MySqlConnection connection = new MySqlConnection(connString))
{
    MySqlDataAdapter adapter = new MySqlDataAdapter();
    adapter.SelectCommand = new MySqlCommand(queryString, connection);
    adapter.Fill(dataset);
}

city, country, countrylanguage は MySQL のサンプルデータベース world に含まれるものです。確認のため DataGridView に表示して見ましたが、以下の画像の通りちゃんと取得できているようです。

という訳で、質問の、

③ それ以外に、同時にアクセスする方法があるようでしたら教えてください。

については上の手段を検討してはいかがでしょう？

【追記２】

以下の記事に書いてあるように SqlDataReader を使って、それを Close しなくても複数の SELECT クエリの結果セットを取得することもできます。サンプルコードの reader.NextResult(); に注目してください。

接続文字列でのデータベース名の指定
http://surferonwww.info/BlogEngine/post/2014/05/30/initial-catalog-keyword-in-sqlclient-connection-string.aspx

自分は SQL Server の場合しか確認してませんが、興味がありましたら質問者さんの方で MySqlDataReader でも同じことができるかどうか確認してみてください。

上の【追記】で書いた MySqlDataAdapter.Fill (DataSet) メソッドで複数のクエリから複数の DataTable を取得する場合もそれを使っているはずなので、MySQL でも SQL Server と同じことができるのではないかと思います。

【追記】の方法【追記２】の方法ともラウンドトリップが 1 回で済むので、複数のクエリを投げて複数のラウンドトリップとならざるを得ない質問者さんの原案よりは早そうです。

後は、ホントに DataSet / DataTable を作る必要があるかどうかで、【追記】の方法にするか【追記２】の方法にするか選んではいかがですか？

Comments

[t.kusu]

遅くなりすみません。丁寧なご教示に感謝します。

①②は納得しました。
自分の実装は質問本文を更新して追記しました。そのまま載せられないので簡略化していますが、概要としてはご理解いただけると思います。
KOZ6.0 さんのお話によるとインスタンスを別にすれば別のコネクションを張れる可能性がありそうなのでこちらの検証は別途行いますが、お知らせいただいた情報によるとそれでも解決しないのかも知れませんね。

③について、追記の情報をありがとうございます。
複数のクエリをまとめて実行するなどは知識としては知っていましたが、このケースで利用することはまったく思いついていませんでした。なるほど。
DataReader での参照も、時間的に有利でもあるようなので(連休明けになってしまいますが)実験をしてみようと思います。

[退会済みユーザー]

上の回答で、「そうですね。MySQL のドキュメントの説明ではそれで解決しそうな気がします・・・が、今どのようなコードを書いているのですか？」と書きましたけど、それに対する答えをいただけませんか？

【追記】に書いたようなコードになっていれば、接続は毎回別のものになるので問題は出ないはずです。現状そうなっているにもかかわらず問題が出るということは、問題は DataAtapter が使う接続ではなく、別のところにありそうですけど、そこのところはどうなんですか？　別のところにあるとすると、見当違いのことを考えているということになってしまいますが・・・

[退会済みユーザー]

> 複数のクエリをまとめて実行するなどは知識としては知っていましたが、このケースで利用することはまったく思いついていませんでした。なるほど。

是非検討してみてください。【追記】または【追記２】のような形でクエリを渡せるのであれば、複数のクエリを投げて複数のラウンドトリップとならざるを得ない質問者さんの原案より、速度の面だけ考えてもより良いのは間違いないと思います。

[t.kusu]

重ねて遅くなりすみません。
先に２点お詫びを。
1) コメントの返信をいただいたことに気が付いていませんでした。無視した形になりすみません。
2) 追記したコードに誤りがあります。connection の using ブロックとと Parallel.For のブロックの前後が逆でした。
タイトルも間違っているんですよね。。いまさっき気が付いたのですが。

環境下で確認してみました。
・「現状のコード」について、上の通りに誤りがありました。
・複数の問い合わせクエリを同時に投げる方法について、効果を確認できました。
　ただ現状のソースコードに対して修正部分がそれなりにあるため、特に速度が問題になる個所についてまず対応することとしました。

現状で効果を確認できたこちらをベストアンサーとして設定させていただきます。