トランザクションとロックの基本概念

トランザクション：データの一貫性と回復性を保証するための不可欠な仕組み。

ロック：複数ユーザーが同じデータベースリソースにアクセスする際、アクセス順序を管理するメカニズム。これがないとトランザクションの信頼性が損なわれる。

デッドロック：データベースパフォーマンスを著しく低下させる要因の一つで、複数トランザクションがリソースを競合することで発生します。

以下ではこれらの仕組みを具体的に解説し、実際のコード例を通じて理解を深めます。

トランザクションの種類と基本構造

トランザクションは「出発点に戻る」という性質を持ちます。例えば注文状況更新と在庫調整の2つのSQL処理が含まれるトランザクション中にエラーが発生した場合、すべての変更がロールバックされ、データの一貫性が保持されます。

トランザクションは以下の4つの特性（ACID）を持ちます：

• 原子性：処理は単位として完結または完全に失敗します。
• 一貫性：トランザクション終了後、データは整合性のある状態を保ちます。
• 隔離性：並行実行中のトランザクション同士が相互に影響しません。
• 持久性：コミット後の変更は永続的に保持されます。

SQL Serverでは以下の3種類のトランザクションが存在します：

• 自動コミットトランザクション：各SQLステートメントが個別トランザクションとして扱われます。
• 明示的トランザクション：BEGIN TRANSACTIONで開始し、COMMIT TRANSACTIONまたはROLLBACK TRANSACTIONで終了します。
• 隐式トランザクション：SET IMPLICIT_TRANSACTIONS ONで有効化され、自動的に次のトランザクションへ移行します。

トランザクションの実装例

BEGIN TRY  
    BEGIN TRANSACTION
    -- 正常な挿入
    INSERT INTO activities (Food, Sport, Count) VALUES ('豚肉','サッカー',1)
    -- エラーを発生させる挿入
    INSERT INTO activities (Food, Sport, Count) VALUES ('豚肉','サッカー','abc')
    -- 正常な挿入
    INSERT INTO activities (Food, Sport, Count) VALUES ('鶏肉','バスケットボール',2)
    
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage
        
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
END CATCH

BEGIN TRANSACTION
BEGIN TRY
    INSERT INTO activities (Food, Sport, Count) VALUES ('豚肉','サッカー',1)
    SAVE TRANSACTION activityOne
    
    INSERT INTO activities (Food, Sport, Count) VALUES ('豚肉','サッカー',2)
    INSERT INTO activities (Food, Sport, Count) VALUES ('鶏肉','バスケットボール',3)
    
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage
        
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION activityOne
END CATCH

XACT_ABORTの使用

XACT_ABORTオプションにより、エラー発生時の処理を制御できます。ON設定ではトランザクション全体がロールバックされ、OFF設定ではエラー発生したステートメントのみが中止されます。

DELETE FROM activities
SET XACT_ABORT OFF
BEGIN TRANSACTION
    INSERT INTO activities (Food, Sport, Count) VALUES ('豚肉','サッカー',1)
    -- 超大規模データ挿入によるエラー
    INSERT INTO activities (Food, Sport, Count) VALUES ('豚肉','サッカー',1234567890123456789)
    INSERT INTO activities (Food, Sport, Count) VALUES ('鶏肉','バスケットボール',3)
    
COMMIT TRANSACTION

デッドロックの再現

2つのクエリウィンドウで以下のコードを同時に実行するとデッドロックが発生します。

BEGIN TRANSACTION
    UPDATE activities SET Sport='テニス'
    WAITFOR DELAY '00:00:05'
    UPDATE resources SET Animal='ライオン'
COMMIT TRANSACTION

BEGIN TRANSACTION
    UPDATE resources SET Animal='ライオン'
    WAITFOR DELAY '00:00:05'
    UPDATE activities SET Sport='テニス'
COMMIT TRANSACTION

ロックの種類とその役割

SQL Serverでは以下の6種類のロックが存在します：

• 共有ロック(S)：複数トランザクションが同時読み込みを可能にします。
• 排他ロック(X)：他のトランザクションがリソースアクセスをブロックします。
• 更新ロック(U)：デッドロックの防止に使用されます。
• 意図ロック：階層構造のリソースロックを効率化します。
• スキーマロック：テーブル構造変更時の保護。
• バルク更新ロック：大量データ挿入時の並列処理を可能にします。

ロックの互換性表：

デッドロックの回避策

1. リソースアクセス順序を統一する
2. トランザクションを簡潔に保つ
3. ユーザー応答をトランザクション内に含めない
4. 并行度を適切に管理する
5. パーティションテーブルの活用
6. 複雑な長時間操作を避ける
7. 隔離レベルを適切に選択する

トランザクションの隔離レベル

5種類の隔離レベルがあります：

• read uncommitted：未コミットデータの読込を許可
• read committed：既存データのみ読込可能
• repeatable read：読込中のデータ変更を防ぐ
• snapshot：トランザクション開始時点のデータバージョンを確保
• serializable：最高レベルのデータ保護

ロックタイムアウトの設定

-- 現在のタイムアウト値確認
SELECT @@LOCK_TIMEOUT

-- タイムアウト値設定（ミリ秒）
SET LOCK_TIMEOUT 5000

ストアドプロシージャによるトランザクション処理：

IF OBJECT_ID('proc_Transfer') IS NOT NULL
    DROP PROCEDURE proc_Transfer
GO

CREATE PROCEDURE proc_Transfer
    @FromAccount INT,
    @ToAccount INT,
    @Amount MONEY
AS
BEGIN
    BEGIN TRANSACTION
    
    UPDATE accounts SET Balance = Balance - @Amount WHERE AccountID = @FromAccount
    UPDATE accounts SET Balance = Balance + @Amount WHERE AccountID = @ToAccount
    
    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRANSACTION
        RETURN 0
    END
    
    COMMIT TRANSACTION
END
GO

C#での呼び出し例：

protected void ExecuteTransfer()
{
    string connectionString = ConfigurationManager.ConnectionStrings["DBConn"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("proc_Transfer", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@FromAccount", Convert.ToInt32(txtFrom.Text));
            cmd.Parameters.AddWithValue("@ToAccount", Convert.ToInt32(txtTo.Text));
            cmd.Parameters.AddWithValue("@Amount", Convert.ToDecimal(txtAmount.Text));
            
            int result = cmd.ExecuteNonQuery();
            lblMessage.Text = result > 0 ? "振替成功" : "振替失敗";
        }
    }
}