Transakcja w SQL Server to: sekwencyjna grupa stwierdzeń lub zapytań do wykonywania jednego lub wielu zadań w bazie danych. Każda transakcja może obejmować pojedynczą operację odczytu, zapisu, aktualizacji lub usunięcia lub kombinację wszystkich tych operacji. Każda transakcja musi mieć miejsce dwie rzeczy w SQL Server:
- Każda modyfikacja zakończy się pomyślnie po zatwierdzeniu transakcji.
- Lub wszystkie modyfikacje zostaną cofnięte po wycofaniu transakcji.
Transakcja nie może zakończyć się sukcesem, dopóki nie zostaną zakończone wszystkie operacje w zestawie. Oznacza to, że jeśli jakikolwiek argument nie powiedzie się, operacja transakcji zakończy się niepowodzeniem. Każda transakcja rozpoczyna się od pierwszej wykonywalnej instrukcji SQL i kończy się w momencie znalezienia zatwierdzenia lub wycofania, jawnie lub niejawnie. Używa POPEŁNIAĆ Lub WYCOFANIE instrukcje jawnie, jak również niejawnie, gdy używana jest instrukcja DDL.
Poniższe przedstawienie graficzne wyjaśnia proces transakcji:
Poniższy przykład wyjaśni koncepcję transakcji:
W tym przykładzie zostanie wykorzystany bankowy system baz danych do wyjaśnienia koncepcji transakcji. Załóżmy, że klient banku chce wypłacić pieniądze ze swojego konta, korzystając z trybu bankomatu. Bankomat może wykonać tę operację w trzech krokach:
- The pierwszy krok polega na sprawdzeniu dostępności żądanej kwoty na koncie.
- The drugi krok pobiera kwotę z rachunku, jeśli kwota jest dostępna, a następnie aktualizuje saldo rachunku.
- The trzeci krok polega na zapisaniu operacji wypłaty pieniędzy w pliku dziennika. Ten krok zapisuje informację o tym, że transakcja zakończyła się sukcesem lub niepowodzeniem. Jeśli się powiedzie, zapisz modyfikację danych w bazie danych. W przeciwnym razie transakcja zostanie przywrócona do poprzedniego stanu.
Podstawową zasadą transakcji jest to, że jeśli jedna z instrukcji zwróci błąd, cały zestaw zmian zostanie wycofany, aby zapewnić integralność danych. A jeśli transakcje zakończą się sukcesem, wszystkie zmiany zostaną trwale zapisane w bazie danych. Dlatego też, jeśli podczas wypłaty pieniędzy z bankomatu nastąpi przerwa w dostawie prądu lub inne problemy, transakcje gwarantują utrzymanie salda na stałym poziomie. Zestawienie transakcji najlepiej wykonuje te operacje, ponieważ cztery kluczowe właściwości transakcji sprawiają, że wszystkie operacje są dokładniejsze i spójne. Cztery właściwości transakcji nazywane są ACID.
Właściwości transakcji
Właściwości transakcyjne nazywane są właściwościami ACID (atomowość, konsystencja, izolacja, trwałość), które szczegółowo omówiono poniżej:
Java na przerwę
Atomowość: Ta właściwość zapewnia, że wszystkie instrukcje lub operacje zawarte w transakcji muszą zostać pomyślnie wykonane. W przeciwnym razie cała transakcja zostanie przerwana, a w przypadku niepowodzenia jakiejkolwiek operacji wszystkie operacje zostaną przywrócone do poprzedniego stanu.
Konsystencja: Ta właściwość gwarantuje, że stan bazy danych zmieni się tylko wtedy, gdy transakcja zostanie pomyślnie zatwierdzona. Odpowiada także za ochronę danych przed awariami.
Izolacja: Ta właściwość gwarantuje, że wszystkie transakcje są odizolowane od innych transakcji, co oznacza, że każda operacja w transakcji jest wykonywana niezależnie. Zapewnia również, że oświadczenia są dla siebie przejrzyste.
Trwałość: Ta właściwość gwarantuje, że wynik zatwierdzonych transakcji zostanie trwale zapisany w bazie danych, nawet w przypadku awarii lub awarii systemu.
Tryby transakcji w SQL Server
Istnieją trzy różne tryby transakcji, z których może korzystać SQL Server:
Tryb transakcji automatycznego zatwierdzania: Jest to domyślny tryb transakcji SQL Server. Oceni każdą instrukcję SQL jako transakcję, a wyniki zostaną odpowiednio zatwierdzone lub wycofane. W ten sposób pomyślne instrukcje są natychmiast zatwierdzane, podczas gdy nieudane instrukcje są natychmiast wycofywane.
Niejawny tryb transakcji. Ten tryb pozwala SQL Serverowi rozpocząć niejawną transakcję dla każdej instrukcji DML, ale wyraźnie wymaga użycia poleceń zatwierdzenia lub wycofania na końcu instrukcji.
Jawny tryb transakcji: Tryb ten jest definiowany przez użytkownika i pozwala nam dokładnie określić początek i koniec transakcji. Proces zostanie automatycznie przerwany w przypadku wystąpienia błędu krytycznego.
Kontrola transakcji
Poniżej znajdują się polecenia służące do kontrolowania transakcji:
UWAGA: W przypadku poleceń języka kontroli transakcji możemy używać wyłącznie instrukcji DML (INSERT, UPDATE i DELETE). Nie możemy ich używać podczas tworzenia lub usuwania tabel, ponieważ operacje te są automatycznie przesyłane do bazy danych.
Stan transakcji
Wskazuje, jak przebiegają transakcje w trakcie ich trwania. Opisuje aktualny stan transakcji, a także sposób, w jaki transakcja będzie przetwarzana w przyszłości. Stany te definiują reguły określające, czy transakcja zostanie zatwierdzona, czy przerwana.
Opiszmy każdy stan transakcji w SQL Server:
ile waży kat timpf
Stan aktywny: Transakcja jest w stanie aktywnym w czasie wykonywania instrukcji transakcji. Zmienia się na „stan częściowo zaangażowany” jeśli wszystkie operacje „odczytu i zapisu” zostaną zakończone bez błędów. Jeśli jakakolwiek instrukcja zakończy się niepowodzeniem, przechodzi w „stan niepowodzenia”.
Częściowo zaangażowane: Po zakończeniu wszystkich operacji odczytu i zapisu następuje zmiana w pamięci głównej lub buforze lokalnym. Państwo poszłoby do „stan zaangażowania” jeżeli zmiany zostaną trwale zapisane w bazie danych. W przeciwnym razie przechodzi do „stanu awarii”.
Stan awarii: Transakcja przechodzi do stanu niepowodzenia, gdy jakakolwiek instrukcja transakcji nie powiedzie się lub nie powiedzie się trwała modyfikacja w bazie danych.
Stan przerwany: Transakcja przenosi się z „stan upadku” do „stan abortowany” gdy wystąpi jakakolwiek awaria. Zmiany są usuwane lub wycofywane, ponieważ zmiany te są wprowadzane tylko w buforze lokalnym lub pamięci głównej w poprzednich stanach.
Stan zaangażowania: Transakcja jest zakończona i przechodzi do tego stanu, gdy zmiany zostaną utrwalone w bazie danych i zakończone w „stan zakończenia”.
Stan zakończony: Jeśli nie ma wycofania i transakcja jest w „stan zaangażowania”, system jest spójny i gotowy na nową transakcję, podczas gdy stara zostaje zakończona.
Implementacja transakcji w SQL Server
Weźmy kilka przykładów, aby zrozumieć, jak możemy zaimplementować transakcję w SQL Server. Tutaj skorzystamy z 'Produkt' tabela pokazująca wszystkie stany transakcji.
Poniższe skrypty SQL tworzą tabelę Produkt w wybranej bazie danych:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
Następnie wykonaj poniższe skrypty, aby wstawić dane do tej tabeli:
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);
Wykonaj instrukcję SELECT, aby zweryfikować dane:
Przykład transakcji COMMIT
Dobrym pomysłem jest podzielenie instrukcji SQL używanych w transakcji na wiele logicznych części. Następnie możemy zdecydować, czy zatwierdzić, czy wycofać dane. Poniższe kroki ilustrują tworzenie transakcji:
- Rozpocznij transakcję za pomocą ROZPOCZNIJ TRANSAKCJĘ Komenda.
- Napisz instrukcje SQL i podziel je w zależności od naszych potrzeb
- Użyj POPEŁNIAĆ oświadczenie o dokończeniu transakcji i trwałym zapisaniu zmian.
Poniżej znajdują się polecenia wyjaśniające operacje COMMIT w SQL Server:
-- 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
Jeśli nie zostanie znaleziony żaden błąd, zobaczymy następujący wynik, w którym każda instrukcja SQL transakcji zostanie wykonana niezależnie:
Instrukcje INSERT i UPDATE nie mogą zostać wycofane po zatwierdzeniu transakcji. Kiedy zweryfikujemy tabelę po operacji zatwierdzenia, zobaczymy następujące dane:
polecenie cp w systemie Linux
Przykład transakcji ROLLBACK
Poleceniem ROLLBACK cofniemy wszelkie transakcje, które nie zostały jeszcze zapisane w bazie danych i powrócimy do punktu, w którym transakcja się rozpoczęła. Poniższy przykład wyjaśnia operację ROLLBACK w SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116
Kiedy już wykonamy powyższą transakcję, widzimy, że zostanie ona pomyślnie zrealizowana. Nie będzie to miało jednak wpływu na żadne zmiany w bazie danych, gdyż dopóki nie wykonamy instrukcji COMMIT lub ROLLBACK, zmiany nie mogą stać się trwałe. Dlatego mamy możliwość wykorzystania polecenia transakcji ROLLBACK do wycofania wszystkich operacji na bazie danych. Oto pełny opis transakcji:
-- 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
Użycie zmiennej globalnej @@Error w transakcjach:
Ta zmienna jest służy do sprawdzania, czy wystąpił błąd, czy nie. Poniższy przykład wyjaśnia jego koncepcję. Tutaj najpierw rozpoczniemy transakcję za pomocą polecenia BEGIN, a następnie napiszemy dwie instrukcje wstawiania. Następnie użyjemy globalnej zmiennej systemowej @@BŁĄD w Instrukcja JEŻELI aby sprawdzić błąd. Jeśli wartość jest większa niż 0, oznacza to, że wystąpił błąd. Teraz transakcja została wycofana; w przeciwnym razie transakcja zostaje zatwierdzona.
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
Kiedy powyższa transakcja zostanie zrealizowana, zauważymy, że została ona wycofana. Wynika to z naszej próby wstawienia zduplikowanej wartości w kolumnie Klucz podstawowy.
Transakcja automatycznego wycofywania
Większość transakcji zawiera więcej niż jedno zapytanie. Jeśli podczas realizacji transakcji któraś z instrukcji SQL zwróci błąd, w bazie danych nie nastąpią żadne modyfikacje, a pozostałe instrukcje nie zostaną wykonane. Koncepcja ta jest znana jako transakcja automatycznego wycofywania zmian w programie SQL Server. Aby zademonstrować ten proces, użyjmy prostego przykładu.
rdzeń Javy
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
Ta transakcja generuje następujące dane wyjściowe:
W tym wyniku widzimy, że instrukcja wstawiania została pomyślnie wykonana. Jednak wykonanie instrukcji aktualizacji spowodowało błąd związany z konwersją typu danych. W tym przypadku SQL Server nie pozwala na żadne zmiany w bazie danych, co oznacza, że operacja wstawiania nie dodaje żadnej wartości, a instrukcja Select nie jest wykonywana.
Punkt zapisu w transakcjach
Punkt zapisu wstawia do transakcji specjalny znacznik, który umożliwia wycofanie wszystkich zmian dokonanych po zapisaniu. Służy również do wycofywania określonej części transakcji, a nie całej transakcji. Możemy to zdefiniować za pomocą ZAPISZ TRANSAKCJĘ sp_name oświadczenie. Poniższy przykład wyjaśni użycie punktu zapisu w transakcjach, które zatwierdzają instrukcję wstawiania i wycofują instrukcję usuwania.
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;
Zobacz poniższy wynik, w którym widzimy, że identyfikator produktu 116 został usunięty, a 117 zostało wstawione na pierwszym wyjściu. Jednak w przypadku drugiego wyniku operacja usuwania jest wycofywana ze względu na punkt zapisu.
Jak zwolnić punkt zapisu w transakcji?
Zwolnij punkt zapisu służy do usunięcia nazwanego punktu zapisu z bieżącej transakcji bez odwracania wyników zapytań wykonanych po punkcie zapisu. MySQL ma to polecenie, ale SQL Server nie udostępnia żadnego polecenia zwalniającego punkt zapisu. Zamiast tego są one automatycznie zwalniane pod koniec transakcji zatwierdzania lub wycofywania zmian, więc nie musimy się nimi martwić w międzyczasie.
Niejawna transakcja w SQL Server
Transakcję niejawną możemy zdefiniować włączając opcję IMPLICIT_TRANSACTIONS. Poniższy przykład łatwo wyjaśni tę koncepcję:
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
W tej transakcji skorzystaliśmy z dwóch opcji @@OPCJA i @@TRANCOUNT. @@OPTOPN dostarcza informacji dotyczących bieżących opcji SET, a @@TRANCOUNT dostarcza instrukcję BEGIN TRANSACTION w bieżącej sesji.
Teraz wykonanie transakcji zwróci poniższe dane wyjściowe:
Jawna transakcja w SQL Server
Transakcję jawną należy zdefiniować za pomocą polecenia BEGIN TRANSACTION, ponieważ identyfikuje ona punkt początkowy transakcji jawnej. Możemy zdefiniować jawną transakcję w SQL Server jak poniżej:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
W składni opcja trans_name wskazuje unikalną nazwę transakcji. The @trans_name_var wskazuje zmienną zdefiniowaną przez użytkownika, która przechowuje nazwę transakcji. Wreszcie, OCENA opcja pozwala na zaznaczenie konkretnej transakcji w pliku logu.
Jawna transakcja za pomocą polecenia BEGIN TRANSACTION uzyskała blokadę w zależności od poziomu izolacji zasobów związanych z transakcją. Pomaga zmniejszyć problemy z zamkami. Zobacz poniższy przykład:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Oto dane wyjściowe:
Zaznaczona transakcja w SQL Server
Zaznaczona transakcja służy do dodania opisu konkretnej transakcji w plikach dziennika. Możemy go użyć jako punktu przywracania zamiast daty i godziny podczas przywracania bazy danych do poprzedniego stanu. Musimy wiedzieć, że znak jest dodawany w plikach logów tylko wtedy, gdy oznaczona transakcja modyfikuje bazę danych. Możemy zrozumieć jego koncepcję na poniższym przykładzie.
wyrażenie regresji w Javie
Załóżmy, że przypadkowo zmodyfikowaliśmy bazę danych i nie znamy dokładnego momentu zmiany danych; w takim przypadku odzyskiwanie danych może zająć dużo czasu. Jeśli jednak skorzystamy z zaznaczonych transakcji, może to być przydatne narzędzie do określenia dokładnego czasu zmian danych.
Poniższa składnia ilustruje zaznaczoną transakcję w SQL Server:
BEGIN TRANSACTION trans_name WITH MARK 'description';
Tutaj musimy zdefiniować nazwę transakcji, a następnie dodać opcję Z ZNAKIEM. W poniższym przykładzie usuniemy rekordy i dodamy znacznik w pliku dziennika:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
The historia logmarku tabela jest zawarta w baza danych msdb i przechowuje informacje dotyczące każdej oznaczonej transakcji, która została zrealizowana. Wykonaj poniższą instrukcję, aby uzyskać szczegółowe informacje z tabeli logmarkhistory:
SELECT * FROM msdb.dbo.logmarkhistory
Nazwana transakcja w SQL Server
Możemy również podać nazwę naszej transakcji w SQL Server. Zawsze zaleca się używanie nazwanej transakcji podczas pracy z wieloma transakcjami w jednym zapytaniu. Poniższy przykład wyjaśnia, jak zmienić nazwę transakcji:
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
Oto dane wyjściowe:
Wniosek
W tym artykule przedstawiono pełny przegląd transakcji w instrukcjach SQL Server. Transakcje są pomocne w systemach relacyjnych baz danych, ponieważ zapewniają integralność bazy danych.