Before moving with next blog. We should be aware of some information about Isolation level. As the trasaction is depends on isoalition level the level of isolation are as
1. Uncommitted Read (NoLock)
2. Committed Read (blocking)
3. Repeatable Read (phantom)
4. Snapshot (no blocking)
5. Serializable (no blocking)
more information about it here.
Now the more strong our isolation is more security we will get but it comes with performance, as more secure makes system more Locks and other transaction has to wait to successfully completion of first one.
sqlserver 2005 introduces Snapshot isolation level. And Versioning .
Here I will be discussing more on Committed Read – which is default transaction isolation level.
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.
