Clever Geek Handbook

適切なSQLトランザクションに向けて（パート1）





私は、人々がデータベース内のトランザクションとその必要性を完全に理解しているという事実にしばしば直面しましたが、同時にそれらを正しく使用する方法を常に知っているとは限りません。 もちろん、80レベルの神聖な知識を達成するには、1年以上の経験があり、多くの分厚いSQLブックを読む必要があります。 したがって、この記事では、MS SQLのトランザクションに関連する可能性のあるすべてのものを説明しようとはしません。 開発者が見落としがちな、シンプルだが非常に重要な問題、つまりトランザクション分離レベルに触れたいと思います。

トピックは非常に単純であるという事実にもかかわらず、多くの情報源ではあまり献身的ではありません。情報が非常に少ないか、非常に多くあります。 つまり 5〜6の簡単な理論的定義を読んだ後、それらを実践することは不可能です。 記事の主題を自信を持って理解するには、専門的な文献に目を向ける必要がありますが、そこには多くの情報があり、誰もが同化に必要な時間を割くことができません。

今日、私は簡単なレシピを共有したいと思います。これは、トランザクション分離レベルの機能を思い出し、今日まで問題なく適切なレベルを選択するための情報に基づいた決定を下すのに役立ちました。



提案された暗記法の秘密は、簡単な理論に簡単な実用例が付随することです。これは、詳細な説明よりもはるかに明確です。

したがって、分離レベルの違いを理解するには、トランザクションが互いに分離されていない場合に発生する可能性のある望ましくない副作用に対処する必要があります。 これらの効果の詳細を理解したので、個々のレベルがどの効果から保護するかを見るだけで済みます。 その後、トランザクション分離のトピックは永遠にあなたにとって非常に複雑なもののように見えなくなると確信しています。



並行性の副作用



データベース内のすべての操作は瞬時ではなく、異なるユーザーが同時にデータを変更すると、次の副作用が発生する可能性があります。



さらに、これらの影響を詳細に調べ、実際の問題を示すSQLスクリプトを提示します。 それらを実行して問題をライブで確認することを強くお勧めしますが、そのためにはまずサーバーを準備する必要があります。 スクリプトを実行する準備と機能の手順を以下に説明します。

スクリプトを実行するための要件
  1. 最初にトランザクションNo. 1のスクリプトを実行し、その後すぐにトランザクションNo. 2のスクリプトを実行する必要があります（最初のスクリプトの開始から10秒以内）。
  2. データベースには、Table1という名前とIdとValueの列を持つテーブルが必要です。 単一行が必要です。



    テーブルを作成してデータを入力するには、次のスクリプトを実行できます。 

    IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Table1')) DROP TABLE Table1 CREATE TABLE Table1 (Id INT IDENTITY, Value INT) INSERT INTO Table1 (Value) VALUES(1)


    また、各例を検討する前にこのスクリプトを実行することをお勧めします。 これにより、得られた結果が以下で説明する結果と確実に同一になります。







失われた更新



この効果は、さまざまなトランザクションによる1つのデータブロックの同時変更に現れます。 そして、変更の1つが失われる可能性があります。

この表現はさまざまな方法で解釈される場合があります。

更新の紛失-解釈No. 1


2つのトランザクションが同じ行に対して同時にUPDATEを実行し、1つのトランザクションによって行われた変更が他のトランザクションによって上書きされます。

トランザクション1 トランザクション2 
 UPDATE Table1 SET Value = Value + 5 WHERE Id = 1; SELECT Value FROM Table1 WHERE Id = 1;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     
 UPDATE Table1 SET Value = Value + 7 WHERE Id = 1; SELECT Value FROM Table1 WHERE Id = 1;
結果： 値= 6 値= 8


なぜこれが起こっているのですか？
更新する前に、両方のトランザクションが値列の値を読み取ります。1です。トランザクション2が最初に値を書き込む時間があるとします。次に、値列の新しい値は8（1 + 7）になります。 次に、トランザクション1も新しい値を計算しますが、以前に減算した値（1）を使用して計算します。 その結果、トランザクション1の完了後、Value列は13（1 + 7 + 5）ではなく6（1 + 5）になります。

幸いなことに、このシナリオはMS SQLでは不可能です。なぜなら、最も低い分離レベルでもこの​​状況を防ぎ、結果は常に8ではなく13になるからです。





更新の紛失-解釈No. 2


シナリオは最初のものと似ていますが、値の値が一時変数に差し引かれます。

トランザクション1 トランザクション2 
 BEGIN TRAN; DECLARE @Value INT; SELECT @Value = Value FROM Table1 WHERE Id = 1; WAITFOR DELAY '00:00:10'; UPDATE Table1 SET Value = @Value + 5 WHERE Id = 1; COMMIT TRAN; SELECT Value FROM Table1 WHERE Id = 1;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     
 BEGIN TRAN; DECLARE @Value INT; SELECT @Value = Value FROM Table1 WHERE Id = 1; UPDATE Table1 SET Value = @Value + 7 WHERE Id = 1; COMMIT TRAN; SELECT Value FROM Table1 WHERE Id = 1;
結果： 値= 6 値= 8




ダーティリード



これはそのような読み取りであり、別のトランザクションから追加または変更されたデータを読み取ることができ、その後は確認（ロールバック）されません。

この効果は最小の分離レベルでのみ可能であり、デフォルトではより高い分離レベル（READ COMMITTED）が使用されるため、データ読み取りスクリプトでは分離レベルは明示的にREAD UNCOMMITTEDに設定されます。 トランザクション2のデフォルトの分離レベル（READ COMMITTED）を返すと、動作が変わります。

トランザクション1 トランザクション2 
 BEGIN TRAN; UPDATE Table1 SET Value = Value * 10 WHERE Id = 1; WAITFOR DELAY '00:00:10'; ROLLBACK; SELECT Value FROM Table1 WHERE Id = 1;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN TRAN; SELECT Value FROM Table1 WHERE Id = 1; COMMIT TRAN;
READ UNCOMMITTEDの結果： 値= 1 値= 10
READ COMMITTEDの結果： 値= 1 値= 1
2番目のトランザクション内で、値10が読み取られましたが、データベースに正常に保存されることはありませんでした（ROLLBACKコマンドによって拒否されました）。



繰り返し不可の読み取り



同じトランザクション内で再読み取りを行うときに、以前に読み取ったデータが変更されたことが判明した場合に表示されます。 この効果は、REPEATABLE READよりも低い分離レベルで観察できます。

トランザクション1 トランザクション2 
 SET TRANSACTION ISOLATION LEVEL READ COMMITTED --SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN; SELECT Value FROM Table1 WHERE Id = 1; WAITFOR DELAY '00:00:10'; SELECT Value FROM Table1 WHERE Id = 1; COMMIT;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     
 BEGIN TRAN; UPDATE Table1 SET Value = 42 WHERE Id = 1; COMMIT TRAN;
READ COMMITTEDの結果 値= 1

値= 42

即時実行
繰り返し読み取りの結果 値= 1

値= 1

トランザクションが完了するのを待っています1




ファントムリード



実行中の単一のトランザクションが、同じ基準に従って複数の行を複数回選択することを観察できます。 この場合、これらのサンプルの間隔で別のトランザクションが行を追加または削除するか、最初のトランザクションの選択基準で使用される一部の行の列を変更し、正常に終了します。 その結果、最初のトランザクションの同じサンプルが異なる行セットを提供することがわかりました。 この効果は、絶縁レベルがSERIALIZABLEより低い場合に観察できます。

トランザクション1 トランザクション2 
 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN; SELECT * FROM Table1 WAITFOR DELAY '00:00:10' SELECT * FROM Table1 COMMIT;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     
 BEGIN TRAN; INSERT INTO Table1 (Value) VALUES(100) COMMIT TRAN;
繰り返し読み取りの結果： - 最初の選択

ID：1; 値：1

-2番目のSELECT

ID：1; 値：1

ID：2; 値：100

即時実行
シリアライズ可能の結果： - 最初の選択

ID：1; 値：1

-2番目のSELECT

ID：1; 値：1

トランザクションが完了するのを待っています1




分離レベル



副作用の意味を理解すると、各レベルの分離の目的を理解することは非常に簡単です。 それらは副作用の数において彼ら自身の間で異なります。

効果
失われた更新 汚れた読書 非繰り返し読み取り ファントムリーディング
分離レベル コミットされていない読み取り

いいえ/はい（*）

あります

あります

あります

コミットを読む

または

コミットされたスナップショットの読み取り（**）

いいえ/はい（*）

いや

あります

あります

繰り返し読み取り

いや

いや

いや

あります

シリアライズ可能

または

スナップショット（**）

いや

いや

いや

いや



（*）-「失われた更新-解釈No. 2」のセクションで説明されているように解釈された場合にのみ、効果が現れます。

（**）-これらのレベルでは、ロックによってではなく、トランザクション中にtempdbに配置される可変データのコピーを作成することによって分離が実現されます。 詳細はこちら 。



おわりに



各レベルの目的を理解したので、トランザクションをより意味のある方法で使用する準備ができました。 しかし、私はそこで終わりません。 記事の第2部では 、素材の実用的価値はわずかに低くなりますが、同時にそれほど有用ではありません。 かつて、キャンベルはかつて完璧に言いました：「あなたがプログラムするレベルより下の少なくとも1つの抽象化レベルを理解しなければなりません。」 そのため、実装を理解すると、トピックを可能な限り深く理解でき、提案されたツールを正しく効率的に使用できます。


More articles:


All Articles