cause a LOCK in the database. Methods to View Locking Information

enrique_aeo used Ask the Experts™
hi experts, i have this query
      ,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'
                        WHEN TL1.resource_database_id = DB_ID()
                                    select OBJECT_NAME(object_id, TL1.resource_database_id)
                                    from sys.partitions
                                    where hobt_id = TL1.resource_associated_entity_id
                              '(Run under DB context)'
      END as ObjectName
      ,WT.wait_duration_ms as [Wait Duration (ms)]
                        (ER.statement_start_offset / 2) + 1,
                              WHEN -1
                                    THEN DATALENGTH(S.text)
                                    ELSE ER.statement_end_offset
                              END - ER.statement_start_offset) / 2) + 1)            
                  sys.dm_exec_requests ER
                        cross apply sys.dm_exec_sql_text(ER.sql_handle) S
                  TL1.request_session_id = ER.session_id
       ) as [Query]
      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'
      TL1.request_session_id <> @@SPID
order by

I bring a lock to see how it works this query
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
So are you trying to figure out how to create a lock?

Begin a transaction
select/insert/update/delete data

run your query above

rollback or commit transaction.

To view the locking information....

Use the below mentioned commands:
Select * from Master..SysProcesses where blocked > 0

Use the below mentioned commands to find the details abt SPID
DBCC InputBuffer(SPID)
Sp_who2 SPID

These are the basic commands to view the locking information
sp_who2 and sysprocesses where blocked > 0 will show you BLOCKING, not LOCKING.

The difference is that you can lock records, but if no one else is trying to use them then you have a lock.  As soon as someone tried to access those locked records (unless they are doing a dirty read) then you have a block.
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.


I is not seen records in my query, that means that I have created a lock on the database, what I did was
Begin transaction
select * from AdventureWorks.Sales.SalesOrderDetail
Run the query large
Did not show any


Hello again, I just want to generate a LOCK on my database to see which shows the query
Ok.  I gave an example of that already.


i need an example fro lock the database, then i executed the query above


hi experts, i need generate a lock on the database to see the results of the query

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial