SQL Server トランザクションのロックおよび行のバージョン管理ガイドSQL Server Transaction Locking and Row Versioning Guide

どのようなデータベースであっても、トランザクションを正しく管理しないと、ユーザー数が多いシステムでは競合やパフォーマンスの問題を招くことがあります。In any database, mismanagement of transactions often leads to contention and performance problems in systems that have many users. データにアクセスするユーザー数が多いほど、トランザクションが効率的に行われるアプリケーションを用意することが重要になります。As the number of users that access the data increases, it becomes important to have applications that use transactions efficiently. このガイドでは、各トランザクションの物理的な整合性の確保を目的として SQL Server データベース エンジンSQL Server Database Engineで使用されるロックおよび行のバージョン管理のメカニズムと、アプリケーションで効率的にトランザクションを制御する方法について説明します。This guide describes the locking and row versioning mechanisms the SQL Server データベース エンジンSQL Server Database Engine uses to ensure the physical integrity of each transaction and provides information on how applications can control transactions efficiently.

適用対象:SQL Server 2005SQL Server 2005を通じてSQL Server 2014SQL Server 2014明記しない限りです。Applies to: SQL Server 2005SQL Server 2005 through SQL Server 2014SQL Server 2014 unless noted otherwise.

このガイドでIn This Guide

トランザクションの基礎Transaction Basics

ロックおよび行のバージョン管理の基礎Locking and Row Versioning Basics

データベース エンジンのロックLocking in the Database Engine

データベース エンジンでの行のバージョン管理に基づく分離レベルRow Versioning-based Isolation Levels in the Database Engine

インデックスのロックのカスタマイズCustomizing Locking for an Index

高度なトランザクション情報Advanced Transaction Information

トランザクションの基礎Transaction Basics

トランザクションは、論理的な 1 つの作業単位として実行される一連の操作です。A transaction is a sequence of operations performed as a single logical unit of work. 論理的な 1 つの作業単位がトランザクションとして有効であるには、ACID と呼ばれる 4 つのプロパティ (原子性、一貫性、分離性、および持続性) を備えている必要があります。A logical unit of work must exhibit four properties, called the atomicity, consistency, isolation, and durability (ACID) properties, to qualify as a transaction.

原子性Atomicity
トランザクションは、分離できない 1 つの作業単位であり、そのデータ変更がすべて実行されるか、まったく実行されないかのどちらかである必要があります。A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them are performed.

一貫性Consistency
トランザクションの完了時に、すべてのデータが一貫した状態になければなりません。When completed, a transaction must leave all data in a consistent state. リレーショナル データベースの場合、すべてのデータの整合性を維持するため、トランザクションの変更に対してすべてのルールが適用される必要があります。In a relational database, all rules must be applied to the transaction's modifications to maintain all data integrity. B ツリー インデックスや二重リンク リストなどのすべての内部データ構造は、トランザクションの終了時に正しくなければなりません。All internal data structures, such as B-tree indexes or doubly-linked lists, must be correct at the end of the transaction.

分離性Isolation
同時実行トランザクションによって行われる変更は、他の同時実行トランザクションによって行われる変更と相互に独立している必要があります。Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. トランザクションは、他の同時実行トランザクションが変更する前の状態のデータを認識するか、2 番目のトランザクションが完了した後のデータを認識するかのどちらかであり、中間の状態は認識しません。A transaction either recognizes data in the state it was in before another concurrent transaction modified it, or it recognizes the data after the second transaction has completed, but it does not recognize an intermediate state. これをシリアル化可能性と呼んでいます。最初のデータを再度読み込み、一連のトランザクションを実行しても、元のトランザクションを実行したときと同じ状態で終了できるからです。This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.

DurabilityDurability
完全持続性トランザクションの完了後、その結果がシステム内で持続されます。After a fully durable transaction has completed, its effects are permanently in place in the system. システム障害が発生しても、変更結果は持続されます。The modifications persist even in the event of a system failure. SQL Server 2014SQL Server 2014 以降は、遅延持続性トランザクションに対応しています。and later enable delayed durable transactions. 遅延持続性トランザクションのコミットは、トランザクション ログ レコードがディスクに対して永続化される前に行われます。Delayed durable transactions commit before the transaction log record is persisted to disk. トランザクションの遅延持続性について詳しくは、トランザクションの持続性に関するトピックをご覧ください。For more information on delayed transaction durability see the topic Transaction Durability.

SQL プログラマは、データの論理的な一貫性が確保されるように、トランザクションを開始および終了しなければなりません。SQL programmers are responsible for starting and ending transactions at points that enforce the logical consistency of the data. プログラマは、組織のビジネス ルールに合わせて、データが一貫した状態に保たれるようにデータ修正の順序を定義する必要があります。The programmer must define the sequence of data modifications that leave the data in a consistent state relative to the organization's business rules. これらの変更指示ステートメントは 1 つのトランザクションに含め、SQL Server データベース エンジンSQL Server Database Engineがトランザクションの物理的な整合性を設定できるようにします。The programmer includes these modification statements in a single transaction so that the SQL Server データベース エンジンSQL Server Database Engine can enforce the physical integrity of the transaction.

データベース エンジンDatabase Engineのインスタンスなど、企業のデータベース システムは、各トランザクションの整合性を確保するメカニズムを用意する必要があります。It is the responsibility of an enterprise database system, such as an instance of the データベース エンジンDatabase Engine, to provide mechanisms ensuring the physical integrity of each transaction. データベース エンジンDatabase Engineは、次の機能を提供します。The データベース エンジンDatabase Engine provides:

  • トランザクションの分離性を保持するロック機能。Locking facilities that preserve transaction isolation.

  • トランザクションの持続性を設定するログ機能。Logging facilities ensure transaction durability. 完全持続性トランザクションでは、ログ レコードがディスクに書き込まれた後で、トランザクションがコミットされます。For fully durable transactions the log record is hardened to disk before the transactions commits. したがって、サーバー ハードウェア、オペレーティング システム、またはデータベース エンジンDatabase Engineのインスタンス自体で障害が発生した場合でも、データベース エンジンのインスタンスは再起動時にトランザクション ログを使用して、中断したトランザクションをシステム障害が発生した時点まで自動的にロールバックします。Thus, even if the server hardware, operating system, or the instance of the データベース エンジンDatabase Engine itself fails, the instance uses the transaction logs upon restart to automatically roll back any uncompleted transactions to the point of the system failure. 遅延持続性トランザクションのコミットは、トランザクション ログ レコードがディスクに書き込まれる前に行われます。Delayed durable transactions commit before the transaction log record is hardened to disk. ログ レコードがディスクに書き込まれる前にシステム障害が発生した場合、このようなトランザクションは失われる可能性があります。Such transactions may be lost if there is a system failure before the log record is hardened to disk. トランザクションの遅延持続性について詳しくは、トランザクションの持続性に関するトピックをご覧ください。For more information on delayed transaction durability see the topic Transaction Durability.

  • トランザクションの原子性と一貫性を設定するトランザクション管理機能。Transaction management features that enforce transaction atomicity and consistency. トランザクションは開始したら、正常に完了する (コミットされる) 必要があります。正常に完了しない場合は、トランザクションの開始後に加えた変更がすべて、データベース エンジンDatabase Engineによって取り消されます。After a transaction has started, it must be successfully completed (committed), or the データベース エンジンDatabase Engine undoes all of the data modifications made since the transaction started. データを変更前の状態に戻す操作であるため、これをトランザクションのロールバックといいます。This operation is referred to as rolling back a transaction because it returns the data to the state it was prior to those changes.

トランザクションの制御Controlling Transactions

アプリケーションは、主にトランザクションの開始タイミングと終了タイミングを指定してトランザクションを制御します。Applications control transactions mainly by specifying when a transaction starts and ends. これについては、Transact-SQLTransact-SQL ステートメントまたはデータベース アプリケーション プログラミング インターフェイス (API) 関数を使用して指定できます。This can be specified by using either Transact-SQLTransact-SQL statements or database application programming interface (API) functions. また、トランザクションが完了せずに終了した場合、その原因となったエラーがシステムによって正しく処理される必要があります。The system must also be able to correctly handle errors that terminate a transaction before it completes. 詳細については、トランザクション ステートメント(TRANSACT-SQL)ODBC でのトランザクショントランザクション SQL Server Native Client (OLEDB) を参照してください。For more information, see Transaction Statements (Transact-SQL), Transactions in ODBC and Transactions in SQL Server Native Client (OLEDB).

既定では、トランザクションは接続レベルで管理されます。By default, transactions are managed at the connection level. 接続時にトランザクションが開始すると、その接続で実行されるすべての Transact-SQLTransact-SQL ステートメントが、トランザクションが終了するまでそのトランザクションの一部になります。When a transaction is started on a connection, all Transact-SQLTransact-SQL statements executed on that connection are part of the transaction until the transaction ends. ただし、複数のアクティブな結果セット (MARS) セッションでは、Transact-SQLTransact-SQL の明示的または暗黙的なトランザクションは、バッチ レベルで管理されるバッチスコープのトランザクションになります。However, under a multiple active result set (MARS) session, a Transact-SQLTransact-SQL explicit or implicit transaction becomes a batch-scoped transaction that is managed at the batch level. バッチの完了時にバッチスコープのトランザクションがコミットまたはロールバックされていない場合、SQL ServerSQL Server により、トランザクションは自動的にロールバックされます。When the batch completes, if the batch-scoped transaction is not committed or rolled back, it is automatically rolled back by SQL ServerSQL Server. 詳細については、次を参照してください。複数アクティブな結果セット (MARS) SQL Server でします。For more information, see Multiple Active Result Sets (MARS) in SQL Server.

トランザクションの開始Starting Transactions

Transact-SQLTransact-SQLのインスタンスでは、API 関数や SQL Server データベース エンジンSQL Server Database Engine ステートメントを使用して、明示的、自動コミット、または暗黙のいずれかのトランザクションとしてトランザクションを開始できます。Using API functions and Transact-SQLTransact-SQL statements, you can start transactions in an instance of the SQL Server データベース エンジンSQL Server Database Engine as explicit, autocommit, or implicit transactions.

明示的なトランザクションExplicit Transactions
明示的なトランザクションとは、API 関数またはステートメント (Transact-SQLTransact-SQLBEGIN TRANSACTION、COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION、または ROLLBACK WORKTransact-SQLTransact-SQL) の使用により、トランザクションの開始と終了を明示的に定義したものです。An explicit transaction is one in which you explicitly define both the start and end of the transaction through an API function or by issuing the Transact-SQLTransact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK Transact-SQLTransact-SQL statements. トランザクションが終了すると、接続は明示的なトランザクションを開始する前のトランザクション モード、つまり暗黙のトランザクション モードまたは自動コミット モードに戻ります。When the transaction ends, the connection returns to the transaction mode it was in before the explicit transaction was started, either implicit or autocommit mode.

明示的なトランザクションでは、次のステートメントを除くすべての Transact-SQLTransact-SQL ステートメントを使用できます。You can use all Transact-SQLTransact-SQL statements in an explicit transaction, except for the following statements:

ALTER DATABASEALTER DATABASE CREATE DATABASECREATE DATABASE DROP FULLTEXT INDEXDROP FULLTEXT INDEX
ALTER FULLTEXT CATALOGALTER FULLTEXT CATALOG CREATE FULLTEXT CATALOGCREATE FULLTEXT CATALOG RECONFIGURERECONFIGURE
ALTER FULLTEXT INDEXALTER FULLTEXT INDEX CREATE FULLTEXT INDEXCREATE FULLTEXT INDEX RESTORERESTORE
BACKUPBACKUP DROP DATABASEDROP DATABASE フルテキスト システム ストアド プロシージャFull-text system stored procedures
CREATE DATABASECREATE DATABASE DROP FULLTEXT CATALOGDROP FULLTEXT CATALOG 明示的なトランザクションでも暗黙のトランザクションでも、データベース オプションを設定する sp_dboption および master データベースを変更するシステム プロシージャは使用できません。sp_dboption to set database options or any system procedure that modifies the master database inside explicit or implicit transactions.

注意

UPDATE STATISTICS は、明示的なトランザクションで使用できます。UPDATE STATISTICS can be used inside an explicit transaction. ただし、UPDATE STATISTICS は、このステートメントを含むトランザクションとは別にコミットされ、ロールバックすることはできません。However, UPDATE STATISTICS commits independently of the enclosing transaction and cannot be rolled back.

自動コミット トランザクションAutocommit Transactions
自動コミット モードは、SQL Server データベース エンジンの既定のトランザクション管理モードです。Autocommit mode is the default transaction management mode of the SQL Server Database Engine. すべての Transact-SQL ステートメントは完了時にコミットされるか、ロールバックされます。Every Transact-SQL statement is committed or rolled back when it completes. ステートメントが正常に完了した場合はコミットされ、エラーが検出された場合はロールバックされます。If a statement completes successfully, it is committed; if it encounters any error, it is rolled back. この既定のモードが明示的トランザクションまたは暗黙のトランザクションでオーバーライドされていなければ、データベース エンジンのインスタンスへの接続は自動コミット モードで動作します。A connection to an instance of the Database Engine operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions. ADO、OLE DB、ODBC、および DB-Library の既定モードも自動コミット モードです。Autocommit mode is also the default mode for ADO, OLE DB, ODBC, and DB-Library.

暗黙のトランザクションImplicit Transactions
接続が暗黙のトランザクション モードで動作している場合、データベース エンジンのインスタンスでは、現在のトランザクションがコミットされるかロールバックされた後に新しいトランザクションが自動的に開始されます。When a connection is operating in implicit transaction mode, the instance of the Database Engine automatically starts a new transaction after the current transaction is committed or rolled back. トランザクションの開始を指定する必要はありません。各トランザクションをコミットするかロールバックするだけです。You do nothing to delineate the start of a transaction; you only commit or roll back each transaction. 暗黙のトランザクション モードの場合、トランザクションが連鎖して生成されます。Implicit transaction mode generates a continuous chain of transactions. 暗黙のトランザクション モードは、API 関数または Transact-SQLTransact-SQL SET IMPLICIT_TRANSACTIONS ON ステートメントのいずれかを使用して設定します。Set implicit transaction mode on through either an API function or the Transact-SQLTransact-SQL SET IMPLICIT_TRANSACTIONS ON statement.

接続に対して暗黙のトランザクション モードをオンに設定した後、データベース エンジンDatabase Engineのインスタンスで次のステートメントのうちのどれかが最初に実行されると、トランザクションが自動的に開始されます。After implicit transaction mode has been set on for a connection, the instance of the データベース エンジンDatabase Engine automatically starts a transaction when it first executes any of these statements:

ALTER TABLEALTER TABLE FETCHFETCH REVOKEREVOKE
CREATECREATE GRANTGRANT SELECTSELECT
DELETEDELETE INSERTINSERT TRUNCATE TABLETRUNCATE TABLE
DROPDROP OPENOPEN UPDATEUPDATE

バッチスコープのトランザクションBatch-scoped Transactions
複数のアクティブな結果セット (MARS) にのみ該当します。MARS セッションで開始された Transact-SQLTransact-SQL の明示的または暗黙的なトランザクションは、バッチスコープのトランザクションになります。Applicable only to multiple active result sets (MARS), a Transact-SQLTransact-SQL explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction. バッチの完了時にコミットまたはロールバックされていないバッチスコープのトランザクションは、SQL ServerSQL Server により自動的にロールバックされます。A batch-scoped transaction that is not committed or rolled back when a batch completes is automatically rolled back by SQL ServerSQL Server.

分散トランザクションDistributed Transactions
分散トランザクションは、リソース マネージャーと呼ばれる複数のサーバーに展開されます。Distributed transactions span two or more servers known as resource managers. トランザクションの管理はリソース マネージャー間で、トランザクション マネージャーと呼ばれるサーバー コンポーネントが調整する必要があります。The management of the transaction must be coordinated between the resource managers by a server component called a transaction manager. SQL Server データベース エンジンSQL Server Database Engine分散トランザクション コーディネーター (MS DTC) などのトランザクション マネージャー、または分散トランザクション処理に関する Open Group XA 仕様をサポートするその他のトランザクション マネージャーによって調整される分散トランザクションでは、MicrosoftMicrosoftデータベース エンジンをリソース マネージャーとして操作できます。Each instance of the SQL Server データベース エンジンSQL Server Database Engine can operate as a resource manager in distributed transactions coordinated by transaction managers, such as MicrosoftMicrosoft Distributed Transaction Coordinator (MS DTC), or other transaction managers that support the Open Group XA specification for distributed transaction processing. 詳細については、MS DTC のドキュメントを参照してください。For more information, see the MS DTC documentation.

複数のデータベースにまたがる 1 つのデータベース エンジンDatabase Engine内のトランザクションは実質的には分散トランザクションです。A transaction within a single instance of the データベース エンジンDatabase Engine that spans two or more databases is actually a distributed transaction. ただし、SQL Server インスタンスは分散トランザクションを内部で処理するため、ユーザーにはローカル トランザクションとして動作しているように見えます。The instance manages the distributed transaction internally; to the user, it operates as a local transaction.

アプリケーションでは、分散トランザクションはローカル トランザクションとほぼ同様に管理されます。At the application, a distributed transaction is managed much the same as a local transaction. トランザクションの終了時に、アプリケーションがトランザクションのコミットまたはロールバックを要求します。At the end of the transaction, the application requests the transaction to be either committed or rolled back. ただし、トランザクション マネージャーが分散コミットを別の方法で管理することによって、ネットワーク障害により一部のリソース マネージャーがトランザクションを正常にコミットし、その一方で他のリソース マネージャーがトランザクションをロールバックするという危険性を最小限に抑える必要があります。A distributed commit must be managed differently by the transaction manager to minimize the risk that a network failure may result in some resource managers successfully committing while others roll back the transaction. このため、コミット処理を準備フェーズとコミット フェーズの 2 フェーズで管理します。これを 2PC (2 フェーズ コミット) と呼びます。This is achieved by managing the commit process in two phases (the prepare phase and the commit phase), which is known as a two-phase commit (2PC).

準備フェーズPrepare phase
トランザクション マネージャーはコミット要求を受け取ると、そのトランザクションに関連するすべてのリソース マネージャーに準備コマンドを送ります。When the transaction manager receives a commit request, it sends a prepare command to all of the resource managers involved in the transaction. 各リソース マネージャーは、トランザクションを持続的にするために必要な処理をすべて実行し、そのトランザクションのログ イメージを含むすべてのバッファーをディスクにフラッシュします。Each resource manager then does everything required to make the transaction durable, and all buffers holding log images for the transaction are flushed to disk. リソース マネージャーの準備フェーズが完了すると、トランザクション マネージャーに準備の成否が通知されます。As each resource manager completes the prepare phase, it returns success or failure of the prepare to the transaction manager. SQL Server 2014SQL Server 2014 では、トランザクションの遅延持続性が導入されました。introduced delayed transaction durability. 遅延持続性トランザクションのコミットは、トランザクションのログ イメージがディスクにフラッシュされる前に行われます。Delayed durable transactions commit before log images for the transaction are flushed to disk. トランザクションの遅延持続性について詳しくは、トランザクションの持続性に関するトピックをご覧ください。For more information on delayed transaction durability see the topic Transaction Durability.

コミット フェーズCommit phase
トランザクション マネージャーは、すべてのリソース マネージャーから準備の正常完了通知を受け取ると、リソース マネージャーにコミット コマンドを送ります。If the transaction manager receives successful prepares from all of the resource managers, it sends commit commands to each resource manager. これにより、リソース マネージャーはコミットを完了できます。The resource managers can then complete the commit. すべてのリソース マネージャーがコミットの正常完了を報告した場合、トランザクション マネージャーは、アプリケーションに成功通知を送ります。If all of the resource managers report a successful commit, the transaction manager then sends a success notification to the application. 準備できなかったことを報告するリソース マネージャーがあった場合、トランザクション マネージャーはすべてのリソース マネージャーにロールバック コマンドを送り、アプリケーションにコミットできなかったことを報告します。If any resource manager reported a failure to prepare, the transaction manager sends a rollback command to each resource manager and indicates the failure of the commit to the application.

データベース エンジンDatabase Engine アプリケーションは分散トランザクションの管理に Transact-SQLTransact-SQL またはデータベース API のどちらも使用できます。applications can manage distributed transactions either through Transact-SQLTransact-SQL or the database API. 詳細については、BEGIN DISTRIBUTED TRANSACTION (Transact-SQL) を参照してください。For more information, see BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

トランザクションの終了Ending Transactions

トランザクションは、COMMIT ステートメント、ROLLBACK ステートメント、または対応する API 関数を使用して終了できます。You can end transactions with either a COMMIT or ROLLBACK statement, or through a corresponding API function.

COMMITCOMMIT
トランザクションが正常に完了した場合、そのトランザクションをコミットします。If a transaction is successful, commit it. COMMIT ステートメントは、トランザクションのすべての変更をデータベースの一部として組み込みます。A COMMIT statement guarantees all of the transaction's modifications are made a permanent part of the database. また、COMMIT により、トランザクションで使用されていたロックなどのリソースが解放されます。A COMMIT also frees resources, such as locks, used by the transaction.

ROLLBACKROLLBACK
トランザクションでエラーが発生した場合やユーザーがトランザクションを取り消す場合は、トランザクションをロールバックします。If an error occurs in a transaction, or if the user decides to cancel the transaction, then roll the transaction back. ROLLBACK ステートメントは、データをトランザクションの開始時点の状態に戻すことにより、トランザクションで行われた変更をすべて元に戻します。A ROLLBACK statement backs out all modifications made in the transaction by returning the data to the state it was in at the start of the transaction. また、ROLLBACK により、トランザクションで保持されていたリソースが解放されます。A ROLLBACK also frees resources held by the transaction.

注意

複数のアクティブな結果セット (MARS) をサポートできる接続では、保留中の実行要求がある間は、API 関数を使用して開始された明示的なトランザクションをコミットできません。Under connections enabled to support multiple active result sets (MARS), an explicit transaction started through an API function cannot be committed while there are pending requests for execution. 実行中の未処理の操作があるときに、このようなトランザクションをコミットしようとするとエラーが発生します。Any attempt to commit this type of transaction while there are outstanding operations running will result in an error.

トランザクション処理中のエラーErrors During Transaction Processing

エラーによりトランザクションを正常に完了できない場合、SQL ServerSQL Server によってトランザクションが自動的にロールバックされ、そのトランザクションで保持されていたすべてのリソースが解放されます。If an error prevents the successful completion of a transaction, SQL ServerSQL Server automatically rolls back the transaction and frees all resources held by the transaction. データベース エンジンDatabase Engineのインスタンスへのクライアントのネットワーク接続が切断された場合、ネットワークからインスタンスにこの切断が通知されると、その接続に対する未処理のトランザクションがすべてロールバックされます。If the client's network connection to an instance of the データベース エンジンDatabase Engine is broken, any outstanding transactions for the connection are rolled back when the network notifies the instance of the break. クライアント アプリケーションが失敗した場合、またはクライアント コンピューターがダウンするか再起動される場合も、接続が切断されます。データベース エンジンDatabase Engineのインスタンスでは、ネットワークからこの切断が通知されると、未処理の接続がすべてロールバックされます。If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and the instance of the データベース エンジンDatabase Engine rolls back any outstanding connections when the network notifies it of the break. クライアントがアプリケーションからログオフした場合、未処理のトランザクションはすべてロールバックされます。If the client logs off the application, any outstanding transactions are rolled back.

バッチでランタイム ステートメント エラー (制約違反など) が発生した場合、データベース エンジンDatabase Engineの既定の動作として、エラーの原因となったステートメントだけがロールバックされます。If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the データベース エンジンDatabase Engine is to roll back only the statement that generated the error. この動作を変更するには、SET XACT_ABORT ステートメントを使用します。You can change this behavior using the SET XACT_ABORT statement. SET XACT_ABORT ON の実行後、任意のランタイム ステートメント エラーにより、現在のトランザクションが自動的にロールバックされます。After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. 構文エラーなどのコンパイル エラーは、SET XACT_ABORT の設定の影響を受けません。Compile errors, such as syntax errors, are not affected by SET XACT_ABORT. 詳しくは、「SET XACT_ABORT (Transact-SQL)」をご覧ください。For more information, see SET XACT_ABORT (Transact-SQL).

エラーが発生した場合は、修正措置 (COMMIT または ROLLBACK) をアプリケーション コードに含める必要があります。When errors occur, corrective action (COMMIT or ROLLBACK) should be included in application code. を含むトランザクションでエラーを処理するための効果的なツールは、Transact-SQLTransact-SQLお試しください.コンス トラクターをキャッチします。One effective tool for handling errors, including those in transactions, is the Transact-SQLTransact-SQL TRY...CATCH construct. トランザクションを含む例について詳しくは、「TRY...CATCH (Transact-SQL)」をご覧ください。For more information with examples that include transactions, see TRY...CATCH (Transact-SQL). 以降でSQL Server 2012SQL Server 2012、THROW ステートメントを使用してみてくださいの CATCH ブロックに、例外と転送の実行を発生させることができます.コンス トラクターをキャッチします。Beginning with SQL Server 2012SQL Server 2012, you can use the THROW statement to raise an exception and transfers execution to a CATCH block of a TRY...CATCH construct. 詳しくは、「THROW (Transact-SQL)」をご覧ください。For more information, see THROW (Transact-SQL).

自動コミット モードでのコンパイル エラーと実行時エラーCompile and Run-time Errors in Autocommit mode

自動コミット モードの場合、データベース エンジンDatabase Engineのインスタンスが 1 つの SQL ステートメントだけでなく、バッチ全体をロールバックしたように見える場合があります。In autocommit mode, it sometimes appears as if an instance of the データベース エンジンDatabase Engine has rolled back an entire batch instead of just one SQL statement. これは、検出されたエラーが実行時エラーではなくコンパイル エラーの場合に発生します。This happens if the error encountered is a compile error, not a run-time error. コンパイル エラーが起きると、データベース エンジンDatabase Engineの実行プランが構築できず、バッチ内のどの処理も実行されません。A compile error prevents the データベース エンジンDatabase Engine from building an execution plan, so nothing in the batch is executed. エラーを生成したステートメントよりも前にあるすべてのステートメントがロールバックされたように見えますが、エラーによりバッチ内のどのステートメントも実行されませんでした。Although it appears that all of the statements before the one generating the error were rolled back, the error prevented anything in the batch from being executed. 次の例では、3 番目のバッチ内のどの INSERT ステートメントも、コンパイル エラーにより実行されません。In the following example, none of the INSERT statements in the third batch are executed because of a compile error. 最初の 2 つの INSERT ステートメントが実行されないので、ロールバックされたように見えます。It appears that the first two INSERT statements are rolled back when they are never executed.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));  
GO  
INSERT INTO TestBatch VALUES (1, 'aaa');  
INSERT INTO TestBatch VALUES (2, 'bbb');  
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.  
GO  
SELECT * FROM TestBatch;  -- Returns no rows.  
GO  

次の例では、3 番目の INSERT ステートメントによって、主キーが重複するという実行時エラーが生成されます。In the following example, the third INSERT statement generates a run-time duplicate primary key error. 最初の 2 つの INSERT ステートメントは正常に完了しコミットされるので、実行時エラーの生成後も有効です。The first two INSERT statements are successful and committed, so they remain after the run-time error.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));  
GO  
INSERT INTO TestBatch VALUES (1, 'aaa');  
INSERT INTO TestBatch VALUES (2, 'bbb');  
INSERT INTO TestBatch VALUES (1, 'ccc');  -- Duplicate key error.  
GO  
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.  
GO  

データベース エンジンDatabase Engineでは、名前の遅延解決を採用しています。つまり、オブジェクト名は実行時まで解決されません。The データベース エンジンDatabase Engine uses deferred name resolution, in which object names are not resolved until execution time. 次の例では、最初の 2 つの INSERT ステートメントは正常に実行されてコミットされ、3 番目の TestBatch ステートメントで存在しないテーブルが参照され、実行時エラーになった後も、最初の 2 行は INSERT テーブル内に残ります。In the following example, the first two INSERT statements are executed and committed, and those two rows remain in the TestBatch table after the third INSERT statement generates a run-time error by referring to a table that does not exist.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));  
GO  
INSERT INTO TestBatch VALUES (1, 'aaa');  
INSERT INTO TestBatch VALUES (2, 'bbb');  
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error.  
GO  
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.  
GO  

上部のリンクに戻る で使用される矢印アイコンこのガイドでArrow icon used with Back to Top link In This Guide

ロックおよび行のバージョン管理の基礎Locking and Row Versioning Basics

SQL Server データベース エンジンSQL Server Database Engineでは、複数のユーザーが同時にアクセスしたときにトランザクションの整合性を保証し、データベースの一貫性を保つため、次のメカニズムを使用します。The SQL Server データベース エンジンSQL Server Database Engine uses the following mechanisms to ensure the integrity of transactions and maintain the consistency of databases when multiple users are accessing data at the same time:

  • ロックLocking

    トランザクションの要求があるたびに、そのトランザクションが依存する行、ページ、テーブルなどの各種リソースにロックがかかります。Each transaction requests locks of different types on the resources, such as rows, pages, or tables, on which the transaction is dependent. ロックをかけると、ロックを要求したトランザクションにとって問題になるようなリソースの変更が行われないように、他のトランザクションがブロックされます。The locks block other transactions from modifying the resources in a way that would cause problems for the transaction requesting the lock. 各トランザクションは、ロックしたリソースに対する依存関係がなくなったときにロックを解放します。Each transaction frees its locks when it no longer has a dependency on the locked resources.

  • 行のバージョン管理Row versioning

    行のバージョン管理に基づく分離レベルが有効になっている場合、変更されたそれぞれの行のバージョンがデータベース エンジンDatabase Engineによって管理されます。When a row versioning-based isolation level is enabled, the データベース エンジンDatabase Engine maintains versions of each row that is modified. すべての読み取りをロックで保護するのではなく、トランザクションで使用する行のバージョンを使い分けて、トランザクションまたはクエリの開始時点の状態のデータを参照できるようにすることをアプリケーションで指定できます。Applications can specify that a transaction use the row versions to view data as it existed at the start of the transaction or query instead of protecting all reads with locks. 行のバージョン管理を使用することで、読み取り操作が原因で他のトランザクションがブロックされる可能性が大幅に減少します。By using row versioning, the chance that a read operation will block other transactions is greatly reduced.

ロックおよび行のバージョン管理を実装することで、ユーザーがコミット前のデータを読み取ったり、複数のユーザーが同時に同一のデータを変更したりする危険性を回避できます。Locking and row versioning prevent users from reading uncommitted data and prevent multiple users from attempting to change the same data at the same time. ロックおよび行のバージョン管理を使用しなければ、クエリを実行してもデータベース内のコミット前のデータが返されて、予期しない結果になる場合があります。Without locking or row versioning, queries executed against that data could produce unexpected results by returning data that has not yet been committed in the database.

アプリケーション側でトランザクション分離レベルを選択して、トランザクションを他のトランザクションによる変更から保護するレベルを定義できます。Applications can choose transaction isolation levels, which define the level of protection for the transaction from modifications made by other transactions. 個別の Transact-SQLTransact-SQL ステートメントに対してテーブルレベルのヒントを指定し、アプリケーションの要件に合わせて動作を細かく調整することもできます。Table-level hints can be specified for individual Transact-SQLTransact-SQL statements to further tailor behavior to fit the requirements of the application.

同時実行データ アクセスの管理Managing Concurrent Data Access

ユーザーが同じリソースに同時にアクセスすることを、リソースへのアクセスを同時実行しているといいます。Users who access a resource at the same time are said to be accessing the resource concurrently. 同時実行データ アクセスには、他のユーザーが現在使用しているリソースを複数のユーザーが変更しようとしたときの悪影響を回避するためのメカニズムが必要です。Concurrent data access requires mechanisms to prevent adverse effects when multiple users try to modify resources that other users are actively using.

コンカレンシーの影響Concurrency Effects

あるユーザーがデータを変更すると、同じデータを同時に読み取ったり変更している他のユーザーに影響します。Users modifying data can affect other users who are reading or modifying the same data at the same time. このようなユーザーを、データに同時アクセスしているユーザーと呼びます。These users are said to be accessing the data concurrently. データ ストレージ システムでコンカレンシーが制御されていない場合は、次のような副作用が生じることがあります。If a data storage system has no concurrency control, users could see the following side effects:

  • 更新が失われるLost updates

    この問題は、複数のトランザクションが同じ行を選択し、その行の元の値を更新すると発生します。Lost updates occur when two or more transactions select the same row and then update the row based on the value originally selected. トランザクションは互いに相手を認識しません。Each transaction is unaware of the other transactions. 更新によって他のトランザクションによる更新データが上書きされれば、そのデータが失われてしまいます。The last update overwrites updates made by the other transactions, which results in lost data.

    たとえば、2 人の編集者が同じ文書のコピーを作成したとします。For example, two editors make an electronic copy of the same document. 2 人が自分のコピーを変更し、その変更内容を保存して、元の文書を上書きしたとします。Each editor changes the copy independently and then saves the changed copy thereby overwriting the original document. 変更したコピーを最後に保存した編集者により、他の編集者が行った変更が上書きされます。The editor who saves the changed copy last overwrites the changes made by the other editor. 1 人の編集者が終了してトランザクションをコミットするまで、他の編集者がファイルにアクセスできないようにすれば、この問題を防ぐことができます。This problem could be avoided if one editor could not access the file until the other editor had finished and committed the transaction.

  • 非コミット依存 (ダーティ リード)Uncommitted dependency (dirty read)

    非コミット依存は、トランザクションによって更新されている行を別のトランザクションが選択するときに発生します。Uncommitted dependency occurs when a second transaction selects a row that is being updated by another transaction. 2 番目のトランザクションが読み取るデータは、まだコミットされていないので、行を更新しているトランザクションによって変更される可能性があります。The second transaction is reading data that has not been committed yet and may be changed by the transaction updating the row.

    たとえば、ある編集者が電子文書を変更しているとします。For example, an editor is making changes to an electronic document. その間、他の編集者はそれまでの変更が反映された文書を受け取って、読者に配布します。During the changes, a second editor takes a copy of the document that includes all the changes made so far, and distributes the document to the intended audience. その後、最初の編集者がそれまでの変更を誤りと判断して取り消し、保存したとします。The first editor then decides the changes made so far are wrong and removes the edits and saves the document. 配布されている文書には取り消した編集内容が含まれているため、既に存在しない文書として扱う必要があります。The distributed document contains edits that no longer exist and should be treated as if they never existed. 最初の編集者が最後に変更を保存してトランザクションをコミットするまで、変更された文書をだれも読み取ることができないようにすれば、この問題を防ぐことができます。This problem could be avoided if no one could read the changed document until the first editor does the final save of modifications and commits the transaction.

  • 不一致分析 (反復不能読み取り)Inconsistent analysis (nonrepeatable read)

    不一致分析は、別のトランザクションが同じ行に数回アクセスし、それぞれの場合で異なったデータを読み取るときに発生します。Inconsistent analysis occurs when a second transaction accesses the same row several times and reads different data each time. 不一致分析は、あるトランザクションがデータを変更している間に別のトランザクションがそのデータを読み取るという点で非コミット依存と似ています。Inconsistent analysis is similar to uncommitted dependency in that another transaction is changing the data that a second transaction is reading. ただし不一致分析の場合、別のトランザクションが読み取るデータは変更を行ったトランザクションによってコミットされています。However, in inconsistent analysis, the data read by the second transaction was committed by the transaction that made the change. また、同じ行が複数回読み取られ、そのたびにトランザクションによって情報が変更されます。そのため、反復不能読み取りと呼ばれます。Also, inconsistent analysis involves multiple reads (two or more) of the same row, and each time the information is changed by another transaction; thus, the term nonrepeatable read.

    たとえば、編集者が同じ文書を 2 回読んだ場合に、1 回目と 2 回目の間に執筆者が文書を変更したとします。For example, an editor reads the same document twice, but between each reading the writer rewrites the document. 編集者が 2 回目に文書を読んだときには、内容が大幅に変更されていました。When the editor reads the document for the second time, it has changed. 最初に読んだ内容と同じものは再現されません。The original read was not repeatable. 編集者が最終的に読み終わるまで執筆者が文書を変更できないようにすると、この問題を防ぐことができます。This problem could be avoided if the writer could not change the document until the editor has finished reading it for the last time.

  • ファントム読み取りPhantom reads

    ファントム読み取りとは、2 つの同じクエリが実行されたときに 2 番目のクエリによって返された行のコレクションが異なる場合に発生する状況です。A phantom read is a situation that occurs when two identical queries are executed and the collection of rows returned by the second query is different. 以下の例に、この状況がどのように発生するかを示します。The example below shows how this may occur. 次の 2 つのトランザクションが同時に実行されると仮定します。Assume the two transactions below are executing at the same time. 最初のトランザクションにある 2 つの SELECT ステートメントは、異なる結果を返す可能性があります。これは、これら 2 つのステートメントで使用されるデータが 2 番目のトランザクションの INSERT ステートメントで変更されるためです。The two SELECT statements in the first transaction may return different results because the INSERT statement in the second transaction changes the data used by both.

    --Transaction 1  
    BEGIN TRAN;  
    SELECT ID FROM dbo.employee  
    WHERE ID > 5 and ID < 10;  
    --The INSERT statement from the second transaction occurs here.  
    SELECT ID FROM dbo.employee  
    WHERE ID > 5 and ID < 10;  
    COMMIT;  
    
    
    --Transaction 2  
    BEGIN TRAN;  
    INSERT INTO dbo.employee  
       SET name = 'New' WHERE ID = 5;  
    COMMIT;   
    
  • 行の更新による読み取りの欠落および重複Missing and double reads caused by row updates

    • 更新された行の欠落または更新された行の複数回表示Missing a updated row or seeing an updated row multiple times

      READ UNCOMMITTED レベルで実行しているトランザクションは、他のトランザクションが、現在のトランザクションによって読み取られるデータの変更を防ぐために共有ロックを発行しません。Transactions that are running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ COMMITTED レベルで実行されるトランザクションでは共有ロックが取得されますが、行ロックまたはページ ロックは行が読み取られた時点で解放されます。Transactions that are running at the READ COMMITTED level do issue shared locks, but the row or page locks are released after the row is read. どちらの場合も、インデックスをスキャンしているときに、読み取り中の行のインデックス キー列が他のユーザーによって変更された場合は、このキーの変更によって行がスキャン前の位置に移動すると、その行は再び出現する可能性があります。In either case, when you are scanning an index, if another user changes the index key column of the row during your read, the row might appear again if the key change moved the row to a position ahead of your scan. 同様に、キーの変更によって、既に読み取ったインデックスの位置に行が移動すると、その行は出現しない可能性があります。Similarly, the row might not appear if the key change moved the row to a position in the index that you had already read. この問題を回避するには、SERIALIZABLE または HOLDLOCK のヒントや、行のバージョン管理を使用します。To avoid this, use the SERIALIZABLE or HOLDLOCK hint, or row versioning. 詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。For more information, see Table Hints (Transact-SQL).

    • 更新の対象でなかった 1 行または複数行の欠落Missing one or more rows that were not the target of update

      使用する場合、READ UNCOMMITTED、クエリで割り当て順序スキャン (IAM ページを使用) を使用して行を読み取る場合、別のトランザクションが、ページ分割が発生すると行が欠落する可能性があります。When you are using READ UNCOMMITTED, if your query reads rows using an allocation order scan (using IAM pages), you might miss rows if another transaction is causing a page split. READ COMMITTED を使用している場合は、ページ分割時にテーブル ロックが保持されているため、この問題は発生しません。また、クラスター化インデックスを含まないテーブルの場合は、更新時にページ分割が行われることはないため、この問題は発生しません。This cannot occur when you are using read committed because a table lock is held during a page split and does not happen if the table does not have a clustered index, because updates do not cause page splits.

コンカレンシーの種類Types of Concurrency

多くのユーザーが同時にデータベースのデータを変更する場合、あるユーザーによる変更が別のユーザーによる変更に悪影響を及ぼすことを防ぐために、何らかの制御機構を実装する必要があります。When many people attempt to modify data in a database at the same time, a system of controls must be implemented so that modifications made by one person do not adversely affect those of another person. この制御機構をコンカレンシー制御と呼びます。This is called concurrency control.

コンカレンシー制御の原理は、制御方法によって次の 2 種類に分類されます。Concurrency control theory has two classifications for the methods of instituting concurrency control:

  • ペシミスティック コンカレンシーPessimistic concurrency control

    ロック機構により、ユーザーが他のユーザーに影響するデータの変更を行うことを防ぎます。A system of locks prevents users from modifying data in a way that affects other users. あるユーザーによる操作でロックがかかると、そのユーザーがロックを解放するまで他のユーザーはロックと競合する操作を実行できません。After a user performs an action that causes a lock to be applied, other users cannot perform actions that would conflict with the lock until the owner releases it. この方式は、データの競合が多く、コンカレンシーによる競合が発生した場合にトランザクションをロールバックするコストに比べデータをロックして保護するコストの方が低い環境で主に使用されるので、ペシミスティック コンカレンシー制御と呼ばれます。This is called pessimistic control because it is mainly used in environments where there is high contention for data, where the cost of protecting data with locks is less than the cost of rolling back transactions if concurrency conflicts occur.

  • オプティミスティック コンカレンシーOptimistic concurrency control

    オプティミスティック コンカレンシーでは、データを読み取る時点ではロックがかかりません。In optimistic concurrency control, users do not lock data when they read it. データを更新するときに、そのユーザーが読み取ってから他のユーザーによる変更がなかったかが確認されます。When a user updates data, the system checks to see if another user changed the data after it was read. 他のユーザーがデータを更新していた場合、エラーが発生します。If another user updated the data, an error is raised. 通常は、エラーが報告されたユーザーがトランザクションをロールバックして再実行します。Typically, the user receiving the error rolls back the transaction and starts over. この方式は、データの競合が少なく、データを読み取るときにロックして保護するコストに比べトランザクションを必要に応じてロールバックするコストの方が低い環境で主に使用されるので、オプティミスティック同時実行制御と呼ばれます。This is called optimistic because it is mainly used in environments where there is low contention for data, and where the cost of occasionally rolling back a transaction is lower than the cost of locking data when read.

SQL ServerSQL Server ではさまざまなコンカレンシー制御がサポートされています。supports a range of concurrency control. 接続のトランザクション分離レベルやカーソルのコンカレンシー オプションを選択することで、コンカレンシー制御の種類を指定できます。Users specify the type of concurrency control by selecting transaction isolation levels for connections or concurrency options on cursors. これらの属性を選択するには、Transact-SQLTransact-SQL ステートメントを使用するか、ADO、ADO.NET、OLE DB、ODBC などのデータベース API (アプリケーション プログラミング インターフェイス) のプロパティおよび属性を指定します。These attributes can be defined using Transact-SQLTransact-SQL statements, or through the properties and attributes of database application programming interfaces (APIs) such as ADO, ADO.NET, OLE DB, and ODBC.

データベース エンジンにおける分離レベルIsolation Levels in the Database Engine

各トランザクションでは、別のトランザクションによって行われたリソースまたはデータの変更から特定のトランザクションを分離する際の程度を定義する分離レベルを指定します。Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. 分離レベルは、ダーティ リードやファントム読み取りなど、コンカレンシーの副作用が許可されるのかという観点で定義されます。Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed.

トランザクション分離レベルでは次のことを制御しています。Transaction isolation levels control:

  • データの読み取り時にロックを獲得するかどうか、要求されるロックの種類。Whether locks are taken when data is read, and what type of locks are requested.

  • 読み取りロックの保持期間。How long the read locks are held.

  • 別のトランザクションによって変更された行を参照している読み取り操作で、次のことを行うかどうか。Whether a read operation referencing rows modified by another transaction:

    • その行に対する排他ロックが解放されるまでブロックする。Blocks until the exclusive lock on the row is freed.

    • ステートメントまたはトランザクションの開始時に存在していた行の、コミット済みのバージョンを取得する。Retrieves the committed version of the row that existed at the time the statement or transaction started.

    • コミットされていないデータ変更を読み取る。Reads the uncommitted data modification.

重要

トランザクション分離レベルを選択しても、データ変更を保護するために獲得したロックは影響を受けません。Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. トランザクションでは、設定されたトランザクション分離レベルに関係なく、常に、そのトランザクションで変更するデータについて排他ロックを獲得し、トランザクションが完了するまでそのロックを保持します。A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. トランザクション分離レベルでは主に、読み取り操作に対して、他のトランザクションによって行われる変更の影響からの保護レベルを定義します。For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions.

分離レベルが低いほど多くのユーザーが同時にデータにアクセスできるようになりますが、ユーザーに影響が及ぶ可能性があるコンカレンシーの影響 (ダーティ リードや更新データの喪失) の種類が多くなります。A lower isolation level increases the ability of many users to access data at the same time, but increases the number of concurrency effects (such as dirty reads or lost updates) users might encounter. 反対に、分離レベルが高いほど、ユーザーに影響が及ぶ可能性があるコンカレンシーの影響の種類は減りますが、必要なシステム リソースが増加し、あるトランザクションによって別のトランザクションがブロックされる状況も多くなります。Conversely, a higher isolation level reduces the types of concurrency effects that users may encounter, but requires more system resources and increases the chances that one transaction will block another. 適切な分離レベルの選択は、アプリケーションのデータ整合性の要件と各分離レベルのオーバーヘッドとのバランスによって決まります。Choosing the appropriate isolation level depends on balancing the data integrity requirements of the application against the overhead of each isolation level. 最も高い分離レベルの SERIALIZABLE は、トランザクションで読み取り操作が繰り返し実行されるたびに、そのトランザクションで完全に同じデータが取得されることを保証します。このことの実現には、マルチユーザー システムにおいて他のユーザーが影響を受ける可能性が高いロック レベルが適用されています。The highest isolation level, serializable, guarantees that a transaction will retrieve exactly the same data every time it repeats a read operation, but it does this by performing a level of locking that is likely to impact other users in multi-user systems. 最も低い分離レベルは READ UNCOMMITTED ですが、このレベルでは、他のトランザクションによって変更され、まだコミットされていないデータを取得する場合があります。The lowest isolation level, read uncommitted, may retrieve data that has been modified but not committed by other transactions. READ UNCOMMITTED レベルではコンカレンシーのすべての副作用が発生する可能性がありますが、このレベルでは読み取りロックやバージョン管理が行われないのでオーバーヘッドが最小限に抑えられます。All of the concurrency side effects can happen in read uncommitted, but there is no read locking or versioning, so overhead is minimized.

データベース エンジンの分離レベルDatabase Engine Isolation Levels

ISO 標準では、次に示す分離レベルが定義されています。それらのすべてが SQL Server データベース エンジンSQL Server Database Engineでサポートされます。The ISO standard defines the following isolation levels, all of which are supported by the SQL Server データベース エンジンSQL Server Database Engine:

[分離レベル]Isolation Level 定義Definition
READ UNCOMMITTEDRead uncommitted 物理的に破損したデータを読み取らないことのみが保証されるようにトランザクションを分離する、最も低い分離レベル。The lowest isolation level where transactions are isolated only enough to ensure that physically corrupt data is not read. このレベルではダーティ リードが許可されるため、トランザクションで行われたコミットされていない変更を、他のトランザクションで読み取ることが可能です。In this level, dirty reads are allowed, so one transaction may see not-yet-committed changes made by other transactions.
READ COMMITTEDRead committed トランザクションは、別のトランザクションが以前に読み取った (変更されていない) データを読み取ることができるので、最初のトランザクションが完了するまで待機する必要がありません。Allows a transaction to read data previously read (not modified) by another transaction without waiting for the first transaction to complete. データベース エンジンDatabase Engineは、(選択されたデータに対して取得された) 書き込みロックをトランザクションの終わりまで保持しますが、読み取りロックは SELECT 操作の実行が終わると解放します。The データベース エンジンDatabase Engine keeps write locks (acquired on selected data) until the end of the transaction, but read locks are released as soon as the SELECT operation is performed. これはデータベース エンジンDatabase Engineの既定のレベルです。This is the データベース エンジンDatabase Engine default level.
REPEATABLE READRepeatable read データベース エンジンDatabase Engineは、選択されたデータに対して取得された読み取り/書き込みロックをトランザクションの終わりまで保持します。The データベース エンジンDatabase Engine keeps read and write locks that are acquired on selected data until the end of the transaction. ただし、範囲ロックが管理されないため、ファントム読み取りが発生する可能性はあります。However, because range-locks are not managed, phantom reads can occur.
SerializableSerializable 各トランザクションが完全に分離される、最も高い分離レベル。The highest level where transactions are completely isolated from one another. データベース エンジンDatabase Engineは、選択されたデータに対して取得された読み取り/書き込みロックを保持し、トランザクションの終わりに開放します。The データベース エンジンDatabase Engine keeps read and write locks acquired on selected data to be released at the end of the transaction. 範囲指定付きの WHERE 句を SELECT 操作に使用する場合には、特にファントム読み取りを回避するために範囲ロックが取得されます。Range-locks are acquired when a SELECT operation uses a ranged WHERE clause, especially to avoid phantom reads.

注: SERIALIZABLE 分離レベルが要求された場合、レプリケートされたテーブルの DDL 操作やトランザクションは失敗することがあります。Note: DDL operations and transactions on replicated tables may fail when serializable isolation level is requested. レプリケーションのクエリで使用されるヒントは、SERIALIZABLE 分離レベルと互換性がない可能性があるためです。This is because replication queries use hints that may be incompatible with serializable isolation level.

また、SQL ServerSQL Server では、行のバージョン管理を使用する 2 つの追加トランザクション分離レベルがサポートされます。SQL ServerSQL Server also supports two additional transaction isolation levels that use row versioning. 1 つは、READ COMMITTED 分離の実装であり、1 つはトランザクション分離レベルである "スナップショット" です。One is an implementation of read committed isolation, and one is a transaction isolation level, snapshot.

行のバージョン管理分離レベルRow Versioning Isolation Level 定義Definition
READ COMMITTED SNAPSHOTRead Committed Snapshot READ_COMMITTED_SNAPSHOT データベース オプションが ON に設定されている場合、READ COMMITTED 分離では、行のバージョン管理を使用して読み取りの一貫性をステートメント レベルで維持します。When the READ_COMMITTED_SNAPSHOT database option is set ON, read committed isolation uses row versioning to provide statement-level read consistency. 読み取り操作にはテーブル レベルの SCH-S ロックだけが必要であり、ページ ロックや行ロックは不要です。Read operations require only SCH-S table level locks and no page or row locks. つまりデータベース エンジンでは行のバージョン管理が使用され、各ステートメントでは、トランザクション全体で一貫性のあるデータのスナップショットが使用されます。このスナップショットは、ステートメント開始時点に存在したデータのスナップショットです。That is, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. ただし、他のトランザクションによるデータ更新を防ぐためのロックは使用されません。Locks are not used to protect the data from updates by other transactions. ユーザー定義関数から返されるデータは、そのユーザー定義関数を含んでいるステートメントの開始後にコミットされたものである可能性があります。A user-defined function can return data that was committed after the time the statement containing the UDF began.

READ_COMMITTED_SNAPSHOT データベース オプションが OFF (既定) に設定されている場合は、READ_COMMITTED 分離に共有ロックが使用されます。これにより、現在のトランザクションでの読み取り操作中に他のトランザクションによって行が変更されるのを防ぐことができます。When the READ_COMMITTED_SNAPSHOT database option is set OFF, which is the default setting, read committed isolation uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. また、ステートメントが他のトランザクションで変更された行を読み取ろうとしても、そのトランザクションが完了するまでステートメントはブロックされます。The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. どちらの実装も READ COMMITTED 分離の ISO 定義に準拠しています。Both implementations meet the ISO definition of read committed isolation.
スナップショットSnapshot スナップショット分離レベルでは、行のバージョン管理を使用して読み取りの一貫性をトランザクション レベルで維持します。The snapshot isolation level uses row versioning to provide transaction-level read consistency. 読み取り操作では、ページ ロックも行ロックも獲得しません。テーブル レベルの SCH-S ロックだけを獲得します。Read operations acquire no page or row locks; only SCH-S table locks are acquired. 別のトランザクションによって変更された行を読み取るときは、トランザクションの開始時に存在していた行のバージョンを取得します。When reading rows modified by another transaction, they retrieve the version of the row that existed when the transaction started. データベースに対してスナップショット分離を使用できるのは、ALLOW_SNAPSHOT_ISOLATION データベース オプションが ON に設定されている場合のみです。You can only use Snapshot isolation against a database when the ALLOW_SNAPSHOT_ISOLATION database option is set ON. 既定では、ユーザー データベースのこのオプションは OFF に設定されています。By default, this option is set OFF for user databases.

注意: SQL ServerSQL Server では、メタデータのバージョン管理はサポートされません。Note: SQL ServerSQL Server does not support versioning of metadata. そのため、スナップショット分離下で実行されている明示的なトランザクションでは、実行できる DDL 操作に制限があります。For this reason, there are restrictions on what DDL operations can be performed in an explicit transaction that is running under snapshot isolation. スナップショット分離下では、BEGIN TRANSACTION ステートメントの後に、ALTER TABLE、CREATE INDEX、CREATE XML INDEX、ALTER INDEX、DROP INDEX、DBCC REINDEX、ALTER PARTITION FUNCTION、ALTER PARTITION SCHEME などの DDL ステートメントを実行することはできません。共通言語ランタイム (CLR) の DDL ステートメントも同様です。The following DDL statements are not permitted under snapshot isolation after a BEGIN TRANSACTION statement: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, or any common language runtime (CLR) DDL statement. 暗黙的なトランザクション内でスナップショット分離を使用している場合は、これらのステートメントが許可されます。These statements are permitted when you are using snapshot isolation within implicit transactions. 暗黙的なトランザクションとは、原則的に、DDL ステートメントでもスナップショット分離のセマンティックを適用することのできる単一のステートメントをいいます。An implicit transaction, by definition, is a single statement that makes it possible to enforce the semantics of snapshot isolation, even with DDL statements. この原則に反した場合、エラー 3961 が発生し、"スナップショット分離トランザクションがデータベースで失敗しました ' %. * ls' オブジェクトがステートメントによってアクセスされるため、によって変更されたは、このトランザクションが開始されてから別の同時トランザクションの DDL ステートメント。Violations of this principle can cause error 3961: "Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. メタデータはバージョン管理されないため、この操作は許可されません。It is not allowed because the metadata is not versioned. メタデータに対する同時更新は、スナップショット分離と組み合わせると一貫性を損なう結果になる可能性があります。" というメッセージが表示されます。A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation."

次の表に、各分離レベルで許容されているコンカレンシーの副作用を示します。The following table shows the concurrency side effects enabled by the different isolation levels.

分離レベルIsolation level ダーティ リードDirty read 反復不可能読み取りNonrepeatable read ファントムPhantom
READ UNCOMMITTEDRead uncommitted はいYes [はい]Yes はいYes
READ COMMITTEDRead committed いいえNo はいYes はいYes
REPEATABLE READRepeatable read いいえNo いいえNo はいYes
SNAPSHOTSnapshot いいえNo いいえNo いいえNo
SERIALIZABLESerializable いいえNo いいえNo いいえNo

各トランザクション分離レベルによって制御される特定のロックまたは行のバージョン管理の種類について詳しくは、「SET TRANSACTION ISOLATION LEVEL (Transact-SQL)」をご覧ください。For more information about the specific types of locking or row versioning controlled by each transaction isolation level, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

トランザクションの分離レベルは、Transact-SQLTransact-SQL を使用するかデータベース API を使用して設定できます。Transaction isolation levels can be set using Transact-SQLTransact-SQL or through a database API.

Transact-SQLTransact-SQL

Transact-SQLTransact-SQL スクリプトでは、SET TRANSACTION ISOLATION LEVEL ステートメントが使用されます。scripts use the SET TRANSACTION ISOLATION LEVEL statement.

ADO (ADO)ADO
ADO アプリケーションでは、Connection オブジェクトの IsolationLevel プロパティが adXactReadUncommitted、adXactReadCommitted、adXactRepeatableRead、または adXactReadSerializable に設定されます。ADO applications set the IsolationLevel property of the Connection object to adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead, or adXactReadSerializable.

ADO.NETADO.NET
System.Data.SqlClient マネージド名前空間を使用している ADO.NET アプリケーションは、SqlConnection.BeginTransaction メソッドを呼び出し、IsolationLevel オプションを Unspecified、Chaos、ReadUncommitted、ReadCommitted、RepeatableRead、Serializable、および Snapshot に設定することができます。ADO.NET applications using the System.Data.SqlClient managed namespace can call the SqlConnection.BeginTransaction method and set the IsolationLevel option to Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable, and Snapshot.

OLE DB (OLE DB)OLE DB
トランザクションを開始するとき、OLE DB を使用するアプリケーションは、isoLevel を ISOLATIONLEVEL_READUNCOMMITTED、ISOLATIONLEVEL_READCOMMITTED、ISOLATIONLEVEL_REPEATABLEREAD、ISOLATIONLEVEL_SNAPSHOT、または ISOLATIONLEVEL_SERIALIZABLE に設定して、ITransactionLocal::StartTransaction を呼び出します。When starting a transaction, applications using OLE DB call ITransactionLocal::StartTransaction with isoLevel set to ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOT, or ISOLATIONLEVEL_SERIALIZABLE.

トランザクション分離レベルを自動コミット モードで指定する場合、OLE DB アプリケーションは、DBPROPSET_SESSION プロパティの DBPROP_SESS_AUTOCOMMITISOLEVELS を DBPROPVAL_TI_CHAOS、DBPROPVAL_TI_READUNCOMMITTED、DBPROPVAL_TI_BROWSE、DBPROPVAL_TI_CURSORSTABILITY、DBPROPVAL_TI_READCOMMITTED、DBPROPVAL_TI_REPEATABLEREAD、DBPROPVAL_TI_SERIALIZABLE、DBPROPVAL_TI_ISOLATED、または DBPROPVAL_TI_SNAPSHOT に設定できます。When specifying the transaction isolation level in autocommit mode, OLE DB applications can set the DBPROPSET_SESSION property DBPROP_SESS_AUTOCOMMITISOLEVELS to DBPROPVAL_TI_CHAOS, DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, DBPROPVAL_TI_ISOLATED, or DBPROPVAL_TI_SNAPSHOT.

ODBCODBC
ODBC アプリケーションは、Attribute を SQL_ATTR_TXN_ISOLATION に設定し、ValuePtr を SQL_TXN_READ_UNCOMMITTED、SQL_TXN_READ_COMMITTED、SQL_TXN_REPEATABLE_READ、または SQL_TXN_SERIALIZABLE に設定して、SQLSetConnectAttr を呼び出します。ODBC applications call SQLSetConnectAttr with Attribute set to SQL_ATTR_TXN_ISOLATION and ValuePtr set to SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ, or SQL_TXN_SERIALIZABLE.

スナップショット トランザクションでは、アプリケーションは、Attribute を SQL_COPT_SS_TXN_ISOLATION に設定し、ValuePtr を SQL_TXN_SS_SNAPSHOT に設定して、SQLSetConnectAttr を呼び出します。For snapshot transactions, applications call SQLSetConnectAttr with Attribute set to SQL_COPT_SS_TXN_ISOLATION and ValuePtr set to SQL_TXN_SS_SNAPSHOT. スナップショット トランザクションは、SQL_COPT_SS_TXN_ISOLATION または SQL_ATTR_TXN_ISOLATION のいずれかを使用して取得できます。A snapshot transaction can be retrieved using either SQL_COPT_SS_TXN_ISOLATION or SQL_ATTR_TXN_ISOLATION.

上部のリンクに戻る で使用される矢印アイコンこのガイドでArrow icon used with Back to Top link In This Guide

データベース エンジンのロックLocking in the Database Engine

SQL Server データベース エンジンSQL Server Database Engineでは、"ロック" というメカニズムを使用して、複数のユーザーによる同じデータへの同時アクセスが同期されます。Locking is a mechanism used by the SQL Server データベース エンジンSQL Server Database Engine to synchronize access by multiple users to the same piece of data at the same time.

トランザクションでは、データの読み取りや変更など、データの現在の状態に対する依存関係を取得する前に、そのトランザクションを、別のトランザクションで同じデータが変更される影響から保護する必要があります。Before a transaction acquires a dependency on the current state of a piece of data, such as by reading or modifying the data, it must protect itself from the effects of another transaction modifying the same data. トランザクションでは、データのロックを要求することにより、この問題に対処しています。The transaction does this by requesting a lock on the piece of data. ロックには、共有ロックや排他ロックなど複数のモードがあります。Locks have different modes, such as shared or exclusive. ロック モードは、データに対するトランザクションの依存関係の度合いを定義します。The lock mode defines the level of dependency the transaction has on the data. 別のトランザクションに既に許可されているロックのモードと競合するロックを、トランザクションに許可することはできません。No transaction can be granted a lock that would conflict with the mode of a lock already granted on that data to another transaction. トランザクションで、あるデータに対して既に許可されたロックと競合するロックのモードが要求された場合、データベース エンジンDatabase Engineのインスタンスにより、既に許可されたロックが解放されるまで、要求を行ったトランザクションは保留されます。If a transaction requests a lock mode that conflicts with a lock that has already been granted on the same data, the instance of the データベース エンジンDatabase Engine will pause the requesting transaction until the first lock is released.

トランザクションでデータが変更される場合、そのトランザクションでは、トランザクションが完了するまでロックを保持して、データの変更を保護します。When a transaction modifies a piece of data, it holds the lock protecting the modification until the end of the transaction. トランザクションが読み取り操作を保護するために取得したロックの保持期間は、トランザクションの分離レベルの設定により異なります。How long a transaction holds the locks acquired to protect read operations depends on the transaction isolation level setting. トランザクションで保持されているすべてのロックは、トランザクションが完了 (コミットまたはロールバック) した時点で解放されます。All locks held by a transaction are released when the transaction completes (either commits or rolls back).

通常、アプリケーションから、ロックが直接要求されることはありません。Applications do not typically request locks directly. ロックは、データベース エンジンDatabase Engineのロック マネージャーにより、内部で管理されます。Locks are managed internally by a part of the データベース エンジンDatabase Engine called the lock manager. データベース エンジンDatabase Engineのインスタンスで Transact-SQLTransact-SQL ステートメントが処理されると、データベース エンジンDatabase Engineのクエリ プロセッサにより、アクセスするリソースが判断されます。When an instance of the データベース エンジンDatabase Engine processes a Transact-SQLTransact-SQL statement, the データベース エンジンDatabase Engine query processor determines which resources are to be accessed. クエリ プロセッサでは、アクセスの種類とトランザクションの分離レベルの設定に基づいて、各リソースを保護するために必要なロックの種類が決定されます。The query processor determines what types of locks are required to protect each resource based on the type of access and the transaction isolation level setting. その後、クエリ プロセッサから、ロック マネージャーに適切なロックが要求されます。The query processor then requests the appropriate locks from the lock manager. ロック マネージャーでは、別のトランザクションで保持されているロックに競合するロックがない場合、要求されたロックを許可します。The lock manager grants the locks if there are no conflicting locks held by other transactions.

ロックの粒度と階層Lock Granularity and Hierarchies

SQL Server データベース エンジンSQL Server Database Engineでは、複数粒度のロックがサポートされており、種類の異なるリソースをトランザクションでロックできます。The SQL Server データベース エンジンSQL Server Database Engine has multigranular locking that allows different types of resources to be locked by a transaction. データベース エンジンDatabase Engineにより、タスクに適したレベルでリソースが自動的にロックされるので、最小限のコストでロックされます。To minimize the cost of locking, the データベース エンジンDatabase Engine locks resources automatically at a level appropriate to the task. ロックの粒度を細かくすると (行単位など)、コンカレンシーが高くなります。ただし、多くの行をロックすると、ロック数が増えるのでオーバーヘッドが増大します。Locking at a smaller granularity, such as rows, increases concurrency but has a higher overhead because more locks must be held if many rows are locked. ロックの粒度を粗くすると (テーブル単位など)、テーブル全体がロックされるので、他のトランザクションがそのテーブルにアクセスできなくなります。このため、コンカレンシーが低下します。Locking at a larger granularity, such as tables, are expensive in terms of concurrency because locking an entire table restricts access to any part of the table by other transactions. ただし、ロック数が減るので、オーバーヘッドは減少します。However, it has a lower overhead because fewer locks are being maintained.

データベース エンジンDatabase Engineではリソースを完全に保護するために、多くの場合、レベルの異なる粒度でロックを取得する必要が生じます。The データベース エンジンDatabase Engine often has to acquire locks at multiple levels of granularity to fully protect a resource. この複数レベルの粒度でのロックのグループを、ロック階層と呼びます。This group of locks at multiple levels of granularity is called a lock hierarchy. たとえば、データベース エンジンDatabase Engineのインスタンスは、インデックスの読み取りを完全に保護するために、行の共有ロックと、ページやテーブルのインテント共有ロックを取得しなければならない場合があります。For example, to fully protect a read of an index, an instance of the データベース エンジンDatabase Engine may have to acquire share locks on rows and intent share locks on the pages and table.

次の表に、データベース エンジンDatabase Engineがロックできるリソースを示します。The following table shows the resources that the データベース エンジンDatabase Engine can lock.

リソースResource 説明Description
RIDRID ヒープ内の 1 行をロックするのに使用される行識別子 (ROWID)。A row identifier used to lock a single row within a heap.
KEYKEY シリアル化可能なトランザクションのキー範囲の保護に使用されるインデックス内の行ロック。A row lock within an index used to protect key ranges in serializable transactions.
PAGEPAGE データ ページやインデックス ページなど、データベース内の 8 KB のページ。An 8-kilobyte (KB) page in a database, such as data or index pages.
EXTENTEXTENT データ ページやインデックス ページなど、連続した 8 ページのグループ。A contiguous group of eight pages, such as data or index pages.
HoBTHoBT ヒープまたは B ツリー。A heap or B-tree. B ツリー (インデックス)、またはクラスター化インデックスのないテーブルのヒープ データ ページを保護するロックです。A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index.
TABLETABLE すべてのデータとインデックスを含むテーブル全体。The entire table, including all data and indexes.
FILEFILE データベース ファイル。A database file.
APPLICATIONAPPLICATION アプリケーションにより指定されたリソース。An application-specified resource.
METADATAMETADATA メタデータのロック。Metadata locks.
ALLOCATION_UNITALLOCATION_UNIT アロケーション ユニット。An allocation unit.
DATABASEDATABASE データベース全体。The entire database.

注意

HoBT ロックおよび TABLE ロックは、ALTER TABLE の LOCK_ESCALATION オプションの影響を受ける可能性があります。HoBT and TABLE locks can be affected by the LOCK_ESCALATION option of ALTER TABLE.

ロック モードLock Modes

SQL Server データベース エンジンSQL Server Database Engineは、さまざまなロック モードを使用してリソースをロックします。これにより、同時に実行されている複数のトランザクションがリソースにアクセスする方法が決まります。The SQL Server データベース エンジンSQL Server Database Engine locks resources using different lock modes that determine how the resources can be accessed by concurrent transactions.

次の表に、データベース エンジンDatabase Engineのリソース ロック モードを示します。The following table shows the resource lock modes that the データベース エンジンDatabase Engine uses.

ロック モードLock mode 説明Description
共有 (S)Shared (S) SELECT ステートメントなど、データの変更や更新を伴わない読み取り操作で使用します。Used for read operations that do not change or update data, such as a SELECT statement.
更新 (U)Update (U) 更新可能なリソースに使用します。Used on resources that can be updated. 複数のセッションがリソースを読み取り、ロックして、後で更新する可能性がある場合に発生する一般的な形式のデッドロックを防ぎます。Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
排他 (X)Exclusive (X) INSERT、UPDATE、DELETE などのデータ変更操作に使用します。Used for data-modification operations, such as INSERT, UPDATE, or DELETE. 複数の更新操作により 1 つのリソースを同時に更新しないようにするためのロック モードです。Ensures that multiple updates cannot be made to the same resource at the same time.
インテントIntent ロック階層を設定するのに使用します。Used to establish a lock hierarchy. インテント ロックの種類にはインテント共有 (IS)、インテント排他 (IX)、およびインテント排他付き共有 (SIX) があります。The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
スキーマSchema テーブルのスキーマに依存する操作を行うときに使用します。Used when an operation dependent on the schema of a table is executing. スキーマ ロックの種類には、スキーマ修正 (Sch-M) およびスキーマ安定度 (Sch-S) があります。The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).
一括更新 (BU)Bulk Update (BU) ときに使用では、テーブルにデータのコピーを一括とTABLOCKヒントが指定されています。Used when bulk copying data into a table and the TABLOCK hint is specified.
キー範囲Key-range トランザクション分離レベルが SERIALIZABLE のとき、クエリにより読み取られる行の範囲を保護します。Protects the range of rows read by a query when using the serializable transaction isolation level. シリアル化可能トランザクションのクエリを再度実行した場合に対象となるような行を、他のトランザクションは挿入できなくなります。Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

共有ロックShared Locks

共有 (S) ロックを設定すると、同時に実行されている複数のトランザクションがペシミスティック コンカレンシー制御の下でリソースの読み取り (SELECT) を行います。Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control. 他のトランザクションは、リソースに共有 (S) ロックがかけられている間はデータを変更できません。No other transactions can modify the data while shared (S) locks exist on the resource. リソースにかけられている共有 (S) ロックは、読み取りが完了するとすぐに解除されます。ただし、トランザクションの分離レベルが REPEATABLE READ 以上に設定されている場合や、トランザクションの間、ロック ヒントを使用して共有 (S) ロックを保つ場合を除きます。Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.

更新ロックUpdate Locks

更新 (U) ロックは、一般的な形式のデッドロックを防ぎます。Update (U) locks prevent a common form of deadlock. REPEATABLE READ または SERIALIZABLE のトランザクションは、データを読み取るときにリソース (ページまたは行) に共有 (S) ロックをかけます。その後、行を変更しますが、そのときにロックを排他 (X) ロックに変換する必要があります。In a repeatable read or serializable transaction, the transaction reads data, acquiring a shared (S) lock on the resource (page or row), and then modifies the data, which requires lock conversion to an exclusive (X) lock. 2 つのトランザクションが 1 つのリソースに対して共有ロックをかけデータを同時に更新する場合、一方のトランザクションは排他 (X) ロックへの変換を試みます。If two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently, one transaction attempts the lock conversion to an exclusive (X) lock. 一方のトランザクションの排他ロックは、もう一方のトランザクションの共有ロックとは両立しないので、共有ロックから排他ロックへの変換が待機状態になります。つまり、ロック待機となります。The shared-mode-to-exclusive lock conversion must wait because the exclusive lock for one transaction is not compatible with the shared-mode lock of the other transaction; a lock wait occurs. もう一方のトランザクションも更新のために排他 (X) ロックの取得を試みます。The second transaction attempts to acquire an exclusive (X) lock for its update. この場合、2 つのトランザクションが排他 (X) ロックへの変換を行っており、相手方のトランザクションが共有ロックを解除するのを待っている状態なので、デッドロックが発生します。Because both transactions are converting to exclusive (X) locks, and they are each waiting for the other transaction to release its shared-mode lock, a deadlock occurs.

このような潜在的なデッドロックの問題を解決するには、更新 (U) ロックを使用します。To avoid this potential deadlock problem, update (U) locks are used. 更新 (U) ロックでは、1 つのリソースを一度にロックできるトランザクションは、1 つだけです。Only one transaction can obtain an update (U) lock to a resource at a time. トランザクションがリソースを変更する場合に更新 (U) ロックが排他 (X) ロックに変換されます。If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock.

排他ロックExclusive Locks

排他 (X) ロックは、同時に実行されている複数のトランザクションが同じリソースにアクセスすることを防ぎます。Exclusive (X) locks prevent access to a resource by concurrent transactions. 排他 (X) ロックをかけたトランザクション以外はデータを変更できませんが、NOLOCK ヒントまたは READ UNCOMMITTED 分離レベルが指定されている場合に限り、読み取り操作は行うことができます。With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level.

INSERT、UPDATE、DELETE などのデータ変更ステートメントの中では、変更操作と読み取り操作が組み合わせて使用されます。Data modification statements, such as INSERT, UPDATE, and DELETE combine both modification and read operations. 最初に読み取り操作でデータを取得してから、必要な変更操作を実行します。The statement first performs read operations to acquire data before performing the required modification operations. したがって、一般的にデータ変更ステートメントには共有ロックおよび排他ロックの両方が必要です。Data modification statements, therefore, typically request both shared locks and exclusive locks. たとえば、UPDATE ステートメントによって、他のテーブルとの結合を基にテーブルの行を変更するとします。For example, an UPDATE statement might modify rows in one table based on a join with another table. このとき、結合テーブルの読み取る行に対する共有ロックと、更新する行に対する排他ロックが要求されます。In this case, the UPDATE statement requests shared locks on the rows read in the join table in addition to requesting exclusive locks on the updated rows.

インテント ロックIntent Locks

データベース エンジンDatabase Engineではインテント ロックを使用して、下位のロック階層に位置するリソースに共有 (S) ロックまたは排他 (X) ロックがかかるのを保護します。The データベース エンジンDatabase Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy. インテント ロックの命名の由来は、ロックのタイミングが下位レベルのロックよりも前であり、下位レベルでロックをかける意図 (intent) を示すためです。Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level.

インテント ロックの用途は次の 2 つです。Intent locks serve two purposes:

  • 他のトランザクションが上位のリソースを変更することにより下位レベルのロックを無効にしてしまうことを防ぐ。To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.

  • データベース エンジンDatabase Engineによって高い粒度でロックの競合を検出する効率を向上する。To improve the efficiency of the データベース エンジンDatabase Engine in detecting lock conflicts at the higher level of granularity.

たとえば、あるテーブルのページまたは行の共有 (S) ロックを要求する前に、テーブル レベルの共有インテント ロックを要求するとします。For example, a shared intent lock is requested at the table level before shared (S) locks are requested on pages or rows within that table. テーブル レベルのインテント ロックを設定すると、それ以降、別のトランザクションによってそのページを含むテーブルに排他 (X) ロックがかけられる状態を回避することができます。Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. インテント ロックを使用すればパフォーマンスが向上します。データベース エンジンDatabase Engineでは、トランザクションがテーブルに対して安全にロックをかけることができるかどうかを判断する際に、テーブル レベルのインテント ロックを調べるだけで済みます。Intent locks improve performance because the データベース エンジンDatabase Engine examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. これにより、トランザクションがテーブル全体をロックできるかどうかを判断するために、テーブルの各行や各ページのロックを調べる必要がなくなります。This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.

インテント ロックにはインテント共有 (IS)、インテント排他 (IX)、およびインテント排他付き共有 (SIX) があります。Intent locks include intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

ロック モードLock mode 説明Description
インテント共有 (IS)Intent shared (IS) 下位の階層に位置するリソースの (すべてではなく) 一部に対し、要求されているかかけられている共有ロックを保護します。Protects requested or acquired shared locks on some (but not all) resources lower in the hierarchy.
インテント排他 (IX)Intent exclusive (IX) 下位の階層に位置するリソースの (すべてではなく) 一部に対し、要求されているかかけられている排他ロックを保護します。Protects requested or acquired exclusive locks on some (but not all) resources lower in the hierarchy. IX は IS のスーパーセットです。また、下位のリソースに対する共有ロックの要求を保護します。IX is a superset of IS, and it also protects requesting shared locks on lower level resources.
インテント排他付き共有 (SIX)Shared with intent exclusive (SIX) 下位の階層に位置するすべてのリソースに対し、要求されているかかけられている共有ロックを保護し、下位のリソースの (すべてではなく) 一部のインテント排他ロックを保護します。Protects requested or acquired shared locks on all resources lower in the hierarchy and intent exclusive locks on some (but not all) of the lower level resources. 上位リソースで同時実行している IS ロックは可能です。Concurrent IS locks at the top-level resource are allowed. たとえば、テーブルに対し SIX ロックをかけると、変更中のページにインテント排他ロックが、変更中の行に排他ロックがかかります。For example, acquiring a SIX lock on a table also acquires intent exclusive locks on the pages being modified and exclusive locks on the modified rows. 1 つのリソースに対しては、一度に 1 つの SIX ロックしかかけられません。その結果、他のトランザクションによってリソースが更新されることはなくなりますが、他のトランザクションはテーブル レベルの IS ロックをかけることで下位のリソースを読み取ることができます。There can be only one SIX lock per resource at one time, preventing updates to the resource made by other transactions, although other transactions can read resources lower in the hierarchy by obtaining IS locks at the table level.
インテント更新 (IU)Intent update (IU) 下位の階層に位置するすべてのリソースに対し、要求または取得された更新ロックを保護します。Protects requested or acquired update locks on all resources lower in the hierarchy. IU ロックはページ リソースに対してのみ使用します。IU locks are used only on page resources. 更新操作が発生すると、IU ロックは IX ロックに変換されます。IU locks are converted to IX locks if an update operation takes place.
共有インテント更新 (SIU)Shared intent update (SIU) S ロックと IU ロックを個別にかけるか、同時にかけるかして組み合わせたものです。A combination of S and IU locks, as a result of acquiring these locks separately and simultaneously holding both locks. たとえば、トランザクションで PAGLOCK ヒントを指定してクエリを実行してから更新操作を実行するとします。For example, a transaction executes a query with the PAGLOCK hint and then executes an update operation. PAGLOCK ヒントを指定したクエリで S ロックをかけ、更新操作で IU ロックをかけます。The query with the PAGLOCK hint acquires the S lock, and the update operation acquires the IU lock.
更新インテント排他 (UIX)Update intent exclusive (UIX) U ロックと IX ロックを個別にかけるか、同時にかけるかして組み合わせたものです。A combination of U and IX locks, as a result of acquiring these locks separately and simultaneously holding both locks.

スキーマ ロックSchema Locks

データベース エンジンDatabase Engineは、テーブルにデータ定義言語 (DDL) 操作 (列の追加やテーブルの削除など) を行うときにスキーマ修正 (Sch-M) ロックを使用します。The データベース エンジンDatabase Engine uses schema modification (Sch-M) locks during a table data definition language (DDL) operation, such as adding a column or dropping a table. ロックが保持されている場合、Sch-M ロックはテーブルへの同時アクセスを防ぎます。During the time that it is held, the Sch-M lock prevents concurrent access to the table. つまり、Sch-M ロックは、ロックが解除されるまで外部からの操作をすべてブロックします。This means the Sch-M lock blocks all outside operations until the lock is released.

テーブルの切り捨てなど一部のデータ操作言語 (DML) 操作では、同時操作によって影響を受けるテーブルへのアクセスを防ぐために Sch-M ロックを使用します。Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent access to affected tables by concurrent operations.

データベース エンジンDatabase Engineは、クエリをコンパイルして実行する際にスキーマ安定度 (Sch-S) ロックを使用します。The データベース エンジンDatabase Engine uses schema stability (Sch-S) locks when compiling and executing queries. Sch-S ロックは、排他 (X) ロックを含めて、どのトランザクション ロックもブロックしません。Sch-S locks do not block any transactional locks, including exclusive (X) locks. したがって、その他のトランザクション (テーブルを X ロックするトランザクションなど) は、クエリのコンパイル中も継続して実行されます。Therefore, other transactions, including those with X locks on a table, continue to run while a query is being compiled. ただし、Sch-M ロックを取得する DDL 同時操作や DML 同時操作をテーブルに対して実行することはできません。However, concurrent DDL operations, and concurrent DML operations that acquire Sch-M locks, cannot be performed on the table.

一括更新ロックBulk Update Locks

一括更新 (BU) ロックをかけると、同時に複数のスレッドによりデータを同一のテーブルに一括で読み込むことができますが、一括読み込みに参加していない他のプロセスは、その間テーブルにアクセスできません。Bulk update (BU) locks allow multiple threads to bulk load data concurrently into the same table while preventing other processes that are not bulk loading data from accessing the table. 次に示す両方の条件に該当する場合、データベース エンジンDatabase Engineでは一括更新 (BU) ロックが使用されます。The データベース エンジンDatabase Engine uses bulk update (BU) locks when both of the following conditions are true.

  • Transact-SQL の BULK INSERT ステートメント、OPENROWSET(BULK) 関数、またはいずれかの BULK INSERT API コマンド (.NET の SqlBulkCopy、OLEDB の高速読み込み API、ODBC の一括コピー API など) を使用してテーブルにデータを一括コピーする場合。You use the Transact-SQL BULK INSERT statement, or the OPENROWSET(BULK) function, or you use one of the Bulk Insert API commands such as .NET SqlBulkCopy, OLEDB Fast Load APIs, or the ODBC Bulk Copy APIs to bulk copy data into a table.

  • TABLOCK ヒントを指定した場合または sp_tableoption を使用して table lock on bulk load テーブル オプションを設定した場合。The TABLOCK hint is specified or the table lock on bulk load table option is set using sp_tableoption.

ヒント

より制限の少ない一括更新ロックを保持する BULK INSERT ステートメントとは異なり、TABLOCK ヒントが指定された INSERT INTO...SELECT は、テーブルに対する排他的な (X) ロックを保持します。Unlike the BULK INSERT statement, which holds a less restrictive Bulk Update lock, INSERT INTO...SELECT with the TABLOCK hint holds an exclusive (X) lock on the table. したがって、並列挿入操作を使用して行を挿入することはできません。This means that you cannot insert rows using parallel insert operations.

キー範囲ロックKey-Range Locks

キー範囲ロックは、SERIALIZABLE トランザクション分離レベルの状態で、Transact-SQLTransact-SQL ステートメントで読み取っているレコード セットに含まれている行の範囲を暗黙的に保護します。Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQLTransact-SQL statement while using the serializable transaction isolation level. キー範囲ロックを使用すると、ファントム読み取りを回避できます。Key-range locking prevents phantom reads. 行間のキー範囲を保護することで、トランザクションからアクセスするレコード セットへのファントム挿入やファントム削除も回避されます。By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction.

ロックの互換性Lock Compatibility

ロックの互換性により、複数のトランザクションが同じリソースのロックを同時に獲得できるかどうかが制御されます。Lock compatibility controls whether multiple transactions can acquire locks on the same resource at the same time. リソースが別のトランザクションによって既にロックされている場合、要求されたロックのモードと既存のロックのモードに互換性がある場合のみ、新しいロック要求の許可が可能になります。If a resource is already locked by another transaction, a new lock request can be granted only if the mode of the requested lock is compatible with the mode of the existing lock. 要求されたロックのモードと既存のロックとの互換性がない場合、新しいロックを要求しているトランザクションは、既存のロックが解除されるか、またはロックがタイムアウトするのを待機します。If the mode of the requested lock is not compatible with the existing lock, the transaction requesting the new lock waits for the existing lock to be released or for the lock timeout interval to expire. たとえば、排他ロックと互換性があるロック モードはありません。For example, no lock modes are compatible with exclusive locks. 排他 (X) ロックが保持されている間は、その排他 (X) ロックが解除されるまで、他のトランザクションはこのリソースに対してどの種類のロック (共有、更新、排他) も獲得できません。While an exclusive (X) lock is held, no other transaction can acquire a lock of any kind (shared, update, or exclusive) on that resource until the exclusive (X) lock is released. リソースに共有 (S) ロックが適用されている場合は、他のトランザクションもそのアイテムの共有ロックまたは更新 (U) ロックを獲得できます。最初のトランザクションが完了している必要はありません。Alternatively, if a shared (S) lock has been applied to a resource, other transactions can also acquire a shared lock or an update (U) lock on that item even if the first transaction has not completed. ただし、共有ロックが解除されないと、他のトランザクションは排他ロックを獲得できません。However, other transactions cannot acquire an exclusive lock until the shared lock has been released.

次の表に、最も一般的に使用されるロック モードの互換性を示します。The following table shows the compatibility of the most commonly encountered lock modes.

既に許可されているモードExisting granted mode
要求されたモードRequested mode ISIS SS UU IXIX SIXSIX XX
インテント共有 (IS)Intent shared (IS) はいYes [はい]Yes [はい]Yes [はい]Yes [はい]Yes いいえNo
共有 (S)Shared (S) はいYes [はい]Yes [はい]Yes いいえNo いいえNo いいえNo
更新 (U)Update (U) はいYes [はい]Yes いいえNo いいえNo いいえNo いいえNo
インテント排他 (IX)Intent exclusive (IX) はいYes いいえNo いいえNo はいYes いいえNo いいえNo
インテント排他付き共有 (SIX)Shared with intent exclusive (SIX) はいYes いいえNo いいえNo いいえNo いいえNo いいえNo
排他 (X)Exclusive (X) いいえNo いいえNo いいえNo いいえNo いいえNo いいえNo

注意

インテント排他 (IX) はすべての行ではなく一部の行を更新することを指定するものなので、IX ロックは IX ロック モードと互換性があります。An intent exclusive (IX) lock is compatible with an IX lock mode because IX means the intention is to update only some of the rows rather than all of them. 一部の行を読み取ったり更新したりする他のトランザクションも、他のトランザクションによって更新されている同じ行でない限り、許可されます。Other transactions that attempt to read or update some of the rows are also permitted as long as they are not the same rows being updated by other transactions. また、2 つのトランザクションが同じ行を更新しようとする場合は、両方のトランザクションにテーブル レベルとページ レベルの IX ロックが許可されます。Further, if two transactions attempt to update the same row, both transactions will be granted an IX lock at table and page level. ただし、行レベルの X ロックは一方のトランザクションに許可されるため、However, one transaction will be granted an X lock at row level. もう一方のトランザクションは、行レベルのロックが解除されるまで待機する必要があります。The other transaction must wait until the row-level lock is removed.

次の表を使用すると、SQL ServerSQL Server で使用できるすべてのロック モードの互換性を確認できます。Use the following table to determine the compatibility of all the lock modes available in SQL ServerSQL Server.

ダイアグラムが表示されたロックの互換性対応表Diagram showing lock compatibility matrix

キー範囲ロックKey-Range Locking

キー範囲ロックは、SERIALIZABLE トランザクション分離レベルの状態で、Transact-SQLTransact-SQL ステートメントで読み取っているレコード セットに含まれている行の範囲を暗黙的に保護します。Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQLTransact-SQL statement while using the serializable transaction isolation level. SERIALIZABLE 分離レベルでは、トランザクション中に実行されるクエリは、そのトランザクション内で実行されるたびに同一の行セットを取得する必要があります。The serializable isolation level requires that any query executed during a transaction must obtain the same set of rows every time it is executed during the transaction. キー範囲ロックではこの要件を満たすために、新しい行のキーが SERIALIZABLE トランザクションで読み取られるキー範囲内にある場合に、他のトランザクションが新しい行を挿入できないようにします。A key range lock protects this requirement by preventing other transactions from inserting new rows whose keys would fall in the range of keys read by the serializable transaction.

キー範囲ロックを使用すると、ファントム読み取りを回避できます。Key-range locking prevents phantom reads. 各行のキー範囲を保護することで、トランザクションからアクセスされるレコード セットへのファントム挿入も回避されます。By protecting the ranges of keys between rows, it also prevents phantom insertions into a set of records accessed by a transaction.

キー範囲ロックは、キー範囲の開始値と終了値を指定して、インデックスに対して設定されます。A key-range lock is placed on an index, specifying a beginning and ending key value. このロックでは、範囲内のキー値を持つ行を挿入、更新、または削除する操作がブロックされます。挿入操作、更新操作、または削除操作では、最初にインデックスに対するロックを取得する必要があるためです。This lock blocks any attempt to insert, update, or delete any row with a key value that falls in the range because those operations would first have to acquire a lock on the index. たとえば、シリアル化可能なトランザクションでは、 ' AAA '' CZZ ' の間のキー値を持つすべての行を読み取る SELECT ステートメントを実行できます。For example, a serializable transaction could issue a SELECT statement that reads all rows whose key values are between ' AAA ' and ' CZZ '. ' AAA ' から ' CZZ ' の範囲内のキー値にキー範囲ロックをかけると、他のトランザクションからは ' ADG '' BBD '' CAL ' など、その範囲内のキー値を持つ行は挿入されません。A key-range lock on the key values in the range from ' AAA ' to ' CZZ ' prevents other transactions from inserting rows with key values anywhere in that range, such as ' ADG ', ' BBD ', or ' CAL '.

キー範囲ロック モードKey-Range Lock Modes

キー範囲ロックには、範囲-行形式で指定される範囲と行のコンポーネントが含まれています。Key-range locks include both a range and a row component specified in range-row format:

  • 範囲は 2 つの連続したインデックス エントリ間の範囲を保護するロック モードを表します。Range represents the lock mode protecting the range between two consecutive index entries.

  • 行はインデックス エントリを保護するロック モードを表します。Row represents the lock mode protecting the index entry.

  • モードは使用する組み合わされたロック モードを表します。Mode represents the combined lock mode used. キー範囲ロック モードは 2 つの部分から成ります。Key-range lock modes consist of two parts. 最初の部分はインデックス範囲 (RangeT) をロックするのに使用するロックの種類を表し、その次の部分は特定のキー (K) をロックするのに使用するロックの種類を表します。The first represents the type of lock used to lock the index range (RangeT) and the second represents the lock type used to lock a specific key (K). RangeT-K のように、2 つの部分はハイフン (-) で連結されます。The two parts are connected with a hyphen (-), such as RangeT-K.

    範囲Range Row モードMode 説明Description
    RangeSRangeS SS RangeS-SRangeS-S 共有範囲。共有リソース ロック、シリアル化可能範囲スキャン。Shared range, shared resource lock; serializable range scan.
    RangeSRangeS UU RangeS-URangeS-U 共有範囲。更新リソース ロック。シリアル化可能更新スキャン。Shared range, update resource lock; serializable update scan.
    RangeIRangeI [Null]Null RangeI-NRangeI-N 挿入範囲。NULL リソース ロック。新しいキーをインデックスに挿入する前に範囲をテストするのに使用します。Insert range, null resource lock; used to test ranges before inserting a new key into an index.
    RangeXRangeX xX RangeX-XRangeX-X 排他範囲。排他リソース ロック。範囲内のキーを更新するのに使用します。Exclusive range, exclusive resource lock; used when updating a key in a range.

注意

内部 NULL ロック モードは、他のすべてのロック モードと互換性があります。The internal Null lock mode is compatible with all other lock modes.

各キー範囲ロック モードには、重なり合うキーと範囲に対して取得されるロックが、どのロックと互換性があるかを示す互換性マトリックスがあります。Key-range lock modes have a compatibility matrix that shows which locks are compatible with other locks obtained on overlapping keys and ranges.

既に許可されているモードExisting granted mode
要求されたモードRequested mode SS UU XX RangeS-SRangeS-S RangeS-URangeS-U RangeI-NRangeI-N RangeX-XRangeX-X
共有 (S)Shared (S) はいYes [はい]Yes いいえNo はいYes [はい]Yes [はい]Yes いいえNo
更新 (U)Update (U) はいYes いいえNo いいえNo はいYes いいえNo はいYes いいえNo
排他 (X)Exclusive (X) いいえNo いいえNo いいえNo いいえNo いいえNo はいYes いいえNo
RangeS-SRangeS-S はいYes [はい]Yes いいえNo はいYes [はい]Yes いいえNo いいえNo
RangeS-URangeS-U はいYes いいえNo いいえNo はいYes いいえNo いいえNo いいえNo
RangeI-NRangeI-N はいYes [はい]Yes [はい]Yes いいえNo いいえNo はいYes いいえNo
RangeX-XRangeX-X いいえNo いいえNo いいえNo いいえNo いいえNo いいえNo いいえNo

変換ロックConversion Locks

変換ロックは、キー範囲ロックが別のロックと重なり合うときに作成されます。Conversion locks are created when a key-range lock overlaps another lock.

ロック 1Lock 1 ロック 2Lock 2 変換ロックConversion lock
SS RangeI-NRangeI-N RangeI-SRangeI-S
UU RangeI-NRangeI-N RangeI-URangeI-U
xX RangeI-NRangeI-N RangeI-XRangeI-X
RangeI-NRangeI-N RangeS-SRangeS-S RangeX-SRangeX-S
RangeI-NRangeI-N RangeS-URangeS-U RangeX-URangeX-U

変換ロックは、同時実行プロセスを実行しているときなど、さまざまな環境で短時間発生することがあります。Conversion locks can be observed for a short period of time under different complex circumstances, sometimes while running concurrent processes.

シリアル化可能な範囲スキャン、単一フェッチ、削除、および挿入Serializable Range Scan, Singleton Fetch, Delete, and Insert

キー範囲ロックは、次の操作のシリアル化を保証します。Key-range locking ensures that the following operations are serializable:

  • 範囲スキャン クエリRange scan query

  • 存在しない行の単一フェッチSingleton fetch of nonexistent row

  • 削除操作Delete operation

  • 挿入操作Insert operation

キー範囲ロックを実行する前に次の条件を満たしておく必要があります。Before key-range locking can occur, the following conditions must be satisfied:

  • トランザクション分離レベルを SERIALIZABLE に設定する。The transaction-isolation level must be set to SERIALIZABLE.

  • クエリ プロセッサではインデックスを使用して範囲フィルター述語を実装する必要があります。The query processor must use an index to implement the range filter predicate. たとえば、SELECT ステートメントで WHERE 句を使用すると、述語ColumnX BETWEEN N ' AAA ' AND N ' CZZ ' を使用して範囲条件を設定できます。For example, the WHERE clause in a SELECT statement could establish a range condition with this predicate: ColumnX BETWEEN N ' AAA ' AND N ' CZZ '. ColumnX がインデックス キーに含まれている場合、キー範囲ロックだけを取得できます。A key-range lock can only be acquired if ColumnX is covered by an index key.

使用例Examples

次のテーブルとインデックスは、この後のキー範囲ロックの例の基準として使用されます。The following table and index are used as a basis for the key-range locking examples that follow.

データベース テーブル インデックス b ツリーの図でDatabase table with index b-tree illustration

範囲スキャン クエリRange Scan Query

範囲スキャン クエリを確実にシリアル化するには、同じトランザクション内で同じクエリを実行するたびに同じ結果が返されるようにします。To ensure a range scan query is serializable, the same query should return the same results each time it is executed within the same transaction. 他のトランザクションによる範囲スキャン クエリ内に新しい行を挿入しないでください。これはファントム挿入になります。New rows must not be inserted within the range scan query by other transactions; otherwise, these become phantom inserts. たとえば、上の図のテーブルとインデックスを使用する次のクエリについて考えます。For example, the following query uses the table and index in the previous illustration:

SELECT name  
    FROM mytable  
    WHERE name BETWEEN 'A' AND 'C';  

名前が Adam と Dale の間に含まれるデータ行の範囲に対応するインデックス エントリにキー範囲ロックを設定し、上のクエリで指定した範囲内に新しい行を追加、削除することを禁止します。Key-range locks are placed on the index entries corresponding to the range of data rows where the name is between the values Adam and Dale, preventing new rows qualifying in the previous query from being added or deleted. この範囲の最初の名前は Adam ですが、このインデックス エントリに対する RangeS-S モードのキー範囲ロックによって、Abigail など英字 A で始まる新しい名前を Adam の前に追加することが禁止されます。Although the first name in this range is Adam, the RangeS-S mode key-range lock on this index entry ensures that no new names beginning with the letter A can be added before Adam, such as Abigail. 同様に、インデックス エントリ Dale に対する RangeS-S キー範囲ロックによって、Clive など英字 C で始まる新しい名前を Carlos の後に追加することが禁止されます。Similarly, the RangeS-S key-range lock on the index entry for Dale ensures that no new names beginning with the letter C can be added after Carlos, such as Clive.

注意

保持される RangeS-S ロック数は n + 1 個です。n はクエリに該当する行数です。The number of RangeS-S locks held is n+1, where n is the number of rows that satisfy the query.

存在しないデータの単一フェッチSingleton Fetch of Nonexistent Data

トランザクション内のクエリで存在しない行を選択しようとする場合、同じトランザクション内で再度そのクエリを実行しても、同じ結果を返す必要があります。If a query within a transaction attempts to select a row that does not exist, issuing the query at a later point within the same transaction has to return the same result. どのトランザクションも、存在しない行を追加することはできません。No other transaction can be allowed to insert that nonexistent row. たとえば、次のクエリについて考えてみます。For example, given this query:

SELECT name  
    FROM mytable  
    WHERE name = 'Bill';  

名前 Ben は隣接するインデックス エントリである BingBill の間に挿入されるため、この名前範囲に対応するインデックス エントリにキー範囲ロックが設定されます。A key-range lock is placed on the index entry corresponding to the name range from Ben to Bing because the name Bill would be inserted between these two adjacent index entries. RangeS-S モードのキー範囲ロックは、インデックス エントリ Bing に設定されます。The RangeS-S mode key-range lock is placed on the index entry Bing. これにより、Bill などの値がインデックス エントリの BenBing の間に挿入されるのを防ぎます。This prevents any other transaction from inserting values, such as Bill, between the index entries Ben and Bing.

削除操作Delete Operation

トランザクション内で値を削除する場合、削除処理を実行するトランザクションの間、値が存在する範囲をロックする必要はありません。When deleting a value within a transaction, the range the value falls into does not have to be locked for the duration of the transaction performing the delete operation. シリアル化可能性を維持するには、削除するキー値をトランザクションの終了時までロックするだけで十分です。Locking the deleted key value until the end of the transaction is sufficient to maintain serializability. たとえば、次の DELETE ステートメントについて考えてみます。For example, given this DELETE statement:

DELETE mytable  
    WHERE name = 'Bob';  

Bob に対応するインデックス エントリに排他 (X) ロックを設定します。An exclusive (X) lock is placed on the index entry corresponding to the name Bob. 他のトランザクションは、削除する値 Bob の前後に値を挿入したり、削除することができます。Other transactions can insert or delete values before or after the deleted value Bob. ただし、値 Bob の読み取り、挿入、または削除を試みるトランザクションは、削除を実行中のトランザクションがコミットまたはロールバックするまでブロックされます。However, any transaction that attempts to read, insert, or delete the value Bob will be blocked until the deleting transaction either commits or rolls back.

範囲削除は、行ロック、ページ ロック、またはテーブル ロックの 3 つの基本的なロック モードを使用して実行できます。Range delete can be executed using three basic lock modes: row, page, or table lock. そのうちどのモードを使用するかは、クエリ オプティマイザーにより決定されるか、または ROWLOCK、PAGLOCK、TABLOCK などのオプティマイザー ヒントによってユーザーが指定できます。The row, page, or table locking strategy is decided by query optimizer or can be specified by the user through optimizer hints such as ROWLOCK, PAGLOCK, or TABLOCK. PAGLOCK または TABLOCK を使用した場合、すべての行をインデックス ページから削除すると、データベース エンジンDatabase Engineによりすぐにインデックス ページの割り当てが解除されます。When PAGLOCK or TABLOCK is used, the データベース エンジンDatabase Engine immediately deallocates an index page if all rows are deleted from this page. 対照的に、ROWLOCK を使用する場合、削除対象のすべての行には削除のマークが付けられるだけです。これらは、後でバックグラウンド タスクによってインデックス ページから削除されます。In contrast, when ROWLOCK is used, all deleted rows are marked only as deleted; they are removed from the index page later using a background task.

挿入操作Insert Operation

トランザクション内で値を挿入する場合、挿入処理を行うトランザクションの実行中、その値が含まれている範囲をロックする必要はありません。When inserting a value within a transaction, the range the value falls into does not have to be locked for the duration of the transaction performing the insert operation. シリアル化可能性を維持するには、挿入するキー値をトランザクションの終了時までロックするだけで十分です。Locking the inserted key value until the end of the transaction is sufficient to maintain serializability. たとえば、次の INSERT ステートメントについて考えてみます。For example, given this INSERT statement:

INSERT mytable VALUES ('Dan');  

範囲をテストするために、David という名前に対応するインデックス エントリに RangeI-N モードのキー範囲ロックを設定します。The RangeI-N mode key-range lock is placed on the index entry corresponding to the name David to test the range. ロックが許可されると、Dan が挿入され、値 Dan に排他 (X) ロックが設定されます。If the lock is granted, Dan is inserted and an exclusive (X) lock is placed on the value Dan. RangeI-N モードのキー範囲ロックは範囲のテストだけに必要で、挿入処理を行うトランザクションの実行中は保持されません。The RangeI-N mode key-range lock is necessary only to test the range and is not held for the duration of the transaction performing the insert operation. 他のトランザクションは、挿入する値 Dan の前後に値を挿入したり、前後の値を削除できます。Other transactions can insert or delete values before or after the inserted value Dan. ただし、値 Dan の読み取り、挿入、または削除を試みるトランザクションは、挿入を実行中のトランザクションがコミットまたはロールバックするまでロックされます。However, any transaction attempting to read, insert, or delete the value Dan will be locked until the inserting transaction either commits or rolls back.

動的ロックDynamic Locking

行ロックなど、レベルの低いロックを使用すると、2 つのトランザクションが同時にデータの同じ部分に対するロックを要求する可能性が減ってコンカレンシーが高まります。Using low-level locks, such as row locks, increases concurrency by decreasing the probability that two transactions will request locks on the same piece of data at the same time. 同時に、ロックの数も増えるので、ロックを管理するために多くのリソースが必要になります。Using low-level locks also increases the number of locks and the resources needed to manage them. テーブルまたはページに対する高レベルのロックはオーバーヘッドが減りますが、コンカレンシーは低下します。Using high-level table or page locks lowers overhead, but at the expense of lowering concurrency.

コストと粒度を示す図Diagram showing cost versus granularity

MicrosoftMicrosoft SQL Server データベース エンジンSQL Server Database Engine動的ロック ストラテジを使用して、最もコストの低いロックを判断します。The MicrosoftMicrosoft SQL Server データベース エンジンSQL Server Database Engine uses a dynamic locking strategy to determine the most cost-effective locks. データベース エンジンDatabase Engineはクエリを実行する際に、スキーマおよびクエリの特性に基づいて最適なロックを自動的に判断します。The データベース エンジンDatabase Engine automatically determines what locks are most appropriate when the query is executed, based on the characteristics of the schema and query. たとえば、インデックス スキャンの実行時に、インデックス内でのページレベルのロックが選択されます。これにより、ロックのオーバーヘッドを減少させることができます。For example, to reduce the overhead of locking, the optimizer may choose page-level locks in an index when performing an index scan.

動的ロックには次の利点があります。Dynamic locking has the following advantages:

  • データベースの管理が簡略化されます。Simplified database administration. データベース管理者がロック エスカレーションのしきい値を調整する必要はありません。Database administrators do not have to adjust lock escalation thresholds.

  • パフォーマンスが高まります。Increased performance. データベース エンジンDatabase Engineによりタスクに適したロックが使用されるので、システム オーバーヘッドが最小になります。The データベース エンジンDatabase Engine minimizes system overhead by using locks appropriate to the task.

  • アプリケーション開発者が開発に専念できます。Application developers can concentrate on development. ロックはデータベース エンジンDatabase Engineにより自動的に調整されます。The データベース エンジンDatabase Engine adjusts locking automatically.

SQL Server 2008SQL Server 2008し、以降のバージョンでは、LOCK_ESCALATION オプションの導入に伴い、ロックのエスカレーションの動作が変更されました。In SQL Server 2008SQL Server 2008 and later versions, the behavior of lock escalation has changed with the introduction of the LOCK_ESCALATION option. 詳細については、の LOCK_ESCALATION オプションを参照してください。 ALTER TABLEします。For more information, see the LOCK_ESCALATION option of ALTER TABLE.

デッドロックDeadlocking

デッドロックは、複数のタスクが永続的に相互ブロックすることで発生します。つまり、一方のタスクがロックを試みているリソースに他方のタスクがロックを獲得していて、これが相互に行われるとデッドロックが発生します。A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. 以下に例を示します。For example:

  • トランザクション A が行 1 の共有ロックを取得します。Transaction A acquires a share lock on row 1.

  • トランザクション B が行 2 の共有ロックを取得します。Transaction B acquires a share lock on row 2.

  • トランザクション A が行 2 の排他ロックを要求しますが、トランザクション B が完了し、B が保持している行 2 の共有ロックが解放されるまで A はブロックされます。Transaction A now requests an exclusive lock on row 2, and is blocked until transaction B finishes and releases the share lock it has on row 2.

  • このとき、トランザクション B が行 1 の排他ロックを要求すると、トランザクション A が完了し、A が保持している行 1 の共有ロックが解放されるまで B はブロックされます。Transaction B now requests an exclusive lock on row 1, and is blocked until transaction A finishes and releases the share lock it has on row 1.

トランザクション A は、トランザクション B が完了するまで完了できませんが、トランザクション B もトランザクション A によってブロックされます。この状態は、循環依存関係とも呼ばれます。トランザクション A がトランザクション B に依存し、トランザクション B がトランザクション A に依存するため、依存関係が循環します。Transaction A cannot complete until transaction B completes, but transaction B is blocked by transaction A. This condition is also called a cyclic dependency: Transaction A has a dependency on transaction B, and transaction B closes the circle by having a dependency on transaction A.

デッドロックになったどちらのトランザクションも、外部処理からデッドロックを解除されない限り、永久的に待機を続けます。Both transactions in a deadlock will wait forever unless the deadlock is broken by an external process. SQL Server データベース エンジンSQL Server Database Engineのデッドロック モニターでは、デッドロックになったタスクがあるかどうかの確認が定期的に行われます。The SQL Server データベース エンジンSQL Server Database Engine deadlock monitor periodically checks for tasks that are in a deadlock. モニターによって循環依存関係が検出されると、一方のタスクがデッドロックの犠牲者として選択され、そのトランザクションはエラーで終了されます。If the monitor detects a cyclic dependency, it chooses one of the tasks as a victim and terminates its transaction with an error. その結果、もう一方のタスクのトランザクションを完了できます。This allows the other task to complete its transaction. トランザクションがエラーで終了したアプリケーションは、そのトランザクションを再試行できます。通常は、デッドロックの一方のトランザクションが完了してからこのトランザクションも完了します。The application with the transaction that terminated with an error can retry the transaction, which usually completes after the other deadlocked transaction has finished.

デッドロックが、通常のブロッキングと混同されることがあります。Deadlocking is often confused with normal blocking. あるトランザクションが、別のトランザクションによってロックされているリソースのロックを要求すると、ロックを要求したトランザクションはロックが解放されるまで待機します。When a transaction requests a lock on a resource locked by another transaction, the requesting transaction waits until the lock is released. 既定では、LOCK_TIMEOUT を設定しない限り、SQL ServerSQL Server のトランザクションはタイムアウトになりません。By default, SQL ServerSQL Server transactions do not time out, unless LOCK_TIMEOUT is set. この場合、ロックを要求したトランザクションはブロックされているだけで、デッドロックが発生しているわけではありません。つまり、ロックを要求したトランザクションは、ロックを所有しているトランザクションをブロックする操作を行っていません。The requesting transaction is blocked, not deadlocked, because the requesting transaction has not done anything to block the transaction owning the lock. 最終的には、ロックを所有しているトランザクションが完了してロックが解放され、ロックを要求したトランザクションがロックを取得し、続行されます。Eventually, the owning transaction will complete and release the lock, and then the requesting transaction will be granted the lock and proceed.

デッドロックは、「破壊的な支配」と呼ばれることもあります。Deadlocks are sometimes called a deadly embrace.

デッドロックの状態は、リレーショナル データベース管理システムだけでなく、複数のスレッドを使用していれば、どのようなシステムでも発生する可能性があります。また、データベース オブジェクトのロック以外でも発生する可能性があります。Deadlock is a condition that can occur on any system with multiple threads, not just on a relational database management system, and can occur for resources other than locks on database objects. たとえば、マルチスレッド オペレーティング システムの 1 つのスレッドが、メモリのブロックなど、1 つ以上のリソースを取得するとします。For example, a thread in a multithreaded operating system might acquire one or more resources, such as blocks of memory. 取得しようとしているリソースが別のスレッドに所有されている場合、最初のスレッドはリソースを所有しているスレッドがそのリソースを解放するまで待機することになります。If the resource being acquired is currently owned by another thread, the first thread may have to wait for the owning thread to release the target resource. このとき、待機しているスレッドのことを「そのリソースについて、所有側のスレッドに対する依存関係がある」といいます。The waiting thread is said to have a dependency on the owning thread for that particular resource. データベース エンジンDatabase Engineのインスタンスでは、メモリやスレッドなど、データベース以外のリソースを取得するときにデッドロックが発生する可能性があります。In an instance of the データベース エンジンDatabase Engine, sessions can deadlock when acquiring nondatabase resources, such as memory or threads.

トランザクションのデッドロックを示す図Diagram showing transaction deadlock

この例では、トランザクション T1 は Part テーブルのロック リソースに関して、トランザクション T2 に依存関係があります。In the illustration, transaction T1 has a dependency on transaction T2 for the Part table lock resource. 同様に、Supplier テーブルのロック リソースに関しては、トランザクション T2 がトランザクション T1 に対する依存関係を持っています。Similarly, transaction T2 has a dependency on transaction T1 for the Supplier table lock resource. これらの依存関係は相互に働くため、トランザクション T1 と T2 の間でデッドロックが発生します。Because these dependencies form a cycle, there is a deadlock between transactions T1 and T2.

デッドロックは、テーブルがパーティション分割されており、ALTER TABLE の LOCK_ESCALATION 設定が AUTO に設定されている場合にも発生することがあります。Deadlocks can also occur when a table is partitioned and the LOCK_ESCALATION setting of ALTER TABLE is set to AUTO. LOCK_ESCALATION を AUTO に設定すると、同時実行が許可することで増加、データベース エンジンDatabase Engineテーブル レベルではなく HoBT レベルでテーブル パーティションをロックします。When LOCK_ESCALATION is set to AUTO, concurrency increases by allowing the データベース エンジンDatabase Engine to lock table partitions at the HoBT level instead of at the TABLE level. ただし、個々のトランザクションがテーブルのパーティション ロックを保持し、他のトランザクション パーティションのどこかをロックする必要がある場合、デッドロックが発生します。However, when separate transactions hold partition locks in a table and want a lock somewhere on the other transactions partition, this causes a deadlock. LOCK_ESCALATION をテーブルに設定して、このタイプのデッドロックを回避できます。ただし、この設定では、テーブル ロックを待機するパーティションに大規模な更新を強制することで同時実行性が減少します。This type of deadlock can be avoided by setting LOCK_ESCALATION to TABLE; although this setting will reduce concurrency by forcing large updates to a partition to wait for a table lock.

デッドロックの検出と終了Detecting and Ending Deadlocks

デッドロックは、複数のタスクが永続的に相互ブロックすることで発生します。つまり、一方のタスクがロックを試みているリソースに他方のタスクがロックを獲得していて、これが相互に行われるとデッドロックが発生します。A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. 次の図に、デッドロック状態の概要を示します。The following graph presents a high level view of a deadlock state where:

  • タスク T1 は、リソース R1 のロックを所有し (R1 から T1 への矢印で表しています)、リソース R2 のロックを要求しました (T1 から R2 への矢印で表しています)。Task T1 has a lock on resource R1 (indicated by the arrow from R1 to T1) and has requested a lock on resource R2 (indicated by the arrow from T1 to R2).

  • タスク T2 は、リソース R2 のロックを所有し (R2 から T2 への矢印で表しています)、リソース R1 のロックを要求しました (T2 から R1 への矢印で表しています)。Task T2 has a lock on resource R2 (indicated by the arrow from R2 to T2) and has requested a lock on resource R1 (indicated by the arrow from T2 to R1).

  • どちらのタスクもリソースが使用できるようになるまで続行できず、どちらのリソースもタスクが続行するまで解放できないため、デッドロック状態が発生します。Because neither task can continue until a resource is available and neither resource can be released until a task continues, a deadlock state exists.

デッドロック状態にあるタスクを示す図Diagram showing tasks in a deadlock state

SQL Server データベース エンジンSQL Server Database Engineは、SQL ServerSQL Server 内のデッドロック サイクルを自動的に検出します。The SQL Server データベース エンジンSQL Server Database Engine automatically detects deadlock cycles within SQL ServerSQL Server. データベース エンジンDatabase Engineがセッションの 1 つをデッドロックの対象として選択すると、現在のトランザクションはエラーで終了し、デッドロックが解除されます。The データベース エンジンDatabase Engine chooses one of the sessions as a deadlock victim and the current transaction is terminated with an error to break the deadlock.

デッドロックの原因となるリソースResources That Can Deadlock

各ユーザー セッションに含まれている 1 つ以上のタスクが、そのセッションのためにさまざまなリソースを取得したり、リソースを取得するために待機状態にある場合があります。Each user session might have one or more tasks running on its behalf where each task might acquire or wait to acquire a variety of resources. 次のような種類のリソースは、デッドロックの原因となるブロッキングを発生させる可能性があります。The following types of resources can cause blocking that could result in a deadlock.

  • ロック.Locks. オブジェクト、ページ、行、メタデータ、およびアプリケーションなどのリソースに対してロック取得のために待機していると、デッドロックが発生する場合があります。Waiting to acquire locks on resources, such as objects, pages, rows, metadata, and applications can cause deadlock. たとえば、トランザクション T1 では、行 r1 の共有 (S) ロックを所有しており、r2 に排他 (X) ロックがかかるのを待機しているとします。For example, transaction T1 has a shared (S) lock on row r1 and is waiting to get an exclusive (X) lock on r2. トランザクション T2 では、r2 の共有 (S) ロックを所有しており、行 r1 に排他 (X) ロックがかかるのを待機しているとします。Transaction T2 has a shared (S) lock on r2 and is waiting to get an exclusive (X) lock on row r1. この結果、T1 と T2 では、互いにロックされているリソースが解放されるのを待機するロック サイクルが発生します。This results in a lock cycle in which T1 and T2 wait for each other to release the locked resources.

  • ワーカー スレッドWorker threads. キューに登録されたタスクが利用可能なワーカー スレッドを待機していると、デッドロックが発生する場合があります。A queued task waiting for an available worker thread can cause deadlock. キューに登録されたタスクが、ワーカー スレッドをすべてブロックしているリソースを所有している場合、デッドロックが発生します。If the queued task owns resources that are blocking all worker threads, a deadlock will result. たとえば、セッション S1 で、トランザクションが開始され、行 r1 に共有 (S) ロックがかけられ、スリープ状態になったとします。For example, session S1 starts a transaction and acquires a shared (S) lock on row r1 and then goes to sleep. 使用可能なすべてのワーカー スレッドで実行中のアクティブなセッションは、行 r1 に排他 (X) ロックをかけようとします。Active sessions running on all available worker threads are trying to acquire exclusive (X) locks on row r1. セッション S1 ではワーカー スレッドを取得できないので、トランザクションをコミットして行 r1 のロックを解放することができません。Because session S1 cannot acquire a worker thread, it cannot commit the transaction and release the lock on row r1. この結果、デッドロックが発生します。This results in a deadlock.

  • メモリMemory. 同時要求で使用できるメモリ量以上のメモリ許可を待機している場合、デッドロックが発生することがあります。When concurrent requests are waiting for memory grants that cannot be satisfied with the available memory, a deadlock can occur. たとえば、2 つの同時実行クエリ Q1 と Q2 は、それぞれ 10 MB と 20 MB のメモリを取得するユーザー定義関数として実行されます。For example, two concurrent queries, Q1 and Q2, execute as user-defined functions that acquire 10MB and 20MB of memory respectively. 各クエリで 30 MB が必要でも、使用できるメモリの合計が 20 MB の場合、Q1 および Q2 では、互いにメモリが解放されるまで待機する必要があります。その結果、デッドロックが発生します。If each query needs 30MB and the total available memory is 20MB, then Q1 and Q2 must wait for each other to release memory, and this results in a deadlock.

  • 並列クエリ実行関連のリソース。交換ポートに関連付けられたコーディネーター、プロデューサー、またはコンシューマーのスレッドが互いをブロックし、デッドロックを発生させることがあります。通常、この現象は、並列クエリに含まれていない別のプロセスを 1 つ以上含めようとした場合に発生します。Parallel query execution-related resources Coordinator, producer, or consumer threads associated with an exchange port may block each other causing a deadlock usually when including at least one other process that is not a part of the parallel query. また、並列クエリの実行が開始されると、SQL ServerSQL Server は、現在のワークロードに基づいて並列処理の次数やワーカー スレッドの数を決定します。Also, when a parallel query starts execution, SQL ServerSQL Server determines the degree of parallelism, or the number of worker threads, based upon the current workload. たとえば、サーバーで新しいクエリの実行が開始されたり、システムのワーカー スレッドが不足したりするなど、システムのワークロードが予期せず変更される場合は、デッドロックが発生する可能性があります。If the system workload unexpectedly changes, for example, where new queries start running on the server or the system runs out of worker threads, then a deadlock could occur.

  • 複数のアクティブな結果セット (MARS) のリソースMultiple Active Result Sets (MARS) resources. これらのリソースは、MARS でアクティブな複数の要求のインターリーブを制御する際に使用します。These resources are used to control interleaving of multiple active requests under MARS. 詳細については、次を参照してください。複数アクティブな結果セット (MARS) SQL Server でします。For more information, see Multiple Active Result Sets (MARS) in SQL Server.

    • ユーザー リソースUser resource. ユーザー アプリケーションで制御されている可能性のあるリソースをスレッドが待機している場合、そのリソースは、外部リソースまたはユーザー リソースと見なされ、ロックと同様に処理されます。When a thread is waiting for a resource that is potentially controlled by a user application, the resource is considered to be an external or user resource and is treated like a lock.

    • セッション ミューテックスSession mutex. 1 つのセッションで実行中のタスクはインターリーブされます。つまり、セッションでは、一度に 1 つのタスクしか実行できません。The tasks running in one session are interleaved, meaning that only one task can run under the session at a given time. タスクを実行する前に、セッション ミューテックスに排他でアクセスする必要があります。Before the task can run, it must have exclusive access to the session mutex.

    • トランザクション ミューテックスTransaction mutex. 1 つのトランザクションで実行中のすべてのタスクはインターリーブされます。つまり、トランザクションでは、一度に 1 つのタスクしか実行できません。All tasks running in one transaction are interleaved, meaning that only one task can run under the transaction at a given time. タスクを実行する前に、トランザクション ミューテックスに排他でアクセスする必要があります。Before the task can run, it must have exclusive access to the transaction mutex.

    タスクを MARS で実行するには、セッション ミューテックスを取得する必要があります。In order for a task to run under MARS, it must acquire the session mutex. タスクがトランザクションで実行されている場合は、トランザクション ミューテックスを取得する必要があります。If the task is running under a transaction, it must then acquire the transaction mutex. これにより、そのセッションやトランザクションでは、一度に 1 つのタスクだけがアクティブになります。This guarantees that only one task is active at one time in a given session and a given transaction. 必要なミューテックスを取得後に、タスクを実行できます。Once the required mutexes have been acquired, the task can execute. タスクが終了するか、または要求の途中で中断されると、取得とは逆の順序で、最初にトランザクション ミューテックスが解放され、次にセッション ミューテックスが解放されます。When the task finishes, or yields in the middle of the request, it will first release transaction mutex followed by the session mutex in reverse order of acquisition. ただし、これらのリソースでデッドロックが発生する場合があります。However, deadlocks can occur with these resources. 次に、ユーザーの要求 U1 と U2 という 2 つのタスクが同じセッション内で実行されているコード例を示します。In the following code example, two tasks, user request U1 and user request U2, are running in the same session.

    U1:    Rs1=Command1.Execute("insert sometable EXEC usp_someproc");  
    U2:    Rs2=Command2.Execute("select colA from sometable");  
    

    ユーザーの要求 U1 で実行されているストアド プロシージャで、セッション ミューテックスが取得されています。The stored procedure executing from user request U1 has acquired the session mutex. このストアド プロシージャの実行に時間がかかると、データベース エンジンDatabase Engineにより、ストアド プロシージャではユーザーからの入力を待機していると見なされます。If the stored procedure takes a long time to execute, it is assumed by the データベース エンジンDatabase Engine that the stored procedure is waiting for input from the user. ユーザーの要求 U2 ではセッション ミューテックスが解放されるのを待機しているのに対し、ユーザーは U2 の結果セットが返されるのを待機しています。さらに、U1 では、ユーザー リソースが解放されるのを待機しています。User request U2 is waiting for the session mutex while the user is waiting for the result set from U2, and U1 is waiting for a user resource. 次の図に、このデッドロック状態を論理的に示します。This is deadlock state logically illustrated as:

ロジック ユーザー プロセス デッドロックを示す図Logic diagram showing user process deadlock.

デッドロックの検出Deadlock Detection

上記のすべてのリソースには、データベース エンジンDatabase Engineのデッドロック検出方式が適用されます。All of the resources listed in the section above participate in the データベース エンジンDatabase Engine deadlock detection scheme. デッドロックの検出は、データベース エンジンDatabase Engine インスタンスのすべてのタスクの検索を定期的に開始するロック モニター スレッドにより実行されます。Deadlock detection is performed by a lock monitor thread that periodically initiates a search through all of the tasks in an instance of the データベース エンジンDatabase Engine. 検索プロセスは次のとおりです。The following points describe the search process:

  • 既定の間隔は 5 秒です。The default interval is 5 seconds.

  • ロック モニター スレッドでデッドロックが検出されると、デッドロック検出の間隔は、デッドロックの頻度に応じて、5 秒より短くなります。最短の間隔は 100 ミリ秒です。If the lock monitor thread finds deadlocks, the deadlock detection interval will drop from 5 seconds to as low as 100 milliseconds depending on the frequency of deadlocks.

  • ロック モニター スレッドでデッドロックが検出されなくなると、データベース エンジンDatabase Engineにより、検索の間隔は 5 秒に引き上げられます。If the lock monitor thread stops finding deadlocks, the データベース エンジンDatabase Engine increases the intervals between searches to 5 seconds.

  • デッドロックが検出されると、ロックが解除されるのを待機する必要のある次のスレッドがデッドロック サイクルに入っていることが想定されます。If a deadlock has just been detected, it is assumed that the next threads that must wait for a lock are entering the deadlock cycle. デッドロックが検出されると、最初の 2 つのロック待機では、次のデッドロックの検出間隔まで待機せず、すぐにデッドロックの検索が開始されます。The first couple of lock waits after a deadlock has been detected will immediately trigger a deadlock search rather than wait for the next deadlock detection interval. たとえば、検索の間隔が 5 秒に設定されている場合にデッドロックが検出されると、次のロック待機により、直ちにデッドロックの検出が開始されます。For example, if the current interval is 5 seconds, and a deadlock was just detected, the next lock wait will kick off the deadlock detector immediately. このロック待機がデッドロックの一部である場合は、このロック待機は、次のデッドロックの検索時ではなく、直ちに検出されます。If this lock wait is part of a deadlock, it will be detected right away rather than during next deadlock search.

通常、データベース エンジンDatabase Engineでは、定期的なデッドロックの検出のみが実行されます。The データベース エンジンDatabase Engine typically performs periodic deadlock detection only. システムでデッドロックが発生することはまれであるため、デッドロック検出を定期的に実行することにより、システムのデッドロック検出のオーバーヘッドを削減できます。Because the number of deadlocks encountered in the system is usually small, periodic deadlock detection helps to reduce the overhead of deadlock detection in the system.

ロック モニターで、特定のスレッドに対するデッドロック検出が開始されると、スレッドが待機中のリソースが特定されます。When the lock monitor initiates deadlock search for a particular thread, it identifies the resource on which the thread is waiting. その後、ロック モニターでは、その特定のリソースを所有するスレッドが検出され、相互の従属性を検出するまで、スレッドのデッドロック検出が繰り返されます。The lock monitor then finds the owner(s) for that particular resource and recursively continues the deadlock search for those threads until it finds a cycle. このように検出された相互の従属性により、デッドロックが発生します。A cycle identified in this manner forms a deadlock.

デッドロックが検出されると、データベース エンジンDatabase Engineでは、スレッドの 1 つをデッドロックの対象として選択することによりデッドロックを終了します。After a deadlock is detected, the データベース エンジンDatabase Engine ends a deadlock by choosing one of the threads as a deadlock victim. データベース エンジンDatabase Engineでは、スレッドに対して現在実行中のバッチを終了し、デッドロックの対象のトランザクションをロールバックして、アプリケーションに 1205 エラーを返します。The データベース エンジンDatabase Engine terminates the current batch being executed for the thread, rolls back the transaction of the deadlock victim, and returns a 1205 error to the application. デッドロックの対象のトランザクションをロールバックすると、そのトランザクションで保持されていたすべてのロックが解放されます。Rolling back the transaction for the deadlock victim releases all locks held by the transaction. ロックが解放されると、他のスレッドのトランザクションのブロックは解除され、処理を続行することができるようになります。This allows the transactions of the other threads to become unblocked and continue. デッドロックの対象を示す 1205 エラーにより、デッドロックに関係するスレッドおよびリソースに関する情報がエラー ログに記録されます。The 1205 deadlock victim error records information about the threads and resources involved in a deadlock in the error log.

既定では、データベース エンジンDatabase Engineにより、ロールバックに最もコストのかからないトランザクションを実行しているセッションがデッドロックの対象として選択されます。By default, the データベース エンジンDatabase Engine chooses as the deadlock victim the session running the transaction that is least expensive to roll back. また、ユーザーは、SET DEADLOCK_PRIORITY ステートメントを使用して、デッドロックが発生した場合のセッションの優先度を指定することもできます。Alternatively, a user can specify the priority of sessions in a deadlock situation using the SET DEADLOCK_PRIORITY statement. DEADLOCK_PRIORITY には、LOW、NORMAL、または HIGH を設定するか、あるいは -10 ~ 10 の範囲の整数値を設定することができます。DEADLOCK_PRIORITY can be set to LOW, NORMAL, or HIGH, or alternatively can be set to any integer value in the range (-10 to 10). DEADLOCK_PRIORITY の既定値は NORMAL です。The deadlock priority defaults to NORMAL. 2 つのセッションのデッドロックの優先度が異なる場合、優先度の低いセッションがデッドロックの対象として選択されます。If two sessions have different deadlock priorities, the session with the lower priority is chosen as the deadlock victim. 2 つのセッションのデッドロックの優先度が同じ場合、ロールバックに最もコストのかからないトランザクションを含むセッションがデッドロックの対象として選択されます。If both sessions have the same deadlock priority, the session with the transaction that is least expensive to roll back is chosen. デッドロック サイクルに関連するセッションのデッドロックの優先度とコストが同じ場合、対象はランダムに選択されます。If sessions involved in the deadlock cycle have the same deadlock priority and the same cost, a victim is chosen randomly.

CLR を使用して作業する場合、デッドロック モニターでは、マネージド プロシージャ内でアクセスされる同期リソース (モニター、リーダー ロックとライター ロック、およびスレッド結合) のデッドロックが自動的に検出されます。When working with CLR, the deadlock monitor automatically detects deadlock for synchronization resources (monitors, reader/writer lock and thread join) accessed inside managed procedures. ただし、デッドロックは、デッドロックの対象として選択されたプロシージャに例外をスローすることによって解決されます。However, the deadlock is resolved by throwing an exception in the procedure that was selected to be the deadlock victim. デッドロックの対象が現在所有しているリソースは、この例外により自動的に解放されないことに注意してください。つまり、リソースは明示的に解放する必要があります。It is important to understand that the exception does not automatically release resources currently owned by the victim; the resources must be explicitly released. 例外の動作と一貫性があるため、デッドロックの対象の特定に使用された例外は、キャッチおよび破棄できます。Consistent with exception behavior, the exception used to identify a deadlock victim can be caught and dismissed.

デッドロック情報ツールDeadlock Information Tools

デッドロック情報を表示するために、データベース エンジンDatabase Engineには 2 種類のトレース フラグ形式での監視ツールと SQL Server ProfilerSQL Server Profiler の Deadlock Graph イベントが用意されています。To view deadlock information, the データベース エンジンDatabase Engine provides monitoring tools in the form of two trace flags, and the deadlock graph event in SQL Server ProfilerSQL Server Profiler.

トレース フラグ 1204 およびトレース フラグ 1222Trace Flag 1204 and Trace Flag 1222

デッドロックが発生すると、トレース フラグ 1204 およびトレース フラグ 1222 は SQL ServerSQL Server のエラー ログでキャプチャされる情報を返します。When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL ServerSQL Server error log. トレース フラグ 1204 では、デッドロックに関係するノードごとに形式が設定されたデッドロック情報をレポートします。Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. トレース フラグ 1222 では、最初にプロセスごとに、次にリソースごとにデッドロック情報の形式を設定します。Trace flag 1222 formats deadlock information, first by processes and then by resources. 両方のトレース フラグを有効にして、同じデッドロック イベントを 2 種類の表示方法で取得することも可能です。It is possible to enable both trace flags to obtain two representations of the same deadlock event.

次の表は、トレース フラグ 1204 および 1222 のプロパティの定義の他に、類似点および相違点についても示しています。In addition to defining the properties of trace flag 1204 and 1222, the following table also shows the similarities and differences.

プロパティProperty トレース フラグ 1204 およびトレース フラグ 1222Trace Flag 1204 and Trace Flag 1222 トレース フラグ 1204 のみTrace Flag 1204 only トレース フラグ 1222 のみTrace Flag 1222 only
[出力形式]Output format 出力は SQL ServerSQL Server のエラー ログにキャプチャされます。Output is captured in the SQL ServerSQL Server error log. デッドロックに関係するノードだけが表示されます。Focused on the nodes involved in the deadlock. 各ノードには専用のセクションがあり、最後のセクションではデッドロック対象が示されます。Each node has a dedicated section, and the final section describes the deadlock victim. XML スキーマ定義 (XSD) スキーマには準拠していない、XML に似た形式で情報を返します。Returns information in an XML-like format that does not conform to an XML Schema Definition (XSD) schema. この形式には、3 つの主要なセクションがあります。The format has three major sections. 最初のセクションでは、デッドロック対象が宣言されます。The first section declares the deadlock victim. 2 番目のセクションでは、デッドロックに関係する各プロセスが示されます。The second section describes each process involved in the deadlock. 3 番目のセクションでは、トレース フラグ 1204 のノードと同義のリソースが示されます。The third section describes the resources that are synonymous with nodes in trace flag 1204.
識別属性Identifying attributes SPID:<x > ECID:<x >。SPID:<x> ECID:<x>. 並列処理を行う場合に、システム プロセス ID のスレッドを識別します。Identifies the system process ID thread in cases of parallel processes. エントリSPID:<x> ECID:0ここで、 <x > は SPID 値に置き換え、メイン スレッドを表します。The entry SPID:<x> ECID:0, where <x> is replaced by the SPID value, represents the main thread. エントリSPID:<x> ECID:<y>ここで、 <x > は SPID 値に置き換え、 <y > は 0 より大きく、同じ SPID のサブスレッドを表します。The entry SPID:<x> ECID:<y>, where <x> is replaced by the SPID value and <y> is greater than 0, represents the sub-threads for the same SPID.

BatchID (トレース フラグ 1222 の sbid)。BatchID (sbid for trace flag 1222). コードの実行でロックを要求または保持しているバッチを識別します。Identifies the batch from which code execution is requesting or holding a lock. 複数のアクティブな結果セット (MARS) が無効になっている場合、BatchID の値は 0 になります。When Multiple Active Result Sets (MARS) is disabled, the BatchID value is 0. MARS が有効になっている場合、アクティブなバッチの値は 1 から n になります。When MARS is enabled, the value for active batches is 1 to n. セッションにアクティブなバッチが存在しない場合、BatchID は 0 になります。If there are no active batches in the session, BatchID is 0.

モードMode. スレッドによって要求、許可、または待機される特定のリソースに対して、ロックの種類を指定します。Specifies the type of lock for a particular resource that is requested, granted, or waited on by a thread. モードには、IS (インテント共有)、S (共有)、U (更新)、IX (インテント排他)、SIX (インテント排他付き共有)、および X (排他) があります。Mode can be IS (Intent Shared), S (Shared), U (Update), IX (Intent Exclusive), SIX (Shared with Intent Exclusive), and X (Exclusive).

Line # (トレース フラグ 1222 の)。Line # (line for trace flag 1222). デッドロックが発生したときに実行されていた、現在のステートメントのバッチの行番号が表示されます。Lists the line number in the current batch of statements that was being executed when the deadlock occurred.

Input Buf (トレース フラグ 1222 の inputbuf)。Input Buf (inputbuf for trace flag 1222). 現在のバッチに含まれるステートメントをすべて表示します。Lists all the statements in the current batch.
NodeNode. デッドロック チェーンに含まれるエントリ番号を表します。Represents the entry number in the deadlock chain.

ListsLists. 次の一覧にロックの所有者が含まれる場合があります。The lock owner can be part of these lists:

Grant ListGrant List. リソースの現在の所有者を列挙します。Enumerates the current owners of the resource.

Convert ListConvert List. ロックを高いレベルに変換しようとしている現在の所有者を列挙します。Enumerates the current owners that are trying to convert their locks to a higher level.

Wait ListWait List. リソースに対する現在の新しいロック要求を列挙します。Enumerates current new lock requests for the resource.

Statement TypeStatement Type. 権限を持つスレッドにおける DML ステートメントの種類 (SELECT、INSERT、UPDATE、または DELETE) を示します。Describes the type of DML statement (SELECT, INSERT, UPDATE, or DELETE) on which the threads have permissions.

Victim Resource OwnerVictim Resource Owner. SQL ServerSQL Server がデッドロック サイクルを解除する対象として選択する、参加スレッドを指定します。Specifies the participating thread that SQL ServerSQL Server chooses as the victim to break the deadlock cycle. 選択したスレッドと既存のすべてのサブスレッドを終了します。The chosen thread and all existing sub-threads are terminated.

Next BranchNext Branch. デッドロック サイクルに関係する、同じ SPID からの 2 つ以上のサブスレッドを表します。Represents the two or more sub-threads from the same SPID that are involved in the deadlock cycle.
deadlock victimdeadlock victim. デッドロックの対象として選択されたタスクの物理メモリ アドレス (「sys.dm_os_tasks (Transact-SQL)」を参照) を表します。Represents the physical memory address of the task (see sys.dm_os_tasks (Transact-SQL)) that was selected as a deadlock victim. デッドロックが未解決の場合は、0 になることがあります。It may be 0 (zero) in the case of an unresolved deadlock. ロールバックを実行中のタスクは、デッドロックの対象として選択できません。A task that is rolling back cannot be chosen as a deadlock victim.

executionstackexecutionstack. デッドロックの発生時に実行されている Transact-SQLTransact-SQL コードを表します。Represents Transact-SQLTransact-SQL code that is being executed at the time the deadlock occurs.

prioritypriority. デッドロックの優先度を表します。Represents deadlock priority. データベース エンジンDatabase Engineでは、コンカレンシーを向上させるために、短期間でデッドロックの優先度が変更されることがあります。In certain cases, the データベース エンジンDatabase Engine may opt to alter the deadlock priority for a short duration to achieve better concurrency.

logusedlogused. タスクで使用されているログ領域です。Log space used by the task.

owner id。要求を制御するトランザクションの ID です。owner id. The ID of the transaction that has control of the request.

statusstatus. タスクの状態。State of the task. 次のいずれかの値です。It is one of the following values:

>> pending>> pending. ワーカー スレッドを待機しています。Waiting for a worker thread.

>> runnable>> runnable. 実行できる状態ですが、クォンタムを待機しています。Ready to run but waiting for a quantum.

>> running>> running. スケジューラで現在実行中です。Currently running on the scheduler.

>> suspended>> suspended. 実行は中断されます。Execution is suspended.

>> done>> done. タスクが完了しました。Task has completed.

>> spinloop>> spinloop. スピンロックが解放されるのを待機しています。Waiting for a spinlock to become free.

waitresourcewaitresource. タスクで必要なリソースです。The resource needed by the task.

waittimewaittime. リソースを待機する時間 (ミリ秒単位) です。Time in milliseconds waiting for the resource.

scheduleridschedulerid. このタスクに関連付けられたスケジューラです。Scheduler associated with this task. sys.dm_os_schedulers (Transact-SQL)」をご覧ください。See sys.dm_os_schedulers (Transact-SQL).

hostnamehostname. ワークステーションの名前です。The name of the workstation.

isolationlevelisolationlevel. 現在のトランザクション分離レベルです。The current transaction isolation level.

XactidXactid. 要求を制御するトランザクションの ID です。The ID of the transaction that has control of the request.

currentdbcurrentdb. データベースの ID です。The ID of the database.

lastbatchstartedlastbatchstarted. クライアント プロセスで最後にバッチ実行が開始した時刻です。The last time a client process started batch execution.

lastbatchcompletedlastbatchcompleted. クライアント プロセスで最後にバッチ実行が完了した時刻です。The last time a client process completed batch execution.

clientoption1 and clientoption2clientoption1 and clientoption2. このクライアント接続にオプションを設定します。Set options on this client connection. これは、通常 SET NOCOUNT や SET XACTABORT などの SET ステートメントで制御されているオプションに関する情報を含むビットマスクです。This is a bitmask that includes information about options usually controlled by SET statements such as SET NOCOUNT and SET XACTABORT.

associatedObjectIdassociatedObjectId. HoBT (ヒープまたは B-Tree) の ID を表します。Represents the HoBT (heap or b-tree) ID.
リソース属性Resource attributes RIDRID. ロックが保持または要求されているテーブル内の単一行を識別します。Identifies the single row within a table on which a lock is held or requested. RID は、RID: db_id:file_id:page_no:row_no として表されます。RID is represented as RID: db_id:file_id:page_no:row_no. たとえば、RID: 6:1:20789:0 のようにします。For example, RID: 6:1:20789:0.

OBJECTOBJECT. ロックが保持または要求されているテーブルを識別します。Identifies the table on which a lock is held or requested. OBJECT は、OBJECT: db_id:object_id として表されます。OBJECT is represented as OBJECT: db_id:object_id. たとえば、TAB: 6:2009058193 のようにします。For example, TAB: 6:2009058193.

KEYKEY. ロックが保持または要求されているインデックス内のキー範囲を識別します。Identifies the key range within an index on which a lock is held or requested. KEY は、KEY: db_id:hobt_id (インデックス キー ハッシュ値) として表されます。KEY is represented as KEY: db_id:hobt_id (index key hash value). たとえば、KEY: 6:72057594057457664 (350007a4d329) のようにします。For example, KEY: 6:72057594057457664 (350007a4d329).

PAGPAG. ロックが保持または要求されているページ リソースを識別します。Identifies the page resource on which a lock is held or requested. PAG は、PAG: db_id:file_id:page_no として表されます。PAG is represented as PAG: db_id:file_id:page_no. たとえば、PAG: 6:1:20789 のようにします。For example, PAG: 6:1:20789.

EXTEXT. エクステント構造を識別します。Identifies the extent structure. EXT は、EXT: db_id:file_id:extent_no として表されます。EXT is represented as EXT: db_id:file_id:extent_no. たとえば、EXT: 6:1:9 のようにします。For example, EXT: 6:1:9.

DBDB. データベース ロックを識別します。Identifies the database lock. DB は次のいずれかで表されます。DB is represented in one of the following ways:

DB: db_idDB: db_id

DB: db_id[BULK-OP-DB]。データベースのバックアップに使用されたデータベース ロックを識別します。DB: db_id[BULK-OP-DB], which identifies the database lock taken by the backup database.

DB: db_id[BULK-OP-LOG]。特定のデータベースのバックアップ ログに使用されたロックを識別します。DB: db_id[BULK-OP-LOG], which identifies the lock taken by the backup log for that particular database.

APPAPP. アプリケーション リソースに使用されたロックを識別します。Identifies the lock taken by an application resource. APP は、APP: lock_resource として表されます。APP is represented as APP: lock_resource. たとえば、APP: Formf370f478 のようにします。For example, APP: Formf370f478.

METADATAMETADATA. デッドロックに関係するメタデータ リソースを表します。Represents metadata resources involved in a deadlock. METADATA には多数のサブリソースがあるため、返される値はデッドロックされたサブリソースに依存します。Because METADATA has many subresources, the value returned depends upon the subresource that has deadlocked. たとえば、メタデータです。USER_TYPE 返しますuser_type_id = < integer_value>。For example, METADATA.USER_TYPE returns user_type_id = <integer_value>. METADATA のリソースおよびサブリソースについて詳しくは、「sys.dm_tran_locks (Transact-SQL)」をご覧ください。For more information about METADATA resources and subresources, see sys.dm_tran_locks (Transact-SQL).

HOBTHOBT. デッドロックに関係するヒープまたは B-Tree を表します。Represents a heap or b-tree involved in a deadlock.
このトレース フラグに限定されるリソース属性はありません。None exclusive to this trace flag. このトレース フラグに限定されるリソース属性はありません。None exclusive to this trace flag.
トレース フラグ 1204 の例Trace Flag 1204 Example

次の例は、トレース フラグ 1204 がオンになっている場合の出力を示しています。The following example shows the output when trace flag 1204 is turned on. この場合、ノード 1 のテーブルはインデックスのないヒープ、ノード 2 のテーブルは非クラスター化インデックスのあるヒープになります。In this case, the table in Node 1 is a heap with no indexes, and the table in Node 2 is a heap with a nonclustered index. ノード 2 のインデックス キーは、デッドロックの発生時に更新されます。The index key in Node 2 is being updated when the deadlock occurs.

Deadlock encountered .... Printing deadlock information  
Wait-for graph  
  
Node:1  
  
RID: 6:1:20789:0               CleanCnt:3 Mode:X Flags: 0x2  
 Grant List 0:  
   Owner:0x0315D6A0 Mode: X          
     Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C  
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6  
   Input Buf: Language Event:   
BEGIN TRANSACTION  
   EXEC usp_p2  
 Requested By:   
   ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0   
     Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868)  
  
Node:2  
  
KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0  
 Grant List 0:  
   Owner:0x0315D140 Mode: X          
     Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4  
   SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6  
   Input Buf: Language Event:   
     BEGIN TRANSACTION  
       EXEC usp_p1  
 Requested By:   
   ResType:LockOwner Stype:'OR'Xdes:0x04D9E258   
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)  
  
Victim Resource Owner:  
 ResType:LockOwner Stype:'OR'Xdes:0x04D9E258   
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)  
トレース フラグ 1222 の例Trace Flag 1222 Example

次の例は、トレース フラグ 1222 がオンになっている場合の出力を示しています。The following example shows the output when trace flag 1222 is turned on. この場合、一方のテーブルがインデックスのないヒープになり、他方のテーブルが非クラスター化インデックスのあるヒープになります。In this case, one table is a heap with no indexes, and the other table is a heap with a nonclustered index. 2 番目のテーブルでは、デッドロックの発生時にインデックス キーが更新されます。In the second table, the index key is being updated when the deadlock occurs.

deadlock-list  
 deadlock victim=process689978  
  process-list  
   process id=process6891f8 taskpriority=0 logused=868   
   waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444   
   transactionname=user_transaction   
   lasttranstarted=2005-09-05T11:22:42.733 XDES=0x3a3dad0   
   lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54   
   sbid=0 ecid=0 priority=0 transcount=2   
   lastbatchstarted=2005-09-05T11:22:42.733   
   lastbatchcompleted=2005-09-05T11:22:42.733   
   clientapp=Microsoft SQL Server Management Studio - Query   
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user   
   isolationlevel=read committed (2) xactid=310444 currentdb=6   
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200  
    executionStack  
     frame procname=AdventureWorks2012.dbo.usp_p1 line=6 stmtstart=202   
     sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000  
     UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;       
     frame procname=adhoc line=3 stmtstart=44   
     sqlhandle=0x01000600856aa70f503b8104000000000000000000000000  
     EXEC usp_p1       
    inputbuf  
      BEGIN TRANSACTION  
       EXEC usp_p1  
   process id=process689978 taskpriority=0 logused=380   
   waitresource=KEY: 6:72057594057457664 (350007a4d329)     
   waittime=5015 ownerId=310462 transactionname=user_transaction   
   lasttranstarted=2005-09-05T11:22:44.077 XDES=0x4d9e258 lockMode=U   
   schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0   
   priority=0 transcount=2 lastbatchstarted=2005-09-05T11:22:44.077   
   lastbatchcompleted=2005-09-05T11:22:44.077   
   clientapp=Microsoft SQL Server Management Studio - Query   
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user   
   isolationlevel=read committed (2) xactid=310462 currentdb=6   
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200  
    executionStack  
     frame procname=AdventureWorks2012.dbo.usp_p2 line=6 stmtstart=200   
     sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000  
     UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;       
     frame procname=adhoc line=3 stmtstart=44   
     sqlhandle=0x01000600d688e709b85f8904000000000000000000000000  
     EXEC usp_p2       
    inputbuf  
      BEGIN TRANSACTION  
        EXEC usp_p2      
  resource-list  
   ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks2012.dbo.T2   
   id=lock3136940 mode=X associatedObjectId=72057594057392128  
    owner-list  
     owner id=process689978 mode=X  
    waiter-list  
     waiter id=process6891f8 mode=U requestType=wait  
   keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2012.dbo.T1   
   indexname=nci_T1_COL1 id=lock3136fc0 mode=X   
   associatedObjectId=72057594057457664  
    owner-list  
     owner id=process6891f8 mode=X  
    waiter-list  
     waiter id=process689978 mode=U requestType=wait  
Profiler の Deadlock Graph イベントProfiler Deadlock Graph Event

デッドロックに関係するタスクおよびリソースをグラフィカルに表す SQL Server ProfilerSQL Server Profiler のイベントです。This is an event in SQL Server ProfilerSQL Server Profiler that presents a graphical depiction of the tasks and resources involved in a deadlock. 次の例は、Deadlock Graph イベントがオンになっている場合の SQL Server ProfilerSQL Server Profiler からの出力を示しています。The following example shows the output from SQL Server ProfilerSQL Server Profiler when the deadlock graph event is turned on.

ロジック フロー ダイアグラムが表示されたユーザー プロセスのデッドロック。Logic flow diagram showing user process deadlock.

実行の詳細については、 SQL Server ProfilerSQL Server Profiler deadlock graph は、「 Deadlock Graph の保存(SQL Server Profiler)します。For more information about running the SQL Server ProfilerSQL Server Profiler deadlock graph, see Save Deadlock Graphs (SQL Server Profiler).

デッドロックの処理Handling Deadlocks

SQL Server データベース エンジンSQL Server Database Engineのインスタンスにより、デッドロックの対象となっているトランザクションが判断されると、現在のバッチが終了され、そのトランザクションがロールバックされます。また、このインスタンスからエラー メッセージ 1205 がアプリケーションに返されます。When an instance of the SQL Server データベース エンジンSQL Server Database Engine chooses a transaction as a deadlock victim, it terminates the current batch, rolls back the transaction, and returns error message 1205 to the application.

Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

Transact-SQLTransact-SQL クエリを送信するアプリケーションはどれも、デッドロックの対象と判断される可能性があるため、アプリケーションには、エラー メッセージ 1205 をトラップできるエラー ハンドラーを実装する必要があります。Because any application submitting Transact-SQLTransact-SQL queries can be chosen as the deadlock victim, applications should have an error handler that can trap error message 1205. このエラーがアプリケーションでトラップされない場合、アプリケーションでは自身のトランザクションがロールバックされたことが認識されず、エラーが発生することがあります。If an application does not trap the error, the application can proceed unaware that its transaction has been rolled back and errors can occur.

エラー メッセージ 1205 をトラップするエラー ハンドラーを実装すると、アプリケーションでデッドロックの状況に対応し、デッドロックと関連のあるクエリの自動再送信などの救済措置を講じることができます。Implementing an error handler that traps error message 1205 allows an application to handle the deadlock situation and take remedial action (for example, automatically resubmitting the query that was involved in the deadlock). クエリを自動的に再送信すると、ユーザーにデッドロックが発生したことを通知する必要はありません。By resubmitting the query automatically, the user does not need to know that a deadlock occurred.

アプリケーションは、クエリを再送信する前に少しの間停止する必要があります。The application should pause briefly before resubmitting its query. この一時停止により、デッドロックにかかわっている他のトランザクションでは、処理を完了し、デッドロックのサイクルの一因となっているロックを解放することができます。This gives the other transaction involved in the deadlock a chance to complete and release its locks that formed part of the deadlock cycle. このようにすることで、再送信されたクエリからロックが要求されたときに、デッドロックが再発する可能性を最小限に抑えられます。This minimizes the likelihood of the deadlock reoccurring when the resubmitted query requests its locks.

デッドロックの最小化Minimizing Deadlocks

デッドロックを完全に回避することはできませんが、コーディング上の一定の規則に従うことにより、デッドロックが発生する可能性を最小限に抑えることができます。Although deadlocks cannot be completely avoided, following certain coding conventions can minimize the chance of generating a deadlock. デッドロックの発生数を抑えると、以下の理由から、トランザクションのスループットが向上し、システムのオーバーヘッドが減少します。Minimizing deadlocks can increase transaction throughput and reduce system overhead because fewer transactions are:

  • ロールバック (トランザクションが実行したすべての処理の取り消し) の対象となるトランザクションの数が減少します。Rolled back, undoing all the work performed by the transaction.

  • デッドロック後にロールバックされたトランザクションをアプリケーションが再実行する場合、対象となるトランザクションの数が減少します。Resubmitted by applications because they were rolled back when deadlocked.

デッドロックを最小限に抑えるには、次の手順を実行します。To help minimize deadlocks:

  • 同じ順序でオブジェクトにアクセスします。Access objects in the same order.

  • トランザクション内でのユーザーとのやり取りを避けます。Avoid user interaction in transactions.

  • トランザクションを短くして 1 つのバッチ内に収めます。Keep transactions short and in one batch.

  • 低い分離レベルを使用します。Use a lower isolation level.

  • 行のバージョン管理に基づく分離レベルを使用します。Use a row versioning-based isolation level.

    • READ_COMMITTED_SNAPSHOT データベース オプションを ON に設定して READ COMMITTED トランザクションを有効にして、行のバージョン管理を使用できるようにします。Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.

    • スナップショット分離を使用します。Use snapshot isolation.

  • バインドされた接続を使用します。Use bound connections.

同じ順序でのオブジェクトへのアクセスAccess Objects in the Same Order

すべての同時実行トランザクションが同じ順序でオブジェクトにアクセスすると、デッドロックの発生する可能性は低くなります。If all concurrent transactions access objects in the same order, deadlocks are less likely to occur. たとえば、2 つの同時実行トランザクションが Supplier テーブルでロックを取得してから、Part テーブルでロックを取得する場合、一方のトランザクションは、もう一方のトランザクションが完了するまで Supplier テーブルでブロックされます。For example, if two concurrent transactions obtain a lock on the Supplier table and then on the Part table, one transaction is blocked on the Supplier table until the other transaction is completed. 1 番目のトランザクションがコミットまたはロールバックされた後に 2 番目のトランザクションが続行されるので、デッドロックは発生しません。After the first transaction commits or rolls back, the second continues, and a deadlock does not occur. すべてのデータ変更にストアド プロシージャを使用すると、オブジェクトへのアクセス順序を統一できます。Using stored procedures for all data modifications can standardize the order of accessing objects.

デッドロックを回避することを示す図Diagram showing deadlock avoidance

トランザクション内でのユーザーとのやり取りの回避Avoid User Interaction in Transactions

ユーザーとのやり取りを含むトランザクションを避けます。ユーザーの介入なしにバッチを実行すれば、ユーザーが手動でクエリに応答する場合、つまりアプリケーションによるパラメーター入力要求に手動で応答する場合などよりはるかに高速に処理できます。Avoid writing transactions that include user interaction, because the speed of batches running without user intervention is much faster than the speed at which a user must manually respond to queries, such as replying to a prompt for a parameter requested by an application. たとえば、トランザクションがユーザーの入力を待機しているときに、ユーザーが昼食に出ていたり、週末で家に帰っていると、トランザクションの完了は大幅に延期されます。For example, if a transaction is waiting for user input and the user goes to lunch or even home for the weekend, the user delays the transaction from completing. トランザクションが保持するロックを解除するにはトランザクションのコミットまたはロールバックが必要なので、このような状況ではシステムのスループットが低下してしまいます。This degrades system throughput because any locks held by the transaction are released only when the transaction is committed or rolled back. デッドロックが発生しないとしても、トランザクションが完了するまで、同じリソースにアクセスしている他のトランザクションはブロックされます。Even if a deadlock situation does not arise, other transactions accessing the same resources are blocked while waiting for the transaction to complete.

1 つのバッチ内でのトランザクションの短縮Keep Transactions Short and in One Batch

デッドロックは主に、同じデータベースで長時間動作するトランザクションがいくつか同時に実行されている場合に発生します。A deadlock typically occurs when several long-running transactions execute concurrently in the same database. トランザクションが長くなれば、排他ロックまたは更新ロックが長時間になり、他の処理をブロックしてしまうので、デッドロックが発生する可能性が高くなります。The longer the transaction, the longer the exclusive or update locks are held, blocking other activity and leading to possible deadlock situations.

トランザクションを 1 つのバッチ内に収めると、トランザクション実行時のネットワーク経由のやり取りを最小限に抑えられ、トランザクションの完了やロックの解除が遅延する可能性を低くすることができます。Keeping transactions in one batch minimizes network roundtrips during a transaction, reducing possible delays in completing the transaction and releasing locks.

低い分離レベルの使用Use a Lower Isolation Level

低い分離レベルでトランザクションが実行可能かどうかを調べます。Determine whether a transaction can run at a lower isolation level. READ COMMITTED を実装すると、トランザクションは、別のトランザクションが以前に読み取った (変更されていない) データを読み取ることができるので、最初のトランザクションが完了するまで待機する必要がありません。Implementing read committed allows a transaction to read data previously read (not modified) by another transaction without waiting for the first transaction to complete. READ COMMITTED などの低い分離レベルを使用すると、SERIALIZABLE などの高い分離レベルの場合よりも共有ロックの保持時間が短くなります。Using a lower isolation level, such as read committed, holds shared locks for a shorter duration than a higher isolation level, such as serializable. これにより、ロックの競合が減少します。This reduces locking contention.

行のバージョン管理に基づく分離レベルの使用Use a Row Versioning-based Isolation Level

READ_COMMITTED_SNAPSHOT データベース オプションが ON に設定されている場合、READ COMMITTED 分離レベルで実行されているトランザクションでは、読み取り操作中に、共有ロックではなく行のバージョン管理を使用します。When the READ_COMMITTED_SNAPSHOT database option is set ON, a transaction running under read committed isolation level uses row versioning rather than shared locks during read operations.

注意

一部のアプリケーションは、READ COMMITTED 分離レベルのロックおよびブロックの動作に依存します。Some applications rely upon locking and blocking behavior of read committed isolation. このようなアプリケーションについては、このオプションを使用する前に多少の変更が必要になります。For these applications, some change is required before this option can be enabled.

スナップショット分離レベルでも行のバージョン管理を使用します。行のバージョン管理では、読み取り操作中に共有ロックを使用しません。Snapshot isolation also uses row versioning, which does not use shared locks during read operations. スナップショット分離レベルでトランザクションを実行する前に、ALLOW_SNAPSHOT_ISOLATION データベース オプションを ON に設定する必要があります。Before a transaction can run under snapshot isolation, the ALLOW_SNAPSHOT_ISOLATION database option must be set ON.

これらの分離レベルを実装すると、読み取り操作と書き込み操作の間に発生する可能性があるデッドロックを最小限に抑えることができます。Implement these isolation levels to minimize deadlocks that can occur between read and write operations.

バインドされた接続の使用Use Bound Connections

バインドされた接続を使用すると、同じアプリケーションによって開かれた複数の接続が相互に協調動作できます。Using bound connections, two or more connections opened by the same application can cooperate with each other. 最初の接続が取得したロックと同じように次の接続が取得したロックも保持されます。また、その逆の場合も同様に保持されます。Any locks acquired by the secondary connections are held as if they were acquired by the primary connection, and vice versa. したがって、相互にブロックすることはありません。Therefore they do not block each other.

ロックのパーティション分割Lock Partitioning

大規模なコンピューター システムでは、頻繁に参照されるオブジェクトのロックがパフォーマンスのボトルネックになることがあります。これは、ロックの獲得と解放により、内部ロック リソースで競合が発生するためです。For large computer systems, locks on frequently referenced objects can become a performance bottleneck as acquiring and releasing locks place contention on internal locking resources. ロックのパーティション分割を行うと、単一のロック リソースが複数のロック リソースに分割されるので、ロックのパフォーマンスが向上します。Lock partitioning enhances locking performance by splitting a single lock resource into multiple lock resources. この機能は、16 基以上の CPU を搭載しているシステムでのみ使用でき、自動的に有効になります。この機能を無効にすることはできません。This feature is only available for systems with 16 or more CPUs, and is automatically enabled and cannot be disabled. パーティション分割できるのはオブジェクト ロックのみです。サブタイプがあるオブジェクト ロックはパーティション分割できません。Only object locks can be partitioned.Object locks that have a subtype are not partitioned. 詳しくは、「sys.dm_tran_locks (Transact-SQL)」をご覧ください。For more information, see sys.dm_tran_locks (Transact-SQL).

ロックのパーティション分割についてUnderstanding Lock Partitioning

ロック タスクでは、複数の共有リソースへのアクセスが行われます。これらのうち、次の 2 つがロックのパーティション分割によって最適化されます。Locking tasks access several shared resources, two of which are optimized by lock partitioning:

  • スピンロックSpinlock. 行やテーブルなどのロック リソースへのアクセスを制御します。This controls access to a lock resource, such as a row or a table.

    ロックのパーティション分割を行わない場合は、1 つのスピンロックにより単一のロック リソースのすべてのロック要求が管理されます。Without lock partitioning, one spinlock manages all lock requests for a single lock resource. 大量の処理が行われるシステムでは、スピンロックが使用できるようになるまでロック要求が待機するので、競合が発生する場合があります。On systems that experience a large volume of activity, contention can occur as lock requests wait for the spinlock to become available. この状況では、ロックの獲得がボトルネックになり、パフォーマンスが低下することがあります。Under this situation, acquiring locks can become a bottleneck and can negatively impact performance.

    単一のロック リソースの競合を減らすには、ロックのパーティション分割によって単一のロック リソースを複数のロック リソースに分割し、複数のスピンロックに負荷を分散します。To reduce contention on a single lock resource, lock partitioning splits a single lock resource into multiple lock resources to distribute the load across multiple spinlocks.

  • メモリMemory. ロック リソースの構造を格納するために使用されます。This is used to store the lock resource structures.

    スピンロックが獲得されると、ロック構造がメモリに格納されます。その後、ロック構造へのアクセスが行われ、場合によっては変更されることがあります。Once the spinlock is acquired, lock structures are stored in memory and then accessed and possibly modified. ロックへのアクセスを複数のリソースに分散すると、CPU 間でメモリ ブロックを転送する必要がなくなり、パフォーマンスが向上します。Distributing lock access across multiple resources helps to eliminate the need to transfer memory blocks between CPUs, which will help to improve performance.

ロックのパーティション分割の実装と監視Implementing and Monitoring Lock Partitioning

ロックのパーティション分割は、16 基以上の CPU を搭載しているシステムでは既定で有効になっています。Lock partitioning is turned on by default for systems with 16 or more CPUs. ロックのパーティション分割が有効になっていると、情報メッセージが SQL ServerSQL Server エラー ログに記録されます。When lock partitioning is enabled, an informational message is recorded in the SQL ServerSQL Server error log.

パーティション分割されたリソースのロックを獲得するときの規則を次に示します。When acquiring locks on a partitioned resource:

  • 単一のパーティションに対して獲得されるロック モードは、NL、SCH-S、IS、IU、および IX のみです。Only NL, SCH-S, IS, IU, and IX lock modes are acquired on a single partition.

  • 共有 (S) ロック、排他 (X) ロック、および NL、SCH-S、IS、IU、IX 以外のモードの他のロックは、パーティション ID が 0 のパーティションから、パーティション ID 順に獲得される必要があります。Shared (S), exclusive (X), and other locks in modes other than NL, SCH-S, IS, IU, and IX must be acquired on all partitions starting with partition ID 0 and following in partition ID order. パーティション分割されたリソースでは、パーティションごとに別のロックが獲得されます。そのため、これらのパーティション分割されたリソースのロックでは、パーティション分割されていないリソースの同じモードのロックよりも多くのメモリが使用されます。These locks on a partitioned resource will use more memory than locks in the same mode on a non-partitioned resource since each partition is effectively a separate lock. メモリの増加量は、パーティションの数によって決まります。The memory increase is determined by the number of partitions. Windows パフォーマンス モニターの SQL ServerSQL Server ロック カウンターにより、パーティション分割されたロックとパーティション分割されていないロックによって使用されたメモリに関する情報が表示されます。The SQL ServerSQL Server lock counters in the Windows Performance Monitor will display information about memory used by partitioned and non-partitioned locks.

トランザクションは、開始したときにパーティションに割り当てられます。A transaction is assigned to a partition when the transaction starts. トランザクションでは、パーティション分割できるすべてのロック要求により、そのトランザクションに割り当てられたパーティションが使用されます。For the transaction, all lock requests that can be partitioned use the partition assigned to that transaction. この方法により、複数のトランザクションから同じオブジェクトのロック リソースへのアクセスが異なるパーティションに分散されます。By this method, access to lock resources of the same object by different transactions is distributed across different partitions.

sys.dm_tran_locks 動的管理ビューの resource_lock_partition 列により、ロックがパーティション分割されたリソースのロック パーティション ID が提供されます。The resource_lock_partition column in the sys.dm_tran_locks Dynamic Management View provides the lock partition ID for a lock partitioned resource. 詳しくは、「sys.dm_tran_locks (Transact-SQL)」をご覧ください。For more information, see sys.dm_tran_locks (Transact-SQL).

SQL Server ProfilerSQL Server Profiler の Locks イベントでは、BigintData1 列により、ロックがパーティション分割されたリソースのロック パーティション ID が提供されます。Under the Locks event in SQL Server ProfilerSQL Server Profiler, the BigintData1 column provides the lock partition ID for a lock partitioned resource.

ロックのパーティション分割を使用した作業Working with Lock Partitioning

次に、ロックのパーティション分割の例を示します。The following code examples illustrate lock partitioning. この例では、16 基の CPU を搭載しているコンピューター システムでのロックのパーティション分割の動作を示すために、2 つのトランザクションを 2 つの異なるセッションで実行します。In the examples, two transactions are executed in two different sessions in order to show lock partitioning behavior on a computer system with 16 CPUs.

これらの Transact-SQLTransact-SQL ステートメントにより、その後の例で使用するテスト オブジェクトが作成されます。These Transact-SQLTransact-SQL statements create test objects that are used in the examples that follow.

-- Create a test table.  
CREATE TABLE TestTable  
    (col1        int);  
GO  
  
-- Create a clustered index on the table.  
CREATE CLUSTERED INDEX ci_TestTable   
    ON TestTable (col1);  
GO  
  
-- Populate the table.  
INSERT INTO TestTable VALUES (1);  
GO  
例 AExample A

セッション 1:Session 1:

あるトランザクションで SELECT ステートメントが実行されます。A SELECT statement is executed under a transaction. HOLDLOCK ロック ヒントにより、このステートメントではテーブルのインテント共有 (IS) ロックが獲得および保持されます (ここでは、説明のため、行ロックとページ ロックは無視します)。Because of the HOLDLOCK lock hint, this statement will acquire and retain an Intent shared (IS) lock on the table (for this illustration, row and page locks are ignored). IS ロックは、トランザクションに割り当てられたパーティションに対してのみ獲得されます。The IS lock will be acquired only on the partition assigned to the transaction. この例では、パーティション ID 7 に対して IS ロックが獲得されるものとします。For this example, it is assumed that the IS lock is acquired on partition ID 7.

-- Start a transaction.  
BEGIN TRANSACTION  
    -- This SELECT statement will acquire an IS lock on the table.  
    SELECT col1  
        FROM TestTable  
        WITH (HOLDLOCK);  

セッション 2:Session 2:

トランザクションが開始され、このトランザクションで実行されている SELECT ステートメントにより、テーブルの共有 (S) ロックが獲得および保持されます。A transaction is started, and the SELECT statement running under this transaction will acquire and retain a shared (S) lock on the table. S ロックはすべてのパーティションに対して獲得されるため、複数のテーブル ロック (各パーティションに 1 つのロック) が存在することになります。The S lock will be acquired on all partitions which results in multiple table locks, one for each partition. たとえば、16 基の CPU を搭載しているシステムで、ロック パーティション ID 0 ~ 15 に 16 個の S ロックが発行されるとします。For example, on a 16-cpu system, 16 S locks will be issued across lock partition IDs 0-15. S ロックは、セッション 1 のトランザクションによりパーティション ID 7 に対して保持されている IS ロックと互換性があるので、トランザクション間のブロッキングは発生しません。Because the S lock is compatible with the IS lock being held on partition ID 7 by the transaction in session 1, there is no blocking between transactions.

BEGIN TRANSACTION  
    SELECT col1  
        FROM TestTable  
        WITH (TABLOCK, HOLDLOCK);  

セッション 1:Session 1:

セッション 1 において依然としてアクティブなトランザクションで次の SELECT ステートメントが実行されます。The following SELECT statement is executed under the transaction that is still active under session 1. 排他 (X) テーブル ロック ヒントにより、このトランザクションではテーブルの X ロックの獲得が試行されます。Because of the exclusive (X) table lock hint, the transaction will attempt to acquire an X lock on the table. ただし、セッション 2 のトランザクションで保持されている S ロックにより、パーティション ID 0 で X ロックがブロックされます。However, the S lock that is being held by the transaction in session 2 will block the X lock at partition ID 0.

SELECT col1  
    FROM TestTable  
    WITH (TABLOCKX);  
例 BExample B

セッション 1:Session 1:

あるトランザクションで SELECT ステートメントが実行されます。A SELECT statement is executed under a transaction. HOLDLOCK ロック ヒントにより、このステートメントではテーブルのインテント共有 (IS) ロックが獲得および保持されます (ここでは、説明のため、行ロックとページ ロックは無視します)。Because of the HOLDLOCK lock hint, this statement will acquire and retain an Intent shared (IS) lock on the table (for this illustration, row and page locks are ignored). IS ロックは、トランザクションに割り当てられたパーティションに対してのみ獲得されます。The IS lock will be acquired only on the partition assigned to the transaction. この例では、パーティション ID 6 に対して IS ロックが獲得されるものとします。For this example, it is assumed that the IS lock is acquired on partition ID 6.

-- Start a transaction.  
BEGIN TRANSACTION  
    -- This SELECT statement will acquire an IS lock on the table.  
    SELECT col1  
        FROM TestTable  
        WITH (HOLDLOCK);  

セッション 2:Session 2:

あるトランザクションで SELECT ステートメントが実行されます。A SELECT statement is executed under a transaction. TABLOCKX ロック ヒントにより、このトランザクションではテーブルに対して排他 (X) ロックの獲得が試行されます。Because of the TABLOCKX lock hint, the transaction tries to acquire an exclusive (X) lock on the table. X ロックはパーティション ID が 0 のパーティションから、パーティション ID 順に獲得される必要があることに注意してください。Remember that the X lock must be acquired on all partitions starting with partition ID 0. X ロックはパーティション ID 0 ~ 5 のパーティションに対して獲得されますが、パーティション ID 6 に対して獲得された IS ロックによりブロックされます。The X lock will be acquired on all partitions IDs 0-5 but will be blocked by the IS lock that is acquired on partition ID 6.

X ロックが獲得されていないパーティション ID 7 ~ 15 に対しては、他のトランザクションがロックの獲得を続行できます。On partition IDs 7-15 that the X lock has not yet reached, other transactions can continue to acquire locks.

BEGIN TRANSACTION  
    SELECT col1  
        FROM TestTable  
        WITH (TABLOCKX, HOLDLOCK);  

上部のリンクに戻る で使用される矢印アイコンこのガイドでArrow icon used with Back to Top link In This Guide

データベース エンジンでの行のバージョン管理に基づく分離レベルRow Versioning-based Isolation Levels in the Database Engine

SQL Server 2005 以降のデータベース エンジンでは、既存の READ COMMITTED トランザクション分離レベルで、行のバージョン管理によるステートメント レベルのスナップショットを使用できます。Starting with SQL Server 2005, the Database Engine offers an implementation of an existing transaction isolation level, read committed, that provides a statement level snapshot using row versioning. SQL Server データベース エンジンでは、同じく行のバージョン管理によりトランザクション レベルのスナップショットを提供する SNAPSHOT トランザクション分離レベルも使用できます。SQL Server Database Engine also offers a transaction isolation level, snapshot, that provides a transaction level snapshot also using row versioning.

行のバージョン管理とは、行が変更または削除されると書き込み時コピーのメカニズムを起動する、SQL ServerSQL Server の一般的なフレームワークです。Row versioning is a general framework in SQL ServerSQL Server that invokes a copy-on-write mechanism when a row is modified or deleted. このフレームワークでは、トランザクション内の一貫性に関する以前の状態を必要とするようなトランザクションの実行中に、行の古いバージョンをそのトランザクションで使用できることが求められます。This requires that while the transaction is running, the old version of the row must be available for transactions that require an earlier transactionally consistent state. 行のバージョン管理は、次の目的で使用されます。Row versioning is used to do the following:

  • トリガーで挿入されたテーブルまたは削除されたテーブルを作成する。Build the inserted and deleted tables in triggers. トリガーによって変更された行はすべて、バージョン化されます。Any rows modified by the trigger are versioned. これには、トリガーによりデータが変更された行だけでなく、トリガーを起動したステートメントにより変更された行も含まれます。This includes the rows modified by the statement that launched the trigger, as well as any data modifications made by the trigger.

  • 複数のアクティブな結果セット (MARS) をサポートする。Support Multiple Active Result Sets (MARS). アクティブな結果セットが存在するときに、MARS セッションでデータ変更ステートメント (INSERT、UPDATE、DELETE など) が実行された場合、その変更ステートメントの影響を受けた行はバージョン化されます。If a MARS session issues a data modification statement (such as INSERT, UPDATE, or DELETE) at a time there is an active result set, the rows affected by the modification statement are versioned.

  • ONLINE オプションを指定するインデックス操作をサポートする。Support index operations that specify the ONLINE option.

  • 次の行バージョン ベースのトランザクション分離レベルをサポートする。Support row versioning-based transaction isolation levels:

    • 行のバージョン管理を使用してステートメントレベルの読み取りの一貫性を保証する新しい READ COMMITTED 分離レベルの実装。A new implementation of read committed isolation level that uses row versioning to provide statement-level read consistency.

    • 新しい分離レベルであるスナップショット。このレベルにより、トランザクションレベルの読み取り一貫性を実現します。A new isolation level, snapshot, to provide transaction-level read consistency.

tempdb データベースには、バージョン ストア用の十分なディスク領域が必要です。The tempdb database must have enough space for the version store. tempdb がいっぱいになると、更新操作では、操作を完了するためにバージョンの生成を停止して処理を続行しますが、読み取り操作は失敗することがあります。これは、必要な特定の行のバージョンが存在しないためです。When tempdb is full, update operations will stop generating versions and continue to succeed, but read operations might fail because a particular row version that is needed no longer exists. 特定の行のバージョンが存在しないことにより、トリガー、MARS、オンラインのインデックス構築などの操作が影響を受けます。This affects operations like triggers, MARS, and online indexing.

READ COMMITTED トランザクションとスナップショット トランザクションで行のバージョン管理を使用するには、次の 2 つの手順を実行します。Using row versioning for read-committed and snapshot transactions is a two-step process:

  1. READ_COMMITTED_SNAPSHOT データベース オプションと ALLOW_SNAPSHOT_ISOLATION データベース オプションのいずれかまたは両方を ON に設定します。Set either or both the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options ON.

  2. 次の説明に従って、アプリケーションで適切なトランザクション分離レベルを設定します。Set the appropriate transaction isolation level in an application:

    • READ_COMMITTED_SNAPSHOT データベース オプションを ON に設定すると、READ COMMITTED 分離レベルを設定するトランザクションで行のバージョン管理が使用されます。When the READ_COMMITTED_SNAPSHOT database option is ON, transactions setting the read committed isolation level use row versioning.

    • ALLOW_SNAPSHOT_ISOLATION データベース オプションを ON に設定すると、トランザクションでスナップショット分離レベルを設定できます。When the ALLOW_SNAPSHOT_ISOLATION database option is ON, transactions can set the snapshot isolation level.

READ_COMMITTED_SNAPSHOT データベース オプションまたは ALLOW_SNAPSHOT_ISOLATION データベース オプションのいずれかを ON に設定すると、SQL Server データベース エンジンSQL Server Database Engineにより、行のバージョン管理を使用してデータを操作する各トランザクションにトランザクション シーケンス番号 (XSN) が割り当てられます。When either READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database option is set ON, the SQL Server データベース エンジンSQL Server Database Engine assigns a transaction sequence number (XSN) to each transaction that manipulates data using row versioning. トランザクションは、BEGIN TRANSACTION ステートメントが実行されたときに開始されます。Transactions start at the time a BEGIN TRANSACTION statement is executed. ただし、トランザクション シーケンス番号が始まるのは、BEGIN TRANSACTION ステートメントの後に実行される最初の読み取り操作または書き込み操作からです。However, the transaction sequence number starts with the first read or write operation after the BEGIN TRANSACTION statement. トランザクション シーケンス番号は、トランザクションに割り当てられるたびに 1 ずつ増加します。The transaction sequence number is incremented by one each time it is assigned.

READ_COMMITTED_SNAPSHOT データベース オプションまたは ALLOW_SNAPSHOT_ISOLATION データベース オプションのいずれかを ON に設定すると、データベースで実行されるすべてのデータ変更の論理コピー (バージョン) が保持されます。When either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options are ON, logical copies (versions) are maintained for all data modifications performed in the database. 特定のトランザクションで行が変更されるたびに、データベース エンジンDatabase Engineのインスタンスにより、行の以前にコミットされたイメージのバージョンが tempdb に格納されます。Every time a row is modified by a specific transaction, the instance of the データベース エンジンDatabase Engine stores a version of the previously committed image of the row in tempdb. 各バージョンには、その変更を行ったトランザクションのトランザクション シーケンス番号が付きます。Each version is marked with the transaction sequence number of the transaction that made the change. 変更された行のバージョンは、リンク リストを使用して連結されます。The versions of modified rows are chained using a link list. 最新の行の値は、常に現在のデータベースに格納され、tempdb に格納されているバージョン管理された行に連結されます。The newest row value is always stored in the current database and chained to the versioned rows stored in tempdb.

注意

ラージ オブジェクト (LOB) の変更については、変更された部分のみが tempdb のバージョン ストアにコピーされます。For modification of large objects (LOBs), only the changed fragment is copied to the version store in tempdb.

バージョンストアに格納されているバージョンは、行のバージョン管理に基づく分離レベルで実行されるトランザクションで必要な限り保持されます。Row versions are held long enough to satisfy the requirements of transactions running under row versioning-based isolation levels. データベース エンジンDatabase Engineにより、必要なトランザクション シーケンス番号の中で最も小さい番号が追跡され、それよりもトランザクション シーケンス番号が小さい行のバージョンは定期的にすべて削除されます。The データベース エンジンDatabase Engine tracks the earliest useful transaction sequence number and periodically deletes all row versions stamped with transaction sequence numbers that are lower than the earliest useful sequence number.

両方のデータベース オプションを OFF に設定すると、トリガーまたは MARS セッションで変更された行、あるいは ONLINE インデックス操作で読み取られた行のみがバージョン管理されます。When both database options are set to OFF, only rows modified by triggers or MARS sessions, or read by ONLINE index operations, are versioned. これらの行のバージョンは、必要ではなくなった時点で解放されます。Those row versions are released when no longer needed. また、定期的に実行されるバックグラウンドのスレッドにより、古い行のバージョンが削除されます。A background thread periodically executes to remove stale row versions.

注意

トランザクションの実行時間が短い場合、変更された行のバージョンは、tempdb データベースのディスク ファイルに書き込まれずにバッファー プールにキャッシュされる場合があります。For short-running transactions, a version of a modified row may get cached in the buffer pool without getting written into the disk files of the tempdb database. バージョン管理された行が必要とされる時間が短い場合、その行のバージョンは単純にバッファー プールから削除されるので、I/O のオーバーヘッドが発生しない場合もあります。If the need for the versioned row is short-lived, it will simply get dropped from the buffer pool and may not necessarily incur I/O overhead.

データ読み取り時の動作Behavior When Reading Data

行のバージョン管理に基づく分離レベルで実行されているトランザクションによりデータが読み取られるとき、読み取り操作では、読み取るデータに対して共有 (S) ロックが獲得されないので、データを変更しているトランザクションはブロックされません。When transactions running under row versioning-based isolation read data, the read operations do not acquire shared (S) locks on the data being read, and therefore do not block transactions that are modifying data. また、リソースのロックによるオーバーヘッドは、獲得されるロックの数が少ないほど小さくなります。Also, the overhead of locking resources is minimized as the number of locks acquired is reduced. 行のバージョン管理を使用する READ COMMITTED 分離とスナップショット分離は、バージョン管理されたデータの読み取りの一貫性をステートメントレベルまたはトランザクションレベルで保証するようにデザインされています。Read committed isolation using row versioning and snapshot isolation are designed to provide statement-level or transaction-level read consistencies of versioned data.

行のバージョン管理に基づく分離レベルで実行されているトランザクションを含むすべてのクエリは、コンパイルおよび実行中に Sch-S (スキーマ安定度) ロックを獲得します。All queries, including transactions running under row versioning-based isolation levels, acquire Sch-S (schema stability) locks during compilation and execution. このため、同時実行トランザクションがテーブルの Sch-M (スキーマ修正) ロックを保持している場合、クエリはブロックされます。Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table. たとえば、データ定義言語 (DDL) 操作では、テーブルのスキーマ情報を変更する前にスキーマ修正 (Sch-M) ロックを取得します。For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. 行のバージョン管理に基づく分離レベルで実行されているトランザクションを含むクエリ トランザクションは、Sch-S ロックを獲得しようとするとブロックされます。Query transactions, including those running under a row versioning-based isolation level, are blocked when attempting to acquire a Sch-S lock. 一方、スキーマ安定度 (Sch-S) ロックを保持するクエリによって、スキーマ修正 (Sch-M) ロックを取得しようとする同時実行トランザクションはブロックされます。Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock.

スナップショット分離レベルを使用するトランザクションが開始されると、データベース エンジンDatabase Engineのインスタンスにより、現在アクティブなトランザクションがすべて記録されます。When a transaction using the snapshot isolation level starts, the instance of the データベース エンジンDatabase Engine records all of the currently active transactions. スナップショット トランザクションでバージョン チェーンを持つ行が読み取られると、データベース エンジンDatabase Engineによりチェーンが追跡され、次のトランザクション シーケンス番号を持つ行が取得されます。When the snapshot transaction reads a row that has a version chain, the データベース エンジンDatabase Engine follows the chain and retrieves the row where the transaction sequence number is:

  • 行を読み取っているスナップショット トランザクションのシーケンス番号に最も近く、それよりも小さいトランザクション シーケンス番号。Closest to but lower than the sequence number of the snapshot transaction reading the row.

  • スナップショット トランザクションが開始されたときのアクティブなトランザクションの一覧にないトランザクション シーケンス番号。Not in the list of the transactions active when the snapshot transaction started.

スナップショット トランザクションで実行される読み取り操作では、スナップショット トランザクションが開始されたときにコミットされた各行の最後のバージョンが取得されます。Read operations performed by a snapshot transaction retrieve the last version of each row that had been committed at the time the snapshot transaction started. これにより、トランザクション内で一貫性を持つ、トランザクションが開始されたときのデータのスナップショットが提供されます。This provides a transactionally consistent snapshot of the data as it existed at the start of the transaction.

行のバージョン管理を使用する READ COMMITTED トランザクションも、ほぼ同じように動作します。Read-committed transactions using row versioning operate in much the same way. ただし、READ COMMITTED トランザクションでは、行のバージョンを選択するときにトランザクション自体のトランザクション シーケンス番号が使用されないという点が異なります。The difference is that the read-committed transaction does not use its own transaction sequence number when choosing row versions. ステートメントが開始されるたびに、READ COMMITTED トランザクションでは、このデータベース エンジンDatabase Engineのインスタンスに対して生成された最新のトランザクション シーケンス番号が読み取られます。Each time a statement is started, the read-committed transaction reads the latest transaction sequence number issued for that instance of the データベース エンジンDatabase Engine. これは、そのステートメントに適した行のバージョンを選択するために使用されるトランザクション シーケンス番号です。This is the transaction sequence number used to select the correct row versions for that statement. このトランザクション シーケンス番号により、READ COMMITTED トランザクションでは、各ステートメントが開始されたときのデータのスナップショットを参照できるようになります。This allows read-committed transactions to see a snapshot of the data as it exists at the start of each statement.

注意

行のバージョン管理を使用する READ COMMITTED トランザクションが、トランザクション全体で一貫性のあるデータのビューをステートメント レベルで提供しても、この種類のトランザクションにより生成またはアクセスされる行バージョンは、トランザクションが完了するまで保持されます。Even though read-committed transactions using row versioning provides a transactionally consistent view of the data at a statement level, row versions generated or accessed by this type of transaction are maintained until the transaction completes.

データ変更時の動作Behavior When Modifying Data

行のバージョン管理を使用する READ COMMITTED トランザクションでは、更新する行を選択するときにブロッキング スキャンが使用されます。ブロッキング スキャンでは、データ値を読み取るときにデータ行の更新 (U) ロックが獲得されます。In a read-committed transaction using row versioning, the selection of rows to update is done using a blocking scan where an update (U) lock is taken on the data row as data values are read. この動作は、行のバージョン管理を使用しない READ COMMITTED トランザクションでも発生します。This is the same as a read-committed transaction that does not use row versioning. データ行が更新基準を満たしていない場合は、その行の更新ロックが解放され、次の行がロックおよびスキャンされます。If the data row does not meet the update criteria, the update lock is released on that row and the next row is locked and scanned.

スナップショット分離レベルで実行されているトランザクションでは、制約を設定するためだけに変更を実行する前に、データのロックを獲得することによって、データ変更にオプティミスティック同時実行制御が使用されます。Transactions running under snapshot isolation take an optimistic approach to data modification by acquiring locks on data before performing the modification only to enforce constraints. それ以外の場合、データの変更が確定するまで、そのデータのロックは獲得されません。Otherwise, locks are not acquired on data until the data is to be modified. データ行が更新基準を満たしている場合、スナップショット トランザクションにより、そのスナップショット トランザクションの開始後にコミットされた同時実行トランザクションでそのデータ行が変更されていないかどうかが確認されます。When a data row meets the update criteria, the snapshot transaction verifies that the data row has not been modified by a concurrent transaction that committed after the snapshot transaction began. データ行がスナップショット トランザクションの外部で変更された場合は、更新の競合が発生し、そのスナップショット トランザクションは終了されます。If the data row has been modified outside of the snapshot transaction, an update conflict occurs and the snapshot transaction is terminated. 更新の競合はデータベース エンジンDatabase Engineによって処理されるので、更新の競合が検出されないようにする方法はありません。The update conflict is handled by the データベース エンジンDatabase Engine and there is no way to disable the update conflict detection.

注意

スナップショット分離レベルで実行されている更新操作は、スナップショット トランザクションにより次のアイテムへのアクセスが行われたときに、内部的に READ COMMITTED 分離レベルで実行されます。Update operations running under snapshot isolation internally execute under read committed isolation when the snapshot transaction accesses any of the following:

FOREIGN KEY 制約が適用されたテーブル。A table with a FOREIGN KEY constraint.

別のテーブルの FOREIGN KEY 制約で参照されるテーブル。A table that is referenced in the FOREIGN KEY constraint of another table.

複数のテーブルを参照するインデックス付きビュー。An indexed view referencing more than one table.

ただしこのような状況でも、更新操作では、データが別のトランザクションにより変更されていないかどうかが引き続き確認されます。However, even under these conditions the update operation will continue to verify that the data has not been modified by another transaction. データが別のトランザクションで変更されている場合は、更新の競合が発生し、スナップショット トランザクションは終了します。If data has been modified by another transaction, the snapshot transaction encounters an update conflict and is terminated.

動作のまとめBehavior in Summary

次の表に、行のバージョン管理を使用するスナップショット分離レベルと READ COMMITTED 分離レベルの違いを要約します。The following table summarizes the differences between snapshot isolation and read committed isolation using row versioning.

プロパティProperty 行のバージョン管理を使用する READ COMMITTED 分離レベルRead-committed isolation level using row versioning スナップショット分離レベルSnapshot isolation level
必要なサポートを有効にするために ON に設定されている必要があるデータベース オプション。The database option that must be set to ON to enable the required support. READ_COMMITTED_SNAPSHOTREAD_COMMITTED_SNAPSHOT ALLOW_SNAPSHOT_ISOLATIONALLOW_SNAPSHOT_ISOLATION
セッションが特定の種類の行のバージョン管理を要求する方法。How a session requests the specific type of row versioning. 既定の READ COMMITTED 分離レベルを使用するか、または SET TRANSACTION ISOLATION LEVEL ステートメントを実行して READ COMMITTED 分離レベルを指定します。Use the default read-committed isolation level, or run the SET TRANSACTION ISOLATION LEVEL statement to specify the READ COMMITTED isolation level. この操作は、トランザクションの開始後に実行できます。This can be done after the transaction starts. トランザクションの開始前に、SET TRANSACTION ISOLATION LEVEL を実行してスナップショット分離レベルを指定するように要求します。Requires the execution of SET TRANSACTION ISOLATION LEVEL to specify the SNAPSHOT isolation level before the start of the transaction.
ステートメントにより読み取られるデータのバージョン。The version of data read by statements. 各ステートメントの開始前にコミットされたすべてのデータ。All data that was committed before the start of each statement. 各トランザクションの開始前にコミットされたすべてのデータ。All data that was committed before the start of each transaction.
更新の処理方法。How updates are handled. 行のバージョンを実際のデータに戻して更新する行を選択し、選択したデータ行に対して更新ロックをかけます。Reverts from row versions to actual data to select rows to update and uses update locks on the data rows selected. また、変更する実際のデータ行の排他ロックを獲得します。Acquires exclusive locks on actual data rows to be modified. 更新の競合検出は行われません。No update conflict detection. 行バージョンを使用して更新する行を選択します。Uses row versions to select rows to update. 変更する行の実際のデータに対する排他ロックの獲得を試行します。このデータが別のトランザクションにより変更されている場合は、更新の競合が発生し、スナップショット トランザクションは終了します。Tries to acquire an exclusive lock on the actual data row to be modified, and if the data has been modified by another transaction, an update conflict occurs and the snapshot transaction is terminated.
更新の競合検出。Update conflict detection. [なし] :None. 組み込みによるサポート。Integrated support. この機能は無効にできません。Cannot be disabled.

行のバージョン管理用リソースの使用状況Row Versioning Resource Usage

行のバージョン管理フレームワークでは、SQL ServerSQL Server で使用可能な次の機能がサポートされます。The row versioning framework supports the following features available in SQL ServerSQL Server:

  • トリガーTriggers

  • 複数のアクティブな結果セット (MARS)Multiple Active Results Sets (MARS)

  • オンラインのインデックス構築Online indexing

また、行のバージョン管理フレームワークでは、次に示すように、行のバージョン管理に基づいたトランザクション分離レベルもサポートされます。このレベルは既定では無効になっています。The row versioning framework also supports the following row versioning-based transaction isolation levels, which by default are not enabled:

  • READ_COMMITTED_SNAPSHOT データベース オプションが ON になっている場合、READ_COMMITTED トランザクションで行のバージョン管理を行うことにより、ステートメント レベルでの読み取り操作を一貫性を保った状態で実行できます。When the READ_COMMITTED_SNAPSHOT database option is ON, READ_COMMITTED transactions provide statement-level read consistency using row versioning.

  • ALLOW_SNAPSHOT_ISOLATION データベース オプションが ON になっている場合、SNAPSHOT トランザクションで行のバージョン管理を行うことにより、トランザクション レベルでの読み取り操作を一貫性を保った状態で実行できます。When the ALLOW_SNAPSHOT_ISOLATION database option is ON, SNAPSHOT transactions provide transaction-level read consistency using row versioning.

行のバージョン管理に基づく分離レベルを使用すると、読み取り操作での共有ロックが排除され、トランザクションから取得するロック数が減少します。Row versioning-based isolation levels reduce the number of locks acquired by transaction by eliminating the use of shared locks on read operations. その結果、ロックの管理に使用するリソースを節約できるので、システムのパフォーマンスが向上します。This increases system performance by reducing the resources used to manage locks. また、他のトランザクションから取得したロックによりトランザクションがブロックされる回数が減少することも、パフォーマンスが向上する要因です。Performance is also increased by reducing the number of times a transaction is blocked by locks acquired by other transactions.

行のバージョン管理に基づいて分離レベルを使用すると、データの変更に必要なリソースが増加します。Row versioning-based isolation levels increase the resources needed by data modifications. これらのオプションを有効にすると、データベースに対するすべてのデータ変更がバージョン管理されます。Enabling these options causes all data modifications for the database to be versioned. 行のバージョン管理に基づく分離を使用したアクティブなトランザクションが存在しない場合でも、変更前のデータのコピーは tempdb に格納されます。A copy of the data before modification is stored in tempdb even when there are no active transactions using row versioning-based isolation. 変更後のデータには、tempdb に格納されたバージョン管理されるデータへのポインターが含まれます。The data after modification includes a pointer to the versioned data stored in tempdb. ラージ オブジェクトの場合は、変更されたオブジェクトだけが tempdb にコピーされます。For large objects, only part of the object that changed is copied to tempdb.

tempdb で使用される領域Space Used in tempdb

データベース エンジンDatabase Engineの各インスタンスの tempdb には、インスタンス内の各データベースで生成される行バージョンを保持できるだけの十分な領域が必要です。For each instance of the データベース エンジンDatabase Engine, tempdb must have enough space to hold the row versions generated for every database in the instance. データベース管理者は、バージョン ストアをサポートできるだけの十分な領域を tempdb 内に確保する必要があります。The database administrator must ensure that tempdb has ample space to support the version store. tempdb には次に示す 2 つのバージョン ストアがあります。There are two version stores in tempdb:

  • オンライン インデックス構築用のバージョン ストアは、すべてのデータベースのオンラインのインデックス構築操作に使用されます。The online index build version store is used for online index builds in all databases.

  • 共通バージョン ストアは、すべてのデータベース内のその他すべてのデータ変更操作に使用されます。The common version store is used for all other data modification operations in all databases.

アクティブなトランザクションで行バージョンにアクセスする必要がある限り、その行バージョンを格納しておく必要があります。Row versions must be stored for as long as an active transaction needs to access it. 1 分ごとに、バックグラウンドのスレッドによって、不要になった行バージョンが削除され、tempdb 内のバージョン領域が解放されます。Once every minute, a background thread removes row versions that are no longer needed and frees up the version space in tempdb. 次の条件のいずれかに該当する場合、実行時間の長いトランザクションにより、バージョン ストアの領域の解放が妨げられます。A long-running transaction prevents space in the version store from being released if it meets any of the following conditions:

  • トランザクションで、行のバージョン管理に基づく分離が使用されています。It uses row versioning-based isolation.

  • トランザクションで、トリガー、MARS、またはオンラインのインデックス構築のいずれかの操作が使用されています。It uses triggers, MARS, or online index build operations.

  • トランザクションで、行バージョンが生成されます。It generates row versions.

注意

トランザクションの内部でトリガーが呼び出されるときに、トリガーによって作成される行バージョンは、トリガーの完了後に不要になる行バージョンであっても、トランザクションの終了時まで保持されます。When a trigger is invoked inside a transaction, the row versions created by the trigger are maintained until the end of the transaction, even though the row versions are no longer needed after the trigger completes. これは、行のバージョン管理を使用する READ COMMITTED トランザクションにも適用されます。This also applies to read-committed transactions that use row versioning. この種類のトランザクションを使用すると、トランザクション内の各ステートメントに対してだけ、トランザクション全体で一貫性のあるデータベース ビューが必要です。With this type of transaction, a transactionally consistent view of the database is needed only for each statement in the transaction. つまり、トランザクションでステートメントに対して作成される行バージョンは、ステートメントが完了した後には不要になります。This means that the row versions created for a statement in the transaction are no longer needed after the statement completes. ただし、トランザクションで各ステートメントによって作成される行バージョンは、トランザクションが完了するまで保持されます。However, row versions created by each statement in the transaction are maintained until the transaction completes.

tempdb の領域が不足すると、データベース エンジンDatabase Engineによって強制的にバージョン ストアが圧縮されます。When tempdb runs out of space, the データベース エンジンDatabase Engine forces the version stores to shrink. 圧縮処理では、行バージョンをまだ生成していないトランザクションのうち、実行時間が最も長いトランザクションが圧縮対象になります。During the shrink process, the longest running transactions that have not yet generated row versions are marked as victims. 圧縮対象のトランザクションごとに、メッセージ 3967 がエラー ログに記録されます。A message 3967 is generated in the error log for each victim transaction. あるトランザクションが圧縮の対象として設定されると、そのトランザクションではバージョン ストア内の行バージョンを読み取れなくなります。If a transaction is marked as a victim, it can no longer read the row versions in the version store. そのトランザクションから行バージョンを読み取ろうとすると、メッセージ 3966 が生成され、そのトランザクションはロールバックされます。When it attempts to read row versions, message 3966 is generated and the transaction is rolled back. 圧縮処理が成功すると、tempdb に使用可能な領域が生成されます。If the shrinking process succeeds, space becomes available in tempdb. 失敗した場合は、tempdb の領域が不足し、次のいずれかの現象が発生します。Otherwise, tempdb runs out of space and the following occurs:

  • 書き込み操作が続行および実行されますが、バージョンが生成されません。Write operations continue to execute but do not generate versions. 情報提供用メッセージ (3959) がエラー ログに記録されますが、データを書き込むトランザクションは影響を受けません。An information message (3959) appears in the error log, but the transaction that writes data is not affected.

  • tempdb 全体がロールバックされたために生成されなかった行バージョンにアクセスを試みるトランザクションが、エラー 3958 で終了します。Transactions that attempt to access row versions that were not generated because of a tempdb full rollback terminate with an error 3958.

データ行で使用される領域Space Used in Data Rows

各データベース行では、行の終わりの最大 14 バイトを行のバージョン管理情報用に使用する場合があります。Each database row may use up to 14 bytes at the end of the row for row versioning information. 行のバージョン管理情報には、そのバージョンでコミットしたトランザクションのトランザクション シーケンス番号と、バージョン管理される行へのポインターが含まれています。The row versioning information contains the transaction sequence number of the transaction that committed the version and the pointer to the versioned row. 次に示す条件のいずれかに該当する場合、行が最初に変更されたとき、または新しい行が追加されたときに、この 14 バイトが追加されます。These 14 bytes are added the first time the row is modified, or when a new row is inserted, under any of these conditions:

  • READ_COMMITTED_SNAPSHOT オプションまたは ALLOW_SNAPSHOT_ISOLATION オプションが ON になっています。READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION options are ON.

  • テーブルにトリガーが含まれています。The table has a trigger.

  • 複数のアクティブな結果セット (MARS) が使用されています。Multiple Active Results Sets (MARS) is being used.

  • オンラインのインデックス構築操作が、現在そのテーブルで実行されています。Online index build operations are currently running on the table.

次のすべての条件に該当する状況で最初に行が変更されたときに、そのデータベース行からこの 14 バイトが削除されます。These 14 bytes are removed from the database row the first time the row is modified under all of these conditions:

  • READ_COMMITTED_SNAPSHOT オプションと ALLOW_SNAPSHOT_ISOLATION オプションが OFF になっています。READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION options are OFF.

  • テーブルに既にトリガーが存在しません。The trigger no longer exists on the table.

  • MARS が使用されていません。MARS is not being used.

  • オンラインのインデックス構築操作が現在実行されていません。Online index build operations are not currently running.

行のバージョン管理機能を使用する場合は、データベースに追加のディスク領域を割り当て、各データベース行で 14 バイトを使用できるようにする必要があります。If you use any of the row versioning features, you might need to allocate additional disk space for the database to accommodate the 14 bytes per database row. 行のバージョン管理用情報が追加された際に、現在のページ上に十分な空き領域がない場合、インデックス ページの分割や、新しいデータ ページの割り当てが発生します。Adding the row versioning information can cause index page splits or the allocation of a new data page if there is not enough space available on the current page. たとえば、行の平均の長さが 100 バイトの場合は、14 バイト追加されると既存のテーブルが最大 14% 大きくなります。For example, if the average row length is 100 bytes, the additional 14 bytes cause an existing table to grow up to 14 percent.

FILL FACTOR を小さくすると、インデックス ページの断片化を防止または低減するのに役立ちます。Decreasing the fill factor might help to prevent or decrease fragmentation of index pages. 使用することができます、データとテーブルまたはビューのインデックスの断片化情報を表示するDBCC SHOWCONTIGします。To view fragmentation information for the data and indexes of a table or view, you can use DBCC SHOWCONTIG.

ラージ オブジェクトで使用される領域Space Used in Large Objects

SQL Server データベース エンジンSQL Server Database Engineでは、最大 2 GB の大きなサイズの文字列を保持できる 6 つのデータ型 (nvarchar(max)varchar(max)varbinary(max)ntexttextimage) がサポートされます。The SQL Server データベース エンジンSQL Server Database Engine supports six data types that can hold large strings up to 2 gigabytes (GB) in length: nvarchar(max), varchar(max), varbinary(max), ntext, text, and image. これらのデータ型で格納されたサイズの大きな文字列は、データ行にリンクされている一連のデータ フラグメントに格納されます。Large strings stored using these data types are stored in a series of data fragments that are linked to the data row. 行のバージョン管理情報は、これらの大きな文字列の格納に使用される各フラグメントに格納されます。Row versioning information is stored in each fragment used to store these large strings. データ フラグメントは、テーブル内のラージ オブジェクト専用のページのコレクションです。Data fragments are a collection of pages dedicated to large objects in a table.

新しい大きな値がデータベースに追加されたときに、データ フラグメントには、1 つのフラグメントにつき最大 8,040 バイトのデータが割り当てられます。As new large values are added to a database, they are allocated using a maximum of 8040 bytes of data per fragment. 以前のバージョンのデータベース エンジンDatabase Engineでは、1 つのフラグメントにつき最大 8,080 バイトの ntext データ、text データ、または image データが格納されていました。Earlier versions of the データベース エンジンDatabase Engine stored up to 8080 bytes of ntext, text, or image data per fragment.

ntext 型、text 型、および image 型の既存のラージ オブジェクト (LOB) データは、データベースが以前のバージョンの SQL ServerSQL Server から SQL ServerSQL Server にアップグレードされても、行のバージョン管理情報用の領域を確保するために更新されることはありません。Existing ntext, text, and image large object (LOB) data is not updated to make space for the row versioning information when a database is upgraded to SQL ServerSQL Server from an earlier version of SQL ServerSQL Server. ただし、これらの LOB データが最初に変更されたときに、バージョン管理情報の領域を使用できるように動的にアップグレードされます。However, the first time the LOB data is modified, it is dynamically upgraded to enable storage of versioning information. 行のバージョンが生成されない場合でも、このアップグレードは行われます。This will happen even if row versions are not generated. LOB データがアップグレードされた後、1 フラグメントに格納されている最大バイト数が 8,080 バイトから 8,040 バイトに減少します。After the LOB data is upgraded, the maximum number of bytes stored per fragment is reduced from 8080 bytes to 8040 bytes. このアップグレード処理は、LOB 値を削除し、再度同じ値を挿入する処理に相当します。The upgrade process is equivalent to deleting the LOB value and reinserting the same value. LOB データは、1 バイトしか変更されない場合にもアップグレードされます。The LOB data is upgraded even if only one byte is modified. このアップグレードは、ntext 型、text 型、または image 型の各列で 1 回だけ実行される操作ですが、LOB データのサイズによっては、大量のページが割り当てられたり、大量の I/O 処理が実行されたりする場合があります。This is a one-time operation for each ntext, text, or image column, but each operation may generate a large amount of page allocations and I/O activity depending upon the size of the LOB data. また、変更が完全にログに記録される場合、ログ処理が膨大になる場合があります。It may also generate a large amount of logging activity if the modification is fully logged. WRITETEXT 操作および UPDATETEXT 操作を使用すると、データベースが完全復旧モードに設定されている場合、ログ記録を最小限に抑えることができます。WRITETEXT and UPDATETEXT operations are minimally logged if database recovery mode is not set to FULL.

nvarchar(max)varchar(max)、および varbinary(max) の各データ型は、以前のバージョンの SQL ServerSQL Server では使用できません。The nvarchar(max), varchar(max), and varbinary(max) data types are not available in earlier versions of SQL ServerSQL Server. したがって、これらのデータ型についてはアップグレードの問題は発生しません。Therefore, they have no upgrade issues.

この要件を満たすには、十分なディスク領域を割り当てる必要があります。Enough disk space should be allocated to accommodate this requirement.

行のバージョン管理とバージョン ストアの監視Monitoring Row Versioning and the Version Store

SQL ServerSQL Server では、パフォーマンスや問題について、行のバージョン管理、バージョン ストア、およびスナップショット分離のプロセスを監視するために、動的管理ビュー (DMV) と Windows システム モニターのパフォーマンス カウンターというツールが用意されています。For monitoring row versioning, version store, and snapshot isolation processes for performance and problems, SQL ServerSQL Server provides tools in the form of Dynamic Management Views (DMVs) and performance counters in Windows System Monitor.

DMVDMVs

次に示す DMV からは、行のバージョン管理を使用しているトランザクションについての情報だけではなく、tempdb の現在のシステム状態とバージョン ストアについての情報が提供されます。The following DMVs provide information about the current system state of tempdb and the version store, as well as transactions using row versioning.

sys.dm_db_file_space_usage。sys.dm_db_file_space_usage. データベース内の各ファイルに関する使用領域の情報を返します。Returns space usage information for each file in the database. 詳しくは、「sys.dm_db_file_space_usage (Transact-SQL)」をご覧ください。For more information, see sys.dm_db_file_space_usage (Transact-SQL).

sys.dm_db_session_space_usage。sys.dm_db_session_space_usage. データベースのセッション別に、ページの割り当てと割り当て解除の状態を返します。Returns page allocation and deallocation activity by session for the database. 詳しくは、「sys.dm_db_session_space_usage (Transact-SQL)」をご覧ください。For more information, see sys.dm_db_session_space_usage (Transact-SQL).

sys.dm_db_task_space_usage。sys.dm_db_task_space_usage. データベースに対するタスクごとに、ページの割り当てと割り当て解除の処理に関する情報を返します。Returns page allocation and deallocation activity by task for the database. 詳しくは、「sys.dm_db_task_space_usage (Transact-SQL)」をご覧ください。For more information, see sys.dm_db_task_space_usage (Transact-SQL).

sys.dm_tran_top_version_generators。sys.dm_tran_top_version_generators. バージョン ストア内で最も高いバージョンを生成しているオブジェクトの仮想テーブルを返します。Returns a virtual table for the objects producing the most versions in the version store. 集計済みのレコード長について、長いものから順に 256 位までを database_id と rowset_id でグループ化しています。It groups the top 256 aggregated record lengths by database_id and rowset_id. この関数を使用して、バージョン ストアを最も多く使用しているレコードを見つけます。Use this function to find the largest consumers of the version store. 詳しくは、「sys.dm_tran_top_version_generators (Transact-SQL)」をご覧ください。For more information, see sys.dm_tran_top_version_generators (Transact-SQL).

sys.dm_tran_version_store。sys.dm_tran_version_store. 共通バージョン ストア内のすべてのバージョン レコードを表す仮想テーブルを返します。Returns a virtual table that displays all version records in the common version store. 詳細については、sys.dm_tran_version_store (Transact-SQL)を参照してください。For more information, see sys.dm_tran_version_store (Transact-SQL).

注意

sys.dm_tran_top_version_generators と sys.dm_tran_version_store では、非常に大きくなる可能性があるバージョン ストア全体に対してクエリが実行されるので、これらの関数を実行すると非常に多くのリソースが使用される可能性があります。sys.dm_tran_top_version_generators and sys.dm_tran_version_store are potentially very expensive functions to run, since both query the entire version store, which could be very large.

sys.dm_tran_active_snapshot_database_transactions。sys.dm_tran_active_snapshot_database_transactions. データベース内のすべてのアクティブなトランザクションを表す仮想テーブルを返します。行のバージョン管理を使用する SQL ServerSQL Server インスタンス内のすべてのデータベースが対象です。Returns a virtual table for all active transactions in all databases within the SQL ServerSQL Server instance that use row versioning. システム トランザクションは、この DMV には表示されません。System transactions do not appear in this DMV. 詳しくは、「sys.dm_tran_active_snapshot_database_transactions (Transact-SQL)」をご覧ください。For more information, see sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

sys.dm_tran_transactions_snapshot。sys.dm_tran_transactions_snapshot. トランザクションごとに作成されたスナップショットを表す仮想テーブルを返します。Returns a virtual table that displays snapshots taken by each transaction. このスナップショットには、行のバージョン管理を使用するアクティブなトランザクションのシーケンス番号が含まれています。The snapshot contains the sequence number of the active transactions that use row versioning. 詳しくは、「sys.dm_tran_transactions_snapshot (Transact-SQL)」をご覧ください。For more information, see sys.dm_tran_transactions_snapshot (Transact-SQL).

sys.dm_tran_current_transaction。sys.dm_tran_current_transaction. 現在のセッションにおけるトランザクションの行のバージョン管理に関係した状態情報を表す 1 行を返します。Returns a single row that displays row versioning-related state information of the transaction in the current session. 詳しくは、「sys.dm_tran_current_transaction (Transact-SQL)」をご覧ください。For more information, see sys.dm_tran_current_transaction (Transact-SQL).

sys.dm_tran_current_snapshot。sys.dm_tran_current_snapshot. 現在のスナップショット分離トランザクションの開始時点でアクティブなすべてのトランザクションを表す仮想テーブルを返します。Returns a virtual table that displays all active transactions at the time the current snapshot isolation transaction starts. 現在のトランザクションでスナップショット分離が使用されている場合、この関数は行を返しません。If the current transaction is using snapshot isolation, this function returns no rows. sys.dm_tran_current_snapshot は、sys.dm_tran_transactions_snapshot と類似していますが、現在のスナップショットのアクティブなトランザクションだけを返す点が異なります。sys.dm_tran_current_snapshot is similar to sys.dm_tran_transactions_snapshot, except that it returns only the active transactions for the current snapshot. 詳しくは、「sys.dm_tran_current_snapshot (Transact-SQL)」をご覧ください。For more information, see sys.dm_tran_current_snapshot (Transact-SQL).

パフォーマンス カウンターPerformance Counters

SQL ServerSQL Server のパフォーマンス カウンターからは、SQL ServerSQL Server プロセスによって影響を受けるシステム パフォーマンスについての情報が提供されます。performance counters provide information about the system performance impacted by SQL ServerSQL Server processes. 次に示すパフォーマンス カウンターでは、行のバージョン管理を使用しているトランザクションだけではなく、tempdb とそのバージョン ストアを監視します。The following performance counters monitor tempdb and the version store, as well as transactions using row versioning. パフォーマンス カウンターは SQLServer:Transactions パフォーマンス オブジェクトに含まれています。The performance counters are contained in the SQLServer:Transactions performance object.

Free Space in tempdb (KB)Free Space in tempdb (KB). tempdb データベース内の空き領域 (KB) を監視します。Monitors the amount, in kilobytes (KB), of free space in the tempdb database. tempdb には、スナップショット分離をサポートするバージョン ストアを処理できるだけの十分な空き領域が必要です。There must be enough free space in tempdb to handle the version store that supports snapshot isolation.

次の式を使用すると、バージョン ストアのサイズを概算することができます。The following formula provides a rough estimate of the size of the version store. 実行時間の長いトランザクションの場合、生成率とクリーンアップ率を監視してバージョン ストアの最大サイズを推定すると有益な場合があります。For long-running transactions, it may be useful to monitor the generation and cleanup rate to estimate the maximum size of the version store.

[共通バージョン ストアのサイズ] = 2 * [毎分生成されるバージョン ストア データ] * [トランザクションの最長実行時間 (分)][size of common version store] = 2 * [version store data generated per minute] * [longest running time (minutes) of the transaction]

実行時間が極端に長いトランザクションには、オンラインのインデックス構築操作を含めないでください。The longest running time of transactions should not include online index builds. そのような場合のオンラインのインデックス構築操作は、非常に大きなテーブルでは時間がかかる場合があるので、別のバージョン ストアを使用します。Because these operations may take a long time on very large tables, online index builds use a separate version store. オンライン インデックス構築用のバージョン ストアの大まかなサイズは、オンラインのインデックス構築がアクティブになっている間にテーブル内で変更されたデータ (すべてのインデックスを含む) の量と同じです。The approximate size of the online index build version store equals the amount of data modified in the table, including all indexes, while the online index build is active.

Version Store Size (KB)Version Store Size (KB). すべてのバージョン ストアのサイズを KB 単位で監視します。Monitors the size in KB of all version stores. この情報は、tempdb データベースに必要なバージョン ストア用の領域のサイズを判定する際に役立ちます。This information helps determine the amount of space needed in the tempdb database for the version store. このカウンターを長期間監視すると、tempdb に必要な領域を追加する際に役立つ推定値が得られます。Monitoring this counter over a period of time provides a useful estimate of additional space needed for tempdb.

Version Generation rate (KB/s)Version Generation rate (KB/s). すべてのバージョン ストアについてバージョンの生成率 (KB/秒) を監視します。Monitors the version generation rate in KB per second in all version stores.

Version Cleanup rate (KB/s)Version Cleanup rate (KB/s). すべてのバージョン ストアについてバージョンのクリーンアップ率 (KB/秒) を監視します。Monitors the version cleanup rate in KB per second in all version stores.

注意

Version Generation rate (KB/s) と Version Cleanup rate (KB/s) から得た情報を、tempdb に必要な領域の予測に利用できます。Information from Version Generation rate (KB/s) and Version Cleanup rate (KB/s) can be used to predict tempdb space requirements.

Version Store unit countVersion Store unit count. バージョン ストア ユニットの数を監視します。Monitors the count of version store units.

Version Store unit creationVersion Store unit creation. インスタンスの開始以降に行バージョンを格納するために作成されたバージョン ストア ユニットの総数を監視します。Monitors the total number of version store units created to store row versions since the instance was started.

Version Store unit truncationVersion Store unit truncation. インスタンスの開始以降に切り捨てられたバージョン ストア ユニットの総数を監視します。Monitors the total number of version store units truncated since the instance was started. バージョン ストア ユニットは、バージョン ストア内に格納されているバージョン行が SQL ServerSQL Server によりアクティブなトランザクションの実行に不要と判断された場合に切り捨てられます。A version store unit is truncated when SQL ServerSQL Server determines that none of the version rows stored in the version store unit are needed to run active transactions.

Update conflict ratioUpdate conflict ratio. 更新スナップショット トランザクションの総数に対し、更新に関して競合が発生している更新スナップショット トランザクションの割合を監視します。Monitors the ratio of update snapshot transaction that have update conflicts to the total number of update snapshot transactions.

Longest Transaction Running TimeLongest Transaction Running Time. 行のバージョン管理を使用しているトランザクションの最長実行時間 (秒) を監視します。Monitors the longest running time in seconds of any transaction using row versioning. このパフォーマンス カウンターを使用して、トランザクションの実行時間が不適切でないかどうかを判断できます。This can be used to determine if any transaction is running for an unreasonable amount of time.

TransactionsTransactions. アクティブなトランザクションの総数を監視します。Monitors the total number of active transactions. システム トランザクションは含まれません。This does not include system transactions.

Snapshot TransactionsSnapshot Transactions. アクティブなスナップショット トランザクションの総数を監視します。Monitors the total number of active snapshot transactions.

Update Snapshot TransactionsUpdate Snapshot Transactions. 更新操作を実行するアクティブなスナップショット トランザクションの総数を監視します。Monitors the total number of active snapshot transactions that perform update operations.

NonSnapshot Version TransactionsNonSnapshot Version Transactions. バージョン レコードを生成する、スナップショット以外のアクティブなトランザクションの総数を監視します。Monitors the total number of active non-snapshot transactions that generate version records.

注意

Update Snapshot Transactions と NonSnapshot Version Transactions の合計は、バージョンの生成に関係するトランザクションの総数を表します。The sum of Update Snapshot Transactions and NonSnapshot Version Transactions represents the total number of transactions that participate in version generation. Snapshot Transactions と Update Snapshot Transactions の差分は、読み取り専用のトランザクション数を表します。The difference of Snapshot Transactions and Update Snapshot Transactions reports the number of read-only snapshot transactions.

行のバージョン管理に基づく分離レベルの例Row Versioning-based Isolation Level Example

以下の例は、スナップショット分離トランザクションと、行のバージョン管理を使用する Read Committed トランザクションとの動作の違いを示しています。The following examples show the differences in behavior between snapshot isolation transactions and read-committed transactions that use row versioning.

A.A. スナップショット分離を使用した作業Working with snapshot isolation

この例では、スナップショット分離レベルで実行中のトランザクションが、別のトランザクションにより変更されるデータを読み取ります。In this example, a transaction running under snapshot isolation reads data that is then modified by another transaction. スナップショット トランザクションでは、別のトランザクションで実行される更新操作をブロックしないで、バージョン管理される行から引き続きデータを読み取り、データの変更が無視されます。The snapshot transaction does not block the update operation executed by the other transaction, and it continues to read data from the versioned row, ignoring the data modification. ただし、スナップショット トランザクションが、別のトランザクションによって既に変更されているデータの変更を試みた場合は、そのスナップショット トランザクションがエラーを生成し、終了します。However, when the snapshot transaction attempts to modify the data that has already been modified by the other transaction, the snapshot transaction generates an error and is terminated.

セッション 1:On session 1:

USE AdventureWorks2012;  -- Or the 2008 or 2008R2 version of the AdventureWorks database.  
GO  
  
-- Enable snapshot isolation on the database.  
ALTER DATABASE AdventureWorks2012  
    SET ALLOW_SNAPSHOT_ISOLATION ON;  
GO  
  
-- Start a snapshot transaction  
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;  
GO  
  
BEGIN TRANSACTION;  
    -- This SELECT statement will return  
    -- 48 vacation hours for the employee.  
    SELECT BusinessEntityID, VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  

セッション 2 :On session 2:

USE AdventureWorks2012;  
GO  
  
-- Start a transaction.  
BEGIN TRANSACTION;  
    -- Subtract a vacation day from employee 4.  
    -- Update is not blocked by session 1 since  
    -- under snapshot isolation shared locks are  
    -- not requested.  
    UPDATE HumanResources.Employee  
        SET VacationHours = VacationHours - 8  
        WHERE BusinessEntityID = 4;  
  
    -- Verify that the employee now has 40 vacation hours.  
    SELECT VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  

セッション 1:On session 1:

    -- Reissue the SELECT statement - this shows  
    -- the employee having 48 vacation hours.  The  
    -- snapshot transaction is still reading data from  
    -- the versioned row.  
    SELECT BusinessEntityID, VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  

セッション 2 :On session 2:

-- Commit the transaction; this commits the data  
-- modification.  
COMMIT TRANSACTION;  
GO  

セッション 1:On session 1:

    -- Reissue the SELECT statement - this still   
    -- shows the employee having 48 vacation hours  
    -- even after the other transaction has committed  
    -- the data modification.  
    SELECT BusinessEntityID, VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  
  
    -- Because the data has been modified outside of the  
    -- snapshot transaction, any further data changes to   
    -- that data by the snapshot transaction will cause   
    -- the snapshot transaction to fail. This statement   
    -- will generate a 3960 error and the transaction will   
    -- terminate.  
    UPDATE HumanResources.Employee  
        SET SickLeaveHours = SickLeaveHours - 8  
        WHERE BusinessEntityID = 4;  
  
-- Undo the changes to the database from session 1.   
-- This will not undo the change from session 2.  
ROLLBACK TRANSACTION  
GO  

B.B. 行のバージョン管理を使用する Read CommittedWorking with read-committed using row versioning

この例では、行のバージョン管理を使用する Read Committed トランザクションを、別のトランザクションと同時に実行しています。In this example, a read-committed transaction using row versioning runs concurrently with another transaction. Read Committed トランザクションは、スナップショット トランザクションとは異なる動作をします。The read-committed transaction behaves differently than a snapshot transaction. スナップショット トランザクションと同様に Read Committed トランザクションも、別のトランザクションがデータを変更した後でも、バージョン管理される行を読み取ります。Like a snapshot transaction, the read-committed transaction will read versioned rows even after the other transaction has modified data. ただし、スナップショット トランザクションとは異なり、Read Committed トランザクションは以下のように動作します。However, unlike a snapshot transaction, the read-committed transaction will:

  • 別のトランザクションがデータの変更をコミットした後は、変更されたデータを読み取ります。Read the modified data after the other transaction commits the data changes.

  • 別のトランザクションが変更したデータを変更できます。スナップショット トランザクションでは、このような変更は実行できませんでした。Be able to update the data modified by the other transaction where the snapshot transaction could not.

セッション 1:On session 1:

USE AdventureWorks2012;  -- Or any earlier version of the AdventureWorks database.  
GO  
  
-- Enable READ_COMMITTED_SNAPSHOT on the database.  
-- For this statement to succeed, this session  
-- must be the only connection to the AdventureWorks2012  
-- database.  
ALTER DATABASE AdventureWorks2012  
    SET READ_COMMITTED_SNAPSHOT ON;  
GO  
  
-- Start a read-committed transaction  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
GO  
  
BEGIN TRANSACTION;  
    -- This SELECT statement will return  
    -- 48 vacation hours for the employee.  
    SELECT BusinessEntityID, VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  
  

セッション 2 :On session 2:

USE AdventureWorks2012;  
GO  
  
-- Start a transaction.  
BEGIN TRANSACTION;  
    -- Subtract a vacation day from employee 4.  
    -- Update is not blocked by session 1 since  
    -- under read-committed using row versioning shared locks are  
    -- not requested.  
    UPDATE HumanResources.Employee  
        SET VacationHours = VacationHours - 8  
        WHERE BusinessEntityID = 4;  
  
    -- Verify that the employee now has 40 vacation hours.  
    SELECT VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  
  

セッション 1:On session 1:

    -- Reissue the SELECT statement - this still shows  
    -- the employee having 48 vacation hours.  The  
    -- read-committed transaction is still reading data   
    -- from the versioned row and the other transaction   
    -- has not committed the data changes yet.  
    SELECT BusinessEntityID, VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  
  

セッション 2 :On session 2:

-- Commit the transaction.  
COMMIT TRANSACTION;  
GO  
  

セッション 1:On session 1:

    -- Reissue the SELECT statement which now shows the   
    -- employee having 40 vacation hours.  Being   
    -- read-committed, this transaction is reading the   
    -- committed data. This is different from snapshot  
    -- isolation which reads from the versioned row.  
    SELECT BusinessEntityID, VacationHours  
        FROM HumanResources.Employee  
        WHERE BusinessEntityID = 4;  
  
    -- This statement, which caused the snapshot transaction   
    -- to fail, will succeed with read-committed using row versioning.  
    UPDATE HumanResources.Employee  
        SET SickLeaveHours = SickLeaveHours - 8  
        WHERE BusinessEntityID = 4;  
  
-- Undo the changes to the database from session 1.   
-- This will not undo the change from session 2.  
ROLLBACK TRANSACTION;  
GO  

行のバージョン管理に基づく分離レベルの有効化Enabling Row Versioning-Based Isolation Levels

データベース管理者は、ALTER DATABASE ステートメントの READ_COMMITTED_SNAPSHOT データベース オプションと ALLOW_SNAPSHOT_ISOLATION データベース オプションを使用して、行のバージョン管理用のデータベース レベルの設定を制御します。Database administrators control the database-level settings for row versioning by using the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options in the ALTER DATABASE statement.

READ_COMMITTED_SNAPSHOT データベース オプションを ON に設定すると、このオプションのサポートに使用するメカニズムが直ちにアクティブになります。When the READ_COMMITTED_SNAPSHOT database option is set ON, the mechanisms used to support the option are activated immediately. READ_COMMITTED_SNAPSHOT オプションを設定するときに、ALTER DATABASE コマンドを実行する接続のみが、データベースで許可されます。When setting the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database. ALTER DATABASE が完了するまで、そのデータベースには他に開かれた接続が存在しないようにする必要があります。There must be no other open connection in the database until ALTER DATABASE is complete. データベースをシングル ユーザー モードにする必要はありません。The database does not have to be in single-user mode.

次の Transact-SQLTransact-SQL ステートメントで、READ_COMMITTED_SNAPSHOT を有効にします。The following Transact-SQLTransact-SQL statement enables READ_COMMITTED_SNAPSHOT:

ALTER DATABASE AdventureWorks2012  
    SET READ_COMMITTED_SNAPSHOT ON;  

ALLOW_SNAPSHOT_ISOLATION データベース オプションが ON であれば、SQL Server データベース エンジンSQL Server Database Engineのインスタンスは、データベース内のデータを変更したアクティブなトランザクションがすべて完了するまで、変更されたデータの行に対応するバージョンを生成しません。When the ALLOW_SNAPSHOT_ISOLATION database option is set ON, the instance of the SQL Server データベース エンジンSQL Server Database Engine does not generate row versions for modified data until all active transactions that have modified data in the database complete. アクティブな変更トランザクションが存在すると、SQL ServerSQL Server によってオプションの状態が PENDING_ON に設定されます。If there are active modification transactions, SQL ServerSQL Server sets the state of the option to PENDING_ON. すべての変更トランザクションが完了してから、このオプションの状態が ON に変更されます。After all of the modification transactions complete, the state of the option is changed to ON. ユーザーは、オプションが完全に ON になるまで、そのデータベースでのスナップショット トランザクションを開始できません。Users cannot start a snapshot transaction in that database until the option is fully ON. データベース管理者が ALLOW_SNAPSHOT_ISOLATION オプションを OFF に設定すると、データベースは PENDING_OFF の状態を経てから OFF に設定されます。The database passes through a PENDING_OFF state when the database administrator sets the ALLOW_SNAPSHOT_ISOLATION option to OFF.

次の Transact-SQLTransact-SQL ステートメントで、ALLOW_SNAPSHOT_ISOLATION を有効にします。The following Transact-SQLTransact-SQL statement will enable ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE AdventureWorks2012  
    SET ALLOW_SNAPSHOT_ISOLATION ON;  

次の表では、ALLOW_SNAPSHOT_ISOLATION オプションの状態を一覧し、それぞれについて説明します。The following table lists and describes the states of the ALLOW_SNAPSHOT_ISOLATION option. ALLOW_SNAPSHOT_ISOLATION オプションを指定して ALTER DATABASE を使用すると、現在データベースのデータにアクセスしているユーザーはブロックされません。Using ALTER DATABASE with the ALLOW_SNAPSHOT_ISOLATION option does not block users who are currently accessing the database data.

現在のデータベースのスナップショット分離フレームワークの状態State of snapshot isolation framework for current database 説明Description
OFFOFF スナップショット分離トランザクションのサポートはアクティブになりません。The support for snapshot isolation transactions is not activated. スナップショット分離トランザクションは許可されません。No snapshot isolation transactions are allowed.
PENDING_ONPENDING_ON スナップショット分離トランザクションのサポートが遷移中の状態 (OFF から ON) です。The support for snapshot isolation transactions is in transition state (from OFF to ON). 開いているトランザクションが完了する必要があります。Open transactions must complete.

スナップショット分離トランザクションは許可されません。No snapshot isolation transactions are allowed.
ONON スナップショット分離トランザクションのサポートがアクティブになります。The support for snapshot isolation transactions is activated.

スナップショット トランザクションが許可されます。Snapshot transactions are allowed.
PENDING_OFFPENDING_OFF スナップショット分離トランザクションのサポートが遷移中の状態 (ON から OFF) です。The support for snapshot isolation transactions is in transition state (from ON to OFF).

これ以降に開始されるスナップショット トランザクションは、このデータベースにアクセスできません。Snapshot transactions started after this time cannot access this database. このデータベースのバージョン管理の負荷は、依然として更新トランザクションが担っています。Update transactions still pay the cost of versioning in this database. 既存のスナップショット トランザクションからは、このデータベースに引き続き問題なくアクセスできます。Existing snapshot transactions can still access this database without a problem. PENDING_OFF の状態は、データベースのスナップショット分離の状態が ON のときにアクティブであったすべてのスナップショット トランザクションが完了するまで OFF になりません。The state PENDING_OFF does not become OFF until all snapshot transactions that were active when the database snapshot isolation state was ON finish.

行のバージョン管理データベース オプションの両方の状態を判断するには、sys.databases カタログ ビューを使用します。Use the sys.databases catalog view to determine the state of both row versioning database options.

ユーザー テーブルと、master と msdb に格納されている一部のシステム テーブルに対して更新を行うと、常に行のバージョンが生成されます。All updates to user tables and some system tables stored in master and msdb generate row versions.

master データベースと msdb データベースでは、ALLOW_SNAPSHOT_ISOLATION オプションが自動的に ON に設定されます。このオプションを無効にすることはできません。The ALLOW_SNAPSHOT_ISOLATION option is automatically set ON in the master and msdb databases, and cannot be disabled.

master、tempdb、または msdb では、ユーザーが READ_COMMITTED_SNAPSHOT オプションを ON に設定することはできません。Users cannot set the READ_COMMITTED_SNAPSHOT option ON in master, tempdb, or msdb.

行のバージョン管理に基づく分離レベルの使用Using Row Versioning-based Isolation Levels

行のバージョン管理フレームワークは、SQL ServerSQL Server では常に有効になっており、複数の機能で使用されます。The row versioning framework is always enabled in SQL ServerSQL Server, and is used by multiple features. このフレームワークは、行のバージョン管理に基づく分離レベルを提供するだけでなく、トリガーと複数のアクティブな結果セット (MARS) セッションで行われた変更のサポートおよびオンラインのインデックス操作でのデータ読み取りのサポートに使用されます。Besides providing row versioning-based isolation levels, it is used to support modifications made in triggers and multiple active result sets (MARS) sessions, and to support data reads for ONLINE index operations.

行のバージョン管理に基づく分離レベルは、データベース レベルで有効になっています。Row versioning-based isolation levels are enabled at the database level. この分離レベルが有効になっているデータベースのオブジェクトにアクセスするアプリケーションでは、次の分離レベルを使用してクエリを実行できます。Any application accessing objects from enabled databases can run queries using the following isolation levels:

  • Read Committed。次のコード例に示すように、READ_COMMITTED_SNAPSHOT データベース オプションを ON に設定して行のバージョン管理を使用します。Read-committed that uses row versioning by setting the READ_COMMITTED_SNAPSHOT database option to ON as shown in the following code example:

    ALTER DATABASE AdventureWorks2012  
        SET READ_COMMITTED_SNAPSHOT ON;  
    

    データベースで READ COMMITTED が有効になっている場合、Read Committed 分離レベルで実行されているすべてのクエリで行のバージョン管理が使用されます。つまり、読み取り操作により更新操作がブロックされることはありません。When the database is enabled for READ_COMMITTED_SNAPSHOT, all queries running under the read committed isolation level use row versioning, which means that read operations do not block update operations.

  • 次のコード例に示すように、ALLOW_SNAPSHOT_ISOLATION データベース オプションを ON に設定することによってスナップショット分離を有効にします。Snapshot isolation by setting the ALLOW_SNAPSHOT_ISOLATION database option to ON as shown in the following code example:

    ALTER DATABASE AdventureWorks2012  
        SET ALLOW_SNAPSHOT_ISOLATION ON;  
    

    スナップショット分離レベルで実行中のトランザクションでは、スナップショットが有効になっているデータベースのテーブルにアクセスできます。A transaction running under snapshot isolation can access tables in the database that have been enabled for snapshot. スナップショットが有効になっていないテーブルにアクセスするには、分離レベルを変更する必要があります。To access tables that have not been enabled for snapshot, the isolation level must be changed. たとえば、次のコード例では、スナップショット トランザクションで実行中に 2 つのテーブルを結合する SELECT ステートメントを示します。For example, the following code example shows a SELECT statement that joins two tables while running under a snapshot transaction. 1 つのテーブルは、スナップショット分離が無効なデータベースに属しています。One table belongs to a database in which snapshot isolation is not enabled. スナップショット分離レベルで SELECT ステートメントを実行すると、実行に失敗します。When the SELECT statement runs under snapshot isolation, it fails to execute successfully.

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;  
    BEGIN TRAN  
        SELECT t1.col5, t2.col5  
            FROM Table1 as t1  
            INNER JOIN SecondDB.dbo.Table2 as t2  
                ON t1.col1 = t2.col2;  
    

    次に、トランザクション分離レベルを Read Committed に変更するように変更した同じ SELECT ステートメントのコード例を示します。The following code example shows the same SELECT statement that has been modified to change the transaction isolation level to read-committed. この変更により、SELECT ステートメントは正常に実行されます。Because of this change, the SELECT statement executes successfully.

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;  
    BEGIN TRAN  
        SELECT t1.col5, t2.col5  
            FROM Table1 as t1  
            WITH (READCOMMITTED)  
            INNER JOIN SecondDB.dbo.Table2 as t2  
                ON t1.col1 = t2.col2;  
    

行のバージョン管理に基づく分離レベルを使用したトランザクションの制限事項Limitations of Transactions Using Row Versioning-based Isolation Levels

行のバージョン管理に基づく分離レベルを使用して作業する場合は、次の制限事項を考慮してください。Consider the following limitations when working with row versioning-based isolation levels:

  • READ_COMMITTED_SNAPSHOT は、tempdb、msdb、または master では有効にできません。READ_COMMITTED_SNAPSHOT cannot be enabled in tempdb, msdb, or master.

  • グローバルな一時テーブルは tempdb に格納されます。Global temp tables are stored in tempdb. スナップショット トランザクション内でグローバルな一時テーブルにアクセスする場合は、次のいずれかの操作を行う必要があります。When accessing global temp tables inside a snapshot transaction, one of the following must happen:

    • tempdb で ALLOW_SNAPSHOT_ISOLATION データベース オプションを ON に設定します。Set the ALLOW_SNAPSHOT_ISOLATION database option ON in tempdb.

    • 分離ヒントを使用して、ステートメントの分離レベルを変更します。Use an isolation hint to change the isolation level for the statement.

  • スナップショット トランザクションは、次の場合に失敗します。Snapshot transactions fail when:

    • スナップショット トランザクションが開始してからデータベースにアクセスするまで、データベースが読み取り専用になっている場合。A database is made read-only after the snapshot transaction starts, but before the snapshot transaction accesses the database.

    • 複数のデータベースのオブジェクトにアクセスするときに、スナップショット トランザクションが開始してからデータベースにアクセスするまでの間にデータベースの復旧が行われるようにデータベースの状態が変更された場合。If accessing objects from multiple databases, a database state was changed in such a way that database recovery occurred after a snapshot transaction starts, but before the snapshot transaction accesses the database. たとえば、データベースが OFFLINE に設定されてから ONLINE に設定された場合、データベースを自動終了して開く場合、またはデータベースをデタッチ後にアタッチする場合などがあります。For example: the database was set to OFFLINE and then to ONLINE, database autoclose and open, or database detach and attach.

  • 分散パーティション データベースのクエリなど、分散トランザクションはスナップショット分離ではサポートされていません。Distributed transactions, including queries in distributed partitioned databases, are not supported under snapshot isolation.

  • SQL ServerSQL Server では、複数バージョンのシステム メタデータは保持されません。does not keep multiple versions of system metadata. テーブルおよび他のデータベース オブジェクト (インデックス、ビュー、データ型、ストアド プロシージャ、および共通言語ランタイム関数) のデータ定義言語 (DDL) ステートメントにより、メタデータが変更されます。Data definition language (DDL) statements on tables and other database objects (indexes, views, data types, stored procedures, and common language runtime functions) change metadata. DDL ステートメントでオブジェクトを変更する場合、スナップショット分離では、オブジェクトへの同時参照が原因で、スナップショット トランザクションが失敗します。If a DDL statement modifies an object, any concurrent reference to the object under snapshot isolation causes the snapshot transaction to fail. READ_COMMITTED_SNAPSHOT データベース オプションが ON の場合、Read Committed トランザクションにはこの制限がありません。Read-committed transactions do not have this limitation when the READ_COMMITTED_SNAPSHOT database option is ON.

    たとえば、データベース管理者が、次の ALTER INDEX ステートメントを実行したとします。For example, a database administrator executes the following ALTER INDEX statement.

    USE AdventureWorks2012;  
    GO  
    ALTER INDEX AK_Employee_LoginID  
        ON HumanResources.Employee REBUILD;  
    GO  
    

    ALTER INDEX ステートメントの実行時にアクティブなスナップショット トランザクションでは、HumanResources.Employee ステートメントの実行後に ALTER INDEX テーブルを参照すると、エラーが発生します。Any snapshot transaction that is active when the ALTER INDEX statement is executed receives an error if it attempts to reference the HumanResources.Employee table after the ALTER INDEX statement is executed. 行のバージョン管理を使用する Read Committed トランザクションは影響を受けません。Read-committed transactions using row versioning are not affected.

    注意

    BULK INSERT 操作 (たとえば、制約チェックを無効にする場合など) により、挿入先テーブルのメタデータが変更されることがあります。BULK INSERT operations may cause changes to target table metadata (for example, when disabling constraint checks). このような変更が発生すると、一括挿入されたテーブルにアクセスする同時実行中のスナップショット分離トランザクションは失敗します。When this happens, concurrent snapshot isolation transactions accessing bulk inserted tables fail.

上部のリンクに戻る で使用される矢印アイコンこのガイドでArrow icon used with Back to Top link In This Guide

ロックおよび行のバージョン管理のカスタマイズCustomizing Locking and Row Versioning

ロック タイムアウトのカスタマイズCustomizing the Lock Time-Out

別のトランザクションが競合するロックをリソースで既に所有しているために MicrosoftMicrosoft SQL Server データベース エンジンSQL Server Database Engineで同じリソースへのロックをトランザクションに許可できない場合、そのトランザクションはブロックされ、既存のロックが解放されるまで待機状態になります。When an instance of the MicrosoftMicrosoft SQL Server データベース エンジンSQL Server Database Engine cannot grant a lock to a transaction because another transaction already owns a conflicting lock on the resource, the first transaction becomes blocked waiting for the existing lock to be released. 既定では、強制タイムアウト時間は設定されないので、ロック前にリソースがロックされているかどうかを調べる方法はデータにアクセスする以外にありません。そして、データにアクセスすると無期限にブロックされる可能性があります。By default, there is no mandatory time-out period and no way to test whether a resource is locked before locking it, except to attempt to access the data (and potentially get blocked indefinitely).

注意

SQL ServerSQL Server では、sys.dm_os_waiting_tasks 動的管理ビューを使用して、特定のプロセスがブロックされているかどうか、またどのプロセスがブロックしているかを判断できます。In SQL ServerSQL Server, use the sys.dm_os_waiting_tasks dynamic management view to determine whether a process is being blocked and who is blocking it. 以前のバージョンの SQL ServerSQL Server では、sp_who システム ストアド プロシージャを使用していました。In earlier versions of SQL ServerSQL Server, use the sp_who system stored procedure.

LOCK_TIMEOUT 設定により、ブロックされたリソースをステートメントが待機する最大時間をアプリケーションから設定できます。The LOCK_TIMEOUT setting allows an application to set a maximum time that a statement waits on a blocked resource. 待機時間が LOCK_TIMEOUT の設定を超えると、ブロックされているステートメントが自動的に取り消され、エラー メッセージ 1222 (Lock request time-out period exceeded) がアプリケーションに返されます。When a statement has waited longer than the LOCK_TIMEOUT setting, the blocked statement is canceled automatically, and error message 1222 (Lock request time-out period exceeded) is returned to the application. ただし、このステートメントが含まれているトランザクションは、SQL ServerSQL Server によってロールバックされたり取り消されたりしません。Any transaction containing the statement, however, is not rolled back or canceled by SQL ServerSQL Server. したがって、アプリケーションはエラー メッセージ 1222 をトラップできるエラー ハンドラーを備えている必要があります。Therefore, the application must have an error handler that can trap error message 1222. アプリケーションでこのエラーをトラップしないと、トランザクション内の各ステートメントが取り消されたことが認識されません。トランザクション内のこれ以降のステートメントが、実行されなかったステートメントに依存している可能性があるので、エラーが発生することがあります。If an application does not trap the error, the application can proceed unaware that an individual statement within a transaction has been canceled, and errors can occur because statements later in the transaction might depend on the statement that was never executed.

エラー メッセージ 1222 をトラップするエラー ハンドラーを実装すると、アプリケーションでタイムアウト状況を処理し、ブロックされたステートメントを自動的に再実行したりトランザクション全体をロールバックするなどの救済措置を講じることができます。Implementing an error handler that traps error message 1222 allows an application to handle the time-out situation and take remedial action, such as: automatically resubmitting the statement that was blocked or rolling back the entire transaction.

現在の LOCK_TIMEOUT 設定を確認するのには、実行、@@LOCK_TIMEOUT関数。To determine the current LOCK_TIMEOUT setting, execute the @@LOCK_TIMEOUT function:

SELECT @@lock_timeout;  
GO  

トランザクション分離レベルのカスタマイズCustomizing Transaction Isolation Level

READ COMMITTED は、MicrosoftMicrosoft SQL Server データベース エンジンSQL Server Database Engineの既定の分離レベルです。READ COMMITTED is the default isolation level for the MicrosoftMicrosoft SQL Server データベース エンジンSQL Server Database Engine. アプリケーションを異なる分離レベルで動作させる必要がある場合、次の方法を使用して分離レベルを設定できます。If an application must operate at a different isolation level, it can use the following methods to set the isolation level:

  • SET TRANSACTION ISOLATION LEVEL ステートメントを実行します。Run the SET TRANSACTION ISOLATION LEVEL statement.

  • System.Data.SqlClient マネージド名前空間を使用している ADO.NET アプリケーションでは、SqlConnection.BeginTransaction メソッドを使用して IsolationLevel オプションを指定できます。ADO.NET applications that use the System.Data.SqlClient managed namespace can specify an IsolationLevel option by using the SqlConnection.BeginTransaction method.

  • ADO を使用するアプリケーションでは、Autocommit Isolation Levels プロパティを設定できます。Applications that use ADO can set the Autocommit Isolation Levels property.

  • トランザクションを開始するとき、OLE DB を使用しているアプリケーションでは、isoLevel を必要なトランザクション分離レベルに設定して ITransactionLocal::StartTransaction を呼び出すことができます。When starting a transaction, applications using OLE DB can call ITransactionLocal::StartTransaction with isoLevel set to the desired transaction isolation level. OLE DB を使用するアプリケーションでは、自動コミット モードの分離レベルを指定するときに、DBPROPSET_SESSION プロパティの DBPROP_SESS_AUTOCOMMITISOLEVELS を必要なトランザクション分離レベルに設定できます。When specifying the isolation level in autocommit mode, applications that use OLE DB can set the DBPROPSET_SESSION property DBPROP_SESS_AUTOCOMMITISOLEVELS to the desired transaction isolation level.

  • ODBC を使用するアプリケーションでは、SQLSetConnectAttr を使用して SQL_COPT_SS_TXN_ISOLATION 属性を設定できます。Applications that use ODBC can set the SQL_COPT_SS_TXN_ISOLATION attribute by using SQLSetConnectAttr.

分離レベルを指定すると、SQL ServerSQL Server セッションのクエリおよびデータ操作言語 (DML) ステートメントすべてに対するロック動作は、その分離レベルで動作します。When the isolation level is specified, the locking behavior for all queries and data manipulation language (DML) statements in the SQL ServerSQL Server session operates at that isolation level. 分離レベルは、セッションが終了するか、または分離レベルが別のレベルに設定されるまで有効です。The isolation level remains in effect until the session terminates or until the isolation level is set to another level.

次の例では、SERIALIZABLE 分離レベルを設定します。The following example sets the SERIALIZABLE isolation level:

USE AdventureWorks2012;  
GO  
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  
GO  
BEGIN TRANSACTION;  
SELECT BusinessEntityID  
    FROM HumanResources.Employee;  
GO  

分離レベルは、必要に応じて個別のクエリまたは DML ステートメントでテーブル レベルのヒントを指定することによりオーバーライドできます。The isolation level can be overridden for individual query or DML statements, if necessary, by specifying a table-level hint. テーブル レベルのヒントを指定しても、セッション内の他のステートメントに影響はありません。Specifying a table-level hint does not affect other statements in the session. テーブル レベルのヒントを使用して既定の動作を変更する操作は、どうしても必要な場合にのみ行うことをお勧めします。We recommend that table-level hints be used to change the default behavior only when absolutely necessary.

データの読み取り時に共有ロックが要求されない分離レベルを設定した場合でも、データベース エンジンDatabase Engineではメタデータの読み取り時にロックの取得が必要になる場合があります。The データベース エンジンDatabase Engine might have to acquire locks when reading metadata even when the isolation level is set to a level where share locks are not requested when reading data. たとえば、トランザクションが READ UNCOMMITTED 分離レベルで実行されている場合、データの読み取り時には共有ロックが取得されませんが、システム カタログ ビューの読み取り時にはロックが要求されることがあります。For example, a transaction running at the read-uncommitted isolation level does not acquire share locks when reading data, but might sometime request locks when reading a system catalog view. つまり、READ UNCOMMITTED 分離レベルで実行されているトランザクションでは、同時実行トランザクションでテーブルのメタデータが変更されているときに、そのテーブルに対してクエリが実行されると、ブロッキングを発生させることがあります。This means it is possible for a read uncommitted transaction to cause blocking when querying a table when a concurrent transaction is modifying the metadata of that table.

現在設定されているトランザクション分離レベルを特定するには、次の例に示すように、DBCC USEROPTIONS ステートメントを使用します。To determine the transaction isolation level currently set, use the DBCC USEROPTIONS statement as shown in the following example. 次に示す結果セットは、使用中のシステムの結果セットとは異なる場合があります。The result set may vary from the result set on your system.

USE AdventureWorks2012;  
GO  
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
GO  
DBCC USEROPTIONS;  
GO  

以下に結果セットを示します。Here is the result set.

Set Option Value

---------------------------- -------------------------------------------

textsize 2147483647

language us_english

dateformat mdy

datefirst 7

... ...

Isolation level repeatable read

``

(14 row(s) affected)

``

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

ロック ヒントLocking Hints

SELECT、INSERT、UPDATE、DELETE の各ステートメント内で参照する個別のテーブルにロック ヒントを指定できます。Locking hints can be specified for individual table references in the SELECT, INSERT, UPDATE, and DELETE statements. ロック ヒントでは、SQL Server データベース エンジンSQL Server Database Engineのインスタンスがテーブル データに使用するロックの種類や行のバージョン管理が指定されます。The hints specify the type of locking or row versioning the instance of the SQL Server データベース エンジンSQL Server Database Engine uses for the table data. テーブルレベルのロック ヒントは、オブジェクトにかけるロックの種類を詳細に制御する場合に使用できます。Table-level locking hints can be used when a finer control of the types of locks acquired on an object is required. これらのロック ヒントは、セッションの現在のトランザクション分離レベルをオーバーライドします。These locking hints override the current transaction isolation level for the session.

ロック ヒントの指定とその動作の詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。For more information about the specific locking hints and their behaviors, see Table Hints (Transact-SQL).

注意

データベース エンジンDatabase Engineのクエリ オプティマイザーでは、ほとんどの場合、適切なロック レベルが選択されます。The データベース エンジンDatabase Engine query optimizer almost always chooses the correct locking level. 必要な場合に限り、テーブルレベルのロック ヒントを使用して既定のロック動作を変更することをお勧めします。We recommend that table-level locking hints be used to change the default locking behavior only when necessary. ロック レベルを禁止すると、コンカレンシーが低下することがあります。Disallowing a locking level can adversely affect concurrency.

データベース エンジンDatabase Engineでは、データの読み取り時に共有ロックの要求を回避するロック ヒントが指定された SELECT を処理している場合でも、メタデータの読み取り時にロックの取得が必要になる場合があります。The データベース エンジンDatabase Engine might have to acquire locks when reading metadata, even when processing a select with a locking hint that prevents requests for share locks when reading data. たとえば、NOLOCK ヒントを使用する SELECT では、データの読み取り時には共有ロックが取得されませんが、システム カタログ ビューの読み取り時にはロックが要求される場合があります。For example, a SELECT using the NOLOCK hint does not acquire share locks when reading data, but might sometime request locks when reading a system catalog view. そのため、NOLOCK を使用する SELECT ステートメントはブロックされる可能性があります。This means it is possible for a SELECT statement using NOLOCK to be blocked.

次の例のように、トランザクションの分離レベルを SERIALIZABLE に設定し、テーブルレベルのロック ヒントとして NOLOCKSELECT ステートメントで使用すると、シリアル化可能なトランザクションの管理に通常使用されるキー範囲ロックが取得されません。As shown in the following example, if the transaction isolation level is set to SERIALIZABLE, and the table-level locking hint NOLOCK is used with the SELECT statement, key-range locks typically used to maintain serializable transactions are not taken.

USE AdventureWorks2012;  
GO  
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  
GO  
BEGIN TRANSACTION;  
GO  
SELECT JobTitle  
    FROM HumanResources.Employee WITH (NOLOCK);  
GO  
  
-- Get information about the locks held by   
-- the transaction.  
SELECT    
        resource_type,   
        resource_subtype,   
        request_mode  
    FROM sys.dm_tran_locks  
    WHERE request_session_id = @@spid;  
  
-- End the transaction.  
ROLLBACK;  
GO  

獲得されるロックのうち HumanResources.Employee を参照するのはスキーマ安定度 (Sch-S) ロックのみです。The only lock taken that references HumanResources.Employee is a schema stability (Sch-S) lock. この場合、シリアル化可能性は保証されません。In this case, serializability is no longer guaranteed.

SQL Server 2014SQL Server 2014 では、ALTER TABLE の LOCK_ESCALATION オプションを指定することでテーブル ロックの使用を回避し、パーティション テーブルに対する HoBT ロックを有効にすることができます。In SQL Server 2014SQL Server 2014, the LOCK_ESCALATION option of ALTER TABLE can disfavor table locks, and enable HoBT locks on partitioned tables. このオプションはロック ヒントではありませんが、ロックのエスカレーションを減らすために使用することができます。This option is not a locking hint, but can but used to reduce lock escalation. 詳細については、「ALTER TABLE (Transact-SQL)」を参照してください。For more information, see ALTER TABLE (Transact-SQL).

インデックスのロックのカスタマイズCustomizing Locking for an Index

SQL Server データベース エンジンSQL Server Database Engineでは、ほとんどの場合において、クエリに対し最適なロック粒度を自動的に選択するという動的ロック ストラテジを採用しています。The SQL Server データベース エンジンSQL Server Database Engine uses a dynamic locking strategy that automatically chooses the best locking granularity for queries in most cases. テーブルまたはインデックスのアクセス パターンが一定していることがわかっていて、リソースの競合を解決する必要がある場合を除き、ページと行のロックがオンになっている既定のロック レベルをオーバーライドしないことをお勧めします。We recommend that you do not override the default locking levels, which have page and row locking on, unless table or index access patterns are well understood and consistent, and there is a resource contention problem to solve. ロック レベルをオーバーライドすると、テーブルまたはインデックスへの同時アクセスのパフォーマンスが大きく低下することがあります。Overriding a locking level can significantly impede concurrent access to a table or index. たとえば、ユーザーが頻繁にアクセスする大きなテーブルに対してテーブルレベルのロックのみを指定すると、ボトルネックが発生します。これは、ユーザーがテーブルにアクセスする前に、テーブルレベルのロックが解除されるのを待たなければならなくなるためです。For example, specifying only table-level locks on a large table that users access heavily can cause bottlenecks because users must wait for the table-level lock to be released before accessing the table.

アクセス パターンが一定していることがわかっている場合、ページまたは行のロックを禁止することが効果的なケースもいくつかあります。There are a few cases where disallowing page or row locking can be beneficial, if the access patterns are well understood and consistent. たとえば、あるデータベース アプリケーションでバッチ処理により週単位で更新される参照テーブルを使用しているとします。For example, a database application uses a lookup table that is updated weekly in a batch process. 共有 (S) ロックされているテーブルに、同時に読み取りを行うユーザーがアクセスし、排他 (X) ロックされているテーブルに、週に 1 回のバッチ更新がアクセスします。Concurrent readers access the table with a shared (S) lock and the weekly batch update accesses the table with an exclusive (X) lock. テーブル上でページと行のロックをオフにし、読み取り側が共有テーブル ロックを通過して同時にテーブルにアクセスできるようにすることで、週を通したロック オーバーヘッドを小さくできます。Turning off page and row locking on the table reduces the locking overhead throughout the week by allowing readers to concurrently access the table through shared table locks. バッチ ジョブの実行時には、排他テーブル ロックを獲得できるので、効率的に更新を完了できます。When the batch job runs, it can complete the update efficiently because it obtains an exclusive table lock.

週に 1 回のバッチ更新による更新の実行中は同時読み取りによるテーブルへのアクセスがブロックされるため、ページと行のロックをオフにすることが適切かどうかは状況によって異なります。Turning off page and row locking might or might not be acceptable because the weekly batch update will block the concurrent readers from accessing the table while the update runs. バッチ ジョブによっていくつかの行またはページのみが変更される場合は、行またはページ レベルのロックを許可するようにロック レベルを変更できます。この場合、他のセッションはブロックされることなくテーブルから読み出しを行うことができます。If the batch job only changes a few rows or pages, you can change the locking level to allow row or page level locking, which will enable other sessions to read from the table without blocking. バッチ ジョブで大量の更新を扱う場合は、バッチ ジョブを効率的に完了するためにテーブル上で排他ロックを取得することが最善の方法です。If the batch job has a large number of updates, obtaining an exclusive lock on the table may be the best way to ensure the batch job finishes efficiently.

2 つの同時処理が同じテーブル上で行ロックを取得した後にブロックすると、どちらもページをロックする必要があるため、デッドロックが発生する場合があります。Occasionally a deadlock occurs when two concurrent operations acquire row locks on the same table and then block because they both need to lock the page. 行ロックを禁止することで、どちらかの処理が待機するようになるため、デッドロックを回避できます。Disallowing row locks forces one of the operations to wait, avoiding the deadlock.

インデックスで使用するロックの粒度は、CREATE INDEX ステートメントまたは ALTER INDEX ステートメントを使用して設定できます。The granularity of locking used on an index can be set using the CREATE INDEX and ALTER INDEX statements. ロック設定は、インデックス ページとテーブル ページの両方に適用されます。The lock settings apply to both the index pages and the table pages. また、CREATE TABLE ステートメントと ALTER TABLE ステートメントを使用して、PRIMARY KEY 制約および UNIQUE 制約によるロック粒度を設定することもできます。In addition, the CREATE TABLE and ALTER TABLE statements can be used to set locking granularity on PRIMARY KEY and UNIQUE constraints. 旧バージョンとの互換性、 sp_indexoptionシステム ストアド プロシージャは、粒度も設定できます。For backwards compatibility, the sp_indexoption system stored procedure can also set the granularity. 特定のインデックスの現在のロックのオプションを表示するには、INDEXPROPERTY 関数を使用します。To display the current locking option for a given index, use the INDEXPROPERTY function. 特定のインデックスに対して、ページレベルのロック、行レベルのロック、またはこの 2 つのロックの組み合わせを禁止することができます。Page-level locks, row-level locks, or a combination of page-level and row-level locks can be disallowed for a given index.

禁止されるロックDisallowed locks インデックスにかけられるロックIndex accessed by
ページレベルのロックPage level 行レベルおよびテーブルレベルのロックRow-level and table-level locks
行レベルのロックRow level ページレベルおよびテーブルレベルのロックPage-level and table-level locks
ページレベルおよび行レベルのロックPage level and row level テーブルレベルのロックTable-level locks

上部のリンクに戻る で使用される矢印アイコンこのガイドでArrow icon used with Back to Top link In This Guide

詳細なトランザクション情報Advanced Transaction Information

トランザクションの入れ子Nesting Transactions

明示的なトランザクションは入れ子にすることができます。Explicit transactions can be nested. これは、トランザクション内の既存のプロセスからでもアクティブ トランザクションがないプロセスからでも呼び出せるストアド プロシージャ内のトランザクションをサポートすることを主な目的としています。This is primarily intended to support transactions in stored procedures that can be called either from a process already in a transaction or from processes that have no active transaction.

次の例は、入れ子構造のトランザクションの使用方法を示しています。The following example shows the intended use of nested transactions. プロシージャ TransProc は、プロセスのトランザクション モードに関係なくトランザクションを実行します。The procedure TransProc enforces its transaction regardless of the transaction mode of any process that executes it. トランザクションがアクティブであるときに TransProc を呼び出すと、TransProc 内の入れ子になっているトランザクションは概して無視され、外側のトランザクションに対して行った最終的な操作に基づいて INSERT ステートメントがコミットまたはロールバックされます。If TransProc is called when a transaction is active, the nested transaction in TransProc is largely ignored, and its INSERT statements are committed or rolled back based on the final action taken for the outer transaction. 未完了のトランザクションがないプロセスが TransProc を実行した場合は、プロシージャの最後に INSERT ステートメントが COMMIT TRANSACTION によって有効にコミットされます。If TransProc is executed by a process that does not have an outstanding transaction, the COMMIT TRANSACTION at the end of the procedure effectively commits the INSERT statements.

SET QUOTED_IDENTIFIER OFF;  
GO  
SET NOCOUNT OFF;  
GO  
CREATE TABLE TestTrans(Cola INT PRIMARY KEY,  
               Colb CHAR(3) NOT NULL);  
GO  
CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS  
BEGIN TRANSACTION InProc  
INSERT INTO TestTrans VALUES (@PriKey, @CharCol)  
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol)  
COMMIT TRANSACTION InProc;  
GO  
/* Start a transaction and execute TransProc. */  
BEGIN TRANSACTION OutOfProc;  
GO  
EXEC TransProc 1, 'aaa';  
GO  
/* Roll back the outer transaction, this will  
   roll back TransProc's nested transaction. */  
ROLLBACK TRANSACTION OutOfProc;  
GO  
EXECUTE TransProc 3,'bbb';  
GO  
/* The following SELECT statement shows only rows 3 and 4 are   
   still in the table. This indicates that the commit  
   of the inner transaction from the first EXECUTE statement of  
   TransProc was overridden by the subsequent rollback. */  
SELECT * FROM TestTrans;  
GO  

SQL Server データベース エンジンSQL Server Database Engineでは、入れ子の内側のトランザクションのコミットが無視されます。Committing inner transactions is ignored by the SQL Server データベース エンジンSQL Server Database Engine. 内側のトランザクションは、最も外側にあるトランザクションの最後に行われた操作に基づいてコミットまたはロールバックされます。The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. 外側のトランザクションがコミットされると、入れ子の内側のトランザクションもコミットされます。If the outer transaction is committed, the inner nested transactions are also committed. 外側のトランザクションがロールバックされると、内側のトランザクションも、個々がコミットされたかどうかに関係なくすべてロールバックされます。If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.

COMMIT TRANSACTION または COMMIT WORK への各呼び出しは、最後に実行された BEGIN TRANSACTION に適用されます。Each call to COMMIT TRANSACTION or COMMIT WORK applies to the last executed BEGIN TRANSACTION. BEGIN TRANSACTION ステートメントが入れ子になっている場合、最後の入れ子になっているトランザクション、つまり最も内側のトランザクションだけに COMMIT ステートメントが適用されます。If the BEGIN TRANSACTION statements are nested, then a COMMIT statement applies only to the last nested transaction, which is the innermost transaction. コミット トランザクションの場合でもtransaction_name入れ子になったトランザクション内のステートメントは、外側のトランザクションのトランザクションの名前、コミットは、最も内側のトランザクションにのみ適用されます。Even if a COMMIT TRANSACTION transaction_name statement within a nested transaction refers to the transaction name of the outer transaction, the commit applies only to the innermost transaction.

正しくない、 transaction_nameという名前の入れ子になったトランザクションを一連の内側のトランザクションを参照する ROLLBACK TRANSACTION ステートメントのパラメーター。It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name で参照できるのは、最も外側のトランザクションの名前のみです。transaction_name can refer only to the transaction name of the outermost transaction. 入れ子構造になっている一連のトランザクションの任意のレベルで、外側のトランザクションの名前を使用して ROLLBACK TRANSACTION transaction_name ステートメントで実行すると、入れ子構造のトランザクションすべてがロールバックされます。If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. なし、ROLLBACK WORK ステートメントまたは ROLLBACK TRANSACTION ステートメントの場合、 transaction_nameパラメーターは、一連の入れ子になったトランザクションの任意のレベルで実行されますが、ロールバックされます、最も外側のトランザクションを含めて、入れ子になったトランザクションのすべて。If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

@@TRANCOUNT関数は現在のトランザクションの入れ子レベルを記録します。The @@TRANCOUNT function records the current transaction nesting level. 各 BEGIN TRANSACTION ステートメントはインクリメント@TRANCOUNTを 1 つ。Each BEGIN TRANSACTION statement increments @@TRANCOUNT by one. 各 COMMIT TRANSACTION または COMMIT WORK ステートメントのデクリメント @@TRANCOUNTを 1 つ。Each COMMIT TRANSACTION or COMMIT WORK statement decrements @@TRANCOUNT by one. ROLLBACK WORK ステートメントまたは ROLLBACK TRANSACTION ステートメントをトランザクション名を含まない入れ子になったすべてのトランザクションとロールバック @ デクリメント@TRANCOUNTを 0 にします。A ROLLBACK WORK or a ROLLBACK TRANSACTION statement that does not have a transaction name rolls back all nested transactions and decrements @@TRANCOUNT to 0. 一連の入れ子になったトランザクションの最も外側のトランザクションのトランザクション名を使用する ROLLBACK TRANSACTION は @ デクリメント、入れ子になったトランザクションのすべて@TRANCOUNTを 0 にします。A ROLLBACK TRANSACTION that uses the transaction name of the outermost transaction in a set of nested transactions rolls back all of the nested transactions and decrements @@TRANCOUNT to 0. ときに不明なトランザクションにいる場合、選択@TRANCOUNTが 1 つ以上を判断します。When you are unsure if you are already in a transaction, SELECT @@TRANCOUNT to determine if it is 1 or more. @ If@TRANCOUNTが 0 の場合、トランザクションではないです。If @@TRANCOUNT is 0, you are not in a transaction.

バインドされたセッションの使用Using Bound Sessions

バインドされたセッションを使用すると、同一サーバー上の複数のセッションにまたがるアクションの調整が容易になります。Bound sessions ease the coordination of actions across multiple sessions on the same server. バインドされたセッションでは、2 つ以上のセッションで同じトランザクションとロックを共有できます。また、ロックの競合が発生しないで同じデータを操作できます。Bound sessions allow two or more sessions to share the same transaction and locks, and can work on the same data without lock conflicts. バインドされたセッションは、同じアプリケーション内の複数のセッションからも、セッションが異なる複数のアプリケーションからも作成できます。Bound sessions can be created from multiple sessions within the same application or from multiple applications with separate sessions.

セッションを呼び出し、バインドされたセッションに参加するにsp_getbindtokenまたはsrv_getbindtoken (オープン データ サービス経由)、バインド トークンを取得します。To participate in a bound session, a session calls sp_getbindtoken or srv_getbindtoken (through Open Data Services) to get a bind token. バインド トークンは、バインドされたトランザクションをそれぞれ一意に識別する文字列です。A bind token is a character string that uniquely identifies each bound transaction. 取得したバインド トークンは、現在のセッションにバインドされる他のセッションに送信されます。The bind token is then sent to the other sessions to be bound with the current session. 他のセッションは、最初のセッションから受信したバインド トークンを使用して sp_bindsession を呼び出すことにより、トランザクションにバインドされます。The other sessions bind to the transaction by calling sp_bindsession, using the bind token received from the first session.

注意

セッションの順序でのアクティブなユーザー トランザクションが必要sp_getbindtokenまたはsrv_getbindtokenが成功します。A session must have an active user transaction in order for sp_getbindtoken or srv_getbindtoken to succeed.

最初のセッションを作成するアプリケーション コードから、その後最初のセッションに他のセッションをバインドするアプリケーション コードに、バインド トークンが転送される必要があります。Bind tokens must be transmitted from the application code that makes the first session to the application code that subsequently binds their sessions to the first session. 別のプロセスによって開始されたトランザクションのバインド トークンをアプリケーションで取得するための Transact-SQLTransact-SQL ステートメントや API 関数はありません。There is no Transact-SQLTransact-SQL statement or API function that an application can use to get the bind token for a transaction started by another process. バインド トークンは、次に示す方法を使用して転送できます。Some of the methods that can be used to transmit a bind token include the following:

  • バインドされるセッションがすべて同じアプリケーション プロセスから開始されている場合、バインド トークンをグローバル メモリに格納するか、パラメーターとして関数に渡すことができます。If the sessions are all initiated from the same application process, bind tokens can be stored in global memory or passed into functions as a parameter.

  • バインドされるセッションが異なるアプリケーション プロセスで作成されている場合、RPC (リモート プロシージャ コール) や DDE (動的データ交換) などの IPC (プロセス間通信) を使用してバインド トークンを転送できます。If the sessions are made from separate application processes, bind tokens can be transmitted using interprocess communication (IPC), such as a remote procedure call (RPC) or dynamic data exchange (DDE).

  • SQL Server データベース エンジンSQL Server Database Engineのインスタンス内の、最初のセッションにバインドを試みるプロセスから読み取れるテーブルに、バインド トークンを格納します。Bind tokens can be stored in a table in an instance of the SQL Server データベース エンジンSQL Server Database Engine that can be read by processes wanting to bind to the first session.

バインドされたセッションのうち、一度にアクティブにできるのは 1 つだけです。Only one session in a set of bound sessions can be active at any time. あるセッションがインスタンス上でステートメントを実行している場合、またはインスタンスからの結果が保留中の場合、そのセッションにバインドされている他のセッションでは、現在のセッションが処理を完了するか、現在のステートメントが取り消されるまで、そのインスタンスにアクセスできません。If one session is executing a statement on the instance or has results pending from the instance, no other session bound to it can access the instance until the current session finishes processing or cancels the current statement. そのインスタンスでバインドされた別のセッションからのステートメントが処理されていてビジー状態の場合、トランザクション領域が使用中なのでそのセッションを後で再試行する必要があることを示すエラーが発生します。If the instance is busy processing a statement from another of the bound sessions, an error occurs indicating that the transaction space is in use and the session should retry later.

セッションをバインドするときに、各セッションの分離レベル設定が保持されます。When you bind sessions, each session retains its isolation level setting. SET TRANSACTION ISOLATION LEVEL を使用して 1 つのセッションの分離レベル設定を変更しても、そのセッションにバインドされている他のセッションの分離レベル設定は変更されません。Using SET TRANSACTION ISOLATION LEVEL to change the isolation level setting of one session does not affect the setting of any other session bound to it.

バインドされたセッションの種類Types of Bound Sessions

バインドされたセッションには "ローカル" と "分散" の 2 種類があります。The two types of bound sessions are local and distributed.

  • バインドされたローカル セッションLocal bound session

    バインドされたセッションは、データベース エンジンDatabase Engineの 1 つのインスタンスで、1 つのトランザクションのトランザクション領域を共有できます。Allows bound sessions to share the transaction space of a single transaction in a single instance of the データベース エンジンDatabase Engine.

  • バインドされた分散セッションDistributed bound session

    MicrosoftMicrosoft 分散トランザクション コーディネーター (MS DTC) を使用して、バインドされたセッションは、トランザクション全体がコミットまたはロールバックされるまで、2 つ以上のインスタンス間で同じトランザクションを共有できます。Allows bound sessions to share the same transaction across two or more instances until the entire transaction is either committed or rolled back by using MicrosoftMicrosoft Distributed Transaction Coordinator (MS DTC).

バインドされた分散セッションは、文字列のバインド トークンによって識別されるのではなく、分散トランザクション ID 番号によって識別されます。Distributed bound sessions are not identified by a character string bind token; they are identified by distributed transaction identification numbers. バインドされたセッションがローカル トランザクションに関係していて、リモート サーバーで SET REMOTE_PROC_TRANSACTIONS ON を指定して RPC を実行している場合、MS DTC により、バインドされたローカル トランザクションがバインドされた分散トランザクションに自動的に昇格し、MS DTC セッションが開始します。If a bound session is involved in a local transaction and executes an RPC on a remote server with SET REMOTE_PROC_TRANSACTIONS ON, the local bound transaction is automatically promoted to a distributed bound transaction by MS DTC and an MS DTC session is started.

バインドされたセッションの用途When to Use Bound Sessions

以前のバージョンの SQL ServerSQL Server では、バインドされたセッションは、主に、特定の拡張ストアド プロシージャの開発に使用されていました。このような拡張ストアド プロシージャでは、セッションを呼び出すプロセスに代わって Transact-SQLTransact-SQL ステートメントを実行する必要があります。In earlier versions of SQL ServerSQL Server, bound sessions were primarily used in developing extended stored procedures that must execute Transact-SQLTransact-SQL statements on behalf of the process that calls them. 呼び出しプロセスをバインド トークンで拡張ストアド プロシージャのパラメーターの 1 つとして渡せば、プロシージャは呼び出し側プロセスのトランザクション領域を結合できます。これにより、拡張ストアド プロシージャを呼び出し元プロセスに統合できます。Having the calling process pass in a bind token as one parameter of the extended stored procedure allows the procedure to join the transaction space of the calling process, thereby integrating the extended stored procedure with the calling process.

SQL Server データベース エンジンSQL Server Database Engineでは、CLR を使用して記述されたストアド プロシージャは、拡張ストアド プロシージャよりも安全性、拡張性、安定性が高くなります。In the SQL Server データベース エンジンSQL Server Database Engine, stored procedures written using CLR are more secure, scalable, and stable than extended stored procedures. CLR ストアド プロシージャを使用して、 SqlContextいない呼び出し元のセッションのコンテキストを結合するオブジェクトsp_bindsessionします。CLR-stored procedures use the SqlContext object to join the context of the calling session, not sp_bindsession.

また、バインドされたセッションは、1 つのビジネス トランザクションで個別のプログラムが連携するようなビジネス ロジックを持つ、3 層構造のアプリケーションの開発に使用できます。Bound sessions can be used to develop three-tier applications in which business logic is incorporated into separate programs that work cooperatively on a single business transaction. このようなプログラムでは、データベースへのアクセス調整に注意を払う必要があります。These programs must be coded to carefully coordinate their access to a database. 2 つのセッションで同じロックを共有するので、その 2 つのプログラムで同じデータを同時に変更しないでください。Because the two sessions share the same locks, the two programs must not try to modify the same data at the same time. トランザクションの一部として機能するセッションはどの時点においても 1 つだけです。並列実行はできません。At any point in time, only one session can be doing work as part of the transaction; there can be no parallel execution. すべての DML ステートメントが完了しそれらの結果が取得された時点など、セッション間のトランザクション切り替えは明確な降伏点でのみ行えます。The transaction can only be switched between sessions at well-defined yield points, such as when all DML statements have completed and their results have been retrieved.

効率的なトランザクションのコーディングCoding Efficient Transactions

トランザクションはできるだけ短くすることが重要です。It is important to keep transactions as short as possible. トランザクションが開始されると、終了するまでの間、トランザクションの ACID プロパティ (原子性、一貫性、分離性、および持続性) を損なわないよう、多数のリソースを DBMS (データベース管理システム) で確保する必要があります。When a transaction is started, a database management system (DBMS) must hold many resources until the end of the transaction to protect the atomicity, consistency, isolation, and durability (ACID) properties of the transaction. データを変更する場合、他のトランザクションによる読み取りを防ぐために変更する行に排他ロックをかけて保護する必要があり、排他ロックは、トランザクションがコミットされるかロールバックされるまでかけておく必要があります。If data is modified, the modified rows must be protected with exclusive locks that prevent any other transaction from reading the rows, and exclusive locks must be held until the transaction is committed or rolled back. トランザクションの分離レベルの設定によっては、トランザクションのコミットまたはロールバックまで解除できないロックが SELECT ステートメントによってかけられる場合があります。Depending on transaction isolation level settings, SELECT statements may acquire locks that must be held until the transaction is committed or rolled back. 特に、ユーザー数が多いシステムの場合、コンカレント接続どうしによるリソースのロックの競合を減らす目的で、トランザクションをできるだけ短くする必要があります。Especially in systems with many users, transactions must be kept as short as possible to reduce locking contention for resources between concurrent connections. 実行時間が長く、効率の悪いトランザクションでもユーザー数が少なければ問題になりにくいですが、ユーザー数が数千にも及ぶシステムでは容認できません。Long-running, inefficient transactions may not be a problem with small numbers of users, but they are intolerable in a system with thousands of users. SQL Server 2014SQL Server 2014SQL ServerSQL Server 以降では、遅延持続性トランザクションがサポートされます。Beginning with SQL Server 2014SQL Server 2014SQL ServerSQL Server supports delayed durable transactions. 遅延持続性トランザクションでは、持続性が保証されません。Delayed durable transactions do not guarantee durability. 詳しくは、トランザクションの持続性に関するトピックをご覧ください。See the topic Transaction Durability for more information.

コーディングのガイドラインCoding Guidelines

効率的なトランザクションをコーディングするためのガイドラインは次のとおりです。These are guidelines for coding efficient transactions:

  • トランザクション中にユーザーによる入力を求めないようにします。Do not require input from users during a transaction.

    トランザクションを開始する前に、必要なすべての入力をユーザーが終えるようにします。Get all required input from users before a transaction is started. トランザクション中に追加のユーザー入力が必要になった場合は、現在のトランザクションをロールバックし、ユーザーが入力を終えてからトランザクションを再度開始します。If additional user input is required during a transaction, roll back the current transaction and restart the transaction after the user input is supplied. ユーザーの反応が早くても、人間の反応はコンピューターの処理速度に比べるとはるかに低速です。Even if users respond immediately, human reaction times are vastly slower than computer speeds. トランザクションが確保しているすべてのリソースが長時間確保されることにより、ブロッキングの問題が発生する場合があります。All resources held by the transaction are held for an extremely long time, which has the potential to cause blocking problems. ユーザーが反応しない場合、応答が (場合によっては数分後か数時間後に) あるまでトランザクションはアクティブな状態で、重要なリソースをロックし続けます。If users do not respond, the transaction remains active, locking critical resources until they respond, which may not happen for several minutes or even hours.

  • データの参照中は、できるだけトランザクションを開かないようにします。Do not open a transaction while browsing through data, if at all possible.

    トランザクションは、事前のすべてのデータ分析が完了するまで開始しないでください。Transactions should not be started until all preliminary data analysis has been completed.

  • トランザクションはできるだけ短くします。Keep the transaction as short as possible.

    どのような変更が必要なのか把握した上でトランザクションを開始し、変更ステートメントを実行し、すぐにコミットまたはロールバックします。After you know the modifications that have to be made, start a transaction, execute the modification statements, and then immediately commit or roll back. トランザクションは必要になってから開きます。Do not open the transaction before it is required.

  • ブロックを減らすため、読み取り専用のクエリには行のバージョン管理に基づく分離レベルの使用を検討します。To reduce blocking, consider using a row versioning-based isolation level for read-only queries.

  • 低いトランザクション分離レベルを賢く利用します。Make intelligent use of lower transaction isolation levels.

    多くのアプリケーションは、READ COMMITTED トランザクション分離レベルを使用するように簡単にコーディングできます。Many applications can be readily coded to use a read-committed transaction isolation level. すべてのトランザクションで SERIALIZABLE トランザクション分離レベルが必要なわけではありません。Not all transactions require the serializable transaction isolation level.

  • オプティミスティック コンカレンシー オプションなど、カーソルのコンカレンシーが低いオプションを賢く利用します。Make intelligent use of lower cursor concurrency options, such as optimistic concurrency options.

    同時に更新が行われる確率が低いシステムの場合、めったに発生しない "ユーザーがデータを読み取った後に他のユーザーがそのデータを変更した" というエラーを処理するオーバーヘッドは、読み取る行を常にロックするオーバーヘッドに比べて小さくできます。In a system with a low probability of concurrent updates, the overhead of dealing with an occasional "somebody else changed your data after you read it" error can be much lower than the overhead of always locking rows as they are read.

  • トランザクション中は、アクセスするデータ量をできるだけ少なくします。Access the least amount of data possible while in a transaction.

    アクセスするデータ量が少なければ、ロックされる行数が減るので、トランザクション間の競合が減少します。This lessens the number of locked rows, thereby reducing contention between transactions.

コンカレンシーとリソースの問題の回避Avoiding Concurrency and Resource Problems

コンカレンシーおよびリソースの問題を防ぐには、暗黙のトランザクションを注意深く管理します。To prevent concurrency and resource problems, manage implicit transactions carefully. 暗黙のトランザクションを使用する場合、COMMIT または ROLLBACK の直後の Transact-SQLTransact-SQL ステートメントから新しいトランザクションが自動的に開始されます。When using implicit transactions, the next Transact-SQLTransact-SQL statement after COMMIT or ROLLBACK automatically starts a new transaction. その結果、アプリケーションでデータが参照されている間や、ユーザーからの入力を要求している間にも新しいトランザクションが開くことができます。This can cause a new transaction to be opened while the application browses through data, or even when it requires input from the user. データの変更を防ぐことが必要な最後のトランザクションが完了した後、データの変更を防ぐことが必要な次のトランザクションまでは暗黙のトランザクションを無効にしてください。After completing the last transaction required to protect data modifications, turn off implicit transactions until a transaction is once again required to protect data modifications. そうすることで、アプリケーションでデータが参照されている間やユーザーが入力している間は、SQL Server データベース エンジンSQL Server Database Engineが自動コミット モードになります。This process lets the SQL Server データベース エンジンSQL Server Database Engine use autocommit mode while the application is browsing data and getting input from the user.

スナップショット分離レベルが有効である場合、新しいトランザクションがロックをかけることはありませんが、実行時間の長いトランザクションを実行する間はそれ以前のトランザクションが tempdb から削除されません。In addition, when the snapshot isolation level is enabled, although a new transaction will not hold locks, a long-running transaction will prevent the old versions from being removed from tempdb.

実行時間の長いトランザクションの管理Managing Long-Running Transactions

"実行時間の長いトランザクション" とは、適切なタイミングでコミットまたはロールバックされていないアクティブなトランザクションです。A long-running transaction is an active transaction that has not been committed or roll backed the transaction in a timely manner. たとえば、トランザクションの開始と終了をユーザーが制御する場合、トランザクションの実行時間が長くなる一般的な原因は、トランザクションを開始したユーザーが、トランザクションがユーザーからの応答を待っているにもかかわらず、席を外してしまうことです。For example, if the beginning and end of a transaction is controlled by the user, a typical cause of a long-running transaction is a user starting a transaction and then leaving while the transaction waits for a response from the user.

トランザクションの実行時間が長くなると、次のように、データベースへの深刻な問題が発生する可能性があります。A long running transaction can cause serious problems for a database, as follows:

  • その後の再起動の復旧フェーズで指定した時間よりもかなり長くかかる場合は、アクティブなトランザクションがコミットされていない多くの変更を実行した後、サーバー インスタンスをシャット ダウン、復旧間隔サーバー構成オプションまたは ALTER DATABASE.SET TARGET_RECOVERY_TIME オプションで指定した時間よりもかなり長くかかることがあります。If a server instance is shut down after an active transaction has performed many uncommitted modifications, the recovery phase of the subsequent restart can take much longer than the time specified by the recovery interval server configuration option or by the ALTER DATABASE... SET TARGET_RECOVERY_TIME option. これらのオプションではそれぞれ、アクティブなチェックポイントと間接的なチェックポイントの生成頻度を制御します。These options control the frequency of active and indirect checkpoints, respectively. チェックポイントの種類について詳しくは、「データベース チェックポイント (SQL Server)」をご覧ください。For more information about the types of checkpoints, see Database Checkpoints (SQL Server).

  • さらに重要な注意事項として、待機状態のトランザクション自体によって生成される可能性のあるログ量はわずかですが、ログの切り捨てが無期限に停止されるため、トランザクション ログが大きくなり、満杯になる可能性があります。More importantly, although a waiting transaction might generate very little log, it holds up log truncation indefinitely, causing the transaction log to grow and possibly fill up. トランザクション ログが満杯になると、データベースでは以降の更新を実行できなくなります。If the transaction log fills up, the database cannot perform any more updates. 詳細については、次を参照してください。満杯になったトランザクション ログのトラブルシューティングを行う(SQL Server エラー 9002)、およびトランザクション ログ(SQL Server)します。For more information, see Troubleshoot a Full Transaction Log (SQL Server Error 9002), and The Transaction Log (SQL Server).

実行時間の長いトランザクションの検出Discovering Long-Running Transactions

実行時間の長いトランザクションを検索するには、以下のいずれかの方法を使用します。To look for long-running transactions, use one of the following:

  • sys.dm_tran_database_transactionssys.dm_tran_database_transactions

    この動的管理ビューは、データベース レベルでのトランザクションに関する情報を返します。This dynamic management view returns information about transactions at the database level. 実行時間の長いトランザクションで特に関係のある列としては、最初のログ レコードの時間 (database_transaction_begin_time)、トランザクションの現在の状態 (database_transaction_state)、トランザクション ログ内の開始レコードのログ シーケンス番号 (LSN) (database_transaction_begin_lsn) があります。For a long-running transaction, columns of particular interest include the time of the first log record (database_transaction_begin_time), the current state of the transaction (database_transaction_state), and the log sequence number (LSN) of the begin record in the transaction log (database_transaction_begin_lsn).

    詳しくは、「sys.dm_tran_database_transactions (Transact-SQL)」をご覧ください。For more information, see sys.dm_tran_database_transactions (Transact-SQL).

  • DBCC OPENTRANDBCC OPENTRAN

    このステートメントを使用すると、トランザクション所有者のユーザー ID を特定できます。これにより、トランザクションの実行元を特定して、より規則正しくトランザクションを終了する (トランザクションをロールバックするのではなくコミットする) ことができます。This statement lets you identify the user ID of the owner of the transaction, so you can potentially track down the source of the transaction for a more orderly termination (committing it rather than rolling it back). 詳しくは、「DBCC OPENTRAN (Transact-SQL)」をご覧ください。For more information, see DBCC OPENTRAN (Transact-SQL).

トランザクションの停止Stopping a Transaction

KILL ステートメントの使用が必要になる場合もあります。You may have to use the KILL statement. ただし、重要なプロセスが実行中の場合は特に、このステートメントの使用には十分注意してください。Use this statement very carefully, however, especially when critical processes are running. 詳細については、KILL (Transact-SQL) を参照してください。For more information, see KILL (Transact-SQL).

上部のリンクに戻る で使用される矢印アイコンこのガイドでArrow icon used with Back to Top link In This Guide

参照See Also

SQL Server 2005 の行のバージョン管理ベースのトランザクションの分離 SQL Server 2005 Row Versioning-Based Transaction Isolation
行のバージョン管理のオーバーヘッド Overhead of Row Versioning
SQL Server 2008 で自律トランザクションを作成する方法How to create an autonomous transaction in SQL Server 2008