Lock Info

Lock:

As we have discussed about introduction locks here(in our earlier blog). Here we will discuss how to trouble shoot locking. Locking is good to maintain the consistency of our data. Means when someone is update our data no one should access it until we successfully completes the update. For example :it’s a common example in bank account if we are withdraw amount from our account during the transaction no one should get the dirty data until our transaction completes successfully.

sys.dm_tran_locks

select

    request_session_id as spid,

    resource_type as rt, 

    resource_database_id as rdb,

    (case resource_type

      WHEN ‘OBJECT’ then object_name(resource_associated_entity_id)

      WHEN ‘DATABASE’ then ‘ ‘

      ELSE (select object_name(object_id)

            from sys.partitions

            where hobt_id=resource_associated_entity_id)

    END) as objname,

    resource_description as rd, 

    request_mode as rm,

    request_status as rs

from sys.dm_tran_locks

 

sp_lock

%%lockres%% -inline function

Lock is normal and it maintains the transaction consistency.

Generally we cannot do much on locks just to track are we getting lock escalation.

TraceFlag-1211: It disables lock escalation at the current threshold (5000)

TraceFlag-1224: This trace flag is similar to trace flag 1211 with one key difference. It enables lock escalation when lock manager acquires 40% of the statically allocated memory or (40%) non-AWE(32-bit)/regular(64-bit) dynamically allocated memory.

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/lock-escalation.aspx

as discussed earlier –Locks escalation is Row/Page to Table/Partition to Database.

About these ads
This entry was posted in DeadLock, Lock/Blocking, Performance Tuning and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s