|
|
Objavljeno na 20. 07. 2016 12:37:53
|
|
|
Pregled zapornic
1. Zakaj uvajati ključavnice
Ko več uporabnikov hkrati izvaja operacije na bazi podatkov, pride do naslednjih podatkovnih neskladij:
Manjkajoče posodobitve
Dva uporabnika, A in B, bereta iste podatke in jih spreminjata, rezultat spremembe enega uporabnika pa uniči rezultat druge spremembe, kot je sistem za rezervacijo vstopnic
Umazano branje
Uporabnik A spremeni podatke, nato uporabnik B prebere podatke, vendar uporabnik A iz nekega razloga prekliče spremembo podatkov in podatki se vrnejo na prvotno vrednost
Ne berite večkrat
Uporabnik A prebere podatke, nato pa uporabnik B prebere podatke in jih spremeni
Glavna metoda nadzora sočasnosti je blokiranje, kar pomeni, da uporabnikom za določen čas preprečijo izvajanje določenih operacij, da se izognejo neskladjem podatkov
2. Klasifikacija zapornic
Obstajata dve razdelitvi na kategorije zapornic:
1 . Z vidika podatkovnega sistema: razdeljen je na ekskluzivne ključavnice (tj. ekskluzivne ključavnice), deljene ključavnice in posodobitve
MS - SQL Server uporablja naslednje vzorce zaklepanja virov.
Opis načina zaklepanja
Share(i) se uporablja za operacije, ki ne spreminjajo ali posodabljajo podatkov (operacije samo za branje), kot so stavki SELECT.
Update (U) se uporablja v gradivih, ki jih je mogoče posodobiti. Preprečuje pogoste oblike zastojev, ko se bere, zaklene več sej, in morda pride do posodobitve virov.
Ekskluzivno (X) se uporablja za operacije spreminjanja podatkov, kot so INSERT, UPDATE ali DELETE. Poskrbite, da se na istem viru ne izvaja več posodobitev hkrati.
Namerne ključavnice se uporabljajo za vzpostavitev hierarhije ključavnic. Vrste zaklepov namena so: Namen deljen (IS), Namen izključen (IX) in Namen izključen (SIX).
Schema locki se uporabljajo pri izvajanju operacij, ki so odvisne od sheme tabele. Vrste shemskih zaklepov so: sprememba sheme (Sch -M) in stabilnost sheme (Sch -S).
Množične posodobitve (BU) se uporabljajo, ko se v tabelo kopirajo velike količine podatkov in je določen namig.
Skupne ključavnice
Skupna ključavnica omogoča, da sočasne transakcije preberejo (SELECT) vir. Ko na viru obstaja deljena (S) ključavnica, nobena druga transakcija ne more spreminjati podatkov. Sprostite deljeno (S) zaklepanje na viru takoj, ko so podatki prebrani, razen če je stopnja izolacije transakcije nastavljena na ponovljivo ali višjo, ali če je deljena (S) ključavnica ohranjena z namigom za zaklepanje skozi celotno življenjsko dobo transakcije.
Posodobi zaklep
Update (U) ključavnice preprečujejo zastoje v svoji običajni obliki. Tipičen vzorec posodobitve obsega transakcijo, ki prebere zapis, dobi skupno (S) zaklepanje za vir (stran ali vrstico) in nato spremeni vrstico, kar zahteva, da se ključavnica pretvori v ekskluzivno (X) ključavnico. Če dve transakciji pridobita zaklep v deljenem načinu na viru in nato hkrati poskušata posodobiti podatke, ena transakcija poskuša zaklep pretvoriti v ekskluzivno (X) zaklepanje. Prehod iz deljenega načina v ekskluzivno zaklepanje mora počakati nekaj časa, ker izključna ključavnica ene transakcije ni združljiva z deljeno načinno zaklepanjo druge transakcije; Pride do čakanja na zaklep. Druga transakcija poskuša pridobiti ekskluzivno (X) zaklepanje za posodobitev. Do zastoja pride, ker sta obe transakciji pretvorjeni v ekskluzivne (X) ključavnice, vsaka transakcija pa čaka, da druga transakcija sprosti zaklep v deljenem načinu.
Da bi se izognili morebitni težavi z zastojem, uporabite posodobljeno (U) ključavnico. Le ena transakcija naenkrat lahko dobi posodobljeno (U) zaklepanje za vir. Če transakcija spremeni vir, se zaklep za posodobitev (U) pretvori v ekskluzivni (X) zaklep. V nasprotnem primeru se ključavnica pretvori v skupno ključavnico.
Ekskluzivne ključavnice
Ekskluzivne (X) ključavnice preprečujejo sočasnim transakcijam dostop do virov. Druge transakcije ne morejo prebrati ali spreminjati podatkov, zaklenjenih z ekskluzivno (X) zaklepavnico.
Zaklep namere
Zaklep z namenom pomeni, da mora SQL Server pridobiti skupno (S) ključavnico ali ekskluzivno (X) ključavnico na nekaterih osnovnih virih v hierarhiji. Na primer, zaklep namena delitve na ravni tabele pomeni, da transakcija namerava postaviti zaklep delnic na stran ali vrstico v tabeli. Nastavitev zaklepa namena na ravni tabele prepreči, da bi druga transakcija kasneje pridobila ekskluzivno (X) zaklepanje na tabeli, ki vsebuje to stran. Zaklepi namena lahko izboljšajo zmogljivost, ker SQL Server preverja zaklepanje namena le na ravni tabele, da ugotovi, ali lahko transakcija varno pridobi zaklep na tej tabeli. Namesto da bi preverjali ključavnice na vsaki vrstici ali strani v tabeli, da bi ugotovili, ali lahko transakcija zaklene celotno tabelo.
Zaklepi namena vključujejo deljenje namena (IS), ekskluzivno namero (IX) in ekskluzivno deljenje namena (SIX).
Opis načina zaklepanja
Deljenje namena (IS) označuje, da je namen transakcije nekaj, ne pa vseh, osnovnih virov v hierarhiji branja, tako da se na vsak vir postavijo S-ključavnice.
Namen Izključno (IX) pomeni, da je namen transakcije spremeniti neke, vendar ne vse, osnovnih virov v hierarhiji z uvedbo X-zaklepa na vsak vir. IX je nadmnožica IS.
Ekskluzivno deljenje z namenom (SIX) pomeni, da je namen transakcije prebrati vse osnovne vire v hierarhiji in spremeniti nekatere, a ne vse, osnovnih virov z namestitvijo IX zaklepov na vsak vir. Dovolite sočasne IS zaklepe na najvišje nivojske vire. Na primer, zaklep SIX v tabeli postavi SIX ključavnico na tabelo (kar omogoča sočasne IS ključavnice) in IX ključavnico na trenutno spremenjeni strani (X ključavnico na spremenjeni vrstici). Medtem ko lahko ima vsak vir za določen čas le eno SIX ključavnico, da prepreči posodabljanje vira drugim transakcijam, lahko druge transakcije berejo osnovne vire v hierarhiji z pridobivanjem IS ključavnic na ravni tabele.
Ekskluzivno zaklepanje: Le program, ki izvaja zaklepanje, sme ga uporabljati, vse druge operacije na njem pa ne bodo sprejete. Ko izvedete ukaz za posodobitev podatkov, SQL Server samodejno uporabi ekskluzivno zaklepanje. Ko na objektu obstajajo druge zaklepe, mu ne morete dodati ekskluzivne ključavnice.
Deljena ključavnica: Vir, zaklenjen z deljeno ključavnico, lahko preberejo drugi uporabniki, vendar ga drugi uporabniki ne morejo spreminjati.
Zaklep posodobitve: Ko je SQL Server pripravljen na posodobitev podatkov, najprej zaklene podatkovni objekt, da podatkov ni mogoče spreminjati, lahko pa jih berejo. Ko SQL Server ugotovi, da želi posodobiti podatke, bo samodejno zamenjal zaklep posodobitve z ekskluzivnim zaklepom in ne more dodati zaklepa za posodobitev, kadar na objektu obstajajo druge zaklepe.
2 . Z vidika programerja: razdeljena je na optimistično in pesimistično ključavnico.
Optimism Lock: Za upravljanje dela ključavnice se v celoti zanaša na bazo podatkov.
Pesimistične ključavnice: Programerji upravljajo upravljanje zaklepov na podatkih ali objektih sami.
MS - SQLSERVER uporablja ključavnice za izvajanje pesimističnega nadzora sočasnosti med več uporabniki, ki hkrati izvajajo spremembe v bazi podatkov
3. Velikost delcev ključavnice
Granularnost zaklepa je velikost blokiranega cilja, majhna blokirna granularnost je visoka sočasnost, vendar je režija velika, velika granularnost blokiranja pa nizka sočasnost, vendar je režija majhna
SQL Server podpira zaklepanje granularnosti za vrstice, strani, ključe, razpone ključev, indekse, tabele ali baze podatkov
Opis vira
Identifikator vrstice RID. Uporablja se za zaklepanje vrstice v mizi posamezno.
Zaklepanje vrstice ključev v indeksu. Uporablja se za zaščito razpona ključev v serializiranih transakcijah.
8 kilobajtov (KB) podatkovnih strani ali indeksnih strani.
Razširjeni disk Nabor osmih sosednjih podatkovnih strani ali indeksnih strani.
Tabela: Celotna tabela, vključno z vsemi podatki in indeksi.
Baza podatkov.
4. Dolžina časa zaklepanja
Čas, ko je ključavnica zadržana, je čas, potreben za zaščito vira na zahtevani ravni.
Čas zadrževanja deljene ključavnice, ki se uporablja za zaščito bralnih operacij, je odvisen od ravni izolacije transakcije. Z privzeto stopnjo izolacije transakcij READ COMMITTED je deljeno zaklepanje nadzorovano le za čas trajanja strani za branje. Pri skeniranju se ključavnica ne sprosti, dokler ni zaklenjena na naslednji strani v skeniranju. Če določite poziv za HOLDLOCK ali nastavite stopnjo izolacije transakcije na REPEATABLE READ ali SERIALIZABLE, se zaklep ne sprosti, dokler se transakcija ne konča.
Glede na nastavljeno možnost sočasnosti za kazalec lahko kazalec v deljenem načinu pridobi zaklep za pomikanje, da zaščiti izvleček. Ko je potrebna zaklep zvitka, se zaklep sprosti šele, ko se kazalec naslednjič izvleče ali zapre, kar se zgodi prej. Če pa določite HOLDLOCK, se ta ne sprosti do konca transakcije.
Ekskluzivna ključavnica, uporabljena za zaščito posodobitve, ne bo sproščena do konca transakcije.
Če povezava poskuša pridobiti ključavnico, ki je v konfliktu z zaklepom, ki ga nadzoruje druga povezava, bo povezava, ki poskuša pridobiti ključavnico, blokirana, dokler:
Konfliktna ključavnica se sprosti in povezava pridobi zahtevano ključavnico.
Časovna omejitev povezave je potekla. Privzeto ni časovne omejitve, vendar nekatere aplikacije nastavijo časovne intervale, da preprečijo neomejeno čakanje
Pet prilagoditev ključavnic v SQL Serverju
1 Obvladujte zastoje in nastavite prioritete zastojev
Zastoj je neskončno čakanje, ki ga povzroči več uporabnikov, ki zaprosijo za različne blokade, saj ima vlagatelj del pravice do blokiranja in čaka na delno blokado, ki jo imajo drugi uporabniki
SET DEADLOCK_PRIORITY lahko uporabiš za nadzor, kako seja reagira v primeru zastoja. Če oba procesa zakleneta podatke in vsak proces ne more sprostiti svojega zaklepa, dokler drugi proces ne sprosti svojega, pride do zastoja.
2 Uredite timeoute in določite trajanje timeoutov.
@@LOCK_TIMEOUT V milisekundah vrne trenutno nastavitev časovne omejitve zaklepa za trenutno sejo
Nastavitev SET LOCK_TIMEOUT omogoča aplikaciji, da nastavi največji čas, ki ga stavek čaka na blokado vira. Ko je čakalni čas stavka daljši od LOCK_TIMEOUT nastavitve, sistem samodejno prekliče blokadni ukaz in aplikaciji vrne sporočilo o napaki 1222, da je bil časovni izpad zahteve za zaklepanje prekoračen
primer
V naslednjem primeru je časovna omejitev zaklepa nastavljena na 1.800 milisekund.
SET LOCK_TIMEOUT1800
3) Nastavite stopnjo izolacije transakcije.
4) Uporaba namigov za zaklepanje na ravni tabele za ukaze SELECT, INSERT, UPDATE in DELETE.
5) Konfiguracija granularnosti zaklepanja indeksa
Za nastavitev granularnosti zaklepanja za indeksiranje lahko uporabite sp_indexoption sistemske shranjene postopke
6. Oglejte si informacije o zapornici
1 Izvesti EXEC SP_LOCK poročati o ključavnici
2 Pritisni Ctrl + 2 v analizatorju poizvedb, da vidiš informacije o zaklepu
7. Previdnostni ukrepi za uporabo
Kako se izogniti zastojem
1. Pri uporabi transakcij poskušajte skrajšati logični proces obdelave transakcij in transakcije predložiti ali razveljaviti zgodaj.
2 Nastavite parameter zastoja na razumen razpon, na primer: 3 minute - 10 minut; Po izteku časa se operacija samodejno opusti, da se prepreči zastoj procesa;
3. Optimizirati program, preveriti in se izogniti pojavu zastoja;
4. Natančno testirajte vse skripte in SP-je pred natančno različico.
5 Vsi SP morajo imeti obravnavo napak (preko @error)
6 Ne spreminjajte privzete ravni SQL SERVER transakcij. Prisilno zaklepanje ni priporočljivo
Rešitev problema Kako zakleniti bazo vrstic
8. Več vprašanj o ključavnicah
1 Kako zakleniti vrstico mize
NASTAVI NIVO TRANSACTIONISOLATION READUNCOMMITTED
IZBERI *IZ tabele ROWLOCK, kjer id = 1
2 Zakleni tabelo v bazi podatkov
IZBERI *IZ TABELE WITH( HOLDLOCK )
Izjava o zaklepu:
sybase:
Tabela posodobi množico col1 = stolpec 1, kjer 1 = 0 ;
MSSQL:
Izberite Col1Iz tabele (tablockX), kjer je 1= 0 ;
oracle:
ZAKLEPNA MIZA V EKSKLUZIVNEM NAČINU ;
Ko je zaklep zaklenjen, ga nihče drug ne more uporabljati, dokler ga zaklenjeni uporabnik ne odklene, nato pa se odklene s potrditvijo ali razveljavitvijo
Nekaj primerov vam pomaga poglobiti vaš vtis
Nastavite tabelo1(A,B,C)
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3
1) Ekskluzivna ključavnica
Ustvarite dve novi povezavi
Izvedite naslednjo izjavo v prvi povezavi
Začetek TRAN
Posodobi tabelo1
set A= ' aa '
kjer je B= ' b2 '
počakaj, zamuda' 00:00:30' --počakaj 30 sekund
commit tran
Izvedite naslednjo izjavo v drugi povezavi
Začetek TRAN
izberi *iz tabele1
kjer je B= ' b2 '
commit tran
Če se zgornji dve stavki izvajata hkrati, mora poizvedba za izbiro počakati na izvedbo posodobitve, torej počakati 30 sekund
2) Skupna ključavnica
Izvedite naslednjo izjavo v prvi povezavi
Začetek TRAN
izberi *iz tabele1 holdlock - Holdlock se umetno doda locku
kjer je B= ' b2 '
počakaj, zamuda' 00:00:30' --počakaj 30 sekund
commit tran
Izvedite naslednjo izjavo v drugi povezavi
Začetek TRAN
izberite A,C iz tabele1
kjer je B= ' b2 '
Posodobi tabelo1
set A= ' aa '
kjer je B= ' b2 '
commit tran
Če se zgornji dve izjavi izvedeta hkrati, se lahko izvede izbirna poizvedba v drugi povezavi
Posodobitev mora počakati na prvo transakcijo, da sprosti skupno zaklepanje in ga pretvori v ekskluzivno zaklepanje, preden se lahko izvede, torej počaka 30 sekund
3) Zastoj
Dodana tabela2(D,E)
D E
d1 e1
D2 E2
Izvedite naslednjo izjavo v prvi povezavi
Začetek TRAN
Posodobi tabelo1
set A= ' aa '
kjer je B= ' b2 '
waitfor delay' 00:00:30'
Posodobi tabelo2
set D= ' d5 '
kjer je E= ' e1 '
commit tran
Izvedite naslednjo izjavo v drugi povezavi
Začetek TRAN
Posodobi tabelo2
set D= ' d5 '
kjer je E= ' e1 '
waitfor delay' 00:00:10'
Posodobi tabelo1
set A= ' aa '
kjer je B= ' b2 '
commit tran
Hkrati sistem zazna zastoj in proces prekine
Da dodam:
Namigi za zaklepanje na ravni tabele, ki jih podpira SQL Server 2000
HOLDLOCK hrani skupno zaklepanje, dokler ni celotna transakcija zaključena, in ga je treba sprostiti takoj, ko zaklenjeni objekt ni potreben, kar ustreza ravni izolacije transakcije SERIALIZABLE
Izjava NOLOCK se izvede brez izdaje skupnega zaklepa, kar omogoča umazana branja, kar je enako ravni izolacije transakcij READ UNCOMMITTED
PAGLOCK uporablja več zaklepov strani, kjer se uporablja ena zaklep za mizo
READPAST omogoča sql strežniku, da preskoči vse zaklenjene vrstice in izvede transakcije, za izolacijo transakcij READ UNCOMMITTED pa preskoči le RID ključavnice, ne pa zaklepov strani, con in tabel
ROWLOCK zagotavlja uporabo rowlockov
TABLOCKX zagotavlja uporabo ekskluzivne zaklepanosti na ravni tabele, ki preprečuje, da bi katera koli druga transakcija uporabljala tabelo med transakcijo
UPLOCK zahteva uporabo posodobitev pri branju tabele brez skupnega zaklepa
Zaklep aplikacije:
Aplikacijska ključavnica je ključavnica, ki jo ustvari odjemalska koda, ne pa zaklep, ki ga ustvari SQL Server sam
Dva postopka za upravljanje aplikacijskih zaklepov
sp_getapplock Zakleni aplikacijske vire
sp_releaseapplock Odklenite aplikacijske vire
Opomba: Razlika med zaklepanjem tabele v podatkovni bazi
SELECT *FROM TABLE WITH( HOLDLOCK ) Druge transakcije lahko berejo tabelo, vendar ne morejo posodabljati in brisati
SELECT *FROM TABLE WITH(TABLOCKX) Druge transakcije ne morejo prebrati, posodobiti in izbrisati tabele
|
Prejšnji:Ni bilo nobenega končnega poslušanja http://localhost:111/xxx.svc da bi c...Naslednji:SQL zaklepi NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
