locked
Locks on tables. RRS feed

  • Question

  • Hi All,

    One of customer asked me to look at Locks in database, and I informed them that there are SHARED and UPDATE locks with grant permission, Now customer came back again and asking for the TABLES on which these SHARED and UPDATE locks happen often.

    I did googled and got "sp_lock and sp_lock2,

    USE abcde ;
    GO

    SELECT * FROM sys.dm_tran_locks
      WHERE resource_database_id = DB_ID(), all these provide lot of information which makes me confused.

    So can you please let me how to find out exact tables on which this SHARED and UPDATE locks occur often?

    Thanks in advance,

    Wednesday, November 26, 2014 10:23 PM

Answers

  • Hi Shanky,

    please find below output of the query:

    resource_type DATABASE
    resource_database_id 46
    request_mode S
    request_type LOCK
    request_status GRANT
    request_reference_count 1
    request_session_id 124     

    And so on it gives lot of information, my requirement to find out the tables on which these SHARED and UPDATE locks are often occur.

    Please read this DMV sys.dm_tran_lock documentation carefully its well explained what each column means. Also at the end there are various queries you can use them.

    On a busy server lost of update and shared locks are taken what would you deduce from it. Its normal behavior how locking works


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, November 27, 2014 7:37 PM

All replies

  • Can you tell me what you found  confusing in the output I might be able to explain you better ?

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Wednesday, November 26, 2014 10:33 PM
  • Hi Venkatesh,

    You can use below TSQL to find locked table.

    select
      object_name(resource_associated_entity_id) as 'TableName' ,*
    from
      sys.dm_tran_locks
    where resource_type = 'OBJECT'
      and resource_database_id = DB_ID()
    GO

    Please Mark This As Answer if it solved your issue 
    Please Mark This As Helpful if it helps to solve your issue

    _____________________________________

    Thanks,

    Shashikant

    Thursday, November 27, 2014 5:15 AM
  • Hi Venkatesh,

    Please use below mentioned script find out lock:

    select  
        object_name(p.object_id) as TableName, 
        resource_type, resource_description
    from
        sys.dm_tran_locks l
        join sys.partitions p on l.resource_associated_entity_id = p.hobt_id


    Thanks

    Thursday, November 27, 2014 5:23 AM
  • Try below

    --Locks we have in the system
    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
    

    Thanks

    Saravana Kumar C


    Thursday, November 27, 2014 6:59 AM
    Answerer
  • See also TechNet Scriptcenter: List all Locks of the Current Database

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, November 27, 2014 7:31 AM
  • Hi Shanky,

    please find below output of the query:

    resource_type DATABASE
    resource_database_id 46
    request_mode S
    request_type LOCK
    request_status GRANT
    request_reference_count 1
    request_session_id 124     

    And so on it gives lot of information, my requirement to find out the tables on which these SHARED and UPDATE locks are often occur.

    Thursday, November 27, 2014 7:27 PM
  • Hi Shanky,

    please find below output of the query:

    resource_type DATABASE
    resource_database_id 46
    request_mode S
    request_type LOCK
    request_status GRANT
    request_reference_count 1
    request_session_id 124     

    And so on it gives lot of information, my requirement to find out the tables on which these SHARED and UPDATE locks are often occur.

    Please read this DMV sys.dm_tran_lock documentation carefully its well explained what each column means. Also at the end there are various queries you can use them.

    On a busy server lost of update and shared locks are taken what would you deduce from it. Its normal behavior how locking works


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, November 27, 2014 7:37 PM