Fortunatamente mi è capitato abbastanza di rado che una stored procedure o una query rimanga apparentemente bloccata causando rallentamenti e deadlock: quando succede però di solito è panico anche perché il primo problema che ci si trova a dover affrontare è “e adesso come capisco cosa mi sta bloccando?”
È quindi essenziale sapere come identificare le risorse bloccate, che possono essere tabelle, pagine o righe.
Il metodo più diretto per identificare i lock attivi passa tramite le Dynamic Management Views (DMV). Le più utilizzate sono:
sys.dm_tran_locks: mostra tutti i lock attualmente in esseresys.dm_exec_requests: evidenzia sessioni attive e eventuali blocchi in corsosys.dm_exec_sessions: offre dettagli sulle sessioni SQL attive
Per visualizzare tutte le risorse bloccate può tornare utile una query come questa:
SELECT
/*Tipo di risorsa su cui il lock è applicato. OBJECT (tabella o vista), PAGE (pagina di dati), KEY (chiave indice), RID (riga) ecc.*/
tl.resource_type,
/* ID del database dove si trova la risorsa lockata. Utile per filtrare lock su uno specifico database. */tl.resource_database_id,/*Nome del database */DB_NAME(tl.resource_database_id) AS database_name,/* Identificativo associato alla risorsa. Ad esempio, se resource_type = OBJECT, qui troviamo l’object_id della tabella coinvolta; se PAGE, viene indicato l’ID di quella pagina. */tl.resource_associated_entity_id AS object_id,/* Se RESOURCE_NAME = 'OBJECT' è il nome dell’oggetto (tabella o vista) associato al lock */CASE WHEN tl.resource_type = 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id) ELSE NULL END AS object_name,/* Tipo di lock richiesto o detenuto, ad esempio: S (shared), X (exclusive), IX (intent exclusive), IS (intent shared), ecc. */tl.request_mode AS lock_type,/* Stato della richiesta di lock: può essere GRANTED (lock attivo) o WAIT (lock in attesa).*/tl.request_status,/* ID della sessione SQL che detiene o richiede il lock.*/tl.request_session_id,/* Nome del computer client da cui è partita la sessione.*/es.host_name,/* Nome del programma o applicativo che ha aperto la connessione */es.program_name,/* Se il lock sta bloccando o è bloccato da un'altra sessione, qui troviamo l’ID della sessione che causa il blocco. */er.blocking_session_id
FROM sys.dm_tran_locks AS tl
LEFT JOIN sys.dm_exec_sessions AS es ON tl.request_session_id = es.session_id
LEFT JOIN sys.dm_exec_requests AS er ON tl.request_session_id = er.session_id
WHERE tl.resource_database_id = DB_ID()
ORDER BY tl.request_session_id, tl.resource_type
Che produrrà un output simile a questo:
Ho descritto brevemente le colonne nello script; qui aggiungo che le colonne più rilevanti ai nostri fini sono lock_type, request_session_id e blocking_session_id.
Nello specifico le risorse che presentano un valore NOT NULL nella colonna blocking_session_id vengono bloccate dalle risorse che hanno quel valore nella colonna request_session_id e in particolare quelle che hanno lock_type = 'X'.
A questo punto per sbloccare le risorse, si può usare il comando KILL (qualcuno ha detto Unix?); quindi ad esempio nel nostro caso:
KILL 63
Alcune precisazioni però sono necessarie.
Innanzitutto questa pratica deve costituire l’eccezionalità. Nella prassi normale questo non dovrebbe mai essere necessario; per evitarlo innanzitutto verificare se nel nostro codice SQL (penso a stored procedure, funzione e trigger) non abbiamo lasciato transazioni aperte.
Una buona pratica potrebbe anche essere quella di limitare il tempo lasciato alle query prima di andare in timeout; questo può essere fatto a livello di impostazioni di Sql Server Management Studio oppure nelle impostazioni delle connessioni nei programmi .NET oppure nelle query tramite il comando
SET LOCK_TIMEOUT 5000 -- in millisecondi