Simon Learning SQL Server

Simon Learning SQL Server

In un post precedente ho parlato dei livelli di blocco, blocco e isolamento. In questo post entrerò più in dettaglio sui blocchi dell’intervallo di chiavi utilizzati nel livello di isolamento SERIALIZZABILE e dimostrerò anche di utilizzare sys. dm_tran_locks DMV per visualizzare i blocchi esistenti e anche come capire su quali risorse sono posizionati i blocchi.

I blocchi dell’intervallo di tasti vengono utilizzati solo nel livello di isolamento SERIALIZZABILE e il loro scopo è quello di interrompere le letture fantasma che sono possibili negli altri tre livelli di isolamento della concorrenza pessimistica, ovvero read uncommitted, read committed e repeatable read. Una lettura fantasma è dove una transazione esegue una lettura su un intervallo di dati, ad esempio utilizzando una query con una clausola WHERE e un’altra transazione aggiunge dati che soddisferanno le condizioni della clausola WHERE. Se la prima transazione esegue la stessa lettura, ora restituirà i nuovi dati con conseguente visualizzazione di dati fantasma nella seconda lettura all’interno della stessa transazione. I blocchi dell’intervallo di chiavi vengono utilizzati per impedire l’aggiunta di dati fantasma e vengono posizionati sulle chiavi dell’indice utilizzato per recuperare i dati. Il seguente esempio utilizzando la persona.La tabella Persona nel database AdventureWorks2012 mostrerà come vengono utilizzati questi blocchi dell’intervallo di chiavi.

La Persona.La tabella Person ha un indice non cluster IX_Person_LastName_FirstName_MiddleName con la colonna LastName come prima colonna nella chiave indice. Ciò significa che qualsiasi query in cui stiamo filtrando su LastName dovrebbe utilizzare questo indice per recuperare i dati.

Se eseguiamo una query per restituire alcune righe da Person.Persona che filtra su un intervallo di cognomi, allora dovremmo vedere questo indice utilizzato. Ad esempio

SELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'ORDER BY LastName

Restituisce il seguente set di risultati.

 Gamma chiave 2

E guardando il piano di esecuzione possiamo vedere che questo indice è usato.

 Gamma chiave 3

Ora, se impostiamo il livello di isolamento su SERIALIZABLE, apriamo una transazione ed eseguiamo la stessa istruzione select, dovremmo essere in grado di vedere quali blocchi vengono trattenuti.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

Ora da un’altra connessione possiamo interrogare il DMV sys.dm_tran_locks per vedere i blocchi correnti in corso. Siamo interessati solo ai blocchi sulle risorse CHIAVE, quindi filtriamo di conseguenza.

SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

Gamma chiave 4

Otteniamo sette righe risintonizzate con due diversi valori request_mode. Il request_mode ” S ” corrisponde ai blocchi condivisi posizionati sulle tre righe restituite. Poiché stiamo utilizzando il livello di isolamento SERIALIZZABILE, questi blocchi condivisi vengono mantenuti fino a quando la transazione non esegue il commit o il rollback per impedire letture non ripetibili. Il request_mode “RangeS-S” corrisponde ai blocchi dell’intervallo di chiavi che sono stati concessi. Ma perché ci sono quattro file? Per rispondere a questo possiamo usare i dati restituiti da sys. dm_tran_locks e usarlo per capire quali risorse sono state effettivamente bloccate.

Le colonne che ci interessano sono resource_associated_entity_id e resource_description. I dati in queste colonne possono variare in base al resource_type e per maggiori dettagli su questo vedere TechNet. Per le risorse CHIAVE, resource_associated_entity_id corrisponde a partition_id da sys.partizioni

SELECT *FROM sys.partitionsWHERE partition_id in (72057594057523200, 72057594045595648)

Gamma chiave 5

L’object_id restituito corrisponde alla Persona.Tabella persona

SELECT object_name(1765581328)

Gamma chiave 6

Possiamo vedere che i due diversi valori partition_id corrispondono a diversi indici su Person.Persona

SELECT * FROM sys.indexes WHERE object_id = 1765581328

Gamma chiave 7

Il sistema.la vista partizioni ci dice che le partizioni su cui abbiamo i blocchi sono per gli indici PK_Person_BusinessEntityID e IX_Person_LastName_FirstName_MiddleName.

Possiamo combinare tutto questo in un’unica query per chiarezza.

 SELECT L.resource_type ,L.resource_description ,L.resource_associated_entity_id ,L.request_mode ,L.request_type ,L.request_status ,OBJECT_NAME(I.object_id) AS TableName ,I.object_id ,I.index_id ,I.name AS IndexName ,I.type_desc FROM sys.dm_tran_locks L INNER JOIN sys.partitions P ON P.partition_id = L.resource_associated_entity_id INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id WHERE L.resource_type = 'KEY' ORDER BY L.request_mode

Gamma chiave 8

Questo ci mostra che tre blocchi condivisi sono posizionati sulle righe stesse (il livello foglia dell’indice cluster) e che quattro blocchi dell’intervallo di chiavi sono posizionati sulle chiavi all’interno dell’indice non cluster. Ma questo non ha risposto alla domanda sul perché abbiamo quattro serrature?

Prima di farlo, dimostrerò due modi per dimostrare che i tre blocchi condivisi sono posizionati sulle righe restituite dalla query originale e quindi utilizzarli per capire su quali risorse sono posizionati i quattro blocchi “INTERVALLI-S”.

In primo luogo useremo i valori %%physloc%% e %%lockres%% insieme al sys.Funzione fn_PhysLocFormatter () per dirci il file, la pagina e lo slot delle righe restituite dalla query originale. In secondo luogo usiamo queste informazioni nel comando DBCC PAGE.

Abbiamo solo bisogno di aggiornare la nostra query originale come segue

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS FilePageSlot, %%lockres%% AS LockResource, *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

Gamma chiave 9

Ora possiamo vedere che la riga per Dennis Ware si trova fisicamente sul file di dati 1, a pagina 2806 e allo slot 3. Possiamo anche vedere che la sua risorsa di blocco è (05c585e839d4).

Se guardiamo indietro ai dati restituiti da sys.dm_tran_locks possiamo vedere che uno dei blocchi condivisi ha una resource_description di (05c585e839d4) quindi questo ci dice che quel blocco è posizionato su quella riga nell’indice cluster.

Ora possiamo eseguire il seguente comando DBCC PAGE per visualizzare effettivamente le informazioni su ciò che è sulla pagina, ma affinché ciò restituisca qualsiasi risultato è necessario attivare trace flag 3604.

DBCC TRACEON (3604);DBCC PAGE (10, 1, 2806, 3); -- DB_ID, FileId, PageId, Format

Qui sono visualizzate molte informazioni testuali ed è possibile specificare un numero diverso nel parametro format per restituire diverse quantità di dati. In questo caso il primo bit è l’intestazione della pagina e successivamente abbiamo lo stato di allocazione. Vedrete il primo slot utilizzato è Slot 0. C’è un valore di offset che ci dice dove nella pagina questo slot inizia e un valori di lunghezza che ci dice quanto tempo il record è.

 Gamma chiave 10

Se scorriamo verso il basso fino a trovare Slot 3, e poi scorrere oltre il blocco di dati possiamo vedere i dati fisici per ogni colonna e può confermare questa è la riga per Dennis Ware.

 Gamma chiave 11

Ora, se torniamo alla fine dello slot 3 possiamo vedere

KeyHashValue = (05c585e839d4)

Questo corrisponde alla resource_description in sys.dm_tran_locks e al valore restituito da %%lockres%%.

Ma come facciamo qualcosa di simile per i blocchi della gamma di chiavi? Come facciamo a scoprire quali risorse stanno bloccando? Un modo sarebbe usare sys. dm_db_database_page_allocations per capire quali pagine appartengono all’indice non cluster. Passiamo nell’indice database_id, object_id e index_id dell’indice IX_Person_LastName_FirstName_MiddleName e restituirà una riga per ogni pagina utilizzata all’interno di quell’indice. Possiamo quindi eseguire la PAGINA DBCC con i valori allocated_page_page_id forniti e trovare la pagina che contiene Dennis Ware.

SELECT allocated_page_page_id, *FROM sys.dm_db_database_page_allocations (DB_ID(N'AdventureWorks2012'), object_Id('AdventureWorks2012.Person.Person'), 4, null, 'DETAILED')WHERE is_allocated = 1

Possiamo quindi eseguire DBCC PAGE con il valore fornito e trovare la pagina che contiene Dennis Ware.

DBCC PAGE (10, 1, 24745, 3); -- DB_ID, FileId, PageId, Format

La cosa utile è che in questo caso DBCC PAGE restituisce i dati nella pagina in un set di risultati in modo da poter trovare abbastanza rapidamente la pagina corretta, in questo caso pagina 24745.

 Gamma chiave 12

Ora se usiamo i valori resource_description per i nostri quattro blocchi chiave in sys.dm_tran_locks possiamo vedere che i blocchi “RangeS-S” sono stati effettivamente concessi alle righe che contengono dati per Dennis Ware, Victoria Ware, Clay Warten e Aaron Washington. Ma Aaron Washington non è incluso nel set di risultati, quindi perché è presente questa quarta serratura a chiave? È perché quando un intervallo di chiavi viene preso su una chiave, impedirà che tutti i dati vengano aggiunti immediatamente prima di una chiave con un blocco dell’intervallo di chiavi. In altre parole, nessun dato può essere aggiunto tra, ad esempio, Victoria Ware e Clay Warthen, ad esempio Jennifer Warner.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (888888, 'VC', 'Jennifer', 'Warner')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

Gamma chiave 13

Qui possiamo vedere il blocco della gamma di chiavi in attesa.

Significa anche che non è possibile aggiungere dati tra Timothy Ward e Aaron Washington e significa che i blocchi dell’intervallo di chiavi bloccano effettivamente più dati di quelli che potrebbero soddisfare la query nella transazione aperta. Ad esempio, non abbiamo potuto inserire una riga per Simon Wartz che sarebbe stata messa tra Clay Warthen e Aaron Washington, anche se non sarebbe stata restituita nella query originale nella transazione aperta.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (999999, 'VC', 'Simon', 'Wartz')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

Gamma chiave 14

Ora sappiamo come vengono utilizzati i blocchi dell’intervallo di chiavi possiamo capire quali risorse hanno i blocchi dell’intervallo di chiavi su di essi senza la necessità di cercare tra le pagine dell’indice non cluster. Possiamo semplicemente includere un suggerimento di tabella per restituire parte dei dati di cui abbiamo bisogno.

SELECT sys.fn_PhysLocFormatter(%%physloc%%), %%lockres%%, *FROM Person.Person WITH (INDEX = IX_Person_LastName_FirstName_MiddleName)WHERE LastName BETWEEN 'Ware' AND 'Warthen'

Gamma chiave 18

Come puoi vedere questo ci dà gli stessi dati di file, pagina e slot e la stessa resource_description per le tre righe restituite dalla query. Ora sappiamo che il quarto blocco “RANGE-S” è posizionato sulla chiave nell’indice immediatamente dopo la chiave per Clay Warthen, ad es. slot 140. Ancora una volta possiamo usare la PAGINA DBCC per visualizzare i dati a pagina 25065.

Ora nella dimostrazione che ho appena eseguito ci capita di avere un indice non cluster con la prima colonna nella chiave di clustering che è quella che stiamo usando nella nostra clausola WHERE. Ma cosa succede se rimuoviamo l’indice non cluster? O in altre parole, cosa succede se eseguiamo una query su un altro filtro di tabella su una colonna non inclusa in una chiave di indice? Per dimostrare facilmente questo possiamo semplicemente rimuovere l’indice non cluster.

DROP INDEX ON .BEGIN TRANSELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

Otteniamo le stesse righe come previsto, ma questa volta SQL Server ha dovuto utilizzare l’indice cluster per ottenere i dati.

 Gamma chiave 15

Allora, che serrature abbiamo? Questa volta non esiste un blocco dell’intervallo di tasti che può essere applicato, quindi un blocco condiviso deve essere posizionato sull’intera tabella.

SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'ORDER BY request_modeSELECT object_name(1765581328)

Gamma chiave 16

Poiché questo blocco condiviso viene mantenuto fino alla chiusura della transazione, ciò significa che nessun dato nella tabella può essere aggiornato fino al completamento della transazione, anche se abbiamo letto solo tre righe.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (777777, 'VC', 'Simon', 'ZZZZZ')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'ORDER BY request_mode

Gamma chiave 17

Questo è qualcosa di cui devi essere consapevole se usi il livello di isolamento SERIALIZZABILE.

Sommario

Se una colonna chiave viene utilizzata per filtrare i dati in una query, i blocchi dell’intervallo chiave possono essere presi contro le chiavi in quell’indice. Saranno presi contro tutte le chiavi che soddisfano la query più la chiave successiva in sequenza al di fuori della query. Ciò impedisce quindi l’aggiunta di dati che inserirebbero una nuova chiave immediatamente prima di una chiave con un blocco dell’intervallo di tasti.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.