SQL Server で取得されるロックを確認する方法として、次のような情報から取得するという方法があります。
これらの方法で取得する場合「更新系」だけでなく「参照系」で取得されるロックの情報が取得できます。
SQL Server の運用をしている中で「この DDL や DML でデータや定義の変更を行った場合に、どのようなロックが取得されるのか?」を確認したいというケースがあるのではないでしょうか。
そのような場合、上述の情報だけでなく「トランザクションログ」から情報を確認するという方法をとることもできます。
SQL Server のトランザクションログについては「sys.fn_dblog」というシステム関数を使用することで、情報を取得することができます。
例としては次のようなクエリで情報を取得できます。
SELECT [Current LSN], [Transaction ID], [Begin Time], [End Time], [Transaction Begin], Operation, Context, Description, AllocUnitId, AllocUnitName, PartitionId, [Page ID], [Slot ID], [Lock Information] FROM sys.fn_dblog(NULL, NULL)
トランザクションログの中には「Lock Information」という列があり、この項目からどのようなロックが取得されたか確認できます。
次の画像は、「ALTER TABLE LINEITEM ADD C3 int」で列を追加した際のトランザクションログの内容となります。
「Lock Information」には、「HoBt 0:ACQUIRE_LOCK_SCH_M OBJECT: 11:997578592:0 」という情報が出力されていますね。
このオブジェクト ID の情報を取得してみると、「LINEITEM」テーブルであるということが確認できます。
このことから「ALTER TABLE によって発行されたトランザクションでは LINEITEM に対して SCH_M のロックが取得された」ということが確認できます。
SQL Server のロックの互換性については、SQL Server トランザクションのロックおよび行のバージョン管理ガイド で確認をすることができます。
「SCH_M 」は様々なロックと競合しますので、単純に ALTER TABLE で列を追加すると、同時実行性が低下するということがトランザクションログの情報から確認することができます。
通常トランザクションログの情報をそのまま活用するというケースは少ないと思いますが「DDL / DML によってどのような変化があるのか?」を確認する際には、該当のトランザクションによって書き込まれたトランザクションログの内容を元に推測するというアプローチを知っていると便利なことが多いです。
自分が実施した操作によって取得されるロックを知るためのアプローチとして、トランザクションログから確認するという方法も覚えておくと、動作を理解するための一助となるのではないでしょうか。