|
|
Zverejnené 20. 7. 2016 12:37:53
|
|
|
Prehľad zámkov
1. Prečo zavádzať zámky
Keď viacerí používatelia vykonávajú súbežné operácie s databázou súčasne, vyskytujú sa nasledujúce nezrovnalosti v dátach:
Chýbajúce aktualizácie
Dvaja používatelia, A a B, čítajú tie isté údaje a upravujú ich, pričom výsledok jednej úpravy zničí výsledok druhej úpravy, napríklad systém rezervácie lístkov
Špinavé čítanie
Používateľ A upraví údaje a potom používateľ B údaje prečíta, ale používateľ A z nejakého dôvodu zmenu údajov zruší a údaje sa vrátia na pôvodnú hodnotu
Nečítajte opakovane
Používateľ A číta dáta a používateľ B ich potom číta a upravuje
Hlavnou metódou riadenia súbežnosti je blokovanie, čo znamená zakázať používateľom vykonávať určité operácie na určitý čas, aby sa predišlo nezrovnalostiam v dátach
2. Klasifikácia zámkov
Existujú dve kategórie zámkov do kategórií:
1 . Z pohľadu databázového systému: je rozdelený na exkluzívne zámky (t. j. exkluzívne zámky), zdieľané zámky a aktualizačné zámky
MS - SQL Server používa nasledujúce vzory zámkov zdrojov.
Popis režimu zámku
Share(y) sa používa pre operácie, ktoré nemenia ani neaktualizujú dáta (operácie iba na čítanie), ako sú príkazy SELECT.
Update (U) sa používa v aktualizovateľných zdrojoch. Zabraňuje bežným formám zablokovania pri čítaní, uzamknutí viacerých relácií a možnej aktualizácii zdrojov.
Exkluzívne (X) sa používa na úpravy dát, ako sú INSERT, UPDATE alebo DELETE. Zabezpečte, aby sa na tom istom zdroji nevykonávalo viacero aktualizácií súčasne.
Zámerné zámky sa používajú na vytvorenie hierarchie zámkov. Typy zámkov zámeru sú: Zdieľaný zámer (IS), Výlučný zámer (IX) a Výlučný zámer (SIX).
Zámky schémy sa používajú pri vykonávaní operácií, ktoré závisia od schémy tabuľky. Typy zámkov schémy sú: modifikácia schémy (Sch -M) a stabilita schémy (Sch -S).
Hromadné aktualizácie (BU) sa používajú, keď sa veľké objemy dát kopírujú do tabuľky a je špecifikovaná TABLOCK nápoveda.
Zdieľané zámky
Zdieľaný zámok umožňuje súbežným transakciám čítať (SELECT) zdroj. Keď na zdroji existuje zdieľaný (S) zámok, žiadna iná transakcia nemôže dáta zmeniť. Uvoľnite zdieľaný (S) zámok na zdroji hneď po prečítaní dát, pokiaľ úroveň izolácie transakcie nie je nastavená na opakovateľnú alebo vyššiu, alebo ak zdieľaný (S) zámok nie je zachovaný s náznakom zámku počas celej životnosti transakcie.
Aktualizácia zámku
Update (U) zámky zabraňujú zablokovaniu v ich obvyklej podobe. Typický aktualizačný vzor pozostáva z transakcie, ktorá prečíta záznam, získa zdieľaný (S) zámok pre zdroj (stránku alebo riadok) a potom upraví riadok, čo vyžaduje, aby sa zámok premenil na exkluzívny (X) zámok. Ak dve transakcie získajú zámok v zdieľanom režime na zdroj a potom sa pokúsia súčasne aktualizovať dáta, jedna transakcia sa pokúsi previesť zámok na exkluzívny (X) zámok. Prechod zo zdieľaného režimu na exkluzívny zámok musí chvíľu počkať, pretože exkluzívny zámok jednej transakcie nie je kompatibilný so zámkom zdieľaného režimu inej transakcie; Dochádza k čakaniu na zámok. Druhá transakcia sa snaží získať exkluzívny (X) zámok pre aktualizáciu. Patová situácia nastáva, pretože obe transakcie sú konvertované na exkluzívne (X) zámky a každá transakcia čaká, kým druhá transakcia uvoľní zámok zdieľaného režimu.
Aby ste sa vyhli tomuto možnému problému so zablokovaním, použite aktualizovaný (U) zámok. Len jedna transakcia naraz môže získať aktualizovaný (U) zámok pre zdroj. Ak transakcia modifikuje zdroj, update (U) zámok sa premení na exkluzívny (X) zámok. V opačnom prípade sa zámok premení na zdieľaný zámok.
Exkluzívne zámky
Exkluzívne (X) zámky bránia súbežným transakciám v prístupe k zdrojom. Iné transakcie nemôžu čítať ani meniť údaje uzamknuté exkluzívnym (X) zámkom.
Zámok zámeru
Zámok zámeru znamená, že SQL Server potrebuje získať zdieľaný (S) zámok alebo exkluzívny (X) zámok na niektoré základné zdroje v hierarchii. Napríklad zámok na zámer podielu umiestnený na úrovni tabuľky znamená, že transakcia má za cieľ uzamknúť podiel(y) na stránku alebo riadok v tabuľke. Nastavenie zámku zámeru na úrovni tabuľky zabraňuje ďalšej transakcii získať exkluzívny (X) zámok na tabuľke, ktorá túto stránku obsahuje. Zámerné zámky môžu zlepšiť výkon, pretože SQL Server kontroluje zámok zámeru len na úrovni tabuľky, aby zistil, či transakcia dokáže bezpečne získať zámok na danej tabuľke. Namiesto kontroly zámkov na každom riadku alebo stránke v tabuľke, aby ste zistili, či transakcia dokáže zablokovať celú tabuľku.
Zámky zámeru zahŕňajú zdieľanie zámeru (IS), exkluzívne zámer (IX) a exkluzívne zdieľanie zámeru (SIX).
Popis režimu zámku
Zdieľanie zámeru (IS) označuje, že zámer transakcie je niektoré, nie všetky základné zdroje v hierarchii čítania, tým, že sa na každý zdroj umiestnia S-zámky.
Exkluzívny zámer (IX) označuje, že zámerom transakcie je upraviť niektoré, ale nie všetky, základné zdroje v hierarchii tým, že na každý zdroj uloží X-lock. IX je nadmnožina IS.
Exkluzívne zdieľanie s úmyslom (SIX) znamená, že zámerom transakcie je prečítať všetky základné zdroje v hierarchii a upraviť niektoré, ale nie všetky, základné zdroje uvedením IX zámkov na každý zdroj. Povoľte súbežné IS zámky na najvyšších úrovniach zdrojov. Napríklad zámok SIX tabuľky umiestni SIX zámok na tabuľku (čo umožňuje súbežné IS zámky) a IX zámok na aktuálne upravenej stránke (X zámok na upravenom riadku). Kým každý zdroj môže mať na určitý čas len jeden zámok SIX, aby zabránil iným transakciám v aktualizácii zdroja, iné transakcie môžu čítať základné zdroje v hierarchii získaním IS zámkov na úrovni tabuľky.
Exkluzívny zámok: Iba program, ktorý vykonáva uzamykateľnú operáciu, ho môže používať, a žiadne iné operácie na ňom nebudú akceptované. Keď vykonáte príkaz na aktualizáciu dát, SQL Server automaticky použije exkluzívny zámok. Keď na objekte existujú iné zámky, nemôžete doň pridať exkluzívny zámok.
Zdieľaný zámok: Zdroj uzamknutý zdieľaným zámkom môžu čítať aj iní používatelia, ale iní používatelia ho nemôžu upravovať.
Zámok aktualizácie: Keď je SQL Server pripravený aktualizovať dáta, najprv uzamkne dátový objekt, aby sa dáta nedali upraviť, ale dali sa čítať. Keď SQL Server zistí, že chce aktualizovať dáta, automaticky nahradí aktualizačný zámok exkluzívnym zámkom a nemôže naň pridať aktualizačný zámok, keď na objekte existujú iné zámky.
2 . Z pohľadu programátora: je rozdelený na optimistický zámok a pesimistický zámok.
Optimism Lock: Úplne sa spolieha na databázu pri riadení práce zámku.
Pesimistické zámky: Programátori sami riadia spracovanie zámkov na dátach alebo objektoch.
MS - SQLSERVER používa zámky na implementáciu pesimistickej kontroly súbežnosti medzi viacerými používateľmi, ktorí súčasne vykonávajú úpravy databázy
3. Veľkosť častíc zámku
Granularita zámku je veľkosť blokovaného cieľa, malá granularita blokovania je vysoká súbežnosť, ale režijné náklady sú veľké a veľká blokovacia granularita je nízka súbežnosť, ale režijné náklady sú malé
SQL Server podporuje uzamknutie granularity pre riadky, stránky, kľúče, rozsahy kľúčov, indexy, tabuľky alebo databázy
Popis zdroja
Identifikátor riadku RID. Používa sa na uzamknutie radu v stole jednotlivo.
Kľúčový riadok v indexe. Používa sa na ochranu rozsahu kľúčov v serializovateľných transakciách.
8 kilobajtov (KB) dátových stránok alebo indexových stránok.
Rozšírený disk Súbor ôsmich susediacich dátových strán alebo indexových strán.
Tabuľka Celá tabuľka vrátane všetkých údajov a indexov.
DB databáza.
4. Dĺžka doby uzamknutia
Dĺžka držby zámku je doba potrebná na ochranu zdroja na požadovanej úrovni.
Čas držania zdieľaného zámku používaného na ochranu čítacích operácií závisí od úrovne izolácie transakcie. Pri predvolenej úrovni izolácie transakcií v READ COMMITTED je zdieľaný zámok ovládaný len počas trvania čítanej stránky. Pri skenovaní sa zámok neuvoľní, kým nie je zachytený na ďalšej stránke v skene. Ak zadáte výzvu HOLDLOCK alebo nastavíte úroveň izolácie transakcie na REPEATABLE READ alebo SERIALIZABLE, zámok sa neuvoľní, kým transakcia neskončí.
V závislosti od nastavenia možnosti súbežnosti pre kurzor môže kurzor získať zámok na zvitok v zdieľanom režime na ochranu extraktu. Keď je potrebný scroll lock, scroll lock sa uvoľní až pri ďalšom extrahovaní alebo zatvorení kurzora, podľa toho, čo nastane skôr. Ak však špecifikujete HOLDLOCK, scroll lock sa uvoľní až na konci transakcie.
Exkluzívny zámok použitý na ochranu aktualizácie nebude uvoľnený až do konca transakcie.
Ak sa spojenie pokúsi získať zámok, ktorý je v konflikte so zámkom ovládaným iným spojením, spojenie, ktoré sa snaží získať zámok, bude zablokované až dovtedy:
Konfliktný zámok sa uvoľní a spojenie získa požadovaný zámok.
Časový limit pripojenia vypršal. V predvolenom nastavení neexistuje časový limit, ale niektoré aplikácie nastavujú časové limity, aby zabránili neobmedzenému čakaniu
Päť prispôsobení zámkov v SQL Serveri
1 Riešenie patových situácií a nastavenie priorít zablokovania
Patová situácia je nekonečné čakanie spôsobené viacerými používateľmi, ktorí žiadajú o rôzne bloky, pretože žiadateľ má časť blokovacieho práva a čaká na čiastočné zablokovanie patriace iným používateľom
SET DEADLOCK_PRIORITY môžete použiť na ovládanie, ako relácia reaguje v prípade zablokovania. Ak oba procesy zablokujú dáta a každý proces nemôže uvoľniť svoj vlastný zámok, kým druhý proces neuvoľní svoj vlastný zámok, vzniká situácia patového zablokovania.
2 Riešiť časové limity a nastaviť dĺžku uzamknutia.
@@LOCK_TIMEOUT Vráti aktuálne nastavenie timeoutu pre aktuálnu reláciu v milisekundách
Nastavenie SET LOCK_TIMEOUT umožňuje aplikácii nastaviť maximálny čas, počas ktorého príkaz čaká na blokovanie zdroja. Keď je čakacia doba príkazu väčšia ako nastavenie LOCK_TIMEOUT, systém automaticky zruší blokovací príkaz a vráti aplikácii chybové hlásenie 1222, že doba vypršania lehoty na uzamknutie bola prekročená
príklad
V nasledujúcom príklade je doba vypršania zámku nastavená na 1 800 milisekúnd.
NASTAVTE LOCK_TIMEOUT1800
3) Nastavte úroveň izolácie transakcie.
4) Používať nápovedy na uzamknutie na úrovni tabuľky pre príkazy SELECT, INSERT, UPDATE a DELETE.
5) Nakonfigurovať granularitu uzamykania indexu
Môžete použiť sp_indexoption systémové uložené procedúry na nastavenie granularity zámku pre indexovanie
6. Zobraziť informácie o zámku
1 Vykonať EXEC SP_LOCK nahlásiť informácie o zámku
2 Stlačte Ctrl + 2 v analyzátore dotazov, aby ste videli informácie o zámku
7. Opatrenia pri používaní
Ako sa vyhnúť patovým situáciám
1. Pri používaní transakcií sa snažte skrátiť logický proces spracovania transakcií a odosielať alebo zrušovať transakcie skôr.
2 Nastavte parameter zablokovania na rozumný rozsah, napríklad: 3 minúty - 10 minút; Po uplynutí času sa operácia automaticky ukončí, aby sa zabránilo zaseknutiu procesu;
3. Optimalizovať program, skontrolovať a vyhnúť sa fenoménu patovej situácie;
4. Dôkladne otestovať všetky skripty a SP pred presnou verziou.
5 Všetky SP musia mať spracovanie chýb (cez @error)
6 Nemeňte predvolenú úroveň SQL SERVER transakcií. Nútené zamykanie sa neodporúča
Vyriešiť problém Ako zamknúť databázu riadkových tabuliek
8. Niekoľko otázok o zámkoch
1 Ako zamknúť riadok stola
NASTAVIŤ ÚROVEŇ TRANSACTIONISOLATION READUNCOMMITTED
VYBERTE *Z tabuľky ROWLOCK, kde id = 1
2 Zamknúť tabuľku v databáze
VYBERTE *Z TABUĽKY WITH( HOLDLOCK )
Lock statement:
sybase:
Aktualizácia tabuľky kol1 = stĺpec1, kde 1 = 0 ;
MSSQL:
Vyberte col1z tabuľky (tablockx), kde 1= 0 ;
oracle:
STÔL S UZAMYKATEĽNÝM STOLOM V EXKLUZÍVNOM REŽIME ;
Po uzamknutí zámku ho nikto iný nemôže ovládať, kým ho zamknutý používateľ neoblokuje, a potom sa odomkne commit alebo rollback
Niekoľko príkladov vám pomôže prehĺbiť váš dojem
Nastavte stôl1(A,B,C)
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3
1) Exkluzívny zámok
Vytvorte dve nové spojenia
Vykonajte nasledujúci príkaz v prvom spojení
Begin tran
Aktualizácia tabuľky1
set A= ' aa '
kde B= ' b2 '
počkaj na oneskorenie 00:00:30' --počkaj 30 sekúnd
commit tran
Vykonajte nasledujúci príkaz v druhom spojení
Begin tran
Vyberte *z tabuľky1
kde B= ' b2 '
commit tran
Ak sa vyššie uvedené dva príkazy vykonávajú súčasne, dotaz na výber musí počkať na vykonanie aktualizácie, teda počkať 30 sekúnd
2) Zdieľaný zámok
Vykonajte nasledujúci príkaz v prvom spojení
Begin tran
vyberte *z tabuľky1 holdlock - Holdlock sa umelo pridá k zámku
kde B= ' b2 '
počkaj na oneskorenie 00:00:30' --počkaj 30 sekúnd
commit tran
Vykonajte nasledujúci príkaz v druhom spojení
Begin tran
vyberte A,C z tabuľky1
kde B= ' b2 '
Aktualizácia tabuľky1
set A= ' aa '
kde B= ' b2 '
commit tran
Ak sa vyššie uvedené dva príkazy vykonávajú súčasne, môže sa vykonať výberový dotaz v druhom spojení
Aktualizácia musí počkať na prvú transakciu, aby uvoľnila zdieľaný zámok a premenila ho na exkluzívny zámok, aby mohla byť vykonaná, teda počkať 30 sekúnd
3) Patová situácia
Pridaná tabuľka2(D,E)
D E
d1 e1
d2 e2
Vykonajte nasledujúci príkaz v prvom spojení
Begin tran
Aktualizácia tabuľky1
set A= ' aa '
kde B= ' b2 '
čakaj na oneskorenie 00:00:30'
Tabuľka aktualizácie2
set D= ' d5 '
kde E= ' e1 '
commit tran
Vykonajte nasledujúci príkaz v druhom spojení
Begin tran
Tabuľka aktualizácie2
set D= ' d5 '
kde E= ' e1 '
čakaj na oneskorenie 00:00:10'
Aktualizácia tabuľky1
set A= ' aa '
kde B= ' b2 '
commit tran
Zároveň systém detekuje patovú situáciu a proces preruší
Na doplnenie:
Nápovedy na zamykanie na úrovni tabuliek podporované SQL Server 2000
HOLDLOCK drží zdieľaný zámok až do dokončenia celej transakcie a mal by byť uvoľnený hneď, ako uzamknutý objekt nie je potrebný, čo zodpovedá úrovni izolácie transakcie SERIALIZABLE
Príkaz NOLOCK sa vykonáva bez vydania zdieľaného zámku, čo umožňuje nečisté čítania, čo zodpovedá úrovni izolácie READ UNCOMMITTED transakcie
PAGLOCK používa viacero zámkov stránok, kde sa používa jeden zámok tabuľky
READPAST umožňuje SQL serveru preskočiť akékoľvek uzamknuté riadky a vykonávať transakcie, a pre úrovne izolácie transakcií READ uncommitted preskakuje len RID zámky, nie zámky stránok, zón a tabuliek
ROWLOCK vynucuje používanie rowlockov
TABLOCKX vynucuje použitie exkluzívneho zámku na úrovni tabuľky, ktorý zabraňuje akejkoľvek inej transakcii používať tabuľku počas transakcie
UPLOCK núti používať aktualizácie pri čítaní tabuľky bez zdieľaného zámku
Zámok aplikácie:
Aplikačný zámok je zámok generovaný klientským kódom, nie zámok vytvorený samotným SQL Serverom
Dva procesy na spracovanie aplikačných zámkov
sp_getapplock Uzamknúť aplikačné zdroje
sp_releaseapplock Odomknúť aplikačné zdroje
Poznámka: Rozdiel medzi uzamknutím tabuľky v databáze
SELECT *FROM TABLE WITH( HOLDLOCK ) Ostatné transakcie dokážu tabuľku čítať, ale nemôžu ju aktualizovať a mazať
VYBRAŤ *Z TABUĽKY S(TABLOCKX) Ostatné transakcie nemôžu tabuľku čítať, aktualizovať ani mazať
|
Predchádzajúci:Neexistoval žiadny koncový bod na http://localhost:111/xxx.svc, ktorý by c...Budúci:SQL zámky NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
