Overblog Folge diesem Blog
Edit post Administration Create my blog
Blog von Olaf Helper

Sperren und die betroffenen Objekte ermitteln

11. November 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Das bei DML Aktionen Sperren entstehen, weiß jeder. Wenn man sich in nicht das gerade einfache & trockene Thema einliest und seine Daten kennt, kann in etwa abschätzen, wann welche Sperren entstehen.

Im alten Aktivitätsmonitor oder mit der mittlerweile abgekündigten System Stored Procedure EXEC sp_lock kann man sich die Sperren anzeigen, allerdings ist die Auskunft nicht so informativ.

Über die DMV des Microsoft SQL Server 2005 kann man mehr Informationen ermitteln, wie z.B. auf welche Objekte Sperren liegen.

Interessant ist es dann auch mal mit den unterschiedlichen Isolation Levels oder Table-Hints zu experimentieren. Oder zu sehen, welche weitere Sperren durch ForeignKey Constraints oder History-Trigger entstehen. Zum Experimentieren startet man einfach eine explizite Transaktion, führt das DML Statement aus, wartet aber mit dem Commit. Dann kann man ich Ruhe die Sperren selektieren und committed erst dann. Das sollte aber tunlichst nur auf Test-Systemen machen, sonst gilt: Transaktionen so kurz wie nur irgend möglich.

Beispiel:

 

 USE [AdventureWorks2005] 
 GO 
   
 BEGIN TRANSACTION; 
   
 UPDATE Person.Person WITH (RowLock)  
 -- oder PagLock, TabLock, TabLockX 
 SET FirstName = RTRIM(FirstName) 
 WHERE BusinessEntityID = 1; 
   
 -- Mit dem Commit warten; 
 -- erst die Sperren selektieren 
 COMMIT TRANSACTION; 

 

Hier ein Beispiel für die Selektion der Sperren und deren Daten; wenn es interessiert, kann weitere Felder der Tabellen mit selektieren:

 

 -- Ermitteln aller Sperren einer Datenbank mit den 
 -- Daten der betroffenen Objekte für Sql Server 2005 
 SELECT DB.name AS DatabaseName 
  ,TL.request_owner_type AS RequestType 
  ,TL.request_status AS RequestStatus 
  ,TL.request_mode AS RequestMode  
  ,ES.session_id AS SPID 
  ,ES.login_name AS LoginName 
  ,ES.status as SessionStatus 
  ,TL.resource_type AS ResourceType 
  ,TL.resource_description AS ResourceDescription 
  ,COALESCE(SO.name, PO.name, DB.name) AS ObjectName 
  ,COALESCE(SO.type_desc, PO.type_desc, 'DATABASE') AS ObjectType 
  ,IX.name as IndexName 
  ,OWT.wait_type AS OsWaitType 
  ,OT.task_state AS OsTaskState 
  ,OT.pending_io_count AS OsTaskPendingIo 
 FROM sys.dm_tran_locks AS TL 
  INNER JOIN sys.databases AS DB 
  ON TL.resource_database_id = DB.database_id 
  INNER JOIN sys.dm_exec_sessions AS ES 
  ON TL.request_session_id = ES.session_id 
  LEFT JOIN sys.dm_os_waiting_tasks AS OWT 
   ON TL.lock_owner_address = OWT.resource_address 
  LEFT JOIN sys.dm_os_tasks AS OT 
  ON ES.session_id = OT.session_id 
  LEFT JOIN sys.objects AS SO 
  ON TL.resource_associated_entity_id = SO.object_id 
  AND TL.resource_type = 'OBJECT' 
  LEFT JOIN sys.partitions AS PT 
  ON TL.resource_associated_entity_id = PT.hobt_id 
  AND TL.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT') 
  LEFT JOIN sys.objects AS PO 
  ON PT.object_id = PO.object_id 
  LEFT JOIN sys.indexes AS IX 
  ON PT.index_id = IX.index_id 
  AND PT.object_id = IX.object_id 
 WHERE 1 = 1 
  -- Optimale Filter; einfach entkommentieren 
  --AND TL.resource_database_id = DB_ID() -- Nur aktuelle DB betrachten 
  --AND ES.status <> 'sleeping' -- Keine schlafende Prozesse 
  --AND SO.type_desc = 'USER_TABLE' -- Nur Sperren auf Tabelle 
  --AND SO.name = 'Persons' -- Nur zu einem Objekt 
  --AND TL.request_mode LIKE '%X'  -- Nur exklusive Locks 
 ORDER BY DB.name, ObjectName, ResourceType 
  ,IndexName, ES.session_id 

Diesen Post teilen

Repost 0

Kommentiere diesen Post