On Monday, the first of February, 2010, I will be speaking at the Baltimore SQL Server User Group meeting at 7:00 PM EST. My topic will be SQL Server Locking and Blocking Made Simple. In it, I will discuss:
- The need for locking
- The consequences of blocking
- How SQL Server locks resources
- How blocking affects performance
- Influencing locks with granularity hints
- Setting Transaction Isolation Levels
Here is a sample slide from the presentation. You can download the entire presentation here.
During the presentation, I will regularly use the following script to view the locks being held by SQL Server.
–examine the resources
WHEN resource_type = ‘OBJECT’ THEN object_name(resource_associated_entity_id)
WHEN resource_type IN (‘DATABASE’, ‘FILE’, ‘METADATA’) THEN ‘N/A’
WHEN resource_type IN (‘KEY’, ‘PAGE’, ‘RID’) THEN (
END) AS resource_name
,request_mode as lock_type
,request_owner_id AS transaction_id
resource_type <> ‘DATABASE’;
I hope you’ll join me there.