MariaDBプログラミング必見！クエリタイムアウトの代替制御手法

2025-05-27

主な制限とタイムアウトの種類は以下の通りです。

クエリ実行時間に関するタイムアウト

最も直接的にクエリの実行時間を制限する設定です。

max_statement_time:
- これは、個々のSQLクエリ（特にSELECT文）が実行できる最大時間を秒単位で設定する変数です。
- デフォルトは0で、これはタイムアウトが無効であることを意味します。
- 設定方法としては、以下のレベルで設定できます。
  - グローバル: サーバー全体に適用されます。SET GLOBAL max_statement_time = N;
  - セッション: 現在のセッションにのみ適用されます。SET SESSION max_statement_time = N;
  - ユーザーごと: CREATE USER ... MAX_STATEMENT_TIME N; のように、特定のユーザーに対して設定できます。
  - クエリごと: SET STATEMENT max_statement_time = N FOR SELECT ...; のように、特定のクエリに対して設定できます。
- この時間を超えると、クエリは自動的に中断され、エラーが返されます。これにより、暴走したクエリがシステム全体のパフォーマンスに悪影響を与えるのを防ぎます。

接続に関するタイムアウト

クライアントとサーバー間の接続のライフサイクルに関連するタイムアウトです。

net_read_timeout / net_write_timeout:
- サーバーが接続からデータを受信したり、データを送信したりする際に、次のパケットが来るまで待機する時間（秒）です。ネットワークの問題などで通信が滞る場合に影響します。
interactive_timeout:
- 対話型（対話的なクライアントからの接続など）の接続がアイドル状態（何も操作を行っていない状態）でサーバーが待機する時間（秒）です。
- wait_timeoutと同様に、アイドル状態の接続をクリーンアップするのに役立ちます。通常、wait_timeoutよりも長く設定されることがあります。
wait_timeout:
- 非対話型（バッチ処理など）の接続がアイドル状態（何も操作を行っていない状態）でサーバーが待機する時間（秒）です。
- デフォルトは非常に長く（例：28800秒、8時間）、アイドル状態の接続が長時間残ってしまう可能性があります。
- 適切な値に設定することで、リソースの解放を促進し、"Too many connections"エラーを防ぐのに役立ちます。
connect_timeout:
- MariaDBサーバーがクライアントからの接続パケットを待機する時間（秒）です。
- デフォルトは10秒です。
- クライアントが接続を確立する際にエラーが発生する場合（"Bad handshake"など）、この値を増やすことで解決することがあります。

トランザクションに関するタイムアウト (MariaDB 10.3以降)

MariaDB 10.3以降では、アイドル状態のトランザクションをタイムアウトさせるためのより詳細な設定が追加されました。これにより、コミットまたはロールバックされずにロックを保持し続けるトランザクションによる問題を軽減できます。

idle_readonly_transaction_timeout:
- アイドル状態の読み取り専用トランザクションに対するタイムアウト（秒）です。
idle_write_transaction_timeout:
- アイドル状態の書き込みトランザクションに対するタイムアウト（秒）です。
idle_transaction_timeout:
- すべてのアイドル状態のトランザクションに対するタイムアウト（秒）です。

これらのトランザクションタイムアウトは、デフォルトでは0（無効）に設定されています。

max_allowed_packet:
- サーバーとクライアント間で送受信できる単一のパケットの最大サイズ（バイト）です。
- 大きなBLOBデータを扱ったり、非常に大きなSQL文を送信したりする場合に、この制限に達してエラーとなることがあります。
- 「MySQL server has gone away」といったエラーメッセージが出る場合、この設定が原因である可能性があります。

アプリケーションの堅牢性: アプリケーションがデータベースからの応答を永遠に待つことを防ぎ、タイムアウトにより適切なエラー処理を行う機会を提供します。
リソースの効率的な利用: 使用されていない接続やリソースを適切に解放し、より多くのクライアントが利用できるようにします。
安定性の向上: データベースサーバーが過負荷になり、応答しなくなる事態を防ぎます。
パフォーマンスの維持: 長時間実行されるクエリや多数のアイドル接続がシステムリソース（CPU、メモリ、ロックなど）を占有するのを防ぎ、データベース全体のパフォーマンス低下を防ぎます。

よくあるエラーメッセージ

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

トラブルシューティング:
- 原因の特定:
  - SHOW ENGINE INNODB STATUS;を実行し、LATEST DETECTED DEADLOCKセクションやTRANSACTIONSセクションを確認します。どのトランザクションがロックを保持し、どのトランザクションが待機しているか、またどのテーブルや行が影響を受けているかを特定します。
  - SHOW PROCESSLIST;で実行中のクエリを確認し、StateカラムにLockedやWaiting for table lockなどの表示がないか確認します。
- ロックの解除:
  - 問題のトランザクションを特定し、KILLコマンドで終了させます。ただし、これは慎重に行う必要があります。予期せぬデータ不整合を引き起こす可能性があります。
- クエリの最適化:
  - ロックの競合を減らすために、長時間ロックを保持するクエリを特定し、インデックスの追加、SQL文の書き換え、データ量削減などの最適化を行います。
  - トランザクションの粒度を小さくする（コミット頻度を増やす）ことも有効です。
- innodb_lock_wait_timeoutの調整:
  - 短すぎる場合は一時的に値を増やすことを検討します。ただし、根本的な解決策ではなく、問題解決のための時間稼ぎにしかならない場合が多いです。
発生状況: あるトランザクションが、別のトランザクションが保持しているロックを待機しているときに、その待機時間がinnodb_lock_wait_timeoutで設定された時間を超えた場合に発生します。これは、特にInnoDBストレージエンジンでトランザクションが利用されている場合によく見られます。
エラーの種類: ロック待機タイムアウト

ERROR 2013 (HY000): Lost connection to MySQL server during query

トラブルシューティング:
- max_allowed_packetの確認:
  - 送信または受信するデータがmax_allowed_packetで設定されたサイズを超えている場合、このエラーが発生することがあります。特に大量のデータをINSERTまたはSELECTする場合に疑われます。
  - SET GLOBAL max_allowed_packet = N; で値を増やします。設定ファイル（my.cnfなど）でも変更が必要です。
- wait_timeout / interactive_timeoutの確認:
  - クエリの実行中に接続がアイドル状態になり、これらのタイムアウト値を超えた場合に接続が切断されることがあります。
  - 長時間実行されるクエリがある場合、これらの値を増やすことを検討します。ただし、無闇に長くするとアイドル接続が増加し、リソースを圧迫する可能性があります。
- ネットワークの問題:
  - クライアントとサーバー間のネットワーク接続が不安定な場合、このエラーが発生します。ネットワーク機器の故障、ケーブルの断線、Wi-Fiの不安定さなどが原因である可能性があります。
  - ネットワーク接続の安定性を確認し、必要であればネットワーク管理者に相談します。
- サーバーリソースの不足:
  - サーバーのメモリ、CPU、ディスクI/Oなどが飽和状態になると、MariaDBが応答できなくなり、クライアントとの接続が切断されることがあります。
  - TOP、htop、iostat、vmstatなどのツールでサーバーのリソース使用状況を監視し、ボトルネックを特定します。
- クエリの最適化:
  - 非常に時間がかかるクエリ自体が問題である可能性があります。EXPLAINを使ってクエリの実行計画を分析し、インデックスの追加、JOINの最適化、サブクエリの見直しなどを検討します。
- MariaDBサーバーの再起動:
  - 稀に、MariaDBサーバープロセスが不安定になっている場合に、再起動することで問題が解決することがあります。
発生状況: クエリの実行中に、MariaDBサーバーとクライアントアプリケーション間のネットワーク接続が失われた場合に発生します。これは様々な原因で起こり得ます。
エラーの種類: クエリ実行中の接続喪失

ERROR 1206 (HY000): The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay.

トラブルシューティング:
- クエリの確認と最適化:
  - ほとんどの場合、このエラーはクエリが効率的ではないことを示唆しています。WHERE句の条件不足、不適切なJOIN条件、またはインデックスの欠落が原因である可能性があります。
  - EXPLAINを使用してクエリの実行計画を確認し、大規模なフルテーブルスキャンやネストされたループ結合が発生していないか確認します。
  - 適切なインデックスを追加し、クエリを再記述して結合される行数を減らします。
- max_join_sizeの調整（一時的または慎重に）:
  - クエリが意図的に大量のデータを処理する必要がある場合は、SET SESSION max_join_size = N;で値を増やしたり、SET SQL_BIG_SELECTS = 1;を設定したりすることで、一時的にこの制限を解除できます。
  - ただし、これは通常、根本的な解決策ではなく、より効率的なクエリを設計することが望ましいです。
発生状況: max_join_size変数の値を超える結合（またはスキャン）が行われる可能性のあるSELECTクエリを実行しようとしたときに発生します。これは、誤って大規模なテーブル結合を引き起こすことを防ぐための安全機能です。
エラーの種類: 巨大なSELECTクエリの制限

Query execution was interrupted (クライアント側で表示されることが多い)

トラブルシューティング:
- クエリの実行計画の分析と最適化:
  - EXPLAINを使用してクエリがなぜ時間がかかっているのかを特定します。インデックスの不足、不適切なテーブル設計、複雑すぎる結合、大規模なデータセットに対する集計などが原因である可能性があります。
  - 実行時間が長いクエリを特定し、インデックスの追加、SQL文の書き換え、データのフィルタリング強化などを試みます。
- max_statement_timeの調整:
  - クエリの最適化が困難な場合や、特定のクエリがどうしても長い時間を必要とする場合は、SET SESSION max_statement_time = N;でタイムアウト値を増やすことを検討します。
  - ただし、この値を無闇に大きくすると、他のクエリやシステム全体のパフォーマンスに悪影響を与える可能性があります。
発生状況: MariaDBのmax_statement_time設定を超えてクエリが実行された場合に、サーバー側で強制的に中断され、クライアントにこのエラーが返されます。
エラーの種類: max_statement_timeによる中断

pt-query-digestなどのツール:
- Percona Toolkitのpt-query-digestのようなツールは、スロークエリログを解析し、最も時間のかかるクエリや頻繁に実行されるクエリを特定するのに役立ちます。
MariaDBのバージョンアップ:
- 稀に、MariaDBの特定のバージョンにバグがあり、予期せぬタイムアウトや接続問題が発生することがあります。最新の安定版へのアップグレードを検討します。
アプリケーション側のタイムアウト設定:
- アプリケーションフレームワークやORマッパーによっては、データベース接続やクエリ実行に関する独自のタイムアウト設定を持っている場合があります。これらの設定がMariaDB側のタイムアウトよりも短いと、意図せず接続が切断されることがあります。MariaDB側の設定と整合性があるか確認します。
システムリソースの監視:
- サーバーのCPU使用率、メモリ使用量、ディスクI/O、ネットワークI/Oを監視します。これらのリソースが飽和している場合、クエリの遅延やタイムアウトの原因となります。
- top, htop, vmstat, iostat, netstatなどのLinuxコマンドや、Grafanaなどの監視ツールを活用します。
SHOW PROCESSLISTの定期的な監視:
- データベースに接続し、SHOW FULL PROCESSLIST;を定期的に実行することで、現在実行中のクエリとその状態、実行時間、ロックの状況などをリアルタイムで把握できます。
スロークエリログの活用:
- long_query_time以上の時間がかかったクエリを記録するスロークエリログを有効にすることで、パフォーマンスボトルネックとなっているクエリを特定できます。
- 設定ファイルでslow_query_log = 1とlong_query_time = 1（または適切な秒数）を設定し、ログファイルのパスを指定します。
エラーログの確認:
- MariaDBのエラーログ（通常はhostname.errのようなファイル名）は、発生した問題に関する重要な情報を含んでいます。タイムアウトや接続の問題が発生した正確な時刻と関連するメッセージがないか確認します。

事前準備

PythonでMariaDBに接続するには、mysql-connector-pythonをインストールする必要があります。

pip install mysql-connector-python

max_statement_time (クエリ実行時間制限)

max_statement_timeは、個々のSQLクエリの実行時間を制限するために使用されます。

セッションレベルでの設定

これは、現在の接続セッションに対してのみ適用されます。

import mysql.connector
import time

try:
    conn = mysql.connector.connect(
        host="localhost",
        user="your_user",
        password="your_password",
        database="your_database"
    )
    cursor = conn.cursor()

    # セッションのmax_statement_timeを2秒に設定
    # この設定は、このセッション内の後続のクエリに影響します
    cursor.execute("SET SESSION max_statement_time = 2;")
    print("max_statement_timeを2秒に設定しました。")

    # 1秒のスリープを含むクエリ（タイムアウトしないはず）
    print("1秒のスリープを含むクエリを実行します...")
    start_time = time.time()
    try:
        cursor.execute("SELECT SLEEP(1);")
        result = cursor.fetchone()
        print(f"クエリ成功: SLEEP(1) - 実行時間: {time.time() - start_time:.2f}秒")
    except mysql.connector.Error as err:
        print(f"クエリ失敗: SLEEP(1) - {err}")

    # 3秒のスリープを含むクエリ（タイムアウトするはず）
    print("3秒のスリープを含むクエリを実行します...")
    start_time = time.time()
    try:
        cursor.execute("SELECT SLEEP(3);")
        result = cursor.fetchone()
        print(f"クエリ成功: SLEEP(3) - 実行時間: {time.time() - start_time:.2f}秒")
    except mysql.connector.Error as err:
        # Expected error: ERROR 1205 (HY000): Query execution was interrupted
        print(f"クエリ失敗 (タイムアウト): SLEEP(3) - {err}")

except mysql.connector.Error as err:
    print(f"データベース接続エラー: {err}")
finally:
    if 'conn' in locals() and conn.is_connected():
        cursor.close()
        conn.close()
        print("MariaDB接続を閉じました。")

グローバルレベルでの設定（管理者権限が必要）

これは、MariaDBサーバー全体に影響を与えます。通常、アプリケーションコードではなく、データベース管理者によって設定されます。

-- MariaDBクライアントから実行
SET GLOBAL max_statement_time = 5; -- 全ての新しい接続に5秒のタイムアウトを設定
SELECT @@global.max_statement_time; -- 設定値を確認

クエリごとの設定

MariaDB 10.1.1以降では、SET STATEMENT構文を使って特定のクエリにのみタイムアウトを適用できます。

import mysql.connector
import time

try:
    conn = mysql.connector.connect(
        host="localhost",
        user="your_user",
        password="your_password",
        database="your_database"
    )
    cursor = conn.cursor()

    # 特定のクエリに5秒のタイムアウトを設定
    print("クエリごとのmax_statement_timeを適用します...")
    start_time = time.time()
    try:
        # SET STATEMENT構文を使用
        cursor.execute("SET STATEMENT max_statement_time = 5 FOR SELECT SLEEP(6);")
        result = cursor.fetchone()
        print(f"クエリ成功: SLEEP(6) with per-query timeout - 実行時間: {time.time() - start_time:.2f}秒")
    except mysql.connector.Error as err:
        # Expected error: ERROR 1205 (HY000): Query execution was interrupted
        print(f"クエリ失敗 (タイムアウト): SLEEP(6) with per-query timeout - {err}")

    # 通常のクエリ（セッションまたはグローバル設定に依存）
    print("通常のクエリを実行します...")
    start_time = time.time()
    try:
        cursor.execute("SELECT SLEEP(1);")
        result = cursor.fetchone()
        print(f"クエリ成功: SLEEP(1) without per-query timeout - 実行時間: {time.time() - start_time:.2f}秒")
    except mysql.connector.Error as err:
        print(f"クエリ失敗: SLEEP(1) without per-query timeout - {err}")

except mysql.connector.Error as err:
    print(f"データベース接続エラー: {err}")
finally:
    if 'conn' in locals() and conn.is_connected():
        cursor.close()
        conn.close()
        print("MariaDB接続を閉じました。")

connect_timeout (接続タイムアウト)

クライアントがMariaDBサーバーに接続を確立するまでの最大待機時間です。これは通常、接続文字列のオプションとして設定されます。

import mysql.connector

try:
    # 接続タイムアウトを5秒に設定
    # ホストが存在しない、またはMariaDBサーバーが起動していない場合にタイムアウトする
    conn = mysql.connector.connect(
        host="non_existent_host",  # 存在しないホスト名
        user="your_user",
        password="your_password",
        database="your_database",
        connection_timeout=5  # 接続タイムアウト（秒）
    )
    print("MariaDBに正常に接続しました。")

except mysql.connector.Error as err:
    # Expected error: mysql.connector.errors.OperationalError: 2003 (HY000): Can't connect to MySQL server on 'non_existent_host' (timed out)
    print(f"接続タイムアウトエラーが発生しました: {err}")
finally:
    if 'conn' in locals() and conn.is_connected():
        conn.close()
        print("MariaDB接続を閉じました。")

注意: connection_timeoutは、実際に接続が確立されるまでの時間を測定します。MariaDBサーバーが起動しているが応答が遅い場合などに役立ちます。

wait_timeout / interactive_timeout (アイドル接続タイムアウト)

これらの変数は、非対話型/対話型の接続がアイドル状態である場合に、MariaDBがその接続を閉じるまでの時間を定義します。アプリケーション側で長時間接続をプールする際に重要になります。

import mysql.connector
import time

try:
    conn = mysql.connector.connect(
        host="localhost",
        user="your_user",
        password="your_password",
        database="your_database"
    )
    cursor = conn.cursor()

    # 現在のwait_timeoutとinteractive_timeoutの値を確認
    cursor.execute("SHOW VARIABLES LIKE 'wait_timeout';")
    print(f"現在の wait_timeout: {cursor.fetchone()[1]}秒")
    cursor.execute("SHOW VARIABLES LIKE 'interactive_timeout';")
    print(f"現在の interactive_timeout: {cursor.fetchone()[1]}秒")

    # セッションのwait_timeoutを短く設定（例: 5秒）
    # 通常は本番環境でこれを短くしすぎるべきではありません
    cursor.execute("SET SESSION wait_timeout = 5;")
    print("セッションのwait_timeoutを5秒に設定しました。")

    print("5秒間待機します（この間に接続が切断されるはず）...")
    time.sleep(6) # タイムアウト値より長く待機

    # 切断された接続でクエリを試みる
    try:
        cursor.execute("SELECT 1;")
        print("クエリ成功: 接続は切断されませんでした。")
    except mysql.connector.Error as err:
        # Expected error: mysql.connector.errors.OperationalError: MySQL Connection not available.
        # または 2006 (HY000): MySQL server has gone away
        print(f"クエリ失敗 (接続切断): {err}")

except mysql.connector.Error as err:
    print(f"データベース接続エラー: {err}")
finally:
    if 'conn' in locals() and conn.is_connected():
        cursor.close()
        conn.close()
        print("MariaDB接続を閉じました。")

注意: アプリケーションが接続プールを使用している場合、wait_timeoutよりも頻繁に接続をテストするか、再接続ロジックを実装する必要があります。

トランザクションがロックを取得するのを待つ最大時間です。

このシナリオを再現するには、同時に実行される2つの異なるデータベース接続（またはPythonプロセス）が必要です。ここでは、同じスクリプト内で2つの接続を擬似的に扱いますが、実際のロックは異なるプロセスで行うのが一般的です。

import mysql.connector
import time

def setup_table(conn):
    cursor = conn.cursor()
    cursor.execute("DROP TABLE IF EXISTS test_locks;")
    cursor.execute("CREATE TABLE test_locks (id INT PRIMARY KEY, value VARCHAR(50));")
    cursor.execute("INSERT INTO test_locks (id, value) VALUES (1, 'initial');")
    conn.commit()
    cursor.close()

def transaction_1(conn):
    try:
        cursor1 = conn.cursor()
        cursor1.execute("SET SESSION autocommit = OFF;") # トランザクションを開始
        print("トランザクション1: id=1 の行をロックします...")
        cursor1.execute("UPDATE test_locks SET value = 'locked_by_t1' WHERE id = 1;")
        print("トランザクション1: 10秒間スリープします（ロックを保持）...")
        time.sleep(10) # 10秒間ロックを保持
        conn.commit()
        print("トランザクション1: コミットしました。")
    except mysql.connector.Error as err:
        print(f"トランザクション1エラー: {err}")
        conn.rollback()
    finally:
        if 'cursor1' in locals():
            cursor1.close()

def transaction_2(conn):
    try:
        cursor2 = conn.connector
        # セッションのinnodb_lock_wait_timeoutを5秒に設定
        cursor2.execute("SET SESSION innodb_lock_wait_timeout = 5;")
        print("トランザクション2: innodb_lock_wait_timeoutを5秒に設定しました。")

        cursor2.execute("SET SESSION autocommit = OFF;") # トランザクションを開始
        print("トランザクション2: id=1 の行を更新しようとしています（ロックを待機）...")
        start_time = time.time()
        cursor2.execute("UPDATE test_locks SET value = 'attempt_by_t2' WHERE id = 1;")
        conn.commit()
        print(f"トランザクション2: コミットしました。実行時間: {time.time() - start_time:.2f}秒")
    except mysql.connector.Error as err:
        # Expected error: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
        print(f"トランザクション2エラー (ロックタイムアウト): {err}")
        conn.rollback()
    finally:
        if 'cursor2' in locals():
            cursor2.close()

# メイン処理
if __name__ == "__main__":
    conn1 = None
    conn2 = None
    try:
        # 接続1（ロックを保持する側）
        conn1 = mysql.connector.connect(
            host="localhost",
            user="your_user",
            password="your_password",
            database="your_database"
        )
        setup_table(conn1) # テーブルの準備

        # 接続2（ロックを待機する側）
        conn2 = mysql.connector.connect(
            host="localhost",
            user="your_user",
            password="your_password",
            database="your_database"
        )

        # 2つのトランザクションを同時に実行するためにスレッドを使用することも可能ですが、
        # ここでは単純化のために順次実行し、時間差でロック競合を再現します。
        # 実際の運用では別プロセスやスレッドで実行します。

        # トランザクション1を別スレッドで開始することも可能ですが、
        # 説明のために簡単な方法でタイムアウトを再現します
        print("\n--- ロック競合のシミュレーション開始 ---")

        # トランザクション1でロックを取得し、保持
        cursor_t1 = conn1.cursor()
        cursor_t1.execute("SET autocommit = OFF;")
        cursor_t1.execute("UPDATE test_locks SET value = 'locked_by_t1' WHERE id = 1;")
        print("トランザクション1がロックを取得しました。")

        # 短い遅延を置いて、トランザクション2がロックを待機
        time.sleep(1)

        cursor_t2 = conn2.cursor()
        cursor_t2.execute("SET SESSION innodb_lock_wait_timeout = 5;") # ロック待機タイムアウトを5秒に設定
        cursor_t2.execute("SET autocommit = OFF;")
        print("トランザクション2がロックを待機しています...")
        start_time_t2 = time.time()
        try:
            cursor_t2.execute("UPDATE test_locks SET value = 'attempt_by_t2' WHERE id = 1;")
            conn2.commit()
            print(f"トランザクション2がロックを取得しコミットしました。実行時間: {time.time() - start_time_t2:.2f}秒")
        except mysql.connector.Error as err:
            print(f"トランザクション2エラー (ロックタイムアウト期待): {err}")
            conn2.rollback()
        finally:
            cursor_t2.close()

        # トランザクション1をコミット（ロックを解放）
        conn1.commit()
        print("トランザクション1をコミットし、ロックを解放しました。")

        print("\n--- ロック競合のシミュレーション終了 ---")

    except mysql.connector.Error as err:
        print(f"データベース接続エラー: {err}")
    finally:
        if conn1 and conn1.is_connected():
            conn1.close()
            print("MariaDB接続1を閉じました。")
        if conn2 and conn2.is_connected():
            conn2.close()
            print("MariaDB接続2を閉じました。")

実行結果の解説: 上記のinnodb_lock_wait_timeoutの例は、シングルスレッドでタイムアウトを擬似的に再現しています。実際にLock wait timeout exceededエラーを確実に発生させるには、以下のようにします。

ターミナルAを開き、MariaDBクライアントで接続し、トランザクションを開始してロックを取得します。

SET autocommit = OFF;
UPDATE test_locks SET value = 'locked_by_t1' WHERE id = 1;
-- ここでコミットせずにロックを保持

ターミナルBを開き、MariaDBクライアントで接続し、ロック待機タイムアウトを設定し、同じ行を更新しようとします。
```
SET SESSION innodb_lock_wait_timeout = 5; -- 5秒のタイムアウトを設定
SET autocommit = OFF;
UPDATE test_locks SET value = 'attempt_by_t2' WHERE id = 1;
```
ターミナルBで上記のUPDATEを実行すると、5秒後にERROR 1205 (HY000): Lock wait timeout exceededというエラーが表示されます。
ターミナルAに戻り、COMMIT;またはROLLBACK;を実行してロックを解除します。

Pythonスクリプトでこれを行うには、スレッドやプロセス間通信を使って2つの接続を同時に動かす必要があります。

これらの例は、MariaDBのさまざまなタイムアウト設定がアプリケーションの動作にどのように影響するかを示しています。適切なタイムアウト設定は、アプリケーションの堅牢性とデータベースの安定性を確保するために不可欠です。

innodb_lock_wait_timeout: ロック競合によるトランザクションの無期限待機を防ぐ。
wait_timeout / interactive_timeout: アイドル状態の接続がリソースを不必要に占有するのを防ぐ。
connect_timeout: 接続確立時の遅延や失敗を検知する。
max_statement_time: 長時間実行されるクエリを強制終了し、リソースの枯渇を防ぐ。

アプリケーションレベルでのタイムアウト設定

MariaDBのサーバーサイドのタイムアウト設定とは別に、アプリケーションコード自体でクエリやデータベース操作にタイムアウトを設定することができます。

スレッドまたはプロセスによるタイムアウト

Pythonでの例 (threading と _thread.interrupt_main / signal):

import mysql.connector
import threading
import time
import _thread # for interrupting the main thread

def execute_query_in_thread(conn_params, query, timeout_seconds, result_container):
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**conn_params)
        cursor = conn.cursor()
        start_time = time.time()
        cursor.execute(query)
        result = cursor.fetchall() # または fetchone() など
        execution_time = time.time() - start_time
        result_container['status'] = 'success'
        result_container['data'] = result
        result_container['time'] = execution_time
    except mysql.connector.Error as err:
        result_container['status'] = 'error'
        result_container['message'] = str(err)
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

def run_query_with_timeout(conn_params, query, timeout_seconds):
    result_container = {'status': 'pending', 'data': None, 'message': None, 'time': 0}
    query_thread = threading.Thread(target=execute_query_in_thread, args=(conn_params, query, timeout_seconds, result_container))
    query_thread.start()
    query_thread.join(timeout=timeout_seconds) # スレッドがタイムアウト時間内に完了するのを待つ

    if query_thread.is_alive():
        print(f"警告: クエリがタイムアウトしました ({timeout_seconds}秒)。スレッドを強制終了します。")
        # メインスレッドを中断して、スレッドを強制終了するトリガーとする（推奨されない方法、複雑になる）
        # または、データベース接続を直接閉じる（MySQL Connectorの機能に依存）
        # 最もクリーンな方法は、MariaDBサーバー側で max_statement_time を設定すること

        # ここでは、タイムアウトした場合はエラーとして扱う
        result_container['status'] = 'timeout'
        result_container['message'] = f"クエリ実行が{timeout_seconds}秒でタイムアウトしました。"
        # Note: スレッド自体はバックグラウンドで実行され続ける可能性があるため、
        # サーバー側で max_statement_time も設定することが重要です。
        # または、`mysql.connector`に直接的なキャンセル機能があればそれを使用します。
        # 通常、クライアント側からのキャンセルは「KILL QUERY」コマンドを別の接続で発行する形になります。
    return result_container

# データベース接続パラメータ
db_config = {
    "host": "localhost",
    "user": "your_user",
    "password": "your_password",
    "database": "your_database"
}

# テスト用のテーブル作成（初回のみ）
try:
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor()
    cursor.execute("DROP TABLE IF EXISTS large_data;")
    cursor.execute("CREATE TABLE large_data (id INT PRIMARY KEY AUTO_INCREMENT, value VARCHAR(255));")
    for i in range(1000):
        cursor.execute(f"INSERT INTO large_data (value) VALUES ('data_{i}');")
    conn.commit()
    cursor.close()
    conn.close()
    print("テストテーブル 'large_data' を作成しました。")
except mysql.connector.Error as err:
    print(f"テーブル作成エラー: {err}")


# 遅いクエリの例
slow_query = "SELECT COUNT(*) FROM information_schema.tables a, information_schema.tables b, information_schema.tables c;"
# または、より現実的な例として、大量のデータに対する複雑な結合や集計
# slow_query = "SELECT * FROM large_data WHERE id > 0 ORDER BY RAND() LIMIT 100000;" # RAND()は遅い

# 意図的に遅延させるクエリ
deliberate_slow_query = "SELECT SLEEP(7);" # MariaDBのSLEEP関数を使用

print("\n--- タイムアウトしないクエリ ---")
result = run_query_with_timeout(db_config, "SELECT 1;", 5)
print(f"結果: {result['status']}, データ: {result.get('data')}, 実行時間: {result['time']:.2f}秒, メッセージ: {result.get('message')}")

print("\n--- タイムアウトするクエリ ---")
result = run_query_with_timeout(db_config, deliberate_slow_query, 5) # 5秒でタイムアウトさせる
print(f"結果: {result['status']}, データ: {result.get('data')}, 実行時間: {result['time']:.2f}秒, メッセージ: {result.get('message')}")

print("\n--- タイムアウトしないはずの遅いクエリ ---")
result = run_query_with_timeout(db_config, deliberate_slow_query, 10) # 10秒でタイムアウトさせない
print(f"結果: {result['status']}, データ: {result.get('data')}, 実行時間: {result['time']:.2f}秒, メッセージ: {result.get('message')}")

解説: この例では、threadingモジュールを使ってクエリ実行を別スレッドで行い、join(timeout=...)でタイムアウトを監視しています。スレッドがタイムアウトした場合、is_alive()で検出できますが、Pythonのスレッドを外部から安全に強制終了することは一般的に困難です。そのため、タイムアウトを検出した後は、サーバー側で設定されたmax_statement_timeに頼るか、別の管理接続からKILL QUERYを発行するなどの追加の対応が必要です。

デメリット:
- 実装が複雑になる。特に、安全なスレッド/プロセス終了とリソースクリーンアップには注意が必要。
- データベース接続が切断されずにアイドル状態になる場合がある（サーバー側のwait_timeoutなどで最終的に切断される）。
- データベースサーバー側でクエリが実際に停止するわけではないため、リソース消費が続く可能性がある。
メリット:
- MariaDBサーバーのバージョンや設定に依存しない、純粋なアプリケーションレベルの制御。
- データベース接続の切断ではなく、特定のクエリ実行を中断できる。
- タイムアウト時にカスタムのエラーハンドリングや再試行ロジックを実装しやすい。
概念: クエリを実行する処理を別のスレッドやプロセスで実行し、親プロセス（メインスレッド）からその子スレッド/プロセスに実行時間の上限を設定します。指定時間内に完了しない場合は、子スレッド/プロセスを強制終了します。

ネットワークソケットレベルでのタイムアウト

多くのデータベースクライアントライブラリは、基盤となるネットワークソケット操作（読み取り/書き込み）に対するタイムアウトを設定するオプションを提供しています。

Python (mysql.connector)での例: mysql.connector.connect()にはread_timeoutとwrite_timeoutの引数がありますが、これらの実装はドライバによって異なります。通常、これらはソケットの読み書きタイムアウトであり、クエリ全体のタイムアウトではありません。

import mysql.connector
import time

try:
    conn = mysql.connector.connect(
        host="localhost",
        user="your_user",
        password="your_password",
        database="your_database",
        # read_timeout=5,  # サーバーからのデータ読み込みタイムアウト（秒）
        # write_timeout=5  # サーバーへのデータ書き込みタイムアウト（秒）
        # connect_timeout=5 # これは接続確立時のみ
    )
    cursor = conn.cursor()

    # MariaDB サーバー側で max_statement_time を設定していない場合、
    # アプリケーション側の read_timeout が単一のソケット操作のブロックを検出する可能性があります。
    # ただし、これがクエリ全体のタイムアウトを意味するわけではありません。

    # 遅いクエリの例
    print("SLEEP(7)クエリを実行します（read_timeoutに依存する可能性がある）...")
    start_time = time.time()
    try:
        # ここで read_timeout を設定しても、SELECT SLEEP(7) の場合、
        # データベースサーバーが計算を終えて結果を返すまで待つため、
        # 単純にソケットの読み込みがブロックされるわけではないことに注意。
        # したがって、このtimeoutは長時間のクエリには直接適用されないことが多い。
        cursor.execute("SELECT SLEEP(7);")
        result = cursor.fetchone()
        print(f"クエリ成功: SLEEP(7) - 実行時間: {time.time() - start_time:.2f}秒")
    except mysql.connector.Error as err:
        print(f"クエリ失敗: SLEEP(7) - {err}")

except mysql.connector.Error as err:
    print(f"データベース接続エラー: {err}")
finally:
    if 'conn' in locals() and conn.is_connected():
        cursor.close()
        conn.close()
        print("MariaDB接続を閉じました。")

デメリット: クエリ全体の実行時間ではなく、単一のネットワーク操作のタイムアウトであるため、非常に複雑なクエリの場合、複数のネットワーク操作が行われるため、期待通りのタイムアウトにならないことがある。
メリット: 低レベルでのタイムアウト制御が可能。ネットワークの問題によるハングアップを防ぐのに有効。
概念: クライアントがサーバーからの応答を待つ時間、またはデータを送信する時間を制限します。

コネクションプーリングと健全性チェック

アイドル接続のタイムアウト（wait_timeoutやinteractive_timeout）による問題を防ぐため、アプリケーションレベルでコネクションプーリングを適切に管理することが重要です。

メリット:
- wait_timeoutによる「MySQL server has gone away」エラーを回避できる。
- 接続の確立と切断のオーバーヘッドを削減し、パフォーマンスを向上させる。
概念: データベース接続を再利用することで、接続確立のオーバーヘッドを減らします。プール内の接続が古くなったり、サーバーによって切断されたりしていないか定期的にチェック（pingなど）し、必要に応じて新しい接続に置き換えます。

クエリ最適化とインデックス戦略

これは直接的なタイムアウトの代替策ではありませんが、タイムアウトエラーの根本原因を排除する上で最も重要です。

例（SQLレベルでの最適化）:

-- 遅いクエリの例
SELECT * FROM large_table WHERE some_column LIKE '%pattern%'; -- インデックスが効きにくい

-- 改善策（全文検索インデックスや、前方一致検索に絞るなど）
SELECT * FROM large_table WHERE some_column LIKE 'pattern%'; -- 前方一致ならインデックスが効く
-- または、全文検索機能（MariaDBのFTインデックス）を使用

-- 遅い結合の例
SELECT a.* FROM table_a a JOIN table_b b ON a.id = b.id WHERE b.status = 'active';
-- table_a.id と table_b.id にインデックスがあることを確認

手法:
- インデックスの追加: WHERE句、JOIN条件、ORDER BY句で使われるカラムに適切なインデックスを作成します。
- EXPLAINによるクエリ分析: EXPLAINキーワードを使ってクエリの実行計画を分析し、フルテーブルスキャンや非効率な結合がないかを確認します。
- SQLの書き換え: 不要な結合を削除する、サブクエリを最適化する、SELECT *を避ける、LIMIT句を適切に使うなど。
- データ構造の最適化: テーブル設計の見直し（正規化/非正規化）、適切なデータ型の選択。
- バッチ処理: 大量のデータ操作は、小さなチャンクに分割して処理する。
概念: クエリの実行時間を短縮することで、タイムアウトしにくくします。

監視とアラート

問題の早期発見は、タイムアウトによる被害を最小限に抑える上で不可欠です。

ツール:
- Prometheus + Grafana
- Zabbix
- Datadog, New RelicなどのAPMツール
- MariaDBのスロークエリログ（long_query_time設定）とSHOW PROCESSLISTの定期的なポーリング
概念: MariaDBサーバーのパフォーマンスメトリクス（CPU使用率、メモリ、ディスクI/O、アクティブな接続数、スロークエリ数など）を継続的に監視し、異常を検知した際にアラートを発します。

非常に時間のかかるクエリや処理は、同期的に実行せず、キューシステムを通じて非同期で実行することを検討します。

デメリット:
- システムアーキテクチャが複雑になる。
- 最終的な結果の取得が非同期になるため、アプリケーションの設計に影響がある。
メリット:
- ユーザーインタフェース（Webアプリケーションなど）がブロックされない。
- フロントエンドのタイムアウトと直接関連しない。
- MariaDBサーバーへの負荷を分散し、ピーク時のパフォーマンス問題を緩和できる。
概念:
1. アプリケーションが長時間かかる処理のリクエストを受け付ける。
2. そのリクエストをメッセージキュー（RabbitMQ, Kafka, Redis Queueなど）に入れる。
3. 別のワーカープロセスがキューからリクエストを取り出し、バックグラウンドでMariaDBクエリを実行する。
4. 結果は、データベース、ファイル、または別のキューを通じてアプリケーションに通知される。

MariaDBの「クエリの制限とタイムアウト」は、サーバーサイドで設定するのが最も直接的で効果的です。特にmax_statement_timeは、アプリケーション側での複雑なタイムアウトロジックの実装を大幅に簡素化できます。しかし、上記で説明した代替方法は、以下のような場合に役立ちます。

根本原因の解決: クエリ最適化は、タイムアウトの発生自体を減らすための最も重要なアプローチです。
リソース管理: コネクションプーリングや非同期処理は、データベースリソースをより効率的に利用し、システムの全体的なスケーラビリティを向上させます。
アプリケーションの堅牢性向上: クライアント側でのタイムアウト設定は、サーバー側の予期せぬ挙動やネットワークの問題からアプリケーションを保護します。