• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 124
  • Last Modified:

10774: Viewing Locking Information

Hi experts:
--first session:
use AdventureWorks
GO
begin tran
  update HumanResources.Employee
  set ModifiedDate = GETDATE()

--second session:
use AdventureWorks
GO
select * from HumanResources.Employee

--3 er session
in this code I need to see the query that has an open transaction

select
      TL1.resource_type
      ,DB_NAME(TL1.resource_database_id) as [DB Name]
      ,CASE TL1.resource_type
            WHEN 'OBJECT' THEN OBJECT_NAME(TL1.resource_associated_entity_id, TL1.resource_database_id)
            WHEN 'DATABASE' THEN 'DB'
            ELSE
                  CASE
                        WHEN TL1.resource_database_id = DB_ID()
                        THEN
                              (
                                    select OBJECT_NAME(object_id, TL1.resource_database_id)
                                    from sys.partitions
                                    where hobt_id = TL1.resource_associated_entity_id
                              )
                        ELSE
                              '(Run under DB context)'
                  END
      END as ObjectName
      ,TL1.resource_description
      ,TL1.request_session_id
      ,TL1.request_mode
      ,TL1.request_status
      ,WT.wait_duration_ms as [Wait Duration (ms)]
      ,(
            select
                  SUBSTRING(
                        S.Text,
                        (ER.statement_start_offset / 2) + 1,
                        ((
                              CASE
                                    ER.statement_end_offset
                              WHEN -1
                                    THEN DATALENGTH(S.text)
                                    ELSE ER.statement_end_offset
                              END - ER.statement_start_offset) / 2) + 1)            
            from
                  sys.dm_exec_requests ER
                        cross apply sys.dm_exec_sql_text(ER.sql_handle) S
            where
                  TL1.request_session_id = ER.session_id
       ) as [Query]
from
      sys.dm_tran_locks as TL1 left outer join sys.dm_os_waiting_tasks WT on
            TL1.lock_owner_address = WT.resource_address and TL1.request_status = 'WAIT'
where
      TL1.request_session_id <> @@SPID
order by       TL1.request_session_id desc
0
enrique_aeo
Asked:
enrique_aeo
  • 13
  • 11
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try to move the TL1.request_status = 'WAIT' to the WHERE clause:
select
       TL1.resource_type
       ,DB_NAME(TL1.resource_database_id) as [DB Name]
       ,CASE TL1.resource_type
             WHEN 'OBJECT' THEN OBJECT_NAME(TL1.resource_associated_entity_id, TL1.resource_database_id)
             WHEN 'DATABASE' THEN 'DB'
             ELSE
                   CASE 
                         WHEN TL1.resource_database_id = DB_ID() 
                         THEN
                               (
                                     select OBJECT_NAME(object_id, TL1.resource_database_id)
                                     from sys.partitions
                                     where hobt_id = TL1.resource_associated_entity_id
                               )
                         ELSE
                               '(Run under DB context)'
                   END
       END as ObjectName
       ,TL1.resource_description
       ,TL1.request_session_id
       ,TL1.request_mode
       ,TL1.request_status
       ,WT.wait_duration_ms as [Wait Duration (ms)]
       ,(
             select
                   SUBSTRING(
                         S.Text, 
                         (ER.statement_start_offset / 2) + 1,
                         ((
                               CASE 
                                     ER.statement_end_offset
                               WHEN -1 
                                     THEN DATALENGTH(S.text)
                                     ELSE ER.statement_end_offset
                               END - ER.statement_start_offset) / 2) + 1)            
             from 
                   sys.dm_exec_requests ER 
                         cross apply sys.dm_exec_sql_text(ER.sql_handle) S
             where
                   TL1.request_session_id = ER.session_id
        ) as [Query]
 from
       sys.dm_tran_locks as TL1 
       left outer join sys.dm_os_waiting_tasks WT 
		on TL1.lock_owner_address = WT.resource_address 
 where
       TL1.request_session_id <> @@SPID
       and TL1.request_status = 'WAIT'
 order by       TL1.request_session_id desc 

Open in new window

0
 
enrique_aeoAuthor Commented:
i need see
begin tran
  update HumanResources.Employee
  set ModifiedDate = GETDATE()

because it has an open transaction
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I didn't add those first steps to the code but here they are:
--first session: 
 use AdventureWorks
 GO
 begin tran
   update HumanResources.Employee 
   set ModifiedDate = GETDATE()

 --second session:
 use AdventureWorks
 GO
 select * from HumanResources.Employee

 --3 er session
 in this code I need to see the query that has an open transaction

select
       TL1.resource_type
       ,DB_NAME(TL1.resource_database_id) as [DB Name]
       ,CASE TL1.resource_type
             WHEN 'OBJECT' THEN OBJECT_NAME(TL1.resource_associated_entity_id, TL1.resource_database_id)
             WHEN 'DATABASE' THEN 'DB'
             ELSE
                   CASE 
                         WHEN TL1.resource_database_id = DB_ID() 
                         THEN
                               (
                                     select OBJECT_NAME(object_id, TL1.resource_database_id)
                                     from sys.partitions
                                     where hobt_id = TL1.resource_associated_entity_id
                               )
                         ELSE
                               '(Run under DB context)'
                   END
       END as ObjectName
       ,TL1.resource_description
       ,TL1.request_session_id
       ,TL1.request_mode
       ,TL1.request_status
       ,WT.wait_duration_ms as [Wait Duration (ms)]
       ,(
             select
                   SUBSTRING(
                         S.Text, 
                         (ER.statement_start_offset / 2) + 1,
                         ((
                               CASE 
                                     ER.statement_end_offset
                               WHEN -1 
                                     THEN DATALENGTH(S.text)
                                     ELSE ER.statement_end_offset
                               END - ER.statement_start_offset) / 2) + 1)            
             from 
                   sys.dm_exec_requests ER 
                         cross apply sys.dm_exec_sql_text(ER.sql_handle) S
             where
                   TL1.request_session_id = ER.session_id
        ) as [Query]
 from
       sys.dm_tran_locks as TL1 
       left outer join sys.dm_os_waiting_tasks WT 
		on TL1.lock_owner_address = WT.resource_address 
 where
       TL1.request_session_id <> @@SPID
       and TL1.request_status = 'WAIT'
 order by       TL1.request_session_id desc 

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
enrique_aeoAuthor Commented:
i can not see
resource_type      DB Name      ObjectName      resource_description      request_session_id      request_mode      request_status      Wait Duration (ms)      Query
KEY      AdventureWorks      Employee      (8194443284a0)                                                                                                                                                                                                                                                        54      S      WAIT      NULL      select * from HumanResources.Employee
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What you want it's the opposite of the query you posted. What you posted it's to capture the blocked queries but you want to capture the blocking query. You can see how to do it here.
Adapting it to your needs:
--first session: 
 use AdventureWorks
 GO
 begin tran
   update HumanResources.Employee 
   set ModifiedDate = GETDATE()

 --second session:
 use AdventureWorks
 GO
 select * from HumanResources.Employee

 --3 er session
 in this code I need to see the query that has an open transaction

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

Open in new window

0
 
enrique_aeoAuthor Commented:
no show rows
no-show-rows.png
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to run first these 2 steps:
--first session: 
 use AdventureWorks
 GO
 begin tran
   update HumanResources.Employee 
   set ModifiedDate = GETDATE()

 --second session:
 use AdventureWorks
 GO
 select * from HumanResources.Employee

Open in new window

0
 
enrique_aeoAuthor Commented:
it is running, and go 9 minutes
execute.png
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
And won't stop because it's locked by the first session.
Now you can run the query in the 3rd session to see the results.
0
 
enrique_aeoAuthor Commented:
--first session:
 use AdventureWorks
executing...

 --second session:
executing...

--3er session
this query, no show rows
SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Are you running all in the same query window? Each session should be running in a separate windows.
0
 
enrique_aeoAuthor Commented:
Each session  running in a separate windows.
0
 
enrique_aeoAuthor Commented:
this query show information
select
      TL1.resource_type
      ,DB_NAME(TL1.resource_database_id) as [DB Name]
      ,CASE TL1.resource_type
            WHEN 'OBJECT' THEN OBJECT_NAME(TL1.resource_associated_entity_id, TL1.resource_database_id)
            WHEN 'DATABASE' THEN 'DB'
            ELSE
                  CASE
                        WHEN TL1.resource_database_id = DB_ID()
                        THEN
                              (
                                    select OBJECT_NAME(object_id, TL1.resource_database_id)
                                    from sys.partitions
                                    where hobt_id = TL1.resource_associated_entity_id
                              )
                        ELSE
                              '(Run under DB context)'
                  END
      END as ObjectName
      ,TL1.resource_description
      ,TL1.request_session_id
      ,TL1.request_mode
      ,TL1.request_status
      ,WT.wait_duration_ms as [Wait Duration (ms)]
      ,(
            select
                  SUBSTRING(
                        S.Text,
                        (ER.statement_start_offset / 2) + 1,
                        ((
                              CASE
                                    ER.statement_end_offset
                              WHEN -1
                                    THEN DATALENGTH(S.text)
                                    ELSE ER.statement_end_offset
                              END - ER.statement_start_offset) / 2) + 1)            
            from
                  sys.dm_exec_requests ER
                        cross apply sys.dm_exec_sql_text(ER.sql_handle) S
            where
                  TL1.request_session_id = ER.session_id
       ) as [Query]
from
      sys.dm_tran_locks as TL1 left outer join sys.dm_os_waiting_tasks WT on
            TL1.lock_owner_address = WT.resource_address and TL1.request_status = 'WAIT'
where
      TL1.request_session_id <> @@SPID
order by       TL1.request_session_id desc
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Always did but you told that wasn't the information that you wanted.
i can not see
 resource_type      DB Name      ObjectName      resource_description      request_session_id      request_mode      request_status      Wait Duration (ms)      Query
 KEY      AdventureWorks      Employee      (8194443284a0)                                                                                                                                                                                                                                                        54      S      WAIT      NULL      select * from HumanResources.Employee
0
 
enrique_aeoAuthor Commented:
Victor is what I want to see the T-SQL statement that has not confirmed the transaction, ie:
use AdventureWorks
GO
begin tran
   update HumanResources.Employee
   September ModifiedDate = GETDATE ()

As we proceed in these cases? ie identify the query that does not show information and then kill your session, so we proceed?

What is the solution for these cases?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
First you need to try to understand why is blocking. In your example it's blocking because there's an open transaction that it's expecting for a commit or rollback instruction.
Of course if you kill the transaction will release the lock and your problem will also be resolved.
0
 
enrique_aeoAuthor Commented:
is why we need to see the T-SQL
use AdventureWorks
GO
begin tran
    update HumanResources.Employee
    September ModifiedDate = GETDATE ()

to kill

but, in the query i can not see, only view select * from HumanResources.Employee
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It's strange that the query from this article didn't work for you. It worked well for me.
Can you try it once more?
0
 
enrique_aeoAuthor Commented:
it is not working, see attachment
first-session.png
second-session.png
three-session.png
0
 
enrique_aeoAuthor Commented:
i am using this version
Microsoft SQL Server 2012 - 11.0.2218.0 (Intel X86)
      Jun 12 2012 13:16:18
      Copyright (c) Microsoft Corporation
      Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
0
 
enrique_aeoAuthor Commented:
Please support, already have all the information
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry Enrique but I can't figure out why doesn't work for you.
Please check my example where you can find that it's working for me.
Session1.JPG
Session2.JPG
Session3.JPG
0
 
enrique_aeoAuthor Commented:
this query show rows
SELECT *
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id --

this query it is not show rows
SELECT *
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id --
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
But the rows that it shows aren't the ones you want, right?
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 13
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now