logo

TechCodeview

SQLサーバートランザクション

SQL Server のトランザクションは、 ステートメントまたはクエリの連続したグループ データベース内で単一または複数のタスクを実行します。各トランザクションには、単一の読み取り、書き込み、更新、削除操作、またはこれらすべての操作の組み合わせが含まれる場合があります。 SQL Server では、各トランザクションで次の 2 つのことが発生する必要があります。

  • トランザクションがコミットされると、すべての変更が成功します。
  • または、トランザクションがロールバックされると、すべての変更が元に戻されます。

セット内のすべての操作が完了するまで、トランザクションは成功しません。これは、いずれかの引数が失敗すると、トランザクション操作が失敗することを意味します。各トランザクションは最初の実行可能な SQL ステートメントで始まり、明示的または暗黙的にコミットまたはロールバックが見つかったときに終了します。それは、 専念 または ロールバック DDL ステートメントが使用される場合は、ステートメントを明示的に、また暗黙的にも使用できます。

以下の図は、トランザクション プロセスを説明しています。

SQLサーバートランザクション

次の例では、トランザクションの概念を説明します。

この例では、銀行データベース システムを使用してトランザクションの概念を説明します。銀行の顧客が ATM モードを使用して自分の口座からお金を引き出したいとします。 ATM は、次の 3 つの手順でこの操作を実行できます。

  1. の 最初の一歩 アカウントで要求された金額が利用可能かどうかを確認することです。
  2. の 第二段階 利用可能な金額がある場合はその金額をアカウントから差し引き、アカウントの残高を更新します。
  3. の 第三段階 出金操作をログファイルに書き込むことです。このステップでは、トランザクションが成功したか失敗したかについて書き込みます。成功した場合は、データ変更をデータベースに書き込みます。それ以外の場合、トランザクションは前の状態にロールバックされます。

トランザクションの基本原理は、ステートメントの 1 つがエラーを返した場合、データの整合性を確保するために変更セット全体がロールバックされるということです。トランザクションが成功すると、すべての変更がデータベース上に永続的に保存されます。したがって、ATM からお金を引き出すときに停電やその他の問題が発生した場合でも、取引により残高の一貫性が保証されます。トランザクション ステートメントは、トランザクションの 4 つの主要なプロパティによってすべての操作がより正確で一貫性のあるものになるため、これらの操作を最適に実行します。トランザクションの 4 つのプロパティは ACID と呼ばれます。

トランザクションのプロパティ

トランザクション プロパティは ACID (原子性、一貫性、分離性、耐久性) プロパティと呼ばれ、以下で詳しく説明します。

SQLサーバートランザクション

原子性: このプロパティにより、トランザクションに含まれるすべてのステートメントまたは操作が正常に実行されることが保証されます。そうしないと、トランザクション全体が中止され、操作が失敗するとすべての操作が以前の状態にロールバックされます。

一貫性： このプロパティにより、トランザクションが正常にコミットされた場合にのみデータベースの状態が変更されます。データをクラッシュから保護する役割もあります。

分離： このプロパティは、すべてのトランザクションが他のトランザクションから分離されていることを保証します。つまり、トランザクション内の各操作は独立して実行されます。また、ステートメントが相互に透過的であることも保証されます。

耐久性: このプロパティにより、システムがクラッシュしたり障害が発生したりした場合でも、コミットされたトランザクションの結果がデータベースに永続的に保持されることが保証されます。

SQL Server のトランザクション モード

SQL Server が使用できるトランザクション モードは 3 つあります。

自動コミットトランザクションモード: これは SQL Server のデフォルトのトランザクション モードです。各 SQL ステートメントをトランザクションとして評価し、結果はそれに応じてコミットまたはロールバックされます。したがって、成功したステートメントはすぐにコミットされ、失敗したステートメントはすぐにロールバックされます。

エスケープ文字 Java

暗黙的なトランザクション モード。 このモードでは、SQL Server が各 DML ステートメントの暗黙的なトランザクションを開始できますが、ステートメントの最後で commit または rollback コマンドを使用することが明示的に必要になります。

明示的トランザクション モード: このモードはユーザーによって定義され、トランザクションの開始点と終了点を正確に識別できるようになります。致命的なエラーが発生した場合は自動的に中止されます。

トランザクション制御

トランザクションの制御に使用されるコマンドは次のとおりです。

    取引を開始します:各トランザクションの開始を示すコマンドです。専念：これは、変更をデータベースに永続的に保存するために使用されるコマンドです。ロールバック:これは、すべての変更をキャンセルし、前の状態に戻すために使用されるコマンドです。セーブポイント:このコマンドは、トランザクション全体ではなくトランザクションの一部のみをロールバックできるようにするトランザクションのグループ内にポイントを作成します。セーブポイントを解放:既存の SAVEPOINT を削除するために使用されます。トランザクションの設定:このコマンドはトランザクションに名前を付けます。この名前を使用すると、トランザクションを読み取り専用または読み取り/書き込み可能にしたり、特定のロールバック セグメントに割り当てることができます。

注: トランザクション制御言語コマンドには、DML ステートメント (INSERT、UPDATE、および DELETE) のみを使用できます。これらの操作はデータベースに自動的にコミットされるため、テーブルの作成または削除中にこれらの操作を使用することはできません。

トランザクション状態

これは、トランザクションが存続期間中にどのように進行するかを示します。 トランザクションの現在の状態と、トランザクションが将来どのように処理されるかについて説明します。 これらの状態は、トランザクションをコミットするか中止するかを決定するルールを定義します。

SQLサーバートランザクション

SQL Server の各トランザクション状態について説明します。

アクティブ状態: トランザクションの命令が実行されている間、トランザクションはアクティブ状態になります。に変わります。 「部分的にコミットされた状態」 すべての「読み取りおよび書き込み」操作がエラーなしで完了した場合。いずれかの命令が失敗すると、「失敗状態」に変わります。

部分的にコミット済み: すべての読み取りおよび書き込み操作が完了すると、メイン メモリまたはローカル バッファに変更が加えられます。州は行くだろう 「コミットされた状態」 変更がデータベース上で永続的に行われるかどうか。それ以外の場合は、「失敗状態」になります。

失敗した状態: トランザクションの命令が失敗するか、データベースの永続的な変更が失敗すると、トランザクションは失敗状態になります。

中止された状態: トランザクションは次から移行します。 「失敗した状態」 に 「中止された状態」 何らかの障害が発生したとき。これらの変更は以前の状態のローカル バッファまたはメイン メモリに対してのみ行われるため、変更は削除またはロールバックされます。

コミットされた状態: トランザクションは完了し、変更がデータベース上で永続的に行われ、トランザクションで終了するとこの状態になります。 「終了状態」。

終了状態: ロールバックがなく、トランザクションが 「コミットされた状態」 システムは一貫性があり、古いトランザクションが終了している間、新しいトランザクションの準備ができています。

SQL Server でのトランザクションの実装

SQL Server でトランザクションを実装する方法を理解するために、いくつかの例を見てみましょう。ここでは、 '製品' すべてのトランザクション状態を示す表。

次の SQL スクリプトは、選択したデータベースに Product テーブルを作成します。

 CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )

次に、以下のスクリプトを実行して、このテーブルにデータを挿入します。

 INSERT INTO Product VALUES(111, 'Mobile', 10000, 10), (112, 'Laptop', 20000, 15), (113, 'Mouse', 300, 20), (114, 'Hard Disk', 4000, 25), (115, 'Speaker', 3000, 20);

SELECT ステートメントを実行してデータを確認します。

SQLサーバートランザクション

COMMITトランザクションの例

トランザクションで使用される SQL ステートメントを複数の論理部分に分割することをお勧めします。そして、データをコミットするかロールバックするかを決定できます。次の手順は、トランザクションの作成方法を示しています。

  • を使用してトランザクションを開始します。 取引を開始する 指示。
  • SQL ステートメントを作成し、ニーズに基づいて分割します。
  • 使用 専念 ステートメントを使用してトランザクションを完了し、変更を永続的に保存します。

以下は、SQL Server での COMMIT 操作を説明するコマンドです。 

 -- Start a new transaction BEGIN TRANSACTION -- SQL Statements INSERT INTO Product VALUES(116, 'Headphone', 2000, 30) UPDATE Product SET Price = 450 WHERE Product_id = 113 -- Commit changes COMMIT TRANSACTION

エラーが見つからなかった場合は、トランザクションの各 SQL ステートメントが独立して実行された次の出力が表示されます。

SQLサーバートランザクション

INSERT ステートメントと UPDATE ステートメントは、トランザクションがコミットされた後はロールバックできません。コミット操作後にテーブルを検証すると、次のデータが表示されます。

SQLサーバートランザクション

ROLLBACKトランザクションの例

ROLLBACK コマンドを使用して、まだデータベースに保存されていないトランザクションを取り消し、トランザクションが開始された時点に戻ります。 次の例では、SQL Server での ROLLBACK 操作について説明します。 

 -- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116

上記のトランザクションを実行すると、正常に実行されることがわかります。ただし、COMMIT または ROLLBACK ステートメントを実行しない限り、変更は永続的なものにはならないため、データベース内の変更には影響しません。したがって、ROLLBACK トランザクション コマンドを使用してすべてのデータベース操作をロールバックするオプションがあります。 完全なトランザクションステートメントは次のとおりです。 

 -- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116 --Undo Changes ROLLBACK TRANSACTION

トランザクションでの @@Error グローバル変数の使用:

この変数は エラーがないかどうかを確認するために使用されます。 以下の例でその概念を説明します。ここでは、まず BEGIN コマンドを使用してトランザクションを開始し、次に 2 つの挿入ステートメントを記述します。次に、グローバル システム変数を使用します。 @@エラー の中に IF文 エラーを確認します。値が 0 より大きい場合は、何らかのエラーがあることを意味します。ここで、トランザクションはロールバックされます。それ以外の場合、トランザクションはコミットされます。

 BEGIN TRANSACTION INSERT INTO Product VALUES(115,'Speaker', 3000, 25) -- Check for error IF(@@ERROR > 0) BEGIN ROLLBACK TRANSACTION END ELSE BEGIN COMMIT TRANSACTION END

上記のトランザクションが実行されると、ロールバックされたことがわかります。これは、主キー列に重複した値を挿入しようとしたことが原因です。

自動ロールバックトランザクション

ほとんどのトランザクションには複数のクエリが含まれています。トランザクションの実行中に、SQL ステートメントのいずれかでエラーが発生した場合、データベースは変更されず、残りのステートメントは実行されません。この概念は、SQL Server では自動ロールバック トランザクションとして知られています。このプロセスを説明するために簡単な例を使用してみましょう。

 BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION

このトランザクションは次の出力を生成します。

SQLサーバートランザクション

この出力では、insert ステートメントが正常に実行されたことがわかります。ただし、update ステートメントを実行すると、データ型変換の問題によりエラーが見つかりました。この場合、SQL Server はデータベースの変更を許可しません。つまり、挿入操作では値が追加されず、select ステートメントは実行されません。

トランザクションのセーブポイント

セーブポイントは、セーブポイントの後に実行されたすべての変更をロールバックできるようにする特別なマークをトランザクションに挿入します。また、トランザクション全体ではなく、トランザクションの特定の部分をロールバックするためにも使用されます。を使用して定義できます。 トランザクションの保存 sp_name 声明。次の例では、insert ステートメントをコミットし、delete ステートメントをロールバックするトランザクションでのセーブポイントの使用について説明します。

 BEGIN TRANSACTION INSERT INTO Product VALUES(117, 'USB Drive', 1500, 10) SAVE TRANSACTION InsertStatement DELETE FROM Product WHERE Product_id = 116 SELECT * FROM Product ROLLBACK TRANSACTION InsertStatement COMMIT SELECT * FROM Product;

以下の結果を参照してください。製品 ID 116 が削除され、最初の出力に 117 が挿入されていることがわかります。ただし、2 番目の出力では、セーブポイントにより削除操作がロールバックされます。

SQLサーバートランザクション

トランザクションでセーブポイントを解放するにはどうすればよいですか?

セーブポイントの解放は、セーブポイントの後に実行されたクエリの結果を元に戻さずに、現在のトランザクションから名前付きセーブポイントを削除するために使用されます。 MySQL にはこのコマンドがありますが、SQL Server にはセーブポイントを解放するコマンドがありません。代わりに、コミットまたはロールバック トランザクションの終了時に自動的に解放されるため、途中で心配する必要はありません。

SQL Server の暗黙的なトランザクション

IMPLICIT_TRANSACTIONS オプションを有効にすることで、暗黙的なトランザクションを定義できます。次の例は、この概念を簡単に説明します。

 SET IMPLICIT_TRANSACTIONS ON UPDATE Product SET Quantity = 10 WHERE Product_id = 113 SELECT IIF(@@OPTIONS & 2 = 2, 'Implicit Transaction Mode ON', 'Implicit Transaction Mode OFF' ) AS 'Transaction Mode' SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans

このトランザクションでは 2 つのオプションを使用しました @@OPTION と @@TRANCOUNT。 @@OPTOPN は現在の SET オプションに関する情報を提供し、@@TRANCOUNT は現在のセッションの BEGIN TRANSACTION ステートメントを提供します。

ここで、トランザクションを実行すると、以下の出力が返されます。

SQLサーバートランザクション

SQL Server の明示的なトランザクション

明示的トランザクションは、明示的トランザクションの開始点を識別するため、BEGIN TRANSACTION コマンドを通じて定義する必要があります。 SQL Server で明示的なトランザクションを次のように定義できます。

 BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]

構文では、trans_name オプションはトランザクションの一意の名前を示します。の @trans_name_var トランザクション名を格納するユーザー定義変数を示します。最後に、 マーク オプションを使用すると、ログ ファイル内の特定のトランザクションにマークを付けることができます。

BEGIN TRANSACTION コマンドによる明示的なトランザクションは、トランザクション関連リソースの分離レベルに応じてロックを取得しました。ロックの問題を軽減するのに役立ちます。以下の例を参照してください。

 BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans

出力は次のとおりです。

SQLサーバートランザクション

SQL Server でマークされたトランザクション

マークされたトランザクションは、ログ ファイル内の特定のトランザクションに説明を追加するために使用されます。データベースを以前の状態に復元するときに、日付と時刻の代わりに回復ポイントとして使用できます。マークされたトランザクションがデータベースを変更する場合にのみ、マークがログ ファイルに追加されることを知っておく必要があります。次の例を使用してその概念を理解することができます。

Javaのブール文字列

データベースを誤って変更してしまい、データが変更された正確な瞬間が分からないとします。その場合、データの回復には長い時間がかかる可能性があります。ただし、マークされたトランザクションを使用すると、データ変更の正確なタイミングを決定するための便利なツールになる可能性があります。

次の構文は、SQL Server のマークされたトランザクションを示しています。 

 BEGIN TRANSACTION trans_name WITH MARK 'description';

ここでは、トランザクションの名前を定義してから、WITH MARK オプションを追加する必要があります。以下の例では、レコードを削除し、ログ ファイルにマークを追加します。

 BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct

の ログマーク履歴 テーブルは、 msdbデータベース そして、コミットされたマークされた各トランザクションに関する情報を保存します。以下のステートメントを実行して、logmarkhistory テーブルから詳細を取得します。

 SELECT * FROM msdb.dbo.logmarkhistory

SQL Server の名前付きトランザクション

SQL Server でトランザクションの名前を指定することもできます。単一のクエリで多数のトランザクションを処理する場合は、名前付きトランザクションを使用することを常にお勧めします。次の例は、トランザクションの名前を変更する方法を説明しています。

 BEGIN TRANSACTION AddProduct INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Product_name = 'Pen Drive' WHERE Product_id = 117 COMMIT TRANSACTION AddProduct

出力は次のとおりです。

SQLサーバートランザクション

結論

この記事では、SQL Server ステートメントのトランザクションの完全な概要を説明します。トランザクションはデータベースの整合性を確保するため、リレーショナル データベース システムで役立ちます。