デッドロックについて説明してみる - その 7 ( C# から sp_getapplock と sp_releaseapplock 使ってデッドロックを回避してみる ) - - 都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

　過去に C# からデッドロックを例外処理でキャッチするサンプルを作成しました。
ryuchan.hatenablog.com

　このサンプルソースに、p_getapplock と sp_releaseapplock を適用し、デッドロックを回避します。まず、それぞれのストアドをコールするロジックを作りましょう。

public int GetAppLock(SqlConnection connection, SqlTransaction transaction)
{
    var command = new SqlCommand("sp_getapplock", connection, transaction);
    command.CommandType = CommandType.StoredProcedure;
    command.CommandTimeout = 60000;

    command.Parameters.AddWithValue("Resource", "lock_resource_id");
    command.Parameters.AddWithValue("LockMode", "Exclusive");
    command.Parameters.AddWithValue("LockOwner", "Transaction");
    command.Parameters.AddWithValue("LockTimeout", 60000);

    var retCode = command.Parameters.Add("ReturnValue", SqlDbType.Int);
    retCode.Direction = ParameterDirection.ReturnValue;
    command.ExecuteNonQuery();

    return (int)retCode.Value;
}

public int ReleaseAppLock(SqlConnection connection, SqlTransaction transaction)
{
    var command = new SqlCommand("sp_releaseapplock", connection, transaction);
    command.CommandType = CommandType.StoredProcedure;
    command.CommandTimeout = 60000;

    command.Parameters.AddWithValue("Resource", "lock_resource_id");

    var retCode = command.Parameters.Add("ReturnValue", SqlDbType.Int);
    retCode.Direction = ParameterDirection.ReturnValue;
    command.ExecuteNonQuery();

    return (int)retCode.Value;
}

　下記のように組み込みます。

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    var command = connection.CreateCommand();

    while (0 == 0)
    {
        try
        {
            var transaction = connection.BeginTransaction("query1");
            if (GetAppLock(connection, transaction) >= 0)
            {

                command.Connection = connection;
                command.Transaction = transaction;

                command.CommandText = @"UPDATE 
                            Production.Product
                        SET Name = N'Bearing Ball'
                        WHERE
                            ProductID = 2;";
                command.ExecuteNonQuery();

                command.CommandText = @"UPDATE 
                            Production.Product
                        SET Name = N'Adjustable Race'
                        WHERE
                            ProductID = 1;";
                command.ExecuteNonQuery();
            }
            ReleaseAppLock(connection, transaction);
            transaction.Commit();
            transaction.Dispose();
        }
        catch (SqlException ex)
        {
            MessageBox.Show("エラーNo:" + ex.Number.ToString() + " エラーメッセージ：" + ex.Message.ToString());
            break;
        }
    }
    command.Dispose();
}

　組み込みが完了したら、実行してみましょう。デッドロックが発生しません。dm_tran_locks でアプリケーションロックが発生しているのが判断できます。
f:id:koogucc11:20171230125822p:plain

　LockOwner が Transaction の場合は、明示的に sp_releaseapplock はコールしなくてもいいようですが、個人的に気持ち悪いのでサンプルソースでは呼んでます。さて、今年も残すところあと2日。けど、私は明日から一月中旬までずっとお仕事です....いつになったら、ゆっくり正月を迎えられるようになるんでしょうか....