質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.11%

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,261

t.kusu

score 17

前提・実現したいこと

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 を掛けることによりこのメソッドを同時に実行しないようにしています。

ただ、今回同時アクセスを行うようにしたい箇所は多量の小さなデータの読み込みであり、直列に実行していると時間的にきついところがあります。
まとめて1回のアクセスで読み取れるようにしてもよいのですが、ここまで試した範囲では受け取った後のデータの整理に時間が掛かってしまい、(上記の) 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();
}
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • hihijiji

    2019/08/11 11:09

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

    キャンセル

  • t.kusu

    2019/08/13 15:45

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

    キャンセル

  • hihijiji

    2019/08/13 16:12

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

    キャンセル

回答 2

checkベストアンサー

0

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 に表示して見ましたが、以下の画像の通りちゃんと取得できているようです。

イメージ説明

という訳で、質問の、

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

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

【追記2】

以下の記事に書いてあるように 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 と同じことができるのではないかと思います。

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/08/13 16:02

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

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

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

    キャンセル

  • 2019/08/13 16:16

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

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

    キャンセル

  • 2019/08/13 16:23

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

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

    キャンセル

  • 2019/08/20 21:49

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

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

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

    キャンセル

0

複数の 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% 使い切ることはできません。
パラレル実行すると、待っている間に別のパケットを送れるのでメリットは充分あります。
ダウンローダーの中には複数のコネクションを張って、サーバーからパラレルにダウンロードし、高速化しているものがあるので使ってみると体感できるかと思います。
ディスクなんかもそうで、物理的にアームが動くからパラレル実行しても意味がないっていうのも誤った認識です。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/08/13 15:51

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

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

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

    キャンセル

  • 2019/08/13 16:21

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

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

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

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

    キャンセル

  • 2019/08/20 21:47

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

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

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.11%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる