Introduzione
Nel panorama delle applicazioni moderne, in cui scalabilità e concorrenza rappresentano requisiti essenziali, garantire prestazioni elevate senza sacrificare la coerenza dei dati è una sfida sempre più complessa. SQL Server 2025 introduce diverse novità nell’Engine per migliorare performance e scalabilità. In questo articolo, dopo una panoramica iniziale sulle novità introdotte nel database Engine, ci concentreremo su Optimized Locking, una funzionalità dal nome magico che modifica il modo in cui le istruzioni DML acquisiscono i lock. Introdotta inizialmente in Azure SQL, ora è disponibile anche on-premises.
Overview sulle novità nell’Engine
La versione 2025 di SQL Server ha segnato diversi investimenti nell’Engine, la figura seguente (presentata a Build 2025) illustra le aree di innovazione nelle quali si sono concentrati gli investimenti.

Troviamo innovazioni per aumentare la concorrenza delle transazioni, migliorare le performance e la disponibilità dei servizi in termini HA e DR.
Interessanti le nuove opportunità di ottimizzazione del tempdb che permettono di:
- Configurare Resource Governor per impostare un limite alla quantità totale di spazio utilizzata da un’applicazione o da un carico di lavoro nel tempdb. Quando una query tenta di superare il limite, Resource Governor ne interrompe l’esecuzione, l’applicazione riceverà un errore specifico che indica chiaramente che è stato superato il limite di risorse dedicate al carico di lavoro. L’idea è quella di evitare interruzioni impedendo a carichi di lavoro incontrollati di consumare grandi quantità di spazio nel tempdb
- Accelerated Database Recovery può essere abilitato anche sul tempdb. Le transazioni che coinvolgono tabelle temporanee, variabili di tabella o tabelle non temporanee create nel tempdb possono essere influenzate negativamente da operazioni di rollback e da un utilizzo elevato del transaction log. L’attivazione di ADR nel tempdb permette il troncamento istantaneo del transaction log anche per le transazioni che interessano oggetti nel tempdb
- Abilitare la possibilità di avere il tempdb su tmpfs per SQL Server su Linux
SQL Server 2025 consolida la funzionalità nota con il nome Persisted Statistics for Readable Secondaries introdotta in SQL Server 2022 ma disabilitata by default e la sua attivazione richiedeva l’impostazione del trace flag 12606. Nell’ultima versione di SQL Server, Query Store for Readable Secondaries è attivo by default.
Change Tracking è stato migliorato in SQL Server 2025, il processo di pulizia automatica delle modifiche è stato arricchito di un nuovo approccio noto con il nome di adaptive shallow cleanup. Questo nuovo approccio consente di rimuovere i dati, raggiunto il periodo di conservazione, con passaggi incrementali fino a quando non vengono rimossi tutti i dati che hanno raggiunto il periodo di retention. Nell’ultima versione di SQL Server la pulizia superficiale adattiva è abilitata by default. Queste sono solo alcune delle innovazioni introdotte nell’Engine di SQL Server 2025, basti pensare che nelle tre aree di investimento Security, Performance e HADR si contano più di 40 nuove feature! Nel paragrafo seguente descriveremo i dettagli di Optimized Locking, la funzionalità dal nome magico che modifica il modo in cui le istruzioni DML acquisiscono i lock.
Optimized Locking
Optimized Locking è una funzionalità dell’Engine di SQL Server 2025, è stata implementata per ridurre l’utilizzo della memoria impiegata per la gestione dei lock, ridurre il fenomeno noto con il nome di lock escalation e aumentare la concorrenza dei carichi di lavoro. Prima di addentrarci nel funzionamento di Optimized Locking è necessario fare una panoramica sui modelli di concorrenza e sulle modalità di locking.
Concurrency models e lock mode
Il locking è un meccanismo che impedisce a più transazioni di modificare gli stessi dati contemporaneamente, garantendo così il rispetto delle proprietà ACID delle transazioni.
Quando una transazione deve di modificare i dati, l’Engine richiede l’acquisizione di un lock sugli stessi. Il lock viene concesso solo se non sono presenti altri lock incompatibili sul medesimo set di dati, permettendo così alla transazione di proseguire con l’operazione di modifica. Se un altro lock è già presente sugli stessi dati, la transazione deve attendere che il lock venga rilasciato prima di poter procedere. Il locking è uno dei meccanismi attraverso cui l’Engine garantisce l’isolamento delle transazioni in accordo con il livello di isolamento scelto.
L’Engine di SQL gestisce due modelli di concorrenza:
- Modello di concorrenza pessimistica
- Modello di concorrenza ottimistica
Nel modello di concorrenza pessimistico i lock posti in essere dalle transazioni di lettura possono bloccare le transazioni che cercano di acquisire lock di scrittura e viceversa. Nel modello ottimistico i lock posti in essere dalle transazioni di lettura non bloccano i lock in scrittura; i lock in scrittura non sono compatibili tra loro. Una specifica di ogni transazione è il livello di isolamento che definisce quanto la transazione è isolata dalle altre.
Per il modello di concorrenza pessimistica esistono quattro livelli di isolamento:
- Read Uncommitted
- Read Committed
- Repeatable Reads
- Serializable
Per il modello di concorrenza ottimistica esistono due livelli di isolamento:
- Snapshot
- Read Committed Snapshot
Non tutti sanno che Read Committed Snapshot non è un livello di isolamento separato e distinto dagli altri, ma una modifica del comportamento del livello di isolamento Read Committed quando l’opzione READ_COMMITTED_SNAPSHOT è abilitata.In accordo con il livello di isolamento scelto, l’Engine di SQL blocca le risorse utilizzando diverse modalità di lock, che determinano come tali risorse possono essere accedute (o non accedute) da transazioni concorrenti.
Tecnologie su cui si basa Optimized Locking
Optimized Locking si basa su due tecnologie presenti da tempo nell’Engine di SQL Server.
La prima, è nota con il nome di Accelerated Database Recovery e rappresenta un requisito necessario per l’attivazione di Optimized Locking. Accelerated Database Recovery deve essere abilitato a livello di database e velocizza il recovery delle transazioni migliorando i tempi di rollback e la gestione delle operazioni non confermate, non è abilitato by default sui database SQL Server on-premise. La seconda tecnologia è rappresentata dal livello di isolamento Read Committed Snapshot. Il livello di isolamento Read Committed Snapshot non è un requisito essenziale ma permette di sfruttare appieno Optimized Locking.
Il seguente frammento di codice T-SQL esplora lo stato delle attivazioni per Accelerated Database Recovery, Read Committed Snapshot e Optimized Locking sul database corrente.
-- Optimized locking builds on other database features:
-- 1. Accelerated database recovery (ADR)
-- 2. Read committed snapshot isolation level (RCSI) for the most benefit
SELECT
IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn')
,RCSI = is_read_committed_snapshot_on
,ADR = is_accelerated_database_recovery_on
FROM
sys.databases
WHERE
(name = DB_NAME());
Optimized Locking si basa su due meccanismi chiave
- Transaction ID (TID) locking
- Lock After Qualification (LAQ)
Transaction ID (TID) locking
Quando si utilizza il livello di isolamento Read Committed Snapshot, oppure quando Accelerated Database Recovery è abilitato, ogni riga nel database contiene internamente un Transaction ID (TID). Quando una transazione modifica una riga assegna a quella riga il proprio TID.
Con TID locking, invece di acquisire un lock sulla chiave della riga, l’Engine acquisisce il lock sul TID della riga. La transazione che modifica la riga detiene un lock di tipo exclusive (X) sul proprio TID. Altre transazioni che necessitano di modificare la stessa riga acquisiscono un lock di tipo shared (S) sullo stesso TID per attendere che la prima transazione sia completata.
Con TID locking, i lock sulle pagine e sulle righe continuano ad essere acquisiti durante le modifiche, ma ogni lock di pagina e di riga viene rilasciato immediatamente dopo la modifica, anche se la transazione non è stata confermata o respinta. L’unico lock mantenuto fino alla fine della transazione è il singolo lock di tipo exclusive (X) sulla risorsa TID, sostituendo così i molteplici lock su pagine e righe (chiavi).
Consideriamo di eseguire il seguente frammento di codice T-SQL su un database SQL Server 2025 in cui è attivo Optimized Locking. Viene creata la tabella di esempio dbo.SensorReadings con due colonne, la colonna SensorID di tipo integer rappresenta la chiave primaria e la colonna ReadingValue di tipo integer servirà per memorizzare alcuni valori.
DROP TABLE IF EXISTS dbo.SensorReadings;
CREATE TABLE dbo.SensorReadings
(
SensorID INTEGER PRIMARY KEY NOT NULL,
ReadingValue INTEGER NOT NULL
);
INSERT INTO dbo.SensorReadings VALUES (1, 10), (2, 20), (3, 30);
GO
Eseguiamo un aggiornamento dei valori memorizzati in ReadingValue monitorando i lock acquisiti attraverso la DMV sys.dm_tran_locks.
-- Inspect locks with sys.dm_tran_locks on updated rows
BEGIN TRANSACTION;
UPDATE
dbo.SensorReadings
SET
ReadingValue = ReadingValue + 10;
SELECT
*
FROM
sys.dm_tran_locks
WHERE
request_session_id = @@SPID
AND
resource_type IN ('PAGE', 'RID', 'KEY', 'XACT');
COMMIT TRANSACTION;
GO
L’unico lock che si osserva è quello acquisito sul TID come illustra la figura seguente.

La stessa operazione, eseguita sulla stessa tabella ma in un database con Optimized Locking disabilitato, evidenzia i lock di tipo exclusive (X) sulle righe e il lock di tipo intent exclusive (IX) sulla pagina che contiene le righe; questi lock rimarranno al loro posto fino al termine della transazione come illustra la figura seguente.

Lock After Qualification (LAQ)
Una delle principali cause di rallentamenti nelle operazioni DML è l’acquisizione di lock durante la ricerca delle righe che soddisfano le condizioni di una query (row qualification). Lock After Qualification in SQL Server 2025 modifica il modo in cui le istruzioni DML (UPDATE, DELETE, MERGE) acquisiscono i lock.
Quando Optimized Locking non è attivo, i predicati delle query vengono valutati riga per riga durante un’operazione di scan, viene acquisito un primo lock di tipo update (U) sulla riga che verrà successivamente trasformato in un lock di tipo exclusive (X) prima di aggiornare la riga, se il predicato è soddisfatto. Il lock di tipo exclusive (X) viene mantenuto fino alla fine della transazione.
Quando Optimized Locking è attivo, i predicati vengono valutati sulla versione più recente (committata) della riga senza acquisire alcun lock. Se il predicato non è soddisfatto, la scansione passa alla riga successiva. Se il predicato è soddisfatto, viene acquisito un lock di tipo exclusive (X) per aggiornare la riga; tale lock verrà rilasciato immediatamente dopo il completamento dell’aggiornamento della riga e prima della fine della transazione. Dal momento che la valutazione dei predicati avviene senza acquisire lock, query concorrenti che modificano righe diverse non si bloccano a vicenda. Consideriamo di eseguire il seguente frammento di codice T-SQL su un database SQL Server 2025 in cui è attivo Optimized Locking. Viene creata la tabella di esempio dbo.EntityCounters con due colonne, la colonna EntityID di tipo integer rappresenta la chiave primaria e la colonna CounterValue di tipo integer servirà per memorizzare alcuni valori.
DROP TABLE IF EXISTS dbo.EntityCounters;
CREATE TABLE dbo.EntityCounters
(
EntityID INTEGER NOT NULL,
CounterValue INTEGER NULL
);
INSERT INTO dbo.EntityCounters VALUES (1, 10), (2, 20), (3, 30);
GO
Consideriamo due query concorrenti, eseguite all’interno di due sessioni diverse, nell’ordine schematizzato nella tabella seguente.
Tempo | Sessione 1 | Sessione 2 |
t1 | BEGIN TRANSACTION; UPDATE dbo.EntityCounters SET CounterValue = CounterValue + 10 WHERE EntityID = 1; | |
t2 | BEGIN TRANSACTION; UPDATE dbo.EntityCounters SET CounterValue = CounterValue + 10 WHERE EntityID = 2; | |
t3 | SELECT resource_type ,resource_database_id ,resource_description ,request_mode ,request_type ,request_status ,request_session_id ,resource_associated_entity_id FROM sys.dm_tran_locks WHERE request_session_id IN (spid_sessione_1, spid_sessione_2) AND resource_type IN (‘PAGE’,’RID’,’KEY’,’XACT’); |
Quando Optimized Locking non è attivo, la query eseguita nella sessione 2 viene bloccata perché la query eseguita nella sessione 1 ha acquisito un lock di tipo update (U) sulla riga che la sessione 2 deve aggiornare.
Quando una query di UPDATE esegue una scansione su una tabella, acquisisce in un primo momento un lock di tipo update (U) sulla riga esaminata per garantire che nessun’altra transazione possa modificarla contemporaneamente. Se il predicato nella clausola WHERE è soddisfatto, il lock di tipo update (U) viene convertito in un lock di tipo exclusive (X) e la riga viene aggiornata. Se il predicato non è soddisfatto, il lock di tipo update (U) viene rilasciato senza modificare la riga, al termine della transazione. Questo comportamento causa blocchi perché se un’altra query (sessione 2) tenta di aggiornare la stessa riga, deve aspettare che la prima query (sessione 1) rilasci il lock di tipo update (U), anche se la prima query alla fine non modificherà la riga interessata dal lock di tipo update. La figura seguente illustra i lock acquisiti al tempo t3 quando Optimized Locking non è attivo.

Quando Optimized Locking è attivo, la query nella sessione 2 non viene bloccata perché i lock di tipo update (U) non vengono acquisiti. Inoltre, nella versione più recente e confermata della riga 1, la colonna EntityID è uguale a 1, il che non soddisfa il predicato della query nella sessione 2. La verifica del predicato avviene sulla versione più recente e confermata della riga.
La figura seguente illustra i lock acquisiti al tempo t3 quando Optimized Locking è attivo.

Conclusioni
Optimized locking in SQL Server 2025 rappresenta un’importante evoluzione nella gestione della concorrenza. Grazie a Transaction ID (TID) locking e Lock After Qualification (LAQ), Optimized Locking riduce il consumo della memoria necessaria per la gestione dei lock e riduce il fenomeno noto con il nome di lock escalation minimizzando i lock tra transazioni concorrenti.