Optimalizált zárolás

A következőkre vonatkozik:Azure SQL DatabaseSQL Database a Microsoft Fabricban

Ez a cikk az optimalizált zárolást mutatja be, egy adatbázismotor-képességet, amely továbbfejlesztett tranzakciózárolási mechanizmust kínál a zárolási memóriahasználat csökkentésére és az egyidejű tranzakciók blokkolására.

Mi az optimalizált zárolás?

Az optimalizált zárolás segít csökkenteni a zárolási memóriát, mivel nagyon kevés zárolást tartanak még nagy tranzakciók esetén is. Az optimalizált zárolás emellett a zárolás eszkalációit is elkerüli. Ez több egyidejű hozzáférést tesz lehetővé a táblához.

Az optimalizált zárolás két elsődleges összetevőből áll: tranzakcióazonosító (TID) zárolás és zárolás minősítés után (LAQ).

• A tranzakcióazonosító (TID) egy tranzakció egyedi azonosítója. Minden sor az azt módosító utolsó TID-vel van megjelölve. A rendszer a kulcs- vagy sorazonosítók esetleges zárolása helyett egyetlen zárolást használ a TID-n. További információért lásd: tranzakcióazonosító (TID) zárolása.
• A minősítés utáni zárolás (LAQ) egy olyan optimalizálás, amely a sor legújabb véglegesített verziójával értékeli ki a lekérdezési predikátumokat zárolás beszerzése nélkül, ezáltal javítva az egyidejűséget. További információ: Minősítés utáni zárolás (LAQ).

Például:

• Optimalizált zárolás nélkül a tábla 1000 sorának frissítése 1000 kizárólagos (X) sorzárolást igényelhet a tranzakció végéig.
• Az optimalizált zárolás esetén előfordulhat, hogy egy tábla 1000 sorának frissítése 1000 X sorzárolást igényel, de az egyes zárolások az egyes sorok frissítésekor azonnal felszabadulnak, és a tranzakció végéig csak egy TID-zárolás van tárolva. Mivel a zárolások gyorsan szabadulnak fel, a zárolási memória kihasználtsága csökken, és zárolás eszkalálási sokkal kisebb valószínűséggel fordul elő, ami növeli a számítási feladatok egyidejűségét.

Elérhetőség

Az optimalizált zárolás csak az Azure SQL Database-ben és SQL Database-ben érhető el a Microsoft Fabric minden szolgáltatási szinten és számítási méretben.

Az optimalizált zárolás jelenleg nem érhető el a felügyelt Azure SQL-példányban vagy az SQL Serverben.

Engedélyezve van az optimalizált zárolás?

Az optimalizált zárolás felhasználói adatbázisonként engedélyezve van. Csatlakozzon az adatbázishoz, majd az alábbi lekérdezés használatával ellenőrizze, hogy engedélyezve van-e az optimalizált zárolás:

SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

Az optimalizált zárolási rendszerek más adatbázis-funkciókra épülnek.

• Az optimalizált zároláshoz engedélyezni kell gyorsított adatbázis-helyreállítást (ADR) az adatbázisban.
• Az optimalizált zárolás legnagyobb előnye érdekében az olvasott véglegesített pillanatkép-elkülönítést (RCSI) engedélyezni kell az adatbázishoz. Az optimalizált zárolás LAQ összetevője csak akkor van érvényben, ha az RCSI engedélyezve van.

Az ADR és az RCSI alapértelmezés szerint engedélyezve van az Azure SQL Database-ben. Ha ellenőrizni szeretné, hogy ezek a beállítások engedélyezve vannak-e az aktuális adatbázishoz, csatlakozzon az adatbázishoz, és futtassa a következő T-SQL-lekérdezést:

SELECT name,
       is_read_committed_snapshot_on,
       is_accelerated_database_recovery_on
FROM sys.databases
WHERE name = DB_NAME();

Zárolás áttekintése

Ez a viselkedés rövid összefoglalása, ha az optimalizált zárolás nincs engedélyezve. További információkért tekintse át a tranzakciózárolási és sorverziózás útmutatót.

Az adatbázismotorban a zárolás olyan mechanizmus, amely megakadályozza, hogy több tranzakció egyszerre frissítse ugyanazokat az adatokat annak érdekében, hogy garantálja a tranzakciók ACID tulajdonságait.

Amikor egy tranzakciónak módosítania kell az adatokat, zárolást kér az adatokon. A zárolás akkor adható meg, ha az adatokon nincs más ütköző zárolás, és a tranzakció folytathatja a módosítást. Ha az adatokon egy másik ütköző zárolás van tárolva, a tranzakciónak meg kell várnia a zárolás feloldását, mielőtt továbbléphet.

Ha több tranzakció egyszerre próbál hozzáférni ugyanahhoz az adathoz, az adatbázismotornak meg kell oldania az egyidejű olvasásokkal és írásokkal járó esetlegesen összetett ütközéseket. A zárolás az egyik olyan mechanizmus, amellyel az adatbázismotor biztosítani tudja az ANSI SQL-tranzakció szemantikáját az elkülönítési szinteknél . Bár az adatbázisok zárolása alapvető fontosságú, a csökkentett egyidejűség, a holtpontok, az összetettség és a zárolási többletterhelés hatással lehet a teljesítményre és a méretezhetőségre.

Tranzakcióazonosító (TID) zárolása

Ha sorverziós alapú elkülönítési szintek használatban vannak, vagy ha az ADR engedélyezve van, az adatbázis minden sora tartalmaz egy tranzakcióazonosítót (TID). Ez a TID megmarad a lemezen. Minden tranzakció, amely egy sort módosít, a sort azonosítójával (TID) látja el.

A TID zárolása esetén a sor kulcsának zárolása helyett a sor TID-jén történik a zárolás. A módosító tranzakció X zárolást tartalmaz a TID-n. Más tranzakciók S zárolást kapnak a TID-n, amíg az első tranzakció befejeződik. A TID zárolásával az oldal- és sorzárolások továbbra is módosításra kerülnek, de az egyes sorok módosítása után minden egyes oldal- és sorzárolás feloldva lesz. A tranzakció végéig az egyetlen fenntartott zárolás a TID-erőforrás egyetlen X zárolása, amely több oldal- és sorzár helyett van alkalmazva.

Tekintse meg az alábbi példát, amely az aktuális munkamenet zárolásait mutatja be, miközben egy írási tranzakció aktív:

/* Is optimized locking is enabled? */
SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

CREATE TABLE t0
(
a int PRIMARY KEY,
b int NULL
);

INSERT INTO t0 VALUES (1,10),(2,20),(3,30);
GO

BEGIN TRANSACTION;

UPDATE t0
SET b = b + 10;

SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
      AND
      resource_type IN ('PAGE','RID','KEY','XACT');

COMMIT TRANSACTION;
GO

DROP TABLE IF EXISTS t0;

Ha az optimalizált zárolás engedélyezve van, a kérelem csak egyetlen X zárolást tárol a XACT (tranzakciós) erőforráson.

Ha az optimalizált zárolás nincs engedélyezve, ugyanaz a kérés négy zárolást tartalmaz: egy IX (kizárólagos szándék) zárolást a sorokat tartalmazó oldalon, és három X kulcszárolást minden sorban:

A sys.dm_tran_locks dinamikus felügyeleti nézet (DMV) hasznos a zárolási problémák vizsgálatához vagy hibaelhárításához, például az optimalizált zárolás működés közbeni megfigyeléséhez.

Minősítés utáni zárolás (LAQ)

A TID-infrastruktúrára építve az optimalizált zárolás megváltoztatja a DML-utasítások, például INSERT, UPDATEés DELETE zárolások beszerzését.

Optimalizált zárolás nélkül a lekérdezési predikátumok sorról sorra vannak bejelölve a vizsgálat során, és először egy frissítési (U) sorzárolást végeznek. Ha a predikátum teljesül, a sor frissítése előtt egy kizárólagos (X) sorzárolás történik, amely a tranzakció végéig tart.

Optimalizált zárolás esetén, és ha a READ COMMITTED pillanatkép-elkülönítési szint (RCSI) engedélyezve van, a predikátumok optimista módon ellenőrizhetők a sor legújabb véglegesített verzióján zárolás nélkül. Ha a predikátum nem felel meg az elvárásoknak, a lekérdezés az átolvasás következő sorára lép. Ha a predikátum megfelel, a sor frissítéséhez X sorzárolás történik.

Más szóval a zárolás a sor minősítése után történik meg módosítás céljából. A X sorzár a sorfrissítés befejeződése után, a tranzakció vége előtt szabadul fel.

Mivel a predikátum kiértékelése zárolások beszerzése nélkül történik, a különböző sorokat módosító egyidejű lekérdezések nem blokkolják egymást.

Például:

CREATE TABLE t1
(
a int NOT NULL,
b int NULL
);

INSERT INTO t1
VALUES (1,10),(2,20),(3,30);
GO

Optimalizált zárolás nélkül a 2. munkamenet le van blokkolva, mert az 1. munkamenet U zárolással tartja a sort, amelyre a 2. munkamenetnek szüksége van a frissítéshez. Optimalizált zárolás esetén azonban a 2. munkamenet nem lesz letiltva, mert U zárolások nincsenek meg, és mivel az 1. sor legújabb véglegesített verziójában a a oszlop értéke 1, ami nem felel meg a 2. munkamenet predikátumának.

Az LAQ optimista módon történik azon feltételezés alapján, hogy a sor nem módosul a predikátum ellenőrzése után. Ha a predikátum teljesül, és a predikátum ellenőrzése után a sor nem módosul, az aktuális tranzakció módosítja.

Mivel U zárolások nincsenek megadva, egy egyidejű tranzakció módosíthatja a sort a predikátum kiértékelése után. Ha egy aktív tranzakció X TID-zárolást tartalmaz a sorban, az adatbázismotor megvárja, amíg befejeződik. Ha a sor a predikátum korábbi kiértékelése után módosult, az adatbázismotor újra kiértékeli (újra minősíti) a predikátumot a sor módosítása előtt. Ha a predikátum továbbra is teljesül, a sor módosul.

A predikátum újraminősítését a lekérdezési motor operátorainak egy részhalmaza támogatja. Ha predikátum-újraértékelésre van szükség, de a lekérdezési terv olyan operátort használ, amely nem támogatja a predikátum újraminősítését, az adatbázismotor belsőleg megszakítja az utasításfeldolgozást, és laq nélkül újraindítja azt. Ilyen megszakítás esetén a lock_after_qual_stmt_abort kiterjesztett esemény aktiválódik.

Egyes utasítások, például UPDATE változó hozzárendelésű utasítások és a OUTPUT záradékkal rendelkező utasítások nem szakíthatók le és nem indíthatók újra a szemantikák módosítása nélkül. Ilyen állítások esetén a LAQ nem használható.

A következő példában a predikátum újra lesz értékelve, mert egy másik tranzakció módosította a sort:

CREATE TABLE t3
(
a int NOT NULL,
b int NULL
);

INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
GO

LAQ heurisztika

Az Minősítés utáni zárolás (LAQ)leírásának megfelelően, a LAQ használata esetén előfordulhat, hogy egyes utasítások belsőleg újraindulnak, és LAQ nélkül kerülnek feldolgozásra. Ha ez gyakran fordul elő, az ismétlődő feldolgozás többletterhelése jelentőssé válhat. A terhelés minimalizálása érdekében az optimalizált zárolás heurisztikus mechanizmussal követi nyomon az ismétlődő feldolgozást. Ez a mechanizmus letiltja az adatbázis LAQ-jának használatát, ha a többletterhelés meghaladja a küszöbértéket.

A heurisztikus mechanizmus alkalmazásában az utasítással végzett munkát a feldolgozott oldalak számában (logikai olvasások) mérik. Ha az adatbázismotor módosít egy sort, amelyet egy másik tranzakció módosított az utasítás feldolgozása után, akkor az utasítás által végzett munka valószínűleg elvesztek, mert az utasítás megszakadhat és újraindulhat. A rendszer nyomon követi az összes potenciálisan elpazarolt munkát és az adatbázis összes utasítása által végzett összes munkát.

A LAQ le van tiltva az adatbázis esetében, ha a potenciálisan elpazarolt munka százalékos aránya meghaladja a küszöbértéket. A LAQ akkor is le van tiltva, ha az újraindított utasítások száma meghaladja a küszöbértéket.

Ha a felesleges munka és az újraindult utasítások száma a megfelelő küszöbérték alá csökken, a LAQ újra engedélyezve lesz az adatbázis számára.

A lekérdezés viselkedésének változásai optimalizált zárolással és RCSI-vel

Az olvasható véglegesített pillanatkép-izoláció (RCSI) alatt futó, a tranzakciók szigorú végrehajtási sorrendjére támaszkodó egyidejű munkafolyamatok eltérő lekérdezési viselkedést tapasztalhatnak, ha engedélyezve van az optimalizált zárolás.

Tekintse meg az alábbi példát, amelyben a T2 tranzakció a T1 tranzakció során frissített t4 oszlop alapján frissíti a táblát b.

CREATE TABLE t4
(
a int NOT NULL,
b int NULL
);

INSERT INTO t4
VALUES (1,1);
GO

Értékeljük ki az előző forgatókönyv eredményét a minősítés után zárolással és zárolás nélkül (LAQ).

LAQ nélkül

LAQ nélkül a T2 tranzakció UPDATE utasítása blokkolva van, és a T1 tranzakció befejezésére vár. A T1 befejezése után a T2 frissíti a sor beállítási oszlopát b-ról 3-re, mert a predikátuma teljesül.

Mindkét tranzakció véglegesítése után a t4 tábla a következő sorokat tartalmazza:

 a | b
 1 | 3

LAQ-val

A LAQ esetén a T2 tranzakció azt a sor legfrissebb véglegesített verzióját használja, ahol az b oszlop egyenlő 1 értékkel a predikátum kiértékeléséhez (b = 2). A sor nem felel meg; ezért kihagyásra kerül, és az utasítás végrehajtódik anélkül, hogy a T1 tranzakció leblokkolta volna. Ebben a példában a LAQ eltávolítja a blokkolást, de eltérő eredményekhez vezet.

Mindkét tranzakció véglegesítése után a t4 tábla a következő sorokat tartalmazza:

 a | b
 1 | 2

Diagnosztikai kiegészítések optimalizált zároláshoz

Az alábbi fejlesztések segítségével figyelheti és elháríthatja a blokkolást és a holtpontokat, ha engedélyezve van az optimalizált zárolás:

• Várakozástípusok az optimalizált zároláshoz
  • XACT várakozási típusok a S zárolásához a TID-n, és az erőforrásleírások a sys.dm_os_wait_stats (Transact-SQL):
    • LCK_M_S_XACT_READ – Akkor fordul elő, ha egy tevékenység megosztott zárra vár egy XACTwait_resource típus esetén, olvasási céllal.
    • LCK_M_S_XACT_MODIFY – Akkor fordul elő, ha egy tevékenység egy XACTwait_resource típus megosztott zárolására vár módosítási szándékkal.
    • LCK_M_S_XACT – Akkor fordul elő, ha egy feladat megosztott zárolásra vár egy XACTwait_resource típuson, ahol a szándék nem értelmezhető. Ez a forgatókönyv nem gyakori.
• Erőforrások láthatóságának zárolása
  • XACT erőforrások zárolása. További információért lásd: resource_descriptionsys.dm_tran_locks (Transact-SQL).
• Várakozási erőforrás láthatósága
  • XACT várakozó erőforrások. További információkért lásd: wait_resource a sys.dm_exec_requests (Transact-SQL).
• Holtpont gráf
  • A holtpont jelentés <resource-list>minden egyes erőforrásánál minden <xactlock> elem jelentést ad az adott erőforrásokról és a holtpont egyes tagjainak zárolására vonatkozó specifikus információkat. További információ és példa: Optimalizált zárolás és holtpontok.
• Bővített események
  • A lock_after_qual_stmt_abort esemény akkor aktiválódik, ha egy utasítás belsőleg megszakad, és egy másik tranzakcióval való ütközés miatt újraindul. További információ: Minősítés utáni zárolás (LAQ).

Ajánlott eljárások optimalizált zárolással

Olvasható elkötelezett pillanatkép-elkülönítés engedélyezése (RCSI)

Az optimalizált zárolás előnyeinek maximalizálása érdekében ajánlott engedélyezni lekötött pillanatkép-elkülönítés (RCSI) az adatbázison, és alapértelmezett elkülönítési szintként READ COMMITTED elkülönítést használni. Ha még nincs engedélyezve, engedélyezze az RCSI-t a master adatbázishoz való csatlakozással és a következő utasítás végrehajtásával:

ALTER DATABASE [your-database-name] SET READ_COMMITTED_SNAPSHOT ON;

Az Azure SQL Database-ben az RCSI alapértelmezés szerint engedélyezve van, READ COMMITTED pedig az alapértelmezett elkülönítési szint. Ha az RCSI engedélyezve van, és READ COMMITTED elkülönítési szintet használ, az olvasók az utasítás elején készített pillanatképből olvassák be a sor egy verzióját. A LAQ használatával a szerzők a predikátum alapján minősítik a sorokat, figyelembe véve a legfrissebb véglegesített verziót, és mindezt anélkül, hogy U zárolásokat szereznének be. LAQ esetén a lekérdezés csak akkor várakozik, ha a sor megfelel a feltételeknek, és aktív írási tranzakció van a soron. A legújabb véglegesített verzió alapján történő minősítés és a csak a minősített sorok zárolása csökkenti a blokkolást és növeli az egyidejűséget.

A csökkentett blokkolás mellett a szükséges zárolási memória is csökken. Ennek az az oka, hogy az olvasók nem használnak zárolást, az írók pedig csak rövid időtartamú zárolásokat használnak a tranzakció végéig tartott zárolások helyett. Ha szigorúbb elkülönítési szinteket használ, például REPEATABLE READ vagy SERIALIZABLE, az adatbázismotor a tranzakció végéig sor- és oldalzárolásokat tart, még akkor is, ha az optimalizált zárolás engedélyezve van mind az olvasók, mind az írók számára, ami növeli a blokkolást és a memóriahasználat zárolását.

Kerüld a zárolási tippeket

Bár tábla- és lekérdezési tippek, például UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCKstb. teljesülnek az optimalizált zárolás engedélyezésekor, csökkentik az optimalizált zárolás előnyeit. A zárolási tippek arra kényszerítik az adatbázismotort, hogy sor- vagy oldalzárolást hajtson ki, és tartsa őket a tranzakció végéig, hogy betartsa a zárolási tippek szándékát. Egyes alkalmazások olyan logikával rendelkeznek, ahol zárolási megjegyzésekre van szükség, például amikor egy sor UPDLOCK megjegyzéssel történő olvasása után azt később frissítik. Azt javasoljuk, hogy csak akkor használjunk zárolási tippeket, ha szükséges.

Az optimalizált zárolás esetén nincs korlátozás a meglévő lekérdezésekre, és a lekérdezéseket nem kell újraírni. A nem tippeket használó lekérdezések leginkább az optimalizált zárolást használják.

A lekérdezések egyik táblájára vonatkozó táblázatos tipp nem tiltja le az optimalizált zárolást az ugyanazon lekérdezésben lévő többi táblához. Az optimalizált zárolás emellett csak a DML-utasítással frissített táblák zárolási viselkedését befolyásolja, például INSERT, UPDATE, DELETEvagy MERGE. Például:

CREATE TABLE t5
(
a int NOT NULL,
b int NOT NULL
);

CREATE TABLE t6
(
a int NOT NULL,
b int NOT NULL
);
GO

INSERT INTO t5 VALUES (1,10),(2,20),(3,30);
INSERT INTO t6 VALUES (1,10),(2,20),(3,30);
GO

UPDATE t5 SET t5.b = t6.b
FROM t5
INNER JOIN t6 WITH (UPDLOCK)
ON t5.a = t6.a;

Az előző lekérdezési példában csak a t6 táblázatot érinti a zárolási tipp, míg t5 továbbra is kihasználhatja az optimalizált zárolás előnyeit.

UPDATE t5
SET t5.b = t6.b
FROM t5 WITH (REPEATABLEREAD)
INNER JOIN t6
ON t5.a = t6.a;

Az előző lekérdezési példában csak a t5 tábla használja a REPEATABLE READ elkülönítési szintet, és a tranzakció végéig tartja a zárolásokat. A t5 egyéb frissítései továbbra is kihasználhatják az optimalizált zárolás előnyeit. Ugyanez vonatkozik a HOLDLOCK tippre is.

Gyakori kérdések (GYIK)

Az optimalizált zárolás alapértelmezés szerint be van kapcsolva az új és a meglévő adatbázisokban is?

Az Azure SQL Database-ben igen.

Hogyan észlelhetem, hogy engedélyezve van-e az optimalizált zárolás?

Lásd Engedélyezve van az optimalizált zárolás?.

Mi történik, ha a gyorsított adatbázis-helyreállítás (ADR) nincs engedélyezve az adatbázisban?

Ha az ADR le van tiltva, az optimalizált zárolás is automatikusan le lesz tiltva.

Mi a teendő, ha az optimalizált zárolás ellenére blokkolni szeretném a lekérdezéseket?

Az RCSI-t használó ügyfelek számára az optimalizált zárolás engedélyezésekor kényszerítse a két lekérdezés közötti blokkolást a READCOMMITTEDLOCK lekérdezési tipp használatával.

Az optimalizált zárolás írásvédett másodlagos replikákon használatos?

Nem, mert a DML-utasítások nem futtathatók írásvédett replikákon, és a megfelelő sor- és oldalzárolások nincsenek érvényben.

Optimalizált zárolást használ a tempdb és az ideiglenes táblák adatainak módosításakor?

Jelenleg nem.

Kapcsolódó tartalom

• Tranzakciózárolási és sorverziózás útmutató
• Véglegesített pillanatkép-elkülönítés (RCSI) olvasása
• sys.dm_tran_locks (Transact-SQL)
• Gyorsított adatbázis-helyreállítás