none
SQL Serverでのロック情報の取得について RRS feed

  • 質問

  •  トランザクションを開始してから、
    SELECT ~ FROM A WITH(UPDLOCK) ~ でテーブルAをロックします。

    別のユーザーでロックしているテーブルAを参照しようすると、
    タイムアウトのエラーが返ってきます。
    その際にテーブルAをロックしている端末名とユーザー名を知りたいです。

    ロックしているテーブルはsys.dm_tran_locksからわかるのですが、
    方法がネットで調べてもわかりません。
    ご教授願えませんでしょうか?

    よろしくお願いします。

     開発環境
    OS :Windows7 32bit
    言語:VB2010
     DB :SQLServer2012
     Windows認証で接続しています。


    2014年8月25日 4:57

回答

  • %%lockers%%を使うと行レベルで取得できるようです。ただ、私の環境で試したところ、なぜかresource_descriptionが空で、うまく取得できませんでした。ここについては時間切れで調べ切れていません。すみません・・・
    %%lockers%%は非公式なようなので、その関係かもしれません・・・。

    Undocumented Virtual Column: %%lockres%
    http://www.scarydba.com/2010/03/18/undocumented-virtual-column-lockres/

    ところで、ADO.NETは楽観的ロックが基本となっており、通常は長い間ロックが発生しないのですが、そのような作りにできないのは何か理由があるのでしょうか?


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    • 回答としてマーク あだっち 2014年9月1日 5:04
    2014年8月26日 8:42

すべての返信

  • 検索しただけですが、以下が参考になるかもしれません。

    SQL Server 2005: Get full information about transaction locks
    http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    2014年8月25日 5:43
  • 回答ありがとうございます。

    ロックをかけている情報の一覧は取得できたのですが、
    VB側から既にロック(NOWAIT)がかかっているデータに対してロックを行った際、
    エラーメッセージを返す時に、同時にロック元(ロックをかけているユーザー、コンピューター名)
    の情報を取得したいと思っております。

    すみませんがご教授をお願い致します。

    2014年8月25日 7:00
  • あれ? 私のリンク先にある、

    ES.login_name AS LoginName,
    ES.host_name AS HostName

    ではダメだったってことでしょうか? であれば、すみません。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    2014年8月25日 7:55
  • 教えて頂いた、URLに記述されていた通り実行すると
    現在のDBに対するロック情報が全て取得されてしまい、
    特定が出来ない状況になっております。

    例:ColAはKey
    セッションA(ユーザー名:tarou / コンピューター名:tarou_PC)
    select * from A WITH(ROWLOCK,UPDLOCK,NOWAIT) where ColA = '123'
    (これで行ロック)

    セッションB(ユーザー名:hanako / コンピューター名:hanako_PC)
    select * from A WITH(ROWLOCK,UPDLOCK,NOWAIT) where ColA = '123'
    (セッションAでロックをかけているのでエラーとなる)
    この時にセッションAのユーザー名とコンピューター名を取得したいです。


    質問がへたくそで申し訳ないのですが、宜しくお願い致します。

    2014年8月25日 8:10
  • Sys.objectsとSys.partitionsなどをjoinしてるので、そこからwhere句にテーブル名やロックの種類などを使って絞れないですかね? 

    #とりあえずselect文で全メンバーを表示させてみると、where句で絞るヒントが見るかるかもしれません。見つかれば、本当にそれで絞って良いのか裏を取れば完璧でしょう。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    2014年8月25日 8:28
  • お疲れ様です。

    親切な回答ありがとうございます。

    下記の通りVB側で実現しようとしているのですが。。。

            Try
                'SQL文を設定
                If pNoWait Then
                    pSql = "SET LOCK_TIMEOUT 0 ;" & vbCrLf & pSql
                End If
                m_Dbcommand.CommandText = pSql
                Log.OutPutLog(pSql)

                'トランザクションを設定
                m_Dbcommand.Transaction = m_Dbtransaction

                Using dataadapter As New SqlDataAdapter(m_Dbcommand)
                    If pDt IsNot Nothing Then
                        '取得対象データテーブルを初期化する
                        pDt = Nothing
                        pDt = New DataTable
                    End If
                    dataadapter.Fill(pDt)
                End Using

                '正常終了
                Return True

            Catch ex As Exception

       '************************************************
       '↓対象データがロック掛かっていた場合、ここで「ユーザー名、コンピューター名」を取得したいです。
                Log.OutPutLog(ex)
       '************************************************

                MsgBox(ex.Message MsgBoxStyle.OkOnly + MsgBoxStyle.Critical)
                Return False
            End Try

    exにロック元の情報があればなんとかできるのですが。。。

    2014年8月25日 11:21
  • 少し試してみました。以下のような感じで取れます。ただし、ユーザー名はログイン名になりますので注意が必要です。また、以下のSQLを動作させるには適切な権限が必要ですので、その辺りの注意も必要です。以下のSQLで'TEST01'はテーブル名ですので、適当に変えて下さい。

    SELECT  L.request_session_id AS SPID, 
            DB_NAME(L.resource_database_id) AS DatabaseName,
            O.Name AS LockedObjectName, 
            L.request_mode AS LockType,
            ES.login_name AS LoginName,
            ES.host_name AS HostName
    FROM    sys.dm_tran_locks L
            JOIN sys.partitions P ON P.object_id = L.resource_associated_entity_id
            JOIN sys.objects O ON O.object_id = P.object_id
            JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
            JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
    WHERE   resource_database_id = db_id() and L.resource_type = 'OBJECT'  and O.name = 'TEST01' and TST.is_user_transaction = 1
    ORDER BY L.request_session_id


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    • 編集済み trapemiya 2014年8月26日 2:28 追記
    2014年8月26日 2:27
  • 回答ありがとうございます。

    教えて頂いたSQLですが、実行すると対象テーブル内で行ロックを
    かけている情報が全て取得されてしまいます。
    今回は行ロックを行おうとしたデータが既にロック中(行ロック)であり、
    その行ロックをかけているユーザー名、コンピューター名を取得
    したいと考えております。

    説明不足で申し訳ございません。

    VB側でもロック元の情報が何かエラー時に返ってきていないかを
    調べていますが、なかなか見当たりません。。。


    ロックを管理するテーブルを作成するしかないのですかね。。。。

    2014年8月26日 5:06
  • %%lockers%%を使うと行レベルで取得できるようです。ただ、私の環境で試したところ、なぜかresource_descriptionが空で、うまく取得できませんでした。ここについては時間切れで調べ切れていません。すみません・・・
    %%lockers%%は非公式なようなので、その関係かもしれません・・・。

    Undocumented Virtual Column: %%lockres%
    http://www.scarydba.com/2010/03/18/undocumented-virtual-column-lockres/

    ところで、ADO.NETは楽観的ロックが基本となっており、通常は長い間ロックが発生しないのですが、そのような作りにできないのは何か理由があるのでしょうか?


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    • 回答としてマーク あだっち 2014年9月1日 5:04
    2014年8月26日 8:42
  • WHERE 句で絞込みを掛けたりすると取得できませんか?
    Lock の粒度や種類の関係ですかね・・・。
    sys.dm_tran_locks を FULL OUTER JOIN して実行した結果を見る限り、テーブル単位の Lock が掛かっていたりするときにそうなるように見受けられます。

    MCITP(Database Developer/Database Administrator)

    2014年8月27日 1:01
  • naginoさんからヒントをいただいて解決しました。私が試していたのは、isolation level をserializableにしており、それが原因でした。
    既定のread uncommittedにすれば、問題なく動きました。使用したSQLは、私が上で紹介したページの一番最後で紹介されていたものです。
    ただ、逆に言えば、serializableの場合は困りましたねってことですが・・・

    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    2014年8月27日 2:14
  • 皆様、回答ありがとうございます。
    下記SQL文で、行をロックしている端末とユーザーを取得するようにしました。
    トランザクション分離レベルは[REREAD COMMITTEDAD]としています。
    Test_01はテーブル名、Test_NO = '00001'はキーとなる列名と値になります。

    SELECT
      L.request_session_id AS SPID, 
      L.resource_type AS LockedResource, 
      L.request_mode AS LockType, 
      ES.login_name AS LoginName, 
      ES.host_name AS HostName, 
      TST.is_user_transaction
    FROM
      Test_01
    JOIN
      sys.dm_tran_locks L ON Test_01.%%lockres%% = resource_description
    JOIN
      sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
    JOIN
      sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
    WHERE
      TST.is_user_transaction = 1
    AND
      Test_NO = '00001'

    結果ご報告と御礼が遅れてしまい、申し訳ございませんでした。
    皆様の丁寧な回答により無事に解決できました。
    本当にありがとうございました。

    2014年9月1日 5:03