SQLServerでのトランザクション

12-09-2022
Toanngo92
0 Comments

Mục lục

取引について

ユーザーがデータベース内の多くのテーブルのデータを変更する必要がある状況はたくさんあります。多くの場合、データを個別に実行すると一貫性が失われます。

最初のステートメントが正しく実行されたが、データが正しくないために次のステートメントが失敗したとします。

たとえば、1つの特定の状況は、銀行システムでの送金です。転送には、1つのINSERTステートメントと2つのUPDATEステートメントが必要です。

ユーザーは、ソースアカウントの残高を減らす必要があります。
次に、宛先勘定レコードの銀行システムの勘定の残高を増やす必要があります。

ユーザーは、このトランザクションがコミットされていること、およびソースアカウントと宛先アカウントに同じ変更が加えられているかどうかを確認する必要があります。

トランザクション定義

妥当な作業単位は、トランザクションとして適格となるために、Atomicity、Consistency、Isolation、およびPersistence（ACID）プロパティとして知られる4つのプロパティを示す必要があります。

Atomicity ：トランザクションに多くの操作がある場合は、すべてをコミットする必要があります。グループ内のいずれかの操作が失敗した場合、その操作はロールバックされます。

一貫性：操作の順序は適切である必要があります

分離：実行される操作は、同じサーバーデータベース上の他の操作から永続的に分離する必要があります

耐久性：データベースで実行される操作は、データベースに保存して永続的に保存する必要があります。

暗黙のトランザクション

SQL Serverは、次のようにいくつかのモードでトランザクションをサポートします。

自動コミットトランザクション:(自動コミット）コマンドの各1行は、成功すると自動的にコミットされます。このモードでは、トランザクションを開始および終了するために特定のステートメントを記述する必要はありません。これはSQLServerのデフォルトモードです
Expicit Transactions :(明示的）各明示的トランザクションは、BEGIN TRANSACTIONステートメントで始まり、ROLLBACKまたはCOMMITトランザクションで終わります。
暗黙的なトランザクション:(暗黙的に）前のトランザクションが完了し、各トランザクションがROLLBACKまたはCOMMIT構文を使用して完了すると、トランザクションが自動的にキャプチャされます。
バッチスコープのトランザクション:(バッチスコープの）これらのトランザクションは、Multiple Active Results Set（MARS）の概念に関連しています。また、MARSペレットで始まる暗黙的または明示的な各トランザクションは、バッチスコープトランザクションと呼ばれます。
分散トランザクション:(分散トランザクション）リソースマネージャーと呼ばれる2つ以上のサーバーに分散されます。トランザクション管理は、トランザクションマネージャーと呼ばれるサーバーコンポーネントによってリソースマネージャーの間に配置する必要があります。 SQL Serverの各インスタンスは、Microsoft分散トランザクションコーディネーター（MS DTC）などのトランザクションマネージャーによって配置された分散トランザクションのリソースマネージャーとして機能できます。

トランザクション拡張バッチ

トランザクションステートメントは、ブロックの成功または失敗を判別し、操作をロールバックできるデータベースを提供します。

単純なバッチの実行中にキャッチされたエラーは、部分的に成功する可能性があります。これは、トランザクションを使用する場合の望ましい結果ではありません。

この問題により、データベース内のテーブル間で論理的な競合が発生します。

ユーザーはエラー制御コードを追加して、エラーが発生した場合にトランザクションを古い状態にロールバックできます。

エラー処理コードは、エラーが発生する前にすべての変更を元に戻します。

トランザクション制御

トランザクションは、トランザクションの開始と終了を定義することにより、アプリケーションを介して制御できます。

トランザクションは、デフォルトで接続レイヤーによって管理されます。

トランザクションが接続を開始すると、すべてのT-SQLステートメントが同じ接続で実行され、トランザクションが終了するまで接続の一部になります。

トランザクションの開始

BEGIN TRANSACTIONステートメントは、明示的なトランザクションの開始を示します。

例えば：

USE AdventureWorks2019; GO DECLARE @TranName VARCHAR(30); SELECT @TranName = 'FirstTransaction'; BEGIN TRANSACTION @TranName; DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 13;

トランザクションのコミット

COMMIT TRANSACTIONステートメントは、エンドポイントをマークします。これは、暗黙的または明示的なトランザクションの終了を通知するコミットです。

COMMIT [TRAN | TRANSACTION] [transaction_name | @tran_name_variable]] [;]

例えば：

BEGIN TRANSACTION; GO DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 11; GO COMMIT TRANSACTION; GO

仕事をコミットする

COMMIT WORKステートメントは、トランザクションエンドポイントをマークします。

構文：

COMMIT [WORK] [;]

COMMITTRANSACTIONとCOMMITWORKは、COMMIT TRANSACTIONがユーザー定義のトランザクション名を受け入れることを除いて、同じです。

コミットでトランザクションを作成します。

BEGIN TRANSACTION DeleteCandidate WITH MARK N'Deleting a Job Candidate'; GO DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 11; GO COMMIT TRANSACTION DeleteCandidate;

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

トランザクションを中止して、トランザクションの元のポイントまたはセーブポイントに戻すことができます。

これは、トランザクションの開始またはセーブポイントから生成されたすべての変更されたデータを削除するために使用されます。また、トランザクションによって保持されているリソースを解放します。

トランザクションを保存する

SAVE TRANSACTIONステートメントは、トランザクション内にセーブポイントを設定します。

構文：

SAVE {TRAN | TRANSACTION} {savepoint_name | @savepoint_variable} [;]

例えば：

CREATE PROCEDURE SaveTranExample @InputCandidateID INT AS DECLARE @TranCounter INT; SET @TranCounter = @@TRANCOUNT; IF @TranCounter > 0 SAVE TRANSACTION ProcedureSave; ELSE BEGIN TRANSACTION; DELETE HumanResources.JobCandidate WHERE JobCandidateID = @InputCandidateID; IF @TranCounter = 0 COMMIT TRANSACTION; IF @tranCounter = 1 ROLLBACK TRANSACTION ProcedureSave; GO

上記のコードでは、セーブポイントトランザクションがプロシージャ内に作成されています。プロシージャが実行される前に有効なトランザクションが開始された場合、ストアドプロシージャによってデータ変更が生成された場合にのみ、ロールバックに使用されます。

トランザクション中の@@TRANCOUNT

@@ TRANSCOUNTは、現在の接続で発生するトランザクションステートメントの数値を返すシステム関数です。

例えば：

PRINT @@TRANCOUNT BEGIN TRAN PRINT @@TRANCOUNT BEGIN TRAN PRINT @@TRANCOUNT COMMIT PRINT @@TRANCOUNT COMMIT PRINT @@TRANCOUNT

結果：

ROLLBACKで@@TRANCOUNTを使用する例

PRINT @@TRANCOUNT BEGIN TRAN PRINT @@TRANCOUNT BEGIN TRAN PRINT @@TRANCOUNT ROLLBACK PRINT @@TRANCOUNT

結果：

トランザクションをマークする

トランザクションマーキングは、ユーザーが最近コミットされたトランザクションを失っても構わないと思っている場合、または関連するデータベースをチェックアウトしている場合にのみ役立ちます。

関連するすべてのデータベースでスケジュールに従ってトランザクションをマークすると、データベースに復元ポイントの共通チェーンが作成されます。

マーク付きトランザクションを使用する際の懸念事項：

トランザクションマークは物理スペースを消費し、データベース回復戦略で重要なトランザクションにのみ使用します。

マークされたトランザクションがコミットされると、その行はmsdbテーブルのlogmarkhistoryテーブルに追加されます。

マークされたトランザクションが異なるサーバーまたは同じサーバー上の複数のデータベースにまたがる場合、影響を受けるすべてのデータベースのレコードにマークを記録する必要があります。

マークされたトランザクションを作成する

マークされたトランザクションを作成するには、ユーザーはBEGINTRANSACTIONステートメントの構文をWITHMARK[DESCRIPTION]句とともに使用できます。

トランザクションは、マークの説明、名前、ユーザー、データベース、日時情報、およびログシーケンス番号（LSN）を記録します。

データベースのセットでマークされたトランザクションを作成する手順：

BEGINTRANステートメント内のWITHMARK句を使用したトランザクションの名前。
セット内のすべてのデータベースで更新を実行します。

例えば：

USE AdventureWorks2019; GO BEGIN TRANSACTION ListPriceUpdate WITH MARK 'UPDATE Product List prices'; GO UPDATE Production.Product SET ListPrice = ListPrice * 1.20 WHERE ProductNumber LIKE 'BK-%'; GO COMMIT TRANSACTION ListPriceUpdate; GO

暗黙的トランザクションと明示的トランザクションの違い

暗黙	明示的
トランザクションは、DDLおよびDMLステートメントごとにSQLServerによって維持されます	コンパイラによって定義されたトランザクション
DMLおよびDDLステートメントは、暗黙的なトランザクションの下で実行されます	DMLステートメントで構成され、クエリユニットとして実行されます。
SQLサーバーはステートメント全体をロールバックします	SELECTステートメントはデータを変更しないため、含めないでください。

分離レベル（分離レベル）

トランザクションは、他のトランザクションによって行われたデータまたはリソースの変更からトランザクションを分離する必要がある度合いを定義する分離レイヤーを定義します。

分離レベルは、ダーティリードなどの同時効果を可能にする条件下で定義されます。

トランザクション分離レベルは、以下を制御します。

データが読み取られるとき、適切なロックはありますか？また、どのタイプのロックが必要ですか？
読み取りロックはどのくらい保持されますか？
行を参照する読み取り操作が別のトランザクションによって変更された場合、次のいずれかの状況が発生します。
- 行の一意のロックメカニズムのロックが解除されるまでブロックします。
- トランザクションまたはステートメントの開始時に存在した行のコミットされたバージョンを取得します。
- コミットされていないデータを読み取る

トランザクションには、管理するデータごとに常に一意のキーが必要です。次に、そのトランザクションに設定された分離レベルに関係なく、トランザクションが完了するまでそのロックを保持します。

分離レベル：

分離レベル	ダーティリード	繰り返し不可の読み取り
コミット済みを読む	いいえ	はい
コミットされていない読み取り	はい	いいえ
スナップショット	いいえ	いいえ
繰り返し読み取り	いいえ	いいえ
シリアル化可能	いいえ	いいえ

ロックの範囲と種類

SQLSerの一般的なロックタイプのリスト

ロックモード	説明
アップデート	更新準備リソースで使用
共有	SELECT.ステートメントのようにデータを変更せずに操作を読み取るために使用します
意図する	カスケードロックを確立するために使用されます
エクスクルーシブ	INSERT、UPDATE、DELETEなどのデータ操作操作に使用されます。
バルクアップデート	大量のデータをテーブルにコピーするときに使用されます。
スキーマ	操作がテーブルスキーマに依存する場合に使用されます

ロックの更新

これらのロックは、デッドロック状態を回避します。トランザクションをシリアル化し、トランザクションがデータを読み取り、行またはページの共有ロックを取得します。データを変更するには、ロックを排他ロックに変換する必要があります。

共有ロック

これらのロックにより、並列トランザクションは同時実行制御下でリソースを読み取ることができます。

共有ロックは、読み取り操作が完了するとリソースを解放します。ただし、分離レイヤーは、繰り返される読み取りアクション以上に割り当てられます。

排他的ロック

これらのロックは、トランザクション内のリソースへの同時アクセスを防ぎます。

排他ロックを使用することにより、トランザクションはデータを変更できず、読み取り操作は、コミットされていない分離レイヤーまたはNOLOCKモードを介してのみスケジュールされます。

INSERT、UPDATE、DELETEなどのDMLステートメントは、データを変更するために使用されます。

インテントロック

インテンドロックの役割：

下位レベルのロックを無効にするような方法で、他のトランザクションが上位層のリソースのデータを変更するのを防ぎます。
より高い粒度でキーの競合を識別するデータベースエンジンの効率を向上させるため。

インテントロックの説明のリスト：

ロックモード	説明
インテント共有（IS）	一部の下位層のリソースでは、共有ロック保護が必要です。
インテントエクスクルーシブ（IX）	一部の下位層のリソースでは、排他的ロック保護が必要です。 IXは、ISのスーパーセット（別のセットで構成されるセットのセット）であり、下位のリソース層で必要な共有ロックを保護します。
インテントエクスクルーシブ（SIX）と共有	共有ロックの保護は、階層の下位のリソース全体で必要であり、一部の下位層のリソースでは排他ロックを意図しています。コンカレントISロック（コンカレントISロック）は、トップレベルのリソースで有効になります。
インテントアップデート（IU）	すべての下位層リソースで必要なロックを保護するため。 IUロックは、ページリソースでのみ使用されます。更新操作が実行されると、IUロックはIXロックに変換されます。
共有インテント更新（SIU）	別々のロックを取得し、両方のロックを同時に保持した結果として、SロックとIUロックの組み合わせを提供します。
インテントエクスクルーシブの更新（UIX）	別々のロックを取得し、両方のロックを同時に保持した結果として、UロックとIXロックの組み合わせを提供します。

一括更新ロック

一括更新ロックは、大量のデータがテーブルにコピーされるときに使用されます。これらのロックにより、複数のスレッドを同時に実行して、大量のデータをテーブルに順番にロードできます。

スキーマロック

スキーマ変更ロックは、テーブルや列の削除などのDDL操作を実行するときにデータベースエンジンで使用されます。

スキーマ安定性キーは、クエリのコンパイルおよび実行中にデータベースエンジンによって使用されます。

キーレンジロック

このタイプのロックは、RRsetで表されるレコードのリストを保護します。

キー範囲ロックはファントム読み取りを防止します。newはトランザクションAの検索条件に一致します。Aが同じ条件を再度実行すると、均一ではないデータのセットが取得されます。）

トランザクションを管理する

SQL Serverは、これらのトランザクションのACIDプロパティを保証するさまざまなスコープでトランザクションを実装します。

実際には、これは、共有データベースリソースを照会し、トランザクション間の干渉を防ぐためのトランザクションの基礎としてロックを使用することを意味します。

トランザクションログ

トランザクションログはデータベースの重要なコンポーネントです。システムがクラッシュした場合、トランザクションログはデータを適切な状態に確実に回復します。

ユーザーがその結果を理解するまで、トランザクションログを削除または移動しないでください。

トランザクションログでサポートされる操作：

個々のトランザクションの回復。
SQLServerの起動時の未完了のトランザクションロールバック。
トランザクションレプリケーションのサポート
高いパフォーマンス要件を持つシステムをサポートするディザスタリカバリソリューション。
ファイル、データベース、ファイルグループを復元するか、ページを障害点に転送します。

トランザクションログを切り捨てる

トランザクションログを切り捨てると、ログファイルが占有していたメモリが解放され、ログが続行されます。次のイベントが発生すると、ログは自動的に切り捨てられます。

チェックポイント後（チェックポイント）の単純なリカバリモデル。
最後のバックアップ以降にチェックポイングが発生した場合のバルクリカバリとフルリカバリのモデル。

ログが長時間アクティブになると、トランザクションログが遅延し、システムメモリがいっぱいになる可能性があります。ログの切り捨てはさまざまな理由で遅くなる可能性があります。ユーザーは、sys.databasesカタログビューのlog_reuse_wait_desc列とlog_reuse_wait列にクエリを実行することで、trnassactionログの切り捨てを妨げているものがあるかどうかを確認できます。

2列の値の説明：

Log_reuse_wait	Log_reuse_wait_desc	説明
0	なし	複数の再利用可能な仮想ログファイルを表すことを指定します
最初	チェックポイント	最後のログの切り捨て以降にチェックポイントが表示されていないこと、またはログのタイトルが仮想ログファイルの外に移動していないことを確認します
2	LOG_BACKUP	ログの切り捨てを実行する前に、必要なログバックアップを指定します。
3	ACTIVE_BACKUP_OR_RESTORE	バックアップまたは復元が進行中であることを指定します。
4	ACTIVE_TRANSACTION	アクティブなトランザクションを決定します。
5	DATABASE_MIRRORING	データベースのミラーリングが一時停止されているか、高性能モードであるかを判断します。ミラーデータベースはメインデータベースの背後にあります