SQL Server escalation lock

L’SQL Server è un po’ bizzoso, a volte. Secondo necessità (e opportunità) può decidere di mettere un lock sul record, sulla pagina o persino sull’intera tabella. Questo meccanismo di escalation del lock, dal grano elementare del record all’intera database, a volte crea imbarazzanti situazioni di “conflitto”.

Il concetto è semplicemente: se mi conviene metto il lock sul record; se il numero di record è eccessivo allora passo a mettere dei lock sulle pagine di memoria, se il numero di pagine di memoria è troppo elevato allora sposto il lock sulla tabella, se infine anche il lock sulla tabella è troppo numeroso (qui non dovrebbe arrivarci nessuno…) allora metto il lock sull’intero database.

L’escalation è valutata normalmente sulla base dell’utilizzo della memoria indotti dalla persistenza dei lock: 5.000 lock fanno scattare il livello di lock superiore.

Nei datawarehouse, infatti, accade che si riscrivano significative porzione delle tabelle in tempi non proprio brevissimi, magari in transazioni che durano minuti o decine di minuti; in alcuni casi, per queste tabelle SQL server “preferisce” piazzare un bel lock di tabella perché secondo lui è più “conveniente”. Il concetto di convenienza è, ovviamente, conveniente per lui, non di certo per voi…

A necessità è possibile forzare SQL Server a non utilizzare l’escalation automatica dei lock alterando il comportamento standard su una tabella:

1
2
3
4

-- Controllling Lock Escalation
ALTER TABLE . SET (
LOCK_ESCALATION = AUTO -- o TABLE o DISABLE
);

I flag sono intuitivi: DISABLE significa mantenere i lock a livello di record e non scalare mai a pagina o tabella (vedi sotto le note di memoria), TABLE significa che il lock è sempre sull’intera tabella, AUTO che il lock è quello più conveniente (i famosi 5.000 lock).

Ovviamente c’è l’utilizzo della memoria: ogni lock costa 96 byte e la RAM non è infinita; se la RAM non è sufficiente per contenere i lock, anche se l’escalation è disabilitata, SQL Server scalerà al lock superiore.