Home > News > Breaking News > Optimized Locking in Azure SQL Database: Concorrenza senza limiti!

Optimized Locking in Azure SQL Database: Concorrenza senza limiti!

Nel panorama delle applicazioni moderne, dove scalabilità e concorrenza sono elementi fondamentali, mantenere prestazioni elevate senza compromettere la consistenza dei dati è una sfida costante. Azure SQL Database rappresenta spesso il punto di partenza per l’introduzione di nuove funzionalità destinate ad arricchire tutte le versioni di SQL Server. Seguendo questa tendenza, Microsoft ha annunciato una funzionalità dal nome magico: Optimized Locking!

Questa tecnologia ridefinisce il modo in cui Azure SQL Database gestisce i lock, migliorando sia la concorrenza che l’efficienza. In questo articolo esploreremo i due pilastri fondamentali di questa funzionalità:

  1. Transaction ID (TID) locking, progettato per ottimizzare l’utilizzo della memoria nella gestione dei lock
  2. Lock after qualification (LAQ), che elimina il rischio di lock escalation, favorendo la concorrenza nelle operazioni DML

Overview sul meccanismo di locking

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 optimized locking non è abilitato una transazione che deve modificare alcuni dati, richiede un lock sui dati stessi. Il lock viene concesso se non ci sono altri lock, incompatibili con il tipo di lock che si sta tentando di acquisire, sugli stessi dati. 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 di SQL Server, Azure SQL Database e Azure SQL Managed Instance garantisce l’isolamento delle transazioni in accordo con il livello di isolamento scelto.

Le fondamenta di optimized locking

Optimized locking si basa su due tecnologie presenti da tempo in Azure SQL Database.

La prima, nota con il nome di Accelerated Database Recovery (ADR), 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 delle operazioni di rollback. Sui database Azure SQL, accelerated database recovery è abilitato by default.

La seconda tecnologia è rappresentata dal livello di isolamento Read Committed Snapshot (RCSI). Il livello di isolamento read committed snapshot non è un requisito essenziale ma permette di sfruttare appieno optimized locking perché il componente lock after qualification è attivo solo con il livello di isolamento read committed snapshot. Lock after qualification sfrutta il meccanismo di versioning attivato da read committed snapshot per evitare di bloccare le righe durante il processo di qualificazione delle query, RCSI consente infatti di leggere dallo snapshot l’ultima versione valida delle righe qualificate, riducendo i conflitti tra letture e scritture. Sui database Azure SQL, RCSI è abilitato by default. Non tutti sanno che il livello di isolamento 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.

Il seguente frammento di codice T-SQL esplora lo stato delle attivazioni per le funzionalità accelerated database recovery, read committed snapshot e optimized locking.

-- 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());

Transaction ID (TID) locking

Quando si utilizza il livello di isolamento read committed snapshot, basato sul versioning delle righe, oppure quando accelerated database recovery è abilitato, ogni riga nel database contiene internamente un Transaction ID (TID). Questo TID viene memorizzato e ogni transazione che modifica una riga assegna a quella riga il proprio TID.

Con TID locking, invece di acquisire un lock sulla chiave della riga, viene acquisito un lock sul TID della riga. La transazione che modifica la riga detiene un lock di tipo X sul proprio TID. Altre transazioni che necessitano di modificare la stessa riga acquisiscono un lock di tipo 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 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 Azure SQL in cui è attivo optimized locking. Viene creata la tabella di esempio dbo.TableA con due colonne, la colonna ColumnA di tipo integer rappresenta la chiave primaria e la colonna ColumnB di tipo integer servirà per memorizzare alcuni valori.

DROP TABLE IF EXISTS dbo.TableA;

CREATE TABLE dbo.TableA
(
  ColumnA INTEGER PRIMARY KEY NOT NULL,
  ColumnB INTEGER NOT NULL
);

INSERT INTO dbo.TableA VALUES (1, 10),(2, 20),(3, 30);
GO

Eseguiamo un aggiornamento dei valori memorizzati in ColumnB 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.TableA
SET
  ColumnB = ColumnB + 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 all’interno di un database ospitato in una istanza SQL Server 2022 on-premise, evidenzia i lock di tipo X (exclusive) sulle righe e il lock di tipo IX (intent exclusive) 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 (qualification). Lock after qualification in Azure SQL Database 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 U (update) sulla riga che verrà successivamente trasformato in un lock di tipo X (exclusive) prima di aggiornare la riga, se il predicato è soddisfatto. Il lock di tipo X viene mantenuto fino alla fine della transazione.

Quando optimized locking è attivo e quando il livello di isolamento read committed snapshot è abilitato, 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 X (exclusive) 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 Azure SQL in cui è attivo optimized locking. Viene creata la tabella di esempio dbo.TableB con due colonne, la colonna ID di tipo integer rappresenta la chiave primaria e la colonna CounterValue di tipo integer servirà per memorizzare alcuni valori.

DROP TABLE IF EXISTS dbo.TableB;

CREATE TABLE dbo.TableB
(
  ID INTEGER NOT NULL,
  CounterValue INTEGER NULL
);

INSERT INTO dbo.TableB 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.

TempoSessione 1Sessione 2
t1BEGIN TRANSACTION;

UPDATE
  dbo.TableB
SET
  CounterValue = CounterValue + 10
WHERE
  ID = 1;
t2BEGIN TRANSACTION;

UPDATE
  dbo.TableB
SET
  CounterValue = CounterValue + 10
WHERE
  ID = 2;
t3SELECT
  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 U (update) sulla riga che la sessione 2 deve valutare. Quando una query di UPDATE esegue una scansione su una tabella, acquisisce in un primo momento un lock di tipo U (update) sulla riga esaminata per garantire che nessun’altra transazione possa modificarla contemporaneamente. Se il predicato della WHERE è soddisfatto, il lock di tipo U viene convertito in un lock di tipo X (exclusive) e la riga viene aggiornata. Se il predicato non è soddisfatto, il lock di tipo U viene rilasciato senza modificare la riga. 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 U, anche se la prima query alla fine non modificherà la riga. 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 U non vengono acquisiti. Inoltre, nella versione più recente e confermata della riga 1, la colonna ID è uguale a 1, il che non soddisfa il predicato della query nella sessione 2. La verifica del predicato avviene sulla versione più recente committata della riga. La figura seguente illustra i lock acquisiti al tempo t3 quando optimized locking è attivo.

Conclusioni

Optimized locking in Azure SQL Database rappresenta un’importante evoluzione nella gestione della concorrenza, riduce il numero di lock necessari e migliora le prestazioni delle operazioni DML (UPDATE, DELETE, MERGE). Grazie all’uso dei componenti Transaction ID (TID) locking e Lock After Qualification (LAQ), optimized locking riduce il consumo della memoria necessaria per la gestione dei lock ed elimina il fenomeno noto con il nome di lock escalation minimizzando i lock tra transazioni concorrenti.

Optimized locking si rivela particolarmente utile nei seguenti contesti:

  • Applicazioni con elevata concorrenza: sistemi che gestiscono un elevato numero di transazioni concorrenti come e-commerce, applicazioni bancarie, sistemi ERP, etc. Queste applicazioni possono trarre beneficio da una riduzione della contesa sui lock
  • Operazioni su tabelle di grandi dimensioni: tabelle con milioni di righe, dove le operazioni DML su larga scala rischiano di innescare il fenomeno noto con il nome di lock escalation
  • Carichi di lavoro con query ad-hoc: scenari in cui le query possono cambiare frequentemente, rendendo necessario un approccio flessibile alla gestione dei lock

Buon divertimento con Optimized Locking!

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Data Saturday Pordenone 2025: Appuntamento con l’innovazione!

Se sei appassionato di gestione dei dati? Non puoi perdere l’edizione 2025 del Data Saturday …

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

seventy five + = seventy six

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.