Simon Learning SQL Server

Simon Learning SQL Server

într-un post anterior am vorbit despre nivelurile de blocare, blocare și izolare. În acest post am de gând să merg în mai multe detalii despre încuietori cheie gama utilizate în nivelul de izolare SERIALIZABLE și, de asemenea, să demonstreze de a utiliza SYS.dm_tran_locks DMV pentru a vizualiza încuietori existente și, de asemenea, cum să lucreze ce resurse încuietori sunt plasate pe.

încuietorile cu cheie sunt utilizate numai în nivelul de izolare SERIALIZABIL și scopul lor este de a opri citirile fantomă care sunt posibile în celelalte trei niveluri de izolare a concurenței pesimiste, și anume citirea neangajată, citirea angajată și citirea repetabilă. O citire fantomă este în cazul în care o tranzacție efectuează o citire pe o serie de date, de exemplu, folosind o interogare cu o clauză WHERE, iar o altă tranzacție adaugă date care vor satisface condițiile clauzei WHERE. Dacă prima tranzacție efectuează aceeași citire, aceasta va returna acum noile date care rezultă în date fantomă care apar în a doua citire în cadrul aceleiași tranzacții. Blocările key-range sunt utilizate pentru a preveni adăugarea datelor fantomă și sunt plasate pe tastele indexului care este utilizat pentru a prelua datele. Următorul exemplu folosind persoana.Tabelul de persoane din Baza de date AdventureWorks2012 va demonstra modul în care sunt utilizate aceste încuietori pentru gama de chei.

Persoana.Tabel persoană are un index ix_person_lastname_firstname_middlename non-cluster cu coloana LastName ca prima coloană din cheia index. Aceasta înseamnă că orice interogare în care filtrăm pe LastName ar trebui să utilizeze acest index pentru a prelua datele.

dacă rulăm o interogare pentru a returna câteva rânduri de la persoană.Persoană de filtrare pe o serie de nume de familie, atunci ar trebui să vedem acest index utilizat. De exemplu

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

returnează următorul set de rezultate.

gama de chei 2

și uitându-ne la planul de execuție putem vedea că acest indice este utilizat.

gama de chei 3

acum, dacă setăm nivelul de izolare la SERIALIZABIL, deschideți o tranzacție și rulați aceeași instrucțiune select, ar trebui să putem vedea ce încuietori sunt ținute.

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

acum, dintr-o altă conexiune, putem interoga DMV SYS.dm_tran_locks pentru a vedea blocările curente reținute. Suntem interesați doar de încuietorile resurselor cheie, așa că filtrăm în consecință.

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

gama de chei 4

obținem șapte rânduri reglate cu două valori diferite request_mode. „S” request_mode corespunde încuietori partajate plasate pe cele trei rânduri care sunt returnate. Deoarece folosim nivelul de izolare SERIALIZABIL, aceste blocări partajate sunt ținute până când tranzacția se angajează sau se întoarce pentru a preveni citirile care nu pot fi repetate. „Intervalele-S” request_mode corespunde încuietorile cheie gama care au fost acordate. Dar de ce sunt patru rânduri? Pentru a răspunde la acest lucru, putem folosi datele returnate de la SYS. dm_tran_locks și de a folosi acest lucru pentru a lucra ce resurse au fost efectiv blocate.

coloanele care ne interesează sunt resource_associated_entity_id și resource_description. Datele din aceste coloane pot varia în funcție de tipul resursei și pentru mai multe detalii despre aceasta, consultați TechNet. Pentru resursele cheie resource_associated_entity_id corespunde partition_id din sys.partiții

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

gama de chei 5

object_id returnat corespunde persoanei.Tabel de persoane

SELECT object_name(1765581328)

gama de chei 6

putem vedea că cele două valori partition_id diferite corespund unor indici diferiți pe persoană.Persoană

SELECT * FROM sys.indexes WHERE object_id = 1765581328

gama de chei 7

sistemul.vizualizarea partițiilor ne spune că partițiile pe care le-am blocat sunt pentru indexurile PK_Person_BusinessEntityID și ix_person_lastname_firstname_middlename.

putem combina toate acestea într-o singură interogare pentru claritate.

 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

gama de chei 8

acest lucru ne arată că trei încuietori partajate sunt plasate pe rândurile în sine (nivelul frunzei indexului grupat) și că patru încuietori pentru gama de taste sunt plasate pe tastele din indexul care nu este grupat. Dar acest lucru nu a răspuns la întrebarea De ce avem patru încuietori?

înainte de a face acest lucru, voi demonstra două moduri de a demonstra că cele trei încuietori partajate sunt plasate pe rândurile care au fost returnate de interogarea originală și apoi le voi folosi pentru a afla ce resurse sunt plasate cele patru încuietori „intervale-S”.

în primul rând vom folosi %%physloc%% și %%lockres%% valorile împreună cu sys.fn_physlocformatter () funcție pentru a ne spune fișierul, pagina și slotul rândurilor returnate de interogarea originală. În al doilea rând, folosim aceste informații în comanda paginii DBCC.

trebuie doar să actualizăm interogarea noastră originală după cum urmează

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

gama de chei 9

putem vedea acum că rândul pentru Dennis Ware este localizat fizic pe fișierul de date 1, la pagina 2806 și la slotul 3. Putem vedea, de asemenea, că este resursa de blocare este (05c585e839d4).

dacă ne uităm înapoi la datele returnate de la SYS.dm_tran_locks, putem vedea că una dintre blocările partajate are o descriere a resurselor (05c585e839d4), astfel încât aceasta ne spune că acea blocare este plasată pe acel rând în indexul grupat.

acum putem rula următoarea comandă de pagină DBCC pentru a vedea de fapt informații despre ceea ce este pe pagină, dar pentru ca aceasta să returneze orice rezultat, trebuie să activăm steagul de urmărire 3604.

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

există o mulțime de informații textuale afișate aici și puteți specifica un număr diferit în parametrul format pentru a returna cantități diferite de date. În acest caz, primul bit este antetul paginii și apoi după aceea avem starea de alocare. Veți vedea că primul slot utilizat este slotul 0. Există o valoare Offset care ne spune în cazul în care pe pagina începe acest slot și o lungime de valori care ne spune cât timp înregistrarea este.

gama de chei 10

dacă derulăm în jos până găsim slotul 3, apoi parcurgem blocul de date, putem vedea datele fizice pentru fiecare coloană și putem confirma că acesta este rândul pentru Dennis Ware.

gama de chei 11

acum, dacă ne întoarcem până la sfârșitul slotului 3, putem vedea

KeyHashValue = (05c585e839d4)

aceasta se potrivește cu descrierea resurselor din sys.dm_tran_locks și valoarea returnată de %%lockres%%.

dar cum facem ceva similar pentru încuietorile cu cheie? Cum aflăm ce resurse blochează? O modalitate ar fi de a utiliza SYS.dm_db_database_page_allocations pentru a lucra în care paginile aparțin din index non-grupate. Trecem în database_id, object_id și index_id al indexului IX_Person_LastName_FirstName_Middlename și va returna un rând pentru fiecare pagină utilizată în acel index. Putem rula apoi pagina DBCC cu valorile furnizate allocated_page_page_id și pentru a găsi pagina care conține 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

putem rula apoi pagina DBCC cu valoarea furnizată și pentru a găsi pagina care conține Dennis Ware.

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

lucrul la îndemână este că, în acest caz, pagina DBCC returnează datele din pagină într-un set de rezultate, astfel încât să putem găsi destul de repede pagina corectă, în acest caz pagina 24745.

gama de chei 12

acum, dacă vom folosi valorile resource_description pentru cele patru încuietori cheie-range în sys.dm_tran_locks putem vedea că încuietorile „RangeS-S” au fost acordate efectiv rândurilor care conțin date pentru Dennis Ware, Victoria Ware, Clay Warten și Aaron Washington. Dar Aaron Washington nu este inclus în setul de rezultate, deci de ce este prezentă această a patra cheie de blocare? Acest lucru se datorează faptului că atunci când un interval de taste este preluat pe o cheie, acesta va împiedica adăugarea oricăror date imediat înainte de o cheie cu o blocare a intervalului de taste. Cu alte cuvinte, nu pot fi adăugate date între, de exemplu, Victoria Ware și Clay Warthen, de exemplu, 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

gama de chei 13

aici putem vedea blocarea în așteptare a intervalului de taste.

înseamnă, de asemenea, că nu pot fi adăugate date între Timothy Ward și Aaron Washington și înseamnă că încuietorile cu cheie blochează de fapt mai multe date decât cele care ar putea satisface interogarea în tranzacția deschisă. De exemplu, nu am putut introduce un rând pentru Simon Wartz care să fie pus între Clay Warthen și Aaron Washington, chiar dacă nu ar fi returnat în interogarea inițială în tranzacția deschisă.

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

gama de chei 14

acum știm cum sunt utilizate blocările key-range, putem afla ce resurse au blocările key-range pe ele fără a fi nevoie să căutăm prin paginile indexului non-grupat. Putem include pur și simplu un indiciu de tabel pentru a returna o parte din datele de care avem nevoie.

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

gama de chei 18

după cum puteți vedea, acest lucru ne oferă același fișier, pagină și slot de date și aceeași resource_description pentru cele trei rânduri returnate de interogare. Acum știm că a patra blocare „Games-S” este plasată pe cheia din index imediat după cheia pentru Clay Warthen, adică slotul 140. Din nou, putem folosi pagina DBCC pentru a vizualiza datele de la pagina 25065.

acum, în demonstrația pe care tocmai am trecut-o, se întâmplă să avem un index non-grupat, prima coloană din cheia de grupare fiind cea pe care o folosim în clauza WHERE. Dar ce se întâmplă dacă eliminăm indicele non-grupat? Sau cu alte cuvinte, ce se întâmplă dacă am rulat o interogare împotriva unei alte filtrări de tabel pe o coloană care nu este inclusă într-o cheie index? Pentru a demonstra cu ușurință acest lucru putem elimina doar indicele non-grupate.

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

obținem aceleași rânduri înapoi așa cum era de așteptat, dar de data aceasta SQL Server a trebuit să utilizeze indexul grupat pentru a obține datele.

gama de chei 15

deci, ce încuietori avem? De data aceasta nu există nici o cheie-gama de blocare, care poate fi aplicat, astfel încât un sistem de blocare comun trebuie să plasat pe întreaga masă.

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

gama de chei 16

deoarece această blocare partajată este ținută până la închiderea tranzacției, aceasta înseamnă că nicio informație din tabel nu poate fi actualizată până la finalizarea tranzacției, chiar dacă am citit doar trei rânduri.

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

gama de chei 17

acesta este un lucru de care trebuie să fiți conștienți dacă utilizați nivelul de izolare SERIALIZABIL.

rezumat

dacă o coloană cheie este utilizată pentru a filtra datele într-o interogare, atunci blocările intervalului de taste pot fi luate împotriva tastelor din acel index. Acestea vor fi luate împotriva tuturor cheilor care satisfac interogarea plus următoarea cheie secvențial în afara interogării. Acest lucru împiedică apoi adăugarea de date care ar introduce o nouă cheie imediat înainte de o cheie cu o blocare a intervalului de taste.

Lasă un răspuns

Adresa ta de email nu va fi publicată.