MENU

SQLのタイムアウト設定と管理方法：接続・クエリ・ロック待ち・トランザクションの違い

SQL

SQLのタイムアウトは、すべてが「トランザクション全体の制限時間」ではありません。接続の確立、SQL文の実行、ロック待ち、アイドル状態、トランザクション全体では、設定もタイムアウト後の状態も異なります。混同すると、設定したのに処理が止まらなかったり、失敗したと思って再実行して二重更新したりする原因になります。

本記事ではSQL Server、MySQL 8.4のInnoDB、PostgreSQL、およびADO.NET・JDBCを例に、制限対象と単位、適用範囲、失敗時の後始末を整理します。PostgreSQLのtransaction_timeoutは17以降の機能として分けて説明します。数値は説明用であり、本番向けの推奨値ではありません。

目次

タイムアウトの種類を先に区別する

  • 接続タイムアウト：接続を確立するまでの待ち時間です。すでに接続した後のSQLやトランザクションを制限しません。接続プールからの貸出待ちやDNS、ソケット接続の扱いも、ドライバー・プールごとに確認します。
  • 文・コマンドタイムアウト：1回の文やコマンドの実行待ちを制限します。複数の文の合計時間や、文の間にアプリケーションが処理する時間を一括で制限するとは限りません。結果取得までの扱いも製品によって異なります。
  • ロック待ちタイムアウト：競合するロックを取得できずに待つ時間を制限します。CPUを使い続けるクエリなど、ロックを待たない長時間処理の上限にはなりません。
  • アイドルタイムアウト：クライアントからの次の操作を待っている接続などを対象にします。実行中のSQLの制限とは別です。トランザクション内のアイドルだけを対象にする設定もあります。
  • トランザクションタイムアウト：対応するDBやトランザクション管理機構が、トランザクションの継続時間を制限するものです。SQLに共通の万能な設定はありません。
  • 通信タイムアウト：ソケットの読み取りなどの待ちを制限する別の層です。クエリの取消しやトランザクションのロールバックが完了した証明にはなりません。

タイムアウトは待機を有限にする仕組みであり、デッドロックを予防する仕組みそのものではありません。デッドロック検出・被害トランザクションの終了と、ロック待ち時間の超過を区別します。ロック取得順序の統一や短いトランザクション設計は、別途必要です。

設定前に決めること

  1. どの操作を、どの層で、何秒または何ミリ秒以内に終えたいかを決めます。各文が10秒以内でも、複数文とアプリケーション処理を合わせた全体が10秒以内になるわけではありません。
  2. 平均だけでなくp95・p99の処理時間、ピーク時のロック待ち、呼出元の期限を測ります。接続、実行、取消し・ロールバック、応答返却に必要な時間を全体の予算に含めます。
  3. まず検証専用の接続またはトランザクションに限定します。セッション設定は接続の再利用で次の処理へ残り得るため、元の値への復元とプールの初期化・破棄方針を確認します。
  4. 正常終了、文の失敗、ロールバック失敗、接続切断、COMMIT応答の喪失を分けてテストします。タイムアウト設定の確認だけでなく、失敗後に未完了のトランザクションやロックが残っていないかも確認します。

以下のSQLは、検証専用の新しい接続で使う説明例です。既存の業務トランザクションの途中へ貼り付けないでください。サーバー全体の設定変更、サービス再起動、意図的なロック競合や長時間待機は含めていません。SELECT 1は設定と制御フローの例であり、タイムアウトの発生を再現する負荷試験ではありません。

SQL Server：ロック待ちとクライアントの待機時間

SET LOCK_TIMEOUTはロック待ちをミリ秒で制限する

SET LOCK_TIMEOUTはSQL Server固有の設定で、SQL共通の構文ではありません。3000はロック待ち3秒、0は待たずにエラー、-1は無期限です。変更は接続に残るため、例では元の値を保存して復元します。ロック待ちでエラー1222になっても、含まれるトランザクション全体が自動的にロールバックされるとは限りません。SET LOCK_TIMEOUTの仕様、ロックタイムアウト時のエラー処理。

-- SQL Server。既存トランザクションのない検証専用接続で実行する例。
DECLARE @previous_timeout int = @@LOCK_TIMEOUT;
SET LOCK_TIMEOUT 3000;

BEGIN TRY
    BEGIN TRANSACTION;
    SELECT 1 AS sample_value;
    COMMIT TRANSACTION;
    SET LOCK_TIMEOUT @previous_timeout;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;
    SET LOCK_TIMEOUT @previous_timeout;
    THROW;
END CATCH;

このTRY...CATCHだけで、クライアントからの取消しや接続切断まで処理できるわけではありません。SQL Serverのattentionなどは捕捉対象外です。アプリケーション側でもトランザクションを管理し、例外時のロールバックと接続の破棄・再利用可否を判断します。復元やロールバック自体に失敗した接続を、そのまま次の業務処理に使わないでください。TRY…CATCHの制約。

ADO.NET：ConnectTimeoutとCommandTimeoutを分ける

SqlConnection.ConnectionTimeoutは読み取り専用です。接続を開く前に接続文字列のConnect Timeout、またはSqlConnectionStringBuilder.ConnectTimeoutを設定します。SqlCommand.CommandTimeoutは別のプロパティで、単位はいずれも秒です。次のC#例はMicrosoft.Data.SqlClientを使い、既存の設定から渡された接続文字列を利用します。認証情報をソースへ埋め込んだり、TLS検証を無効化したりしません。

using System;
using Microsoft.Data.SqlClient;

public static class SqlTimeoutExample
{
    public static int ReadOne(string configuredConnectionString)
    {
        var builder = new SqlConnectionStringBuilder(configuredConnectionString)
        {
            ConnectTimeout = 5
        };

        using (var connection = new SqlConnection(builder.ConnectionString))
        {
            connection.Open();
            using (var command = new SqlCommand("SELECT 1;", connection))
            {
                command.CommandTimeout = 10;
                return Convert.ToInt32(command.ExecuteScalar());
            }
        }
    }
}

この例は単一の読み取りコマンドであり、複数文のトランザクション全体に10秒の期限を設けるものではありません。CommandTimeoutにはコマンド実行・結果処理中のネットワーク読み取りに関する意味があり、利用者コードの処理時間まで含む単純なストップウォッチではありません。明示的トランザクションでは各コマンドに同じSqlTransactionを関連付け、失敗時にRollback()を試み、その例外も保持します。ConnectionTimeout、ConnectTimeout、CommandTimeout、Rollback。

remote query timeoutは受信クエリの上限ではない

SQL Serverのremote query timeoutは、データベースエンジンから外部へ開始するリモート操作の待ち時間です。アプリケーションから受信するクエリや、通常のローカルトランザクションの総時間には適用されません。この問題の対処としてサーバー設定を変更するのではなく、まずクライアントのコマンド設定、実行計画、ブロッキングを確認します。remote query timeoutの適用範囲。

MySQL：InnoDBのロック待ち、SELECT、アイドルを分ける

innodb_lock_wait_timeoutはInnoDBの行ロック待ち

innodb_lock_wait_timeoutは秒単位で、InnoDBの行ロック待ちを対象にします。トランザクション全体の所要時間、すべてのテーブルロック、メタデータロックをまとめて制限する設定ではありません。既定の設定では、ロック待ちタイムアウトで取り消されるのは失敗した文で、前の文の変更を含むトランザクション全体ではありません。業務処理を中断するならアプリケーションで明示的に全体をロールバックします。デッドロック時の扱いとは区別してください。InnoDBの設定仕様、InnoDBのエラー処理。

-- MySQL 8.4。検証専用接続のセッション設定だけを変更する例。
SET @previous_lock_wait_timeout = @@SESSION.innodb_lock_wait_timeout;
SET SESSION innodb_lock_wait_timeout = 3;
SELECT @@SESSION.innodb_lock_wait_timeout AS lock_wait_seconds;
-- この例は行ロックを待たない。動作確認後に元の値を戻す。
SET SESSION innodb_lock_wait_timeout = @previous_lock_wait_timeout;

この短い例には業務SQLやトランザクションを含めていません。実処理に組み込む場合は、途中でエラーが起きても後始末で元の値を戻し、復元できなければ接続を破棄します。COMMITやROLLBACKだけで、このセッション設定が元に戻ると考えないでください。セッション既定値を持つ変数のグローバル値を変えても、既存セッションの値は自動的には変わりません。SETのGLOBAL・SESSIONの意味。

max_execution_timeは読み取り専用SELECT向け

MySQL 8.4のmax_execution_timeはミリ秒単位です。通常の読み取り専用SELECTが対象で、INSERT・UPDATE・DELETEやトランザクション全体の期限ではありません。データを変更するストアドファンクションを呼ぶSELECTや、ストアドプログラム内のSELECTにも制約があります。単一の文に限定するなら、次のオプティマイザヒントを使う形で説明できます。

-- MySQL 8.4。読み取り専用の1文だけに1000ミリ秒の上限を指定。
SELECT /*+ MAX_EXECUTION_TIME(1000) */ 1 AS sample_value;

このSELECTは通常すぐ終わるため、タイムアウト発生の検証にはなりません。セッション値を使う場合にも対象と単位は同じで、0は無制限です。max_execution_timeの対象、MAX_EXECUTION_TIMEヒント。

wait_timeoutは接続確立の制限ではない

wait_timeoutは、非対話型の接続で次の活動がないまま待てる秒数です。実行中のSQLや、サーバーへの接続確立を30秒に制限する用途には使いません。対話型接続ではinteractive_timeoutとの関係もあります。短くするとプール内の接続がサーバー側で切断されることがあるため、接続検証や再接続方針との整合が必要です。ここでは値を変更しません。wait_timeoutの仕様。

Connector/Jのソケット接続はconnectTimeout、ソケット操作はsocketTimeoutで、どちらもミリ秒単位です。後述のJDBC標準APIの秒単位とは異なります。接続文字列・データソース・プールのどこで適用されるかを利用中のバージョンで確認してください。Connector/Jのネットワーク設定。

PostgreSQL：文単位とトランザクション単位を分ける

SET LOCALで現在のトランザクションに設定を限定する

statement_timeoutは各文の処理時間、lock_timeoutは各ロック取得の待ち時間、idle_in_transaction_session_timeoutはトランザクションを開いたまま次の問い合わせを待つ時間を対象にします。最後の設定は時間超過でセッションを終了します。SET LOCALで設定が有効な範囲をトランザクションに限定しても、statement_timeoutがトランザクション全体の合計時間制限に変わるわけではありません。

-- PostgreSQL。既存トランザクションのない検証専用接続での正常系。
BEGIN;
SET LOCAL lock_timeout = '3s';
SET LOCAL statement_timeout = '10s';
SET LOCAL idle_in_transaction_session_timeout = '15s';
SELECT 1 AS sample_value;
COMMIT;

SQLエラーになった場合は正常系の残りを続けず、接続が生きていればROLLBACKしてから次の処理へ進みます。セッション自体が終了した場合は、その接続での後始末はできません。SET LOCALの値はコミット・ロールバックで終了し、その後はセッションの元の設定に戻ります。上の設定にサービス再起動は不要です。トランザクション外のSET LOCALは有効な設定方法ではありません。各タイムアウトの仕様、SET LOCALの有効期間。

ここで挙げたPostgreSQLのタイムアウトは、単位を省略するとミリ秒、0なら無効です。例のように3sなど単位を明記すると、秒とミリ秒の取り違えを防げます。

lock_timeoutを有効なstatement_timeout以上にすると、通常は文の期限が先に来るため、目的に合わせて値を分けます。全セッションへ影響する設定ファイルの一律変更を、単一処理の対策として始めないでください。

PostgreSQL 17以降：transaction_timeout

transaction_timeoutはPostgreSQL 17で追加されました。明示的・暗黙的トランザクションの継続時間を制限し、超過時は文だけでなくセッションを終了します。16以前にそのまま設定すると使えません。まずバージョンと対応状況を読み取りで確認します。PostgreSQL 17のリリースノート。

SHOW server_version;
SELECT current_setting('transaction_timeout', true) AS transaction_timeout;
-- 未対応のバージョンでは、上のcurrent_settingはNULLを返す。
-- PostgreSQL 17以降だけの例。16以前では実行しない。
BEGIN;
SET LOCAL transaction_timeout = '30s';
SET LOCAL statement_timeout = '10s';
SET LOCAL lock_timeout = '3s';
SELECT 1 AS sample_value;
COMMIT;

これはトランザクションの継続時間の上限です。ただし、設定を途中で変更することを、すでに経過した処理も含むアプリケーション全体の厳密な期限管理の代わりにしてはいけません。処理開始前の接続設定やアプリケーション側の期限設計も検討します。有効なtransaction_timeoutがstatement_timeoutまたはidle_in_transaction_session_timeout以下なら、長い側のタイムアウトは無視されます。2相コミットのprepared transactionは対象外です。transaction_timeoutの適用範囲と相互作用。

未対応の設定をエラーにせず確認する第2引数の意味は、current_settingの仕様を参照してください。

JDBC：接続前の設定と、明示的なコミット・ロールバック

接続確立の待ち時間はDataSource.setLoginTimeoutなどをgetConnectionより前に設定します。単位は秒です。DriverManager.setLoginTimeoutを使う場合も接続前ですが、DriverManagerに対する共通設定になるため、ここでは専用のDataSourceを例にします。共有プールでは要求ごとに設定を上書きせず、初期化時の設定とプールの貸出待ち時間を別々に管理します。DataSourceの接続タイムアウト、DriverManagerの仕様。

次の例はMySQLまたはPostgreSQL用に接続先・認証を設定済みの、プールを使わない検証専用DataSourceを受け取ります。ベンダーのJDBCドライバーは別途必要です。setAutoCommit(false)、commit()、rollback()を使い、SQL文字列にBEGIN・コメント・COMMITを詰め込みません。読み取りだけの例ですが、更新処理にも必要な失敗時の分岐を示しています。

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;

public final class JdbcTimeoutExample {
    private JdbcTimeoutExample() { }

    public static int readOne(DataSource dedicatedSource) throws SQLException {
        // 非プールの検証専用DataSource。共有プールは初期化時に設定する。
        dedicatedSource.setLoginTimeout(5);
        boolean commitAttempted = false;
        boolean commitConfirmed = false;

        try (Connection connection = dedicatedSource.getConnection()) {
            connection.setAutoCommit(false);
            try {
                int value;
                try (PreparedStatement statement =
                         connection.prepareStatement("SELECT 1")) {
                    statement.setQueryTimeout(10); // 秒。文の待機時間。
                    try (ResultSet rows = statement.executeQuery()) {
                        if (!rows.next()) {
                            throw new SQLException("Expected one row");
                        }
                        value = rows.getInt(1);
                    }
                }
                commitAttempted = true;
                connection.commit();
                commitConfirmed = true;
                return value;
            } catch (SQLException | RuntimeException failure) {
                try {
                    connection.rollback();
                } catch (SQLException | RuntimeException rollbackFailure) {
                    failure.addSuppressed(rollbackFailure);
                }
                throw failure;
            }
        } catch (SQLException | RuntimeException failure) {
            if (commitConfirmed) {
                throw new SQLException(
                    "COMMIT confirmed, but cleanup failed; do not replay",
                    failure);
            }
            if (commitAttempted) {
                throw new SQLException(
                    "COMMIT outcome needs reconciliation; do not retry blindly",
                    failure);
            }
            throw failure;
        }
    }
}

Statement.setQueryTimeoutは文の実行に対する秒単位の待ち時間で、接続確立やトランザクション全体、Connection.commit()の上限を一括で設定するものではありません。ResultSetの読み取りやバッチへの適用には実装差があります。タイムアウト例外は取消し完了やロールバック成功の証明ではなく、接続のclose()だけに暗黙のロールバックを任せるのも避けます。setQueryTimeout、Connection.closeとトランザクション。

このコードは自動再試行をしません。COMMITを試みた後の例外は保守的に結果照合が必要として扱い、COMMIT成功後の接続クローズ失敗も「業務処理の失敗」と混同しないよう区別しています。失敗したロールバックは元の例外のsuppressed例外に残します。実運用では例外メッセージの文字列判定ではなく、処理段階・SQLSTATE・ベンダーエラー番号・要求IDを構造化して記録し、接続プールの破棄手順に接続してください。

pgJDBCではloginTimeout・connectTimeout・socketTimeoutは秒単位です。MySQL Connector/Jの同名のソケット設定はミリ秒単位なので、数値をそのまま移植しないでください。通信異常時の中断、結果取得、プールの健全性まで含めて利用ドライバーで確認します。pgJDBCの接続パラメーター。

タイムアウト後の処理と安全な再試行

  1. 残りの業務SQLを止める。文が失敗しても前の文の変更やロックが残っていることがあります。失敗したまま後続処理やCOMMITへ進まないでください。
  2. ロールバックを試み、結果を確認する。元の失敗を保持し、ロールバック・設定復元の失敗も記録します。接続断なら未完了トランザクションの終了が即座に観測できるとは限りません。壊れた接続はプールへ正常な接続として戻さず、製品の破棄・検証手順に従います。
  3. COMMITの結果不明を別扱いにする。COMMITがサーバーで完了した直後に応答が失われると、クライアントには失敗に見えることがあります。その後のrollback呼び出しや接続の破棄は、すでに確定した更新を取り消した証明にはなりません。要求IDなどで確定結果を照合するまで、同じ更新を無条件に再送しません。
  4. 再試行するならトランザクション全体を、条件付きで。一時的な失敗と判断でき、以前の処理が確定していないことを確認できた場合に、新しい健全な接続・トランザクションで行います。最大試行回数、全体の期限、指数バックオフとジッターを設け、接続障害を増幅させないようにします。
  5. 二重実行を設計で防ぐ。業務要求IDに一意制約を設け、結果記録と更新を同じトランザクションに含めるなど、冪等性を確保します。外部API、メール送信、非トランザクションのテーブルなどはDBのROLLBACKで取り消せないため、別の重複防止・補償設計が必要です。

COMMIT中の通信断と冪等性の問題は、Microsoftの接続回復性の解説でも説明されています。上の要求ID・照合・再試行の方針は、その危険を避けるための設計上の対策です。

運用時の確認項目

  • 実際に発生したタイムアウトの種類、適用値と単位、DB・ドライバーのバージョン、SQLSTATEやエラー番号、要求ID、COMMIT前後の段階を記録します。接続文字列、パスワード、機密パラメーターはログに残しません。
  • クエリ実行計画、インデックス、更新件数、ブロッキングの原因を調べます。時間を延長するだけで、長いトランザクションや待機の原因を隠さないようにします。
  • ユーザー入力待ちや外部APIの呼出しをトランザクションの内側に置かないなど、ロック保持時間を短くします。分割する場合は業務上の原子性を失わないことを確認します。
  • 検証環境でロック待ち、長い文、アイドル、通信断を別々に再現し、エラー時のDB状態とプール再利用を確認します。検証には独立した監視・中断手段を設け、本番環境で無制限の待機を作りません。

まとめ

最初に「何の待ち時間を止めたいか」を決め、接続・文・ロック待ち・アイドル・トランザクション全体を区別することが重要です。SQL ServerのLOCK_TIMEOUT、MySQLのinnodb_lock_wait_timeout、PostgreSQLのstatement_timeoutは互換の設定ではありません。範囲を限定した設定、明示的な後始末、COMMIT結果不明時の照合、安全に再実行できる設計を組み合わせて管理しましょう。

SQL
設定 管理 SQL トランザクション タイムアウト

この記事を書いた人

佐藤健司のアバター 佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

関連記事

コメント

コメントする

目次