|
|
Publicēts 20.07.2016 12:37:53
|
|
|
Slēdzeņu pārskats
1. Kāpēc ieviest slēdzenes
Ja vairāki lietotāji vienlaikus veic darbības datu bāzē, rodas šādas datu neatbilstības:
Trūkst atjauninājumu
Divi lietotāji, A un B, nolasa vienus un tos pašus datus un tos modificē, un viena lietotāja izmaiņu rezultāts iznīcina otras modifikācijas rezultātu, piemēram, biļešu rezervēšanas sistēmu
Netīra lasīšana
Lietotājs A modificē datus, un pēc tam lietotājs B nolasa datus, bet lietotājs A kāda iemesla dēļ atceļ datu modifikāciju, un dati atgriežas sākotnējā vērtībā
Nelasīt atkārtoti
Lietotājs A nolasa datus, un pēc tam lietotājs B nolasa datus un modificē tos
Galvenā vienlaicīguma kontroles metode ir bloķēšana, kas ir aizliegt lietotājiem noteiktu laiku veikt noteiktas darbības, lai izvairītos no datu neatbilstības
2. Slēdzeņu klasifikācija
Ir divi sadalījumi slēdzeņu kategorijās:
1 . No datu bāzes sistēmas viedokļa: tā ir sadalīta ekskluzīvās slēdzenēs (t.i., ekskluzīvās slēdzenēs), koplietojamās slēdzenēs un atjaunināšanas slēdzenēs
MS - SQL Server izmanto šādus resursu bloķēšanas modeļus.
Bloķēšanas režīma apraksts
Share(s) tiek izmantots operācijām, kas nemaina vai neatjaunina datus (tikai lasāmas operācijas), piemēram, SELECT priekšraksti.
Atjauninājums (U) tiek izmantots atjaunināmos resursos. Novērš bieži sastopamus strupceļus, kad tiek lasītas, bloķētas vairākas sesijas un, iespējams, var rasties resursu atjaunināšana.
Ekskluzīvs (X) tiek izmantots datu modificēšanas operācijām, piemēram, INSERT, UPDATE vai DELETE. Pārliecinieties, ka vienam un tam pašam resursam vienlaicīgi netiek veikti vairāki atjauninājumi.
Nolūka slēdzenes tiek izmantotas, lai izveidotu slēdzeņu hierarhiju. Nolūka bloķēšanas veidi ir: Intent Shared (IS), Intent Exclusive (IX) un Intent Exclusive (SIX).
Shēmas slēdzenes tiek izmantotas, veicot darbības, kas ir atkarīgas no tabulas shēmas. Shēmas slēdzeņu veidi ir: shēmas modifikācija (Sch -M) un shēmas stabilitāte (Sch -S).
Lielapjoma atjauninājumi (BU) tiek izmantoti, ja tabulā tiek kopēts liels datu apjoms un ir norādīts TABLOCK mājiens.
Koplietojamās slēdzenes
Koplietota(-u) bloķēšana ļauj vienlaicīgām transakcijām nolasīt (SELECT) resursu. Ja resursam ir koplietojama (S) bloķēšana, neviens cits darījums nevar modificēt datus. Atlaidiet resursa koplietojamo (S) bloķēšanu, tiklīdz dati ir nolasīti, ja vien transakcijas izolācijas līmenis nav iestatīts uz atkārtojamu vai augstāku, vai koplietojamā (S) bloķēšana tiek saglabāta ar bloķēšanas mājienu darījuma darbības laikā.
Atjaunināšanas bloķēšana
Atjaunināšanas (U) slēdzenes novērš strupceļus to parastajā formā. Tipisks atjaunināšanas modelis sastāv no transakcijas, kas nolasa ierakstu, iegūst resursa (lapas vai rindas) koplietojamu (S) bloķēšanu un pēc tam modificē rindu, kas prasa slēdzeni pārvērst par ekskluzīvu (X) slēdzeni. Ja divi darījumi iegūst resursa koplietošanas režīma bloķēšanu un pēc tam mēģina atjaunināt datus vienlaicīgi, viens darījums mēģina pārvērst bloķēšanu par ekskluzīvu (X) slēdzeni. Pārejai no koplietošanas režīma uz ekskluzīvo bloķēšanu ir jāgaida kāds laiks, jo viena darījuma ekskluzīvā bloķēšana nav saderīga ar cita darījuma koplietošanas režīma bloķēšanu; Notiek bloķēšanas gaidīšana. Otrais darījums mēģina iegūt ekskluzīvu (X) bloķēšanu atjaunināšanai. Strupceļš rodas, jo abi darījumi tiek pārvērsti par ekskluzīvām (X) slēdzenēm, un katrs darījums gaida, kamēr otrs darījums atbrīvos koplietojamā režīma bloķēšanu.
Lai izvairītos no šīs iespējamās strupceļa problēmas, izmantojiet atjauninātu (U) slēdzeni. Tikai viena transakcija vienlaikus var iegūt atjauninātu (U) bloķēšanu resursam. Ja transakcija modificē resursu, atjaunināšanas (U) bloķēšana tiek pārvērsta par ekskluzīvu (X) slēdzeni. Pretējā gadījumā slēdzene tiek pārvērsta par koplietojamu slēdzeni.
Ekskluzīvas slēdzenes
Ekskluzīvas (X) slēdzenes neļauj vienlaicīgiem darījumiem piekļūt resursiem. Citi darījumi nevar nolasīt vai modificēt datus, kas bloķēti ar ekskluzīvo (X) slēdzeni.
Nodoma bloķēšana
Nolūka bloķēšana norāda, ka SQL Server ir jāiegūst koplietojama (S) bloķēšana vai ekskluzīva (X) bloķēšana dažiem hierarhijas pamatā esošajiem resursiem. Piemēram, tabulas līmenī ievietota koplietošanas nolūka bloķēšana norāda, ka darījuma mērķis ir ievietot akcijas(S) bloķēšanu tabulas lapā vai rindā. Nolūka bloķēšanas iestatīšana tabulas līmenī neļauj citai transakcijai iegūt ekskluzīvu (X) bloķēšanu tabulā, kurā ir šī lapa. Nolūka bloķēšana var uzlabot veiktspēju, jo SQL Server pārbauda nolūka bloķēšanu tikai tabulas līmenī, lai noteiktu, vai transakcija var droši iegūt šīs tabulas bloķēšanu. Tā vietā, lai pārbaudītu bloķēšanu katrā tabulas rindā vai lapā, lai noteiktu, vai transakcija var bloķēt visu tabulu.
Nolūka bloķēšana ietver nolūka koplietošanu (IS), ekskluzīvu nodomu (IX) un nolūka ekskluzīvu koplietošanu (SIX).
Bloķēšanas režīma apraksts
Nolūka koplietošana (IS) norāda, ka transakcijas nolūks ir daži, nevis visi lasīšanas hierarhijas pamatā esošie resursi, katram resursam ievietojot S-slēdzenes.
Ekskluzīvs nolūks (IX) norāda, ka transakcijas nolūks ir modificēt dažus, bet ne visus hierarhijas pamatā esošos resursus, katram resursam ievietojot X-slēdzeni. IX ir IS virskopa.
Ekskluzīva koplietošana ar nodomu (SIX) norāda, ka darījuma nolūks ir nolasīt visus pamatā esošos resursus hierarhijā un modificēt dažus, bet ne visus pamatā esošos resursus, ievietojot IX slēdzenes katram resursam. Atļaut vienlaicīgu IS bloķēšanu augstākā līmeņa resursos. Piemēram, tabulas SIX bloķēšana novieto SIX bloķēšanu uz tabulas (atļaujot vienlaicīgas IS bloķēšanas) un IX bloķēšanu pašreiz modificētajā lapā (X bloķēšana modificētajā rindā). Lai gan katram resursam var būt tikai viena SIX bloķēšana uz noteiktu laiku, lai neļautu citām transakcijām atjaunināt resursu, citas transakcijas var nolasīt pamatā esošos resursus hierarhijā, iegūstot tabulas līmeņa IS slēdzenes.
Ekskluzīva bloķēšana: To drīkst izmantot tikai programma, kas veic bloķēšanas darbību, un citas darbības ar to netiks pieņemtas. Izpildot datu atjaunināšanas komandu, SQL Server automātiski izmanto ekskluzīvu bloķēšanu. Ja objektā ir citas slēdzenes, tam nevar pievienot ekskluzīvu slēdzeni.
Koplietojamā bloķēšana: resursu, kas bloķēts ar koplietojamo slēdzeni, var lasīt citi lietotāji, bet citi lietotāji to nevar modificēt.
Atjaunināšanas bloķēšana: kad SQL Server ir gatavs atjaunināt datus, tas vispirms bloķē datu objektu, lai datus nevarētu modificēt, bet tos varētu lasīt. Kad SQL Server nosaka, ka vēlas atjaunināt datus, tas automātiski aizstāj atjaunināšanas slēdzeni ar ekskluzīvu slēdzeni un nevar tai pievienot atjaunināšanas slēdzeni, ja objektā ir citas slēdzenes.
2 . No programmētāja viedokļa: tas ir sadalīts optimistiskā slēdzenē un pesimistiskajā slēdzenē.
Optimisma slēdzene: pilnībā paļaujas uz datu bāzi, lai pārvaldītu slēdzenes darbu.
Pesimistiskas slēdzenes: programmētāji paši pārvalda datu vai objektu bloķēšanas apstrādi.
MS - SQLSERVER izmanto slēdzenes, lai īstenotu pesimistisku vienlaicīguma kontroli starp vairākiem lietotājiem, kuri vienlaikus veic izmaiņas datu bāzē
3. Slēdzenes daļiņu izmērs
Bloķēšanas detalizācija ir bloķētā mērķa lielums, mazā bloķēšanas granularitāte ir augsta vienlaicīgums, bet pieskaitāmās izmaksas ir lielas, un lielā bloķēšanas granularitāte ir zema vienlaicīgums, bet pieskaitāmās izmaksas ir mazas
SQL Server atbalsta rindu, lappušu, atslēgu, atslēgu diapazonu, indeksu, tabulu vai datu bāzu bloķēšanas detalizāciju
Resursa apraksts
RID rindas identifikators. Izmanto, lai atsevišķi bloķētu rindu tabulā.
Atslēgas rindas bloķēšana indeksā. Izmanto, lai aizsargātu atslēgu diapazonu serializējamās transakcijās.
8 kilobaiti (KB) datu lappušu vai indeksa lappušu.
Paplašinātais disks Astoņu blakus esošo datu lappušu vai indeksa lappušu kopa.
Tabula Visa tabula, ieskaitot visus datus un indeksus.
DB datu bāze.
4. Bloķēšanas laika ilgums
Bloķēšanas turēšanas ilgums ir laiks, kas nepieciešams, lai aizsargātu resursu pieprasītajā līmenī.
Koplietojamās bloķēšanas aizturēšanas laiks, kas tiek izmantots, lai aizsargātu lasīšanas operācijas, ir atkarīgs no transakcijas izolācijas līmeņa. Izmantojot noklusējuma transakciju izolācijas līmeni READ COMMITTED, koplietojamā bloķēšana tiek kontrolēta tikai lasīšanas lapas laikā. Skenēšanas laikā bloķēšana netiek atbrīvota, kamēr bloķēšana nav iegūta nākamajā skenēšanas lapā. Ja norādāt HOLDLOCK uzvedni vai iestatāt transakcijas izolācijas līmeni uz REPEATABLE READ vai SERIALIZABLE, bloķēšana netiek atbrīvota līdz transakcijas beigām.
Atkarībā no kursoram iestatītās vienlaicīguma opcijas, kursors var iegūt ritināšanas bloķēšanu koplietošanas režīmā, lai aizsargātu izvilkumu. Ja ir nepieciešama ritināšanas bloķēšana, ritināšanas bloķēšana netiek atbrīvota līdz nākamajai kursora izvilkšanas vai aizvēršanas reizei, atkarībā no tā, kas notiek pirmais. Tomēr, ja norādāt HOLDLOCK, ritināšanas bloķēšana netiek atbrīvota līdz transakcijas beigām.
Ekskluzīvā bloķēšana, kas tiek izmantota atjauninājuma aizsardzībai, netiks atbrīvota līdz darījuma beigām.
Ja savienojums mēģina iegūt slēdzeni, kas ir pretrunā ar slēdzeni, ko kontrolē cits savienojums, savienojums, kas mēģina iegūt bloķēšanu, tiks bloķēts līdz:
Konfliktējošā bloķēšana tiek atbrīvota, un savienojums iegūst pieprasīto bloķēšanu.
Savienojuma taimauts ir beidzies. Pēc noklusējuma nav taimauta intervāla, bet dažas programmas iestata taimauta intervālus, lai novērstu nenoteiktu gaidīšanu
Piecas slēdzeņu pielāgošana SQL Server
1 Rīkojieties ar strupceļiem un iestatiet strupceļa prioritātes
Strupceļš ir bezgalīga gaidīšana, ko izraisa vairāki lietotāji, kas piesakās dažādiem blokiem, jo pieteikuma iesniedzējam ir daļa no bloķēšanas tiesībām un gaida daļēju bloķēšanu, kas pieder citiem lietotājiem
Varat izmantot DEADLOCK_PRIORITY SET, lai kontrolētu, kā sesija reaģē strupceļa stāvokļa gadījumā. Ja abi procesi bloķē datus un katrs process nevar atbrīvot savu bloķēšanu, kamēr otrs process neatbrīvo savu bloķēšanu, rodas strupceļa situācija.
2 Rīkojieties ar taimautu un iestatiet bloķēšanas taimauta ilgumu.
@@LOCK_TIMEOUT Atgriež pašreizējo bloķēšanas taimauta iestatījumu pašreizējai sesijai milisekundēs
Iestatījums SET LOCK_TIMEOUT ļauj lietojumprogrammai iestatīt maksimālo laiku, kas priekšraksts gaida, lai bloķētu resursu. Ja pārskata gaidīšanas laiks ir lielāks par LOCK_TIMEOUT iestatījumu, sistēma automātiski atceļ bloķēšanas paziņojumu un atgriež lietojumprogrammai kļūdas ziņojumu 1222, ka bloķēšanas pieprasījuma taimauta periods ir pārsniegts
piemērs
Nākamajā piemērā bloķēšanas taimauta periods ir iestatīts uz 1 800 milisekundēm.
IESTATĪT LOCK_TIMEOUT1800
3) Iestatiet darījuma izolācijas līmeni.
4) Izmantojiet tabulas līmeņa bloķēšanas padomus SELECT, INSERT, UPDATE un DELETE paziņojumiem.
5) Konfigurējiet indeksa bloķēšanas detalizāciju
Varat izmantot sp_indexoption sistēmas saglabātās procedūras, lai iestatītu indeksēšanas bloķēšanas detalizāciju
6. Apskatiet slēdzenes informāciju
1 Veiciet EXEC SP_LOCK ziņojiet par informāciju par slēdzeni
2 Nospiediet Ctrl + 2 vaicājumu analizatorā, lai redzētu slēdzenes informāciju
7. Piesardzības pasākumi lietošanā
Kā izvairīties no strupceļa
1. Izmantojot darījumus, mēģiniet saīsināt darījumu loģisko apstrādes procesu un iesniegt vai atcelt darījumus agrāk.
2 Iestatiet strupceļa taimauta parametru saprātīgā diapazonā, piemēram: 3 minūtes - 10 minūtes; Pēc laika operācija tiks automātiski pārtraukta, lai izvairītos no procesa karāšanās;
3. Optimizējiet programmu, pārbaudiet un izvairieties no strupceļa parādības;
4. Rūpīgi pārbaudiet visus skriptus un SP pirms precīzas versijas.
5 Visiem SP ir jābūt kļūdu apstrādei (izmantojot @error)
6 Nemainiet SQL SERVER transakciju noklusējuma līmeni. Piespiedu bloķēšana nav ieteicama
Atrisināt problēmu Kā bloķēt rindu tabulas datu bāzi
8. Vairāki jautājumi par slēdzenēm
1 Kā bloķēt tabulas rindu
IESTATĪT TRANSAKCIJASIZOLĀCIJAS LĪMENI READUNCOMMITTED
SELECT *FROM tabula ROWLOCKWHERE id = 1
2 Bloķējiet tabulu datu bāzē
SELECT *FROM table WITH( HOLDLOCK )
Slēdzenes paziņojums:
sybase:
atjaunināt tabulas kopu col1 = col1 kur1 = 0 ;
MSSQL:
izvēlieties col1no tabulas (tablockx)kur 1= 0 ;
oracle:
BLOĶĒT GALDA GALDU EKSKLUZĪVĀ REŽĪMĀ;
Pēc bloķēšanas neviens cits to nevar darbināt, kamēr bloķētais lietotājs to neatbloķē, un tas tiek atbloķēts ar saistību vai atcelšanu
Daži piemēri palīdz padziļināt iespaidu
Iestatīt galdu1 (A, B, C)
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3
1) Ekskluzīva slēdzene
Divu jaunu savienojumu izveide
Pirmajā savienojumā izpildiet šādu paziņojumu
sākt tran
Atjaunināt tabulu1
set A = ' aa '
kur B = ' b2 '
waitfor delay' 00:00:30' --wait 30 seconds
veikt tran
Otrajā savienojumā izpildiet šādu paziņojumu
sākt tran
Izvēlieties *no tabulas1
kur B = ' b2 '
veikt tran
Ja iepriekš minētie divi paziņojumi tiek izpildīti vienlaicīgi, atlases vaicājumam jāgaida, līdz tiks izpildīts atjauninājums, tas ir, jāgaida 30 sekundes
2) Koplietošanas slēdzene
Pirmajā savienojumā izpildiet šādu paziņojumu
sākt tran
select *from table1 holdlock - aizturēšanas slēdzene tiek mākslīgi pievienota slēdzenei
kur B = ' b2 '
waitfor delay' 00:00:30' --wait 30 seconds
veikt tran
Otrajā savienojumā izpildiet šādu paziņojumu
sākt tran
izvēlieties A,C no tabulas1
kur B = ' b2 '
Atjaunināt tabulu1
set A = ' aa '
kur B = ' b2 '
veikt tran
Ja iepriekš minētie divi paziņojumi tiek izpildīti vienlaicīgi, var izpildīt atlases vaicājumu otrajā savienojumā
Atjauninājumam jāgaida pirmais darījums, lai atbrīvotu koplietojamo slēdzeni un pārvērstu to par ekskluzīvu slēdzeni, tas ir, pagaidiet 30 sekundes
3) Strupceļš
Pievienota tabula2 (D, E)
D E
d1 e1
d2 e2
Pirmajā savienojumā izpildiet šādu paziņojumu
sākt tran
Atjaunināt tabulu1
set A = ' aa '
kur B = ' b2 '
gaidīt kavēšanos" 00:00:30'
Atjaunināt tabulu2
set D = ' d5 '
kur E = ' e1 '
veikt tran
Otrajā savienojumā izpildiet šādu paziņojumu
sākt tran
Atjaunināt tabulu2
set D = ' d5 '
kur E = ' e1 '
gaidīt kavēšanos" 00:00:10'
Atjaunināt tabulu1
set A = ' aa '
kur B = ' b2 '
veikt tran
Tajā pašā laikā sistēma atklāj strupceļu un pārtrauc procesu
Pievienot:
SQL Server 2000 atbalstītie tabulas līmeņa bloķēšanas padomi
HOLDLOCK saglabā koplietojamo bloķēšanu, līdz visa transakcija ir pabeigta, un tā ir jāatlaiž, tiklīdz bloķētais objekts nav nepieciešams, kas ir vienāds ar SERIALIZABLE transakcijas izolācijas līmeni
NOLOCK priekšraksts tiek izpildīts, neizsniedzot koplietojamu slēdzeni, pieļaujot netīrus lasījumus, kas ir vienādi ar READ UNCOMMITTED darījumu izolācijas līmeni
PAGLOCK izmanto vairākas lappušu bloķēšanas, kur tiek izmantota viena tabulas bloķēšana
READPAST ļauj sql serverim izlaist visas bloķētās rindas un izpildīt transakcijas, un READ UNCOMMITTED transakciju izolācijas līmeņiem izlaist tikai RID slēdzenes, nevis lapas, zonas un tabulas bloķēšanas
ROWLOCK piespiež airu slēdzenes izmantošanu
TABLOCKX ievieš ekskluzīvas tabulas līmeņa bloķēšanas izmantošanu, kas neļauj jebkuram citam darījumam izmantot tabulu darījuma laikā
UPLOCK piespiež izmantot atjauninājumus, lasot tabulu bez koplietojamas bloķēšanas
Lietotnes bloķēšana:
Lietojumprogrammas bloķēšana ir klienta koda ģenerēta slēdzene, nevis paša SQL Server ģenerēta bloķēšana
Divi procesi lietojumprogrammu slēdzeņu apstrādei
sp_getapplock bloķēšanas lietojumprogrammu resursi
sp_releaseapplock Lietojumprogrammu resursu atbloķēšana
Piezīme: Atšķirība starp tabulas bloķēšanu datu bāzē
SELECT *FROM table WITH( HOLDLOCK ) Citas transakcijas var nolasīt tabulu, bet nevar atjaunināt un izdzēst
SELECT *FROM table WITH(TABLOCKX) Citi darījumi nevar nolasīt, atjaunināt un izdzēst tabulu
|
Iepriekšējo:Nebija galapunkta, kas klausījās http://localhost:111/xxx.svc, ka c ...Nākamo:SQL slēdzenes NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
