Egy tranzakció az SQL Serverben a utasítások vagy lekérdezések szekvenciális csoportja egy vagy több feladat elvégzésére egy adatbázisban. Minden tranzakció tartalmazhat egyetlen olvasási, írási, frissítési vagy törlési műveletet vagy ezeknek a műveleteknek a kombinációját. Minden tranzakciónak két dolognak kell történnie az SQL Serverben:
- A tranzakció véglegesítésekor vagy minden módosítás sikeres.
- Vagy az összes módosítás visszavonásra kerül, amikor a tranzakció visszaállításra kerül.
A tranzakció nem lehet sikeres, amíg a készletben lévő összes műveletet be nem fejezik. Ez azt jelenti, hogy ha bármelyik argumentum meghiúsul, a tranzakciós művelet meghiúsul. Minden tranzakció az első végrehajtható SQL-utasítással kezdődik, és akkor ér véget, amikor explicit vagy implicit véglegesítést vagy visszaállítást talál. Használja a ELKÖVETNI vagy VISSZATÉRÍTÉS utasítások explicit módon, valamint implicit módon, ha DDL utasítást használnak.
Az alábbi képi ábrázolás bemutatja a tranzakció folyamatát:
A következő példa bemutatja a tranzakció fogalmát:
Ez a példa a banki adatbázis-rendszert fogja használni a tranzakció fogalmának magyarázatára. Tegyük fel, hogy egy banki ügyfél pénzt szeretne felvenni a számlájáról ATM mód használatával. Az ATM ezt a műveletet három lépésben tudja végrehajtani:
- A első lépés hogy ellenőrizze a kért összeg elérhetőségét a számlán.
- A második lépés levonja az összeget a számláról, ha az összeg rendelkezésre áll, majd frissíti a számlaegyenleget.
- A harmadik lépés a pénzfelvételi művelet beírása a naplófájlba. Ez a lépés arról ír, hogy a tranzakció sikeres vagy sikertelen. Sikeres esetben írja be az adatmódosítást az adatbázisba. Ellenkező esetben a tranzakció visszaáll az előző állapotába.
A tranzakciók mögött meghúzódó alapelv az, hogy ha az egyik utasítás hibát ad vissza, akkor az adatok sértetlenségének biztosítása érdekében a változtatások teljes halmaza visszaáll. És ha a tranzakciók sikeresek lesznek, minden változás állandó lesz az adatbázisban. Ezért, ha áramszünet vagy egyéb probléma adódik az ATM-ből történő pénzfelvétel során, a tranzakciók garantálják, hogy egyenlegünk állandó marad. Ezeket a műveleteket a tranzakciós nyilatkozat hajtja végre a legjobban, mivel a tranzakció négy kulcstulajdonsága pontosabbá és következetesebbé teszi az összes műveletet. A tranzakció négy tulajdonságára ACID néven hivatkozunk.
Tranzakció tulajdonságai
A tranzakciós tulajdonságokat ACID (Atomitás, konzisztencia, izoláció, tartósság) tulajdonságnak nevezzük, amelyeket az alábbiakban részletesen tárgyalunk:
Atomos állapot: Ez a tulajdonság biztosítja, hogy a tranzakcióban szereplő összes utasítást vagy műveletet sikeresen végre kell hajtani. Ellenkező esetben a teljes tranzakció megszakad, és minden művelet visszaáll az előző állapotába, ha bármely művelet meghiúsul.
Következetesség: Ez a tulajdonság biztosítja, hogy az adatbázis állapotát csak akkor változtassa meg, ha a tranzakció sikeres lesz. Feladata továbbá az adatok összeomlással szembeni védelme.
Elkülönítés: Ez a tulajdonság garantálja, hogy minden tranzakció elkülönítve legyen a többi tranzakciótól, ami azt jelenti, hogy a tranzakció minden egyes művelete függetlenül működik. Azt is biztosítja, hogy az állítások átláthatóak legyenek egymás számára.
java kapcsoló
Tartósság: Ez a tulajdonság garantálja, hogy a végrehajtott tranzakciók eredménye tartósan megmarad az adatbázisban akkor is, ha a rendszer összeomlik vagy meghibásodik.
Tranzakciós módok az SQL Serverben
Az SQL Server három különböző tranzakciós módot használhat:
Tranzakciós mód automatikus véglegesítése: Ez az SQL Server alapértelmezett tranzakciós módja. Minden egyes SQL-utasítást tranzakcióként értékel, és az eredményeket ennek megfelelően véglegesíti vagy visszaállítja. Így a sikeres állítások azonnal érvényesítésre kerülnek, míg a sikertelen állítások azonnal visszagurításra kerülnek.
Implicit tranzakciós mód. Ez a mód lehetővé teszi az SQL Server számára, hogy megkezdje az implicit tranzakciót minden DML-utasításhoz, de kifejezetten megköveteli a véglegesítési vagy visszaállítási parancsok használatát az utasítások végén.
Explicit tranzakciós mód: Ezt a módot a felhasználó határozza meg, amely lehetővé teszi számunkra, hogy pontosan azonosítsuk a tranzakció kezdő- és végpontját. Végzetes hiba esetén automatikusan leáll.
Tranzakcióvezérlés
A következő parancsok a tranzakciók vezérlésére szolgálnak:
MEGJEGYZÉS: A Transaction Control Language parancsokhoz csak DML utasításokat (INSERT, UPDATE és DELETE) használhatunk. Nem használhatjuk őket táblák létrehozásakor vagy eldobásakor, mert ezek a műveletek automatikusan az adatbázishoz kötődnek.
Tranzakció állapota
Megmutatja, hogyan zajlanak a tranzakciók életük során. Leírja a tranzakció jelenlegi állapotát, valamint azt, hogy a tranzakció hogyan kerül feldolgozásra a jövőben. Ezek az állapotok határozzák meg azokat a szabályokat, amelyek meghatározzák, hogy egy tranzakció véglegesítésre kerül-e vagy megszakad.
Leírjuk az egyes tranzakciós állapotokat az SQL Serverben:
Aktív állapot: A tranzakció aktív állapotban van, miközben a tranzakció utasításait végrehajtják. Megváltozik a 'részben elkötelezett állapot' ha minden „olvasási és írási” művelet hiba nélkül befejeződött. Ha bármelyik utasítás meghiúsul, akkor „sikertelen állapotra” vált.
Részben elkötelezett: Amikor az összes olvasási és írási művelet befejeződött, a módosítás a fő memóriában vagy a helyi pufferben történik. Az állam odamenne 'elkötelezett állapot' ha a változtatások állandósulnak az adatbázisban. Ellenkező esetben „sikertelen állapotba” kerül.
Sikertelen állapot: Egy tranzakció sikertelen állapotba kerül, ha bármely tranzakció utasítása meghiúsul, vagy az adatbázison végrehajtott végleges módosítás meghiúsul.
havonta hány hét
Megszakított állam: A tranzakció a 'sikertelen állapot' egy 'elvetélt állapot' ha bármiféle meghibásodás történik. A változtatásokat eltávolítja vagy visszaállítja, mert ezek a változtatások csak a helyi pufferben vagy a fő memóriában történtek a korábbi állapotokban.
Elkötelezett állapot: A tranzakció akkor fejeződik be, és ebbe az állapotba kerül, amikor a változtatásokat véglegessé teszik az adatbázisban, és az adatbázisban megszűnnek „lefejezett állapot”.
Megszűnt állapot: Ha nincs visszaállítás, és a tranzakció a 'elkötelezett állapot', a rendszer konzisztens és készen áll egy új tranzakcióra, miközben a régi megszűnik.
Tranzakció megvalósítása SQL Serverben
Vegyünk néhány példát, hogy megértsük, hogyan valósíthatjuk meg a tranzakciót az SQL Serverben. Itt fogjuk használni a 'Termék' táblázat az összes tranzakciós állapot bemutatásához.
A következő SQL-parancsfájlok hozzák létre a Termék táblát a kiválasztott adatbázisban:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
Ezután futtassa az alábbi szkripteket az adatok ebbe a táblázatba történő beszúrásához:
INSERT INTO Product VALUES(111, 'Mobile', 10000, 10), (112, 'Laptop', 20000, 15), (113, 'Mouse', 300, 20), (114, 'Hard Disk', 4000, 25), (115, 'Speaker', 3000, 20);
Az adatok ellenőrzéséhez hajtsa végre a SELECT utasítást:
Példa COMMIT tranzakcióra
Célszerű a tranzakció során használt SQL-utasításokat több logikai részre osztani. Ezután eldönthetjük, hogy véglegesítjük vagy visszavonjuk az adatokat. A következő lépések szemléltetik a tranzakció létrehozását:
- Indítsa el a tranzakciót a TRANZAKCIÓ KEZDÉSE parancs.
- Írjuk fel az SQL utasításokat, és osszuk fel őket igényeink szerint
- Használja a ELKÖVETNI nyilatkozatot a tranzakció befejezéséhez és a változtatások végleges mentéséhez.
Az alábbiakban az SQL Server COMMIT műveleteit magyarázó parancsok találhatók:
konvertálni dupla java-ba
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements INSERT INTO Product VALUES(116, 'Headphone', 2000, 30) UPDATE Product SET Price = 450 WHERE Product_id = 113 -- Commit changes COMMIT TRANSACTION
Ha nem található hiba, akkor a következő kimenetet fogjuk látni, ahol a tranzakció minden SQL utasítása egymástól függetlenül végrehajtódik:
Az INSERT és UPDATE utasításokat nem lehet visszaállítani a tranzakció véglegesítése után. Amikor a véglegesítési művelet után ellenőrizzük a táblát, a következő adatokat fogjuk látni:
Példa ROLLBACK tranzakcióra
A ROLLBACK paranccsal visszavonjuk az adatbázisba még nem mentett tranzakciókat, és visszatérünk oda, ahol a tranzakció elkezdődött. A következő példa az SQL Server ROLLBACK műveletét magyarázza:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116
Miután végrehajtottuk a fenti tranzakciót, láthatjuk, hogy a végrehajtás sikeres lesz. Ez azonban nem érinti az adatbázis változásait, mert amíg nem hajtjuk végre a COMMIT vagy ROLLBACK utasítást, addig a változtatások nem válhatnak állandóvá. Ezért lehetőségünk van a ROLLBACK tranzakció paranccsal visszaállítani az összes adatbázis-műveletet. Íme a teljes tranzakciós nyilatkozat:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116 --Undo Changes ROLLBACK TRANSACTION
Az @@Error globális változó használata a tranzakciókban:
Ez a változó az annak ellenőrzésére szolgál, hogy van-e hiba vagy sem. Az alábbi példa elmagyarázza a koncepcióját. Itt először a BEGIN paranccsal indítjuk el a tranzakciót, majd írunk két insert utasítást. Ezután a globális rendszerváltozót használjuk @@HIBA ban,-ben IF nyilatkozat hiba ellenőrzésére. Ha az érték nagyobb, mint 0, az azt jelenti, hogy hiba történt. Most a tranzakció visszaállításra kerül; ellenkező esetben a tranzakció lekötött.
BEGIN TRANSACTION INSERT INTO Product VALUES(115,'Speaker', 3000, 25) -- Check for error IF(@@ERROR > 0) BEGIN ROLLBACK TRANSACTION END ELSE BEGIN COMMIT TRANSACTION END
Amikor a fenti tranzakció végrehajtásra kerül, észrevesszük, hogy a tranzakció vissza lett állítva. Ez annak köszönhető, hogy megpróbáltunk ismétlődő értéket beszúrni az Elsődleges kulcs oszlopba.
Automatikus visszaállítási tranzakció
A legtöbb tranzakció egynél több lekérdezést tartalmaz. Ha a tranzakció végrehajtása során bármelyik SQL-utasítás hibát ad, akkor az adatbázisban nem történik módosítás, és a többi utasítás nem hajtódik végre. Ez a koncepció az SQL Server automatikus visszaállítási tranzakciójaként ismert. Használjunk egy egyszerű példát ennek a folyamatnak a bemutatására.
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
Ez a tranzakció a következő kimenetet eredményezi:
Ebben a kimenetben láthatjuk, hogy az insert utasítás végrehajtása sikeres volt. A frissítési utasítás végrehajtása azonban hibát talált az adattípus-átalakítási probléma miatt. Ebben az esetben az SQL Server nem engedélyez semmilyen változtatást az adatbázisban, ami azt jelenti, hogy az insert művelet nem ad hozzá értéket, és a select utasítás nem hajtódik végre.
Savepoint a Tranzakciókban
A mentési pont egy speciális jelölést szúr be egy tranzakcióba, amely lehetővé teszi a mentési pont után végrehajtott összes módosítás visszaállítását. A tranzakció bármely meghatározott részének visszaállítására is szolgál, nem pedig a teljes tranzakcióra. A segítségével határozhatjuk meg TRANSACTION MENTÉSE sp_name nyilatkozat. A következő példa elmagyarázza a mentési pont használatát olyan tranzakciókban, amelyek véglegesítik az insert utasítást és visszagörgetik a delete utasítást.
BEGIN TRANSACTION INSERT INTO Product VALUES(117, 'USB Drive', 1500, 10) SAVE TRANSACTION InsertStatement DELETE FROM Product WHERE Product_id = 116 SELECT * FROM Product ROLLBACK TRANSACTION InsertStatement COMMIT SELECT * FROM Product;
Lásd az alábbi eredményt, ahol láthatjuk, hogy a 116-os termékazonosító törölve van, és a 117-es bekerül az első kimenetbe. A második kimenetben azonban a törlési művelet visszaáll a mentési pont miatt.
Hogyan lehet mentési pontot felszabadítani egy tranzakcióban?
A mentési pont felszabadítása a megnevezett mentési pont eltávolítására szolgál az aktuális tranzakcióból a mentési pont után végrehajtott lekérdezések eredményeinek visszaállítása nélkül. A MySQL rendelkezik ezzel a paranccsal, de az SQL Server nem ad parancsot a mentési pont felszabadítására. Ehelyett egy véglegesítési vagy visszaállítási tranzakció végén automatikusan feloldódnak, így nem kell közben közben aggódnunk miattuk.
Implicit tranzakció az SQL Serverben
Az IMPLICIT_TRANSACTIONS opció engedélyezésével implicit tranzakciót definiálhatunk. A következő példa könnyen megmagyarázza ezt a fogalmat:
SET IMPLICIT_TRANSACTIONS ON UPDATE Product SET Quantity = 10 WHERE Product_id = 113 SELECT IIF(@@OPTIONS & 2 = 2, 'Implicit Transaction Mode ON', 'Implicit Transaction Mode OFF' ) AS 'Transaction Mode' SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Ebben a tranzakcióban két lehetőséget használtunk @@OPTION és @@TRANCOUNT. Az @@OPTOPN az aktuális SET beállításokkal kapcsolatos információkat, a @@TRANCOUNT pedig a BEGIN TRANSACTION utasítást az aktuális munkamenetben.
Most a tranzakció végrehajtása az alábbi kimenetet adja vissza:
Explicit tranzakció az SQL Serverben
Egy kifejezett tranzakciót a BEGIN TRANSACTION paranccsal kell meghatározni, mert ez azonosítja az explicit tranzakció kezdőpontját. Az alábbiak szerint határozhatjuk meg az explicit tranzakciót az SQL Serverben:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
A szintaxisban a transz_name beállítás a tranzakció egyedi nevét jelzi. A @trans_name_var egy felhasználó által definiált változót jelöl, amely a tranzakció nevét tárolja. Végül a MARK opció lehetővé teszi egy adott tranzakció megjelölését a naplófájlban.
A BEGIN TRANSACTION parancson keresztüli kifejezett tranzakció a tranzakcióhoz kapcsolódó erőforrások elkülönítési szintjétől függően zárolást kapott. Segít csökkenteni a zárolási problémákat. Lásd az alábbi példát:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Íme a kimenet:
java módszerek
Megjelölt tranzakció az SQL Serverben
A megjelölt tranzakció leírást ad egy adott tranzakcióhoz a naplófájlokban. Használhatjuk helyreállítási pontként a dátum és az idő helyett, amikor egy adatbázist egy korábbi állapotba állítunk vissza. Tudnunk kell, hogy a jelölés csak akkor kerül be a naplófájlokba, ha a megjelölt tranzakció módosítja az adatbázist. Fogalmát a következő példa segítségével érthetjük meg.
Tegyük fel, hogy véletlenül módosítottuk az adatbázist, és nem ismerjük az adatmódosítás pontos pillanatát; ebben az esetben az adatok helyreállítása sokáig tarthat. Ha azonban a megjelölt tranzakciókat használjuk, akkor hasznos eszköz lehet az adatmódosítások pontos időpontjának meghatározásához.
A következő szintaxis illusztrálja a megjelölt tranzakciót az SQL Serverben:
BEGIN TRANSACTION trans_name WITH MARK 'description';
Itt meg kell határoznunk a tranzakció nevét, majd hozzá kell adni a WITH MARK opciót. Az alábbi példában törölni fogjuk a rekordokat, és hozzáadjuk a jelölést a naplófájlhoz:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
A logmarkhistory táblázat tartalmazza a msdb adatbázis és információkat tárol minden egyes megjelölt, végrehajtott tranzakcióról. Hajtsa végre az alábbi utasítást, hogy megkapja a részleteket a logmarkhistory táblázatból:
SELECT * FROM msdb.dbo.logmarkhistory
Tranzakció neve az SQL Serverben
A tranzakciónknak nevet is adhatunk az SQL Serverben. Mindig ajánlott a megnevezett tranzakció használata, ha több tranzakcióval dolgozik egyetlen lekérdezésben. Az alábbi példa bemutatja a tranzakció átnevezését:
BEGIN TRANSACTION AddProduct INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Product_name = 'Pen Drive' WHERE Product_id = 117 COMMIT TRANSACTION AddProduct
Íme a kimenet:
Következtetés
Ez a cikk teljes áttekintést nyújt a tranzakcióról az SQL Server utasításokban. A tranzakciók hasznosak a relációs adatbázis-rendszerekben, mert biztosítják az adatbázis integritását.