Solved

10774: Viewing Locking Information

Posted on 2014-10-12
24
107 Views
Last Modified: 2014-10-23
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
Comment
Question by:enrique_aeo
  • 13
  • 11
24 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40376535
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
 

Author Comment

by:enrique_aeo
ID: 40376696
i need see
begin tran
  update HumanResources.Employee
  set ModifiedDate = GETDATE()

because it has an open transaction
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40376770
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
 

Author Comment

by:enrique_aeo
ID: 40376979
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40377204
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
 

Author Comment

by:enrique_aeo
ID: 40377223
no show rows
no-show-rows.png
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40377242
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
 

Author Comment

by:enrique_aeo
ID: 40377248
it is running, and go 9 minutes
execute.png
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40377257
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
 

Author Comment

by:enrique_aeo
ID: 40377281
--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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40377289
Are you running all in the same query window? Each session should be running in a separate windows.
0
 

Author Comment

by:enrique_aeo
ID: 40377299
Each session  running in a separate windows.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:enrique_aeo
ID: 40377725
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40379250
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
 

Author Comment

by:enrique_aeo
ID: 40380108
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40380147
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
 

Author Comment

by:enrique_aeo
ID: 40380189
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40380228
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
 

Author Comment

by:enrique_aeo
ID: 40380341
it is not working, see attachment
first-session.png
second-session.png
three-session.png
0
 

Author Comment

by:enrique_aeo
ID: 40381086
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
 

Author Comment

by:enrique_aeo
ID: 40382370
Please support, already have all the information
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40383777
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
 

Author Comment

by:enrique_aeo
ID: 40387344
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40388657
But the rows that it shows aren't the ones you want, right?
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now