いがにんのぼやき

WEBエンジニアのブログ。IT、WEB、バンド、アニメ。

Dapperでトランザクションを使用、トランザクション分離レベルを変更する方法のメモ

Dapperでトランザクションの分離レベルを変更するときにどういう挙動をするのかよく分かっていなかったので調べたメモ。
ついでにトランザクション周りのSQLも確認。

今回の環境

今回試しているのはSQLServerだが他のRDBでも方言の違いはあれど基本的に同じなはず。

SQL

まずSQLの確認から。

トランザクションの分離レベルの設定。 Serializableであれば下記。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

docs.microsoft.com

分離レベル オプションは一度に 1 つだけ設定でき、設定したオプションは明示的に変更されない限り、その接続で継続的に使用されます。 ステートメントの FROM 句内にあるテーブル ヒントで、テーブルに対して別のロック動作やバージョン管理動作が指定されない限り、トランザクションのすべての読み取り操作は、指定した分離レベルのルールに従って実行されます。

公式ドキュメントを見ると接続単位でトランザクション分離レベルが決まるらしい。

実際に今の分離レベルを確認するなら。

DBCC USEROPTIONS

docs.microsoft.com

このSQLを実行するとisolation levelの項目に今の分離レベルが表示される。

f:id:igatea:20190421204934p:plain

SSMSで確認すると切断、再接続をしてもずっと変更後の分離レベルになっているのでここらへんの挙動ははっきりわからない。
恐らくセッションが生きているようで、SSMS自体を再起動すると規定値に戻る。

MySQLで分離レベルを確認する場合はこんな感じらしい。(未検証)

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

実際に分離レベルを設定してトランザクションを張るにはBEGIN TRAN、コミットはCOMMIT TRAN。

BEGIN TRAN
    /****** 実行する処理  ******/
COMMIT TRAN

実際にはSQLで全部やるならTRYCATCHしてROLLBACKの処理が入るだろう。

Dapper

次にアプリケーション側でどう書くか。
今回はDapperを使って書いてみる。
まずはusingでSqlConnection.BeginTransactionを呼び出す。
これでトランザクションを張ることが出来る。
BeginTransactionは第一引数で分離レベルを設定することが出来る。

docs.microsoft.com

実際に実行するコードはこんな感じに。 このSQLだと分離レベルを変える意味はないがご愛嬌。

using (var tran = conn.BeginTransaction(IsolationLevel.Serializable))
{
    var sql1 = @"
INSERT INTO Samples ( Id, Name )
VALUES ( @id, @name )";
    var sql2 = @"
INSERT INTO Samples ( Id, Name )
VALUES ( @id, @name + 'duplicate' )";

    try
    {
        await conn.ExecuteAsync(sql1, new { id = sample.Id, name = sample.Name }, tran);
        await conn.ExecuteAsync(sql2, new { id = sample.Id, name = sample.Name }, tran);
        tran.Commit();
        return true;
    }
    catch (SqlException e)
    {
        // log出力とか
        return false;
    }
}

usingでBeginTransactionを囲っておけばこのコードが終わった時にコミットされていなければロールバックしてくれる。
ここで分離レベルを指定しなかった場合、Dapperで実行するとそのコネクションプールが保持されている限り、その接続で前に使用されたトランザクションレベルが使用されてしまう。
・・・と思ったけどDBCC USEROPTIONS の結果は固定化されているが using (var tran = conn.BeginTransaction()) したときは一番最初のトランザクションレベルとなっている。

公式ドキュメントにはこのように記載があった。

分離レベルを指定しない場合は、既定の分離レベルが使用されます。 分離レベルを指定する、BeginTransactionメソッドを受け取るオーバー ロードを使用して、isoパラメーター (BeginTransaction)。 トランザクションの分離レベルは、接続が終了または破棄されるまで、トランザクションが完了した後に永続化します。 分離のレベルを設定スナップショットsnapshot 分離レベルが有効になっていないデータベースではない例外をスローします。 既定の分離レベルを使用して、トランザクションが完了します。

日本語訳が変だけど、以下も併せて読んでみるとどうやらREAD COMMITTEDがSQLServerでのデフォルトでになっており、それが使用されるらしい。

stackoverflow.com

ので、分離レベルはこんな感じに切り替わる。

using (var conn = CreateConnection())
{
    var t1 = conn.Query("DBCC USEROPTIONS"); // 前のトランザクションレベルが表示される
 
     conn.Execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"); // BeginTransactionでは意味なし
 
    using (var tran = conn.BeginTransaction())
    {
        var t2 = conn.Query("DBCC USEROPTIONS", new { }, tran); // READ COMMITTEDが表示、既定のトランザクションレベル

        var sql1 = @"
INSERT INTO Samples ( Id, Name )
VALUES ( @id, @name )";
        var sql2 = @"
INSERT INTO Samples ( Id, Name )
VALUES ( @id, @name + 'duplicate' )";

        try
        {
            await conn.ExecuteAsync(sql1, new { id = sample.Id, name = sample.Name }, tran); // READ COMMITTEDで実行される
            await conn.ExecuteAsync(sql2, new { id = sample.Id, name = sample.Name }, tran);
            tran.Commit();
            return true;
        }
        catch (SqlException e)
        {
            // log出力とか
            return false;
        }
    }
    }
}

trasactionの引数でちょっとした罠があり、パラメータがなくてもQueryAsyncの第二引数に匿名オブジェクトを入れて、第三引数にトランザクションを入れなければいけない。
そうしないとIDbTransactionをオブジェクトとして認識してただのクエリに入れるパラメータと判断し、以下のエラーが出る。

InvalidOperationException: ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

分離レベルを変えて、Serializeレベルで行うならこんな感じになる。

using (var conn = CreateConnection())
{
    var t1 = conn.Query("DBCC USEROPTIONS"); // 前のトランザクションレベルが表示される

    using (var tran = conn.BeginTransaction(IsolationLevel.Serializable)) // 変わる箇所
    {
        var t2 = conn.Query("DBCC USEROPTIONS", new { }, tran); // Serializableが表示

        var sql1 = @"
INSERT INTO Samples ( Id, Name )
VALUES ( @id, @name )";
        var sql2 = @"
INSERT INTO Samples ( Id, Name )
VALUES ( @id, @name + 'duplicate' )";

        try
        {
            await conn.ExecuteAsync(sql1, new { id = sample.Id, name = sample.Name }, tran); // Serializableで実行される
            await conn.ExecuteAsync(sql2, new { id = sample.Id, name = sample.Name }, tran);
            tran.Commit();
            return true;
        }
        catch (SqlException e)
        {
            // log出力とか
            return false;
        }
    }
}

実行したソースはこちら。

https://github.com/igayamaguchi/OrmExample/tree/master/InsertDuplicate

調べている上で知ったこと

SQLServerへの接続を切る方法。

クエリ発行をするセッションは切れない。

docs.microsoft.com

切断。

KILL [セッションID]

セッション確認。

 SELECT conn.session_id, host_name, program_name,
     nt_domain, login_name, connect_time, last_request_end_time 
 FROM sys.dm_exec_sessions AS sess
 JOIN sys.dm_exec_connections AS conn
    ON sess.session_id = conn.session_id;

プライマリーキー、一意性違反の検知

SqlExceptionのNumberに2627という数字が入る。
これはSQLServerの重複違反のエラーコード。
使用するデータベースによって数字は異なる。

public enum CreateResult
{
    Success,
    Duplicate
}

try
{
    await conn.ExecuteAsync(sql, new { id = sample.Id, name = sample.Name }, tran);
    tran.Commit();
    return CreateResult.Success;
}
catch (SqlException e) when (e.Number == 2627)
{
    // log出力とか
    tran.Rollback();
    return CreateResult.Duplicate;
}