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

enrique_aeo
enrique_aeo used Ask the Experts™
on
hi experts, i have this query
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

I bring a lock to see how it works this query
Comment
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.

Commented:
To view the locking information....

Use the below mentioned commands:
SP_Who2
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.

Author

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

Author

Commented:
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.

Author

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

Author

Commented:
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