|
|
Közzétéve 2016. 07. 20. 12:37:53
|
|
|
A zsilipek áttekintése
1. Miért vezetik be a zárakat
Ha több felhasználó egyszerre végez műveleteket az adatbázison, az alábbi adateltérések jelentkeznek:
Hiányzó frissítések
Két felhasználó, A és B, ugyanazt az adatokat olvasza és módosítja azokat, és az egyik felhasználó módosításának eredménye tönkreteszi a másik módosítás eredményét, például a jegyfoglalási rendszert
Piszkos olvasmány
Az A felhasználó módosítja az adatokat, majd B felhasználó kiolvassa az adatokat, de az A felhasználó valamiért lemondja az adatok módosítását, és az adatok visszatérnek eredeti értékére
Ne olvass újra
A felhasználó olvassa az adatokat, majd B felhasználó olvasza az adatokat és módosítja azokat
Az egyidejű szabályozás fő módszere a blokkolás, amely azt szolgálja, hogy a felhasználóknak bizonyos műveleteket végezzenek egy időre az adatkonkonzisztenciák elkerülése érdekében
2. A zsilip osztályozása
Két zsilipkategóriára osztható:
1 . Az adatbázis szempontjából: kizárólagos zárokra (azaz exkluzív zárakra), megosztott zárokra és frissítő zárokra oszlik
Az MS - SQL Server a következő erőforrás-zárolási mintákat használja.
Zár mód leírása
A megosztás(ok) olyan műveletekhez használják, amelyek nem változtatnak vagy frissítenek adatokat (csak olvasható műveletek), például a SELECT utasításokat.
Frissítés (U) a frissíthető erőforrásokban használatos. Megakadályozza a gyakori holtpontokat, amikor több ülést olvasnak, lezárnak, és esetleg erőforrás-frissítés is előfordulhat.
Az exkluzív (X) adatmódosítási műveletekhez használható, mint például INSERT, UPDATE vagy DELETE. Győződj meg róla, hogy több frissítés ne végezzenek egyszerre ugyanazon erőforráson.
A szándékzárak hierarchiáját használják a zárak hierarchiájának kialakítására. A szándékzárak típusai: Szándék Megosztott (IS), Szándék Kizárólagú (IX) és Szándék Kizárólagú (SIX).
A sémazárakat akkor használják, amikor olyan műveleteket végeznek, amelyek a táblázat sémától függenek. A sémák záratípusai: séma módosítás (Sch -M) és séma stabilitás (Sch -S).
A tömeges frissítéseket (BU-k) akkor használják, amikor nagy mennyiségű adatot másolnak egy táblába, és TABLOCK tippet adnak meg.
Közös zsilipek
A megosztott(ok) zár lehetővé teszi, hogy az egyidejű tranzakciók olvassák (SELECT) egy erőforrást. Ha egy erőforráson közös (S) zár létezik, egyetlen más tranzakció sem tudja módosítani az adatokat. Engedd fel a megosztott (S) zárolást az erőforráson, amint az adatokat elolvasták, kivéve, ha a tranzakciós izolációs szint ismételhető vagy magasabb szintre van állítva, vagy a megosztott (S) zár a tranzakció élettartama alatt zár utalással marad.
Frissítés zárolása
Frissítés (U) zárak megelőzik a szokásos holtpontokat. Egy tipikus frissítési minta egy tranzakcióból áll, amely egy rekordot olvas, megosztott (S) zárolást kap egy erőforráshoz (oldal vagy sor), majd módosít egy sort, ami megköveteli, hogy a zárolást exkluzív (X) zárlássá alakítsák. Ha két tranzakció egy megosztott mód zárolást szerez egy erőforráson, majd egyszerre próbálja frissíteni az adatokat, az egyik tranzakció megpróbálja átalakítani a zárat exkluzív (X) zárolásra. A megosztott módból az exkluzív zárolásra való átmenetnek egy ideig kell várnia, mert egy tranzakció exkluzív zárolása nem kompatibilis egy másik tranzakció megosztott mód zárolásához; Zár várakozás következik. A második tranzakció exkluzív (X) zárolást próbál megszerezni egy frissítéshez. Zsákutcolás akkor alakul ki, mert mindkét tranzakciót exkluzív (X) zárlattá alakítják át, és minden tranzakció megvárja, hogy a másik tranzakció feloldja a megosztott mód zárolást.
Ennek a lehetséges holtpontproblémának a megelőzése érdekében használj frissített (U) zárolást. Egyszerre csak egy tranzakció kaphat frissített (U) zárolást egy erőforráshoz. Ha a tranzakció módosítja az erőforrást, a frissítés (U) zár exkluzív (X) zárolássá alakul. Ellenkező esetben a zsilit megosztott zsilipmé alakítják át.
Exkluzív zsilpek
Az exkluzív (X) zárak megakadályozzák, hogy az egyidejű tranzakciók hozzáférjenek az erőforrásokhoz. Más tranzakciók nem tudják olvasni vagy módosítani az exkluzív (X) zárolással zárt adatokat.
Szándékzár
A szándékzár azt jelenti, hogy az SQL Servernek megosztott (S) vagy exkluzív (X) zárolást kell szereznie a hierarchia egyes mögöttes erőforrásaira. Például egy megosztás-szándék zár, amely a táblázat szintjén helyezkedik el, azt jelzi, hogy a tranzakció egy oldalon vagy sorban kíván megosztást (S) zárolni a táblázatban. A szándékzár beállítása a tábla szintjén megakadályozza, hogy egy másik tranzakció később exkluzív (X) zárolást szerezzen azon a táblázaton, amely tartalmazza az adott oldalt. A szándékzárak javíthatják a teljesítményt, mert az SQL Server csak a tábla szintjén ellenőrzi a szándékzárat, hogy megállapítsa, biztonságosan tud-e egy tranzakció zárt rögzíteni az adott táblán. Ahelyett, hogy ellenőriznénk a táblázat minden során vagy oldalán lévő zárolásokat, hogy megállapítsák, egy tranzakció képes lezárni az egész táblát.
A szándékzárak közé tartozik a Szándékmegosztás (IS), a Szándék Kizárólag (IX) és a Szándék Kizárólagú Megosztás (SIX).
Zár mód leírása
A Szándékmegosztás (IS) azt jelzi, hogy a tranzakció szándéka az olvasási hierarchia egyik alaperőforrása, nem az összes, az S-zárakat helyezve minden erőforrásra.
A Intent Exclusive (IX) azt jelzi, hogy a tranzakció célja, hogy módosítsa a hierarchia néhány alapvető erőforrását azzal, hogy minden erőforrásra X-lockot helyezzen. Az IX az IS egy felhalmaza.
Az exkluzív megosztás szándékkal (SIX) azt jelzi, hogy a tranzakció célja, hogy az összes hierarchia mögöttes erőforrást elolvassa, és módosítsa az alapforrások néhányát, de nem az összes erőforrást, IX zárak helyezésével minden erőforrásra. Engedélyezzük az IS zárolásokat a felső szintű erőforrásokon. Például egy tábla HAT zára egy HAT-zárat helyez az asztalra (ami lehetővé teszi az egyidejű IS zárolásokat), és egy IX zárat a jelenleg módosított oldalon (egy X zárat a módosított soron). Míg minden erőforrásnak csak egy SIX zára lehet egy ideig, hogy megakadályozza más tranzakciók frissítését, más tranzakciók képesek a hierarchiában lévő alapforrásokat táblázatszintű IS zárak megszerzésével.
Exkluzív zárolás: Csak a zárolási műveletet végző program használhatja azt, és más műveletek nem fogadnak el rajta. Amikor adatfrissítési parancsot hajtasz végre, az SQL Server automatikusan exkluzív zárolást használ. Ha egy tárgyon más zárak léteznek, nem lehet kizárólagos zárat hozzáadni hozzá.
Megosztott zárolás: A közös zár által lezárt erőforrást más felhasználók is olvashatják, de más felhasználók nem módosíthatják azt.
Frissítési zárolás: Amikor az SQL Server készen áll az adatok frissítésére, először lezárja az adatobjektumot, így az adat nem módosítható, de olvasható. Amikor az SQL Server úgy dönt, hogy adatokat szeretne frissíteni, automatikusan kiváltja a frissítési zárat egy exkluzív zárolásra, és nem tud frissítő zárat hozzáadni, ha más zárak léteznek az objektumon.
2 . A programozó szemszögéből: optimista zárra és pesszimista zárra oszlik.
Optimism Lock: Teljes mértékben az adatbázisra támaszkodik a zár működésének kezeléséhez.
Pesszimista zárak: A programozók maguk kezelik az adatok vagy objektumok zárkezelését.
Az MS - SQLSERVER zárokat használ pesszimista egyidejű vezérlés megvalósítására több felhasználó között, akik egyszerre végeznek módosításokat az adatbázisban
3. A zár részecskemérete
A zár granularitása a blokkolt célpont mérete, a kis blokkoló granularitás magas egyidejű szintű, de a túlterhelés nagy, a nagy blokkoló granularitás pedig alacsony egyidejű egyidejű mennyiség, de a túlterhelés kicsi
Az SQL Server támogatja a zár granularitását sorok, oldalak, kulcsok, kulcstartományok, indexek, táblák vagy adatbázisok esetén
Forrásleírás
RID sor azonosító. Egyénileg rögzítettem egy sor asztalon.
Kulcssorban zár az indexben. A kulcsok tartományának védelmére használják a serializálható tranzakciókban.
8 kilobájt (KB) adatlapot vagy indexoldalakat.
Kiterjesztett lemez Egy nyolc egymás melletti adatlapból vagy indexoldalból álló halmaz.
Táblázat Az egész táblázat, beleértve az összes adatot és indexet.
DB adatbázis.
4. A zárolási idő hossza
A zár tartásának időtartama az a időtartam, amely szükséges az erőforrás védelméhez a kívánt szinten.
A megosztott zár tárolási ideje, amelyet az olvasási műveletek védelmére használnak, a tranzakciós izolációs szinttől függ. Az alapértelmezett tranzakciós izolációs szint, a READ COMMITTED, a megosztott zárolást csak az olvasott oldal időtartama alatt szabályozzák. A szkennelés során a zár csak akkor szabadul fel, amikor a zár a következő oldalon kerül fel. Ha megadsz egy HOLDLOCK promptot, vagy a tranzakciós izolációs szintet REPEATABLE READ vagy SERIALIZABLE -re állítod, a zár csak a tranzakció végéig oldódik fel.
A kurzor konparziációs opciójától függően a kurzor megosztott módban egy görgető zárat kaphat, hogy megvédje a kivonatot. Ha tekergetési zárra van szükség, a görgető zárat csak akkor engedik el, amikor a kurzort legközelebb kihúzzák vagy bezárják, attól függően, melyik előbb történik. Viszont ha megadsz egy HOLDLOCK-ot, a görgető zár csak a tranzakció végén oldódik fel.
Az exkluzív zár, amelyet a frissítés védelmére használnak, csak a tranzakció végén kerül nyilvánosságra.
Ha egy kapcsolat megpróbál olyan zárat szerezni, amely ütközik egy másik kapcsolat által irányított zárral, a zárat megszerező kapcsolat blokkolódik, amíg az alábbiak:
A konfliktus zár feloldódik, és a kapcsolat megszerzi a kért zárat.
A kapcsolat időkorlátja lejárt. Alapértelmezés szerint nincs időtúli intervallum, de néhány alkalmazás időtúli intervallumokat állít be, hogy megakadályozza a határozatlan várakozást
Öt zár testreszabása SQL Serverben
1 Kezeld a holthelyzeteket és állítsd be a holthelyzet prioritásait
A holtpont az a végtelen várakozás, amelyet több felhasználó különböző blokkokra való jelentkezése okoz, mert a jelentkezőnek van egy része a blokkolási jognak, és várja a részleges blokkolást más felhasználók tulajdonában
A SET DEADLOCK_PRIORITY segítségével szabályozhatod, hogyan reagál a szekció holthelyzet esetén. Ha mindkét folyamat zárja az adatokat, és egyik folyamat nem engedheti fel a saját zárját, amíg a másik folyamat nem engedi el a saját zárolását, holthelyzet alakul ki.
2 Kezeld az időkéréseket és állítsd be a zárolási időkorlátokat.
@@LOCK_TIMEOUT Visszaadja az aktuális zárolás időtúli beállítását ezredmásodpercekben
A SET LOCK_TIMEOUT beállítás lehetővé teszi az alkalmazás számára, hogy meghatározza, mennyi időt vár az utasítás a forrás blokkolására. Ha az utasítás várakozási ideje meghaladja az LOCK_TIMEOUT beállítást, a rendszer automatikusan töröli a blokkoló utasítást, és 1222-es hibaüzenetet küld vissza az alkalmazásnak, amely szerint a zárolási kérés időlezárási időszakát túllépték
példa
A következő példában a zár időkorlát időszaka 1 800 milliszekundumra van állítva.
SŐT LOCK_TIMEOUT1800
3) Állítsuk be a tranzakciós izolációs szintet.
4) Táblaszintű zárolási tippeket használj a SELECT, INSERT, UPDATE és DELETE utasításokhoz.
5) Konfiguráld az index zárási granularitását
Használhatod sp_indexoption rendszerben tárolt eljárásokat, hogy beállítsd az indexeléshez szükséges zár granularitását
6. Tekintse meg a zsilip adatait
1 Végrehajtson EXEC-et SP_LOCK jelentést kell a zárról
2 Nyomd meg a Ctrl + 2 gombot a lekérdezési analizátorban, hogy lásd a zár adatait
7. Használati óvintézkedések
Hogyan kerüljük el a patthelyzeteket
1. Tranzakciók használatakor próbáld meg lerövidíteni a logikai feldolgozási folyamatot, és korán küldd be vagy visszafordítsd a tranzakciókat.
2 Állítsuk be a holtzárás időkorlátot ésszerű tartományra, például: 3 perc - 10 perc; Később a művelet automatikusan elhagyja, hogy elkerüljék a folyamat akadását;
3. Optimalizálni a programot, ellenőrizni és elkerülni a holtpont jelenségét;
4. Teszteld alaposan az összes szkriptet és SP-t a pontos verzió előtt.
5 Minden SP-nek hibákezeléssel kell rendelkeznie (@error keresztül)
6 Ne módosítsd az SQL SERVER tranzakciók alapértelmezett szintjét. A kényszerzárás nem ajánlott
Probléma megoldása Hogyan lehet sortábla adatbázist zárolni
8. Több kérdés a zárokkal kapcsolatban
1 Hogyan zárjunk egy asztalsort
ÁLLÍTSA BE A TRANZAKCIÓIZOLÁCIÓS SZINTET READUNCOMMITTED
VÁLASSZ *FROM táblázatból ROWLOCK, ahol azonosító = 1
2 Táblázat zárolása az adatbázisban
VÁLASSZ *TABLE WITH( HOLDLOCK )
Zár nyilatkozat:
sybase:
Frissítési táblázat halmaz: col1 = col1, ahol 1= 0 ;
MSSQL:
Válassz col1-et a táblázatból (tablockx), ahol 1= 0 ;
oracle:
LOCK TABLE TABLE EXEXCLUSIVE MÓDBAN ;
Miután a zárat zárolták, senki más nem használhatja, amíg a zárolt felhasználó nem nyitja fel, és a zárat comcominggal vagy rollback-szel nyílik meg
Néhány példa segít mélyíteni a benyomásodat
Beállítási táblázat1(A,B,C)
A B C
a1 b1 c1
A2 B2 C2
A3 B3 C3
1) Exkluzív zár
Két új kapcsolat létrehozása
Az első kapcsolatban hajtsuk végre a következő utasítást
begin tran
Frissítés táblázat1
halmaz A= ' aa '
ahol B= ' b2 '
Várj késésre' 00:00:30' --várj 30 másodpercet
commit tran
A következő utasítást hajtsd végre a második kapcsolatban
begin tran
Válassz *az 1. táblázatból
ahol B= ' b2 '
commit tran
Ha a fenti két utasítást egyszerre hajtják végre, a select query-nek meg kell várnia a frissítés végrehajtására, vagyis 30 másodpercet kell várnia
2) Közös zár
Az első kapcsolatban hajtsuk végre a következő utasítást
begin tran
válassz *from table1 holdlock - A fogózárat mesterségesen hozzáadják a zárhoz
ahol B= ' b2 '
Várj késésre' 00:00:30' --várj 30 másodpercet
commit tran
A következő utasítást hajtsd végre a második kapcsolatban
begin tran
válassz A,C a táblázat1-ből
ahol B= ' b2 '
Frissítés táblázat1
halmaz A= ' aa '
ahol B= ' b2 '
commit tran
Ha a fenti két állítást egyszerre hajtják végre, a második kapcsolatban végrehajtható select lekérdezés
A frissítésnek meg kell várnia, amíg az első tranzakció feloldja a közös zárolást, és kizárólagos zárolásra alakítja azt, mielőtt végrehajtható legyen, vagyis 30 másodpercet kell várnia
3) Zsákhelyzet
Hozzáadva a table2(D,E)
D E
d1 e1
d2 e2
Az első kapcsolatban hajtsuk végre a következő utasítást
begin tran
Frissítés táblázat1
halmaz A= ' aa '
ahol B= ' b2 '
várj késésre' 00:00:30'
Frissítés táblázat 2
halmaz D= ' d5 '
ahol E= ' e1 '
commit tran
A következő utasítást hajtsd végre a második kapcsolatban
begin tran
Frissítés táblázat 2
halmaz D= ' d5 '
ahol E= ' e1 '
késésre várni 00:00:10'
Frissítés táblázat1
halmaz A= ' aa '
ahol B= ' b2 '
commit tran
Ugyanakkor a rendszer érzékeli a holtpontot és megszakítja a folyamatot
Hozzátéve:
Az SQL Server 2000 által támogatott táblázatszintű zárolási tippek
A HOLDLOCK a megosztott zárolást tartja a teljes tranzakció befejezéséig, és akkor szabadon kell engedni, amint a zárolt objektumra nincs szükség, ami megegyezik a SERIALIZÁLHATÓ tranzakciós izolációs szinttel
A NOLOCK utasítást megosztott zár nélkül hajtják végre, így piszkos olvasásokat engednek le, ami megegyezik a READ UNCOMMITTED tranzakciós izolációs szinttel
A PAGLOCK több oldalzárat használ, ahol egy táblazárat használnak
A READPAST lehetővé teszi, hogy a sql szerver kihagyja a zárt sorokat és végrehajtson tranzakciókat, míg a READ UNCOMMITTED tranzakciós izolációs szinteknél csak a RID zárolásokat hagyja ki, nem az oldal-, zóna- és táblazárakat
A ROWLOCK érvényesíti a sorzárak használatát
A TABLOCKX exkluzív táblázatszintű zárolást kényszerít ki, amely megakadályozza, hogy bármely más tranzakció használja a táblát a tranzakció során
Az UPLOCK kényszeríti a frissítések használatát, amikor egy táblázatot olvasnak megosztott zár nélkül
App Lock:
Az alkalmazászár egy klienskód által generált zár, nem pedig az SQL Server által generált zár
Két folyamat az alkalmazászárak kezelésére
sp_getapplock Alkalmazás erőforrásainak zárolása
sp_releaseapplock Az alkalmazás erőforrásainak feloldása
Megjegyzés: A különbség egy táblázat adatbázisban történő zárolása között
SELECT *FROM table WITH( HOLDLOCK ) Más tranzakciók olvashatják a táblát, de nem tudják frissíteni és törölni
SELECT *FROM table WITH(TABLOCKX) Más tranzakciók nem tudják olvasni, frissíteni és törölni a táblát
|
Előző:Nem volt végállomás hallgatás http://localhost:111/xxx.svc c...Következő:SQL zárolja NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
