Metodyka porównywania wydajności operacji SQL Insert w wybranych RDBMS

Poniżej polska wersja artykułu opublikowanego w PIMR,
oryginalna wersja dostępna tutaj

Wprowadzenie
Kluczową kwestią nabierającą coraz większego znaczenia, braną pod uwagę na etapie projektowania, wytwarzania i eksploatowania bazodanowych systemów informatycznych jest problem wydajności i skalowalności aplikacji[1]. W procesie wieloetapowego projektowania danych generalnie nasza uwaga skupiona jest na ich poprawnym odwzorowaniu zgodnie z rozpoznanymi wymaganiami i z przyjętym architektonicznym modelem danych. Zagadnienie wydajności samo w sobie jest zagadnieniem złożonym albowiem możemy go rozpatrywać w kontekście różnych operacji dokonywanych na bazie produkcyjnej lub też w kontekście procesów tworzenia na jej podstawie baz analitycznych[1][7][8]. Z tego obszaru, dość szerokiego, nakreślonego powyżej autorów zainteresował wpływ istnienia klucza podstawowego oaz sposób jego generowania na wydajność zapisu danych w RDBMS. W podejściu badawczym przyjęto perspektywę, interesującą użytkowników gotowego produktu, jaki jest RDBMS, w związku z tym świadomie pominięto zagadnienia związane z mechanizmami fizycznego rozmieszczenia danych na dysku.
Metodyka

Próba odpowiedzi na pytanie na ile obecność klucza podstawowego oraz sposób jego tworzenia wpływa na zapis danych w relacyjnych wymagało opracowania metodyki i wytworzenia niezbędnych narzędzi programistycznych.
Na etapie początkowym przyjęto szereg założeń, których zachowanie powinno eliminować wpływ czynników zakłócających pomiar. Między innymi zdecydowano się, iż aplikacja powinna działać całkowicie po stronie serwera bazodanowego. Tym samym wyeliminowano by wpływ interfejsów programistycznych dostępu do danych typu ADO, ADO.NET itp. na czas zapisu rekordów. Konsekwencją tej decyzji było zwrócenie naszej uwagi tylko na te RDBMS, których wewnętrzny język jest językiem proceduralnym.
Wytworzona procedura, względnie procedury stanowiące podstawowe narzędzie badawcze, powinny tworzyć strukturę tabeli, generować rekordy, a następnie je zapisywać. Równocześnie należałoby rejestrować czas rozpoczęcia i zakończenia zapisu danych z jednoczesnym odnotowywaniem liczby rekordów osadzonych w bazie. Powyższa druga porcja informacji, potrzebna do analiz, gromadzona byłaby w oddzielnej tabeli.
Skoro podstawowym celem autorów było uzyskanie odpowiedzi na pytanie na ile obecność klucza głównego i sposób jego generowania ma wpływ na szybkość zapisu zdecydowano się na utworzenie szeregu niezależnych procedur, osobno dla każdego przewidywanego wariantu. Wspólnym elementem byłby zapis wyników eksperymentu do tej samej tabeli. Przyjęto następujące przypadki, które wiązały się rodzajem informacjami zapisywanych w bazie:
• dane bez klucza podstawowego,
• dane z kluczem głównym, którego wartości generowane są przez RDBMS,
• dane z kluczem głównym, gdzie wartości są generowane na podstawie algorytmu użytkownika.
Procedury to realizujące powinny być zaimplementowane w co najmniej w dwóch RDBMS. Podjęto decyzję, iż będzie to SQL Server 2008, Oracle XE i MySQL. Podstawą wspomnianej decyzji była popularność wykorzystania tych narzędzi informatycznych. I tak dla przykładu na polskim rynku darmowa wersja MSSQL jest wykorzystywana między innymi w znanych programach: Płatnik, MicroSubiekt. Z kolei jego większy brat (płatny) stosowany jest między innymi w jednym z najpopularniejszych programów typu ERP na naszym rynku tj. CDN XL. Baza Mysql skolei dominuje na rynku aplikacji interneotwych, większość skryptów obsługujących fora to tandem Apache, PHP wraz z MySQL. Oprogramowanie bazodanowe dostarczane przez Oracle ma jedną z najlepszych opinii w środowisku IT, dlatego też darmowa wersja (XE) została poddana analogicznym testom jak MSSQL i MySQL.
W prezentowanej metodyce przyjęto jeszcze jedno założenie, z którym w praktyce bazodanowej bardzo często mamy do czynienia, polegające na tym, iż klucz podstawowy jest jedno elementowy, przyjmujący wartości typu int.
Mimo, że zaproponowana metodyka całościowo nie pokrywa się z operacjami przebiegającymi w rzeczywistości, to zdaniem autorów jest dobrym narzędziem do realizacji badań porównawczych. Świadomie zrezygnowano w rozważaniach i badaniach z specjalistycznych narzędzi służących do ładowania danych (typu ETL) wykorzystywanych w tworzeniu hurtowni danych, skupiając się głównie na rozwiązaniach bazodanowych OLTP.

Narzędzie badawcze – procedury

Sygnalizowane powyżej procedury, stanowiące narzędzie badawcze, wytworzono wpierw w języku T-SQL, który jest językiem wbudowanym w SQL Server 2008R2. Pozwala on nie tylko na manipulowanie danymi (składowe języka DML – Data modyfication Language) ale również na definiowanie struktury (składowe języka DDL – Data definition Language). W zaprezentowanej poniżej procedurze, dotyczące przypadku zapisu danych w tabeli bez zdefiniowanego klucza podstawowego, wykorzystano obie wspomniane możliwości języka T-SQL. Podjęto również decyzję o zapisie wyników cząstkowych eksperymentu w dodatkowej tabeli. Do utworzenia agregatów, które tworzą wyniki cząstkowe, odnotowane w tabeli wykorzystano pytanie grupujące wraz z funkcją DateDiff. Grupę tworzą rekordy, które zostały zapisane do bazy w tym samym przedziale czasowym wynoszącym jedna sekunda. Ten sposób grupowania jest możliwy, albowiem jedno z pól tabeli podstawowej (simple_insert_table) zawiera datę i czas systemowy zwróconą przez funkcję GetDate.

CREATE PROCEDURE [dbo].[simple_insert]
@numerow int=1000;
@numtries int= 100;
AS
BEGIN
SET NOCOUNT ON;
declare @testb datetime;
declare @tests datetime;

create table simple_insert_table (
a int, b varchar(10), c datetime );

declare @numtries1 int=@numtries;
declare @numerow1 int=@numerow;

begin transaction;
set @testb = GETDATE();
while @numtries >0
begin
set @numerow = @numerow1;
while @numerow >0
begin
insert into simple_insert_table values (1,’1234567890’,GETDATE());
set @numerow = @numerow -1;
end;
set @numtries = @numtries -1;
end;
set @tests=GETDATE();

insert into test_results (test_name,start_d,stop_d,param1,param2)
values (‘simple insert test’,@testb,@tests,@numerow1,@numtries1);

set @numerow = @@IDENTITY;
insert into test_subresult (test_id, ins_num, time_agr)
select @numerow,count (DATEDIFF(S, ‘19700101’, c)) e, DATEDIFF(S, ‘19700101’, c) from
simple_insert_table
group by DATEDIFF(S, ‘19700101’, c);
insert into test_results (test_name,param1,param2,param3,param4,param5)
select ‘statistic for simple insert: ‘+ cast(@numerow as varchar),exec_per,max_ins,avg_ins,min_ins,stddev_ins from dbo.simple_insert_stat;
drop table simple_insert_table;
commit transaction;
END

Niewielkie zmiany w prezentowanym kodzie pozwoliły na zbudowanie nowych procedur, które stanowiły narzędzie testujące dla dwóch kolejnych przypadków. W pierwszym wariancie klucz był generowany przez algorytm zawarty w procedurze. Zmienna wykorzystywana do przekazywania wartości generowanego klucza do zapytania, stanowiła równocześnie składową warunku, decydującego o liczbie wykonanych pętli. W drugim przypadku do generowania klucza wykorzystano odpowiednie narzędzia serwera bazodanowego. W przypadku SQL Server 2008R2 wykorzystano mechanizm identity, natomiast jego odpowiednikiem na poziomie MySQL był autoincrement. Z kolei w RDBMS Oracle wykorzystano mechanizm sekwencji. Ingerencja w wspomniane narzędzia dotyczyła tylko ustawień początkowych, obejmujących wartość startową oraz skok, które we wszystkich przypadkach były identyczne. Celem zapewnienia możliwości dokonania uogólnień omawiany algorytm został zaimplementowany w języku PL/SQL oraz w proceduralnym rozszerzeniu języka SQL dla bazy MySQL 5.5.8, co pozwoliło na przeprowadzenie testów z wykorzystaniem innych RDBMS.
W trakcie podjętych przez autorów badań pojawiła się, co prawda nowa wersja SQL Server 2011 wyposażona tym razem w dwa mechanizmy, pozwalające na automatyczne tworzenie klucza podstawowego, lecz nie jest to wersja ostateczna, w związku z powyższym nie stała się ona przedmiotem eksperymentów. Tym dodatkowym narzędziem umożliwiającym automatyczne generowanie klucza na poziomie wspomnianego RDBMS jest mechanizm sekwencji [8].
Badania i wyniki

Badania przeprowadzono w wirtualnym środowisku, opartym o system operacyjny Windows XP Home Edition wraz z najnowszymi aktualizacjami. Instalacja miała charakter standardowy podobnie, jak późniejsza instalacja RDBMS (zaaprobowano wszystkie ustawienia kreatora). Po zakończeniu testów danego RDBMS następowała reinstalacja środowiska badawczego. Każdy z testów został także wykonany na maszynie serwerowej podpiętej do macierzy w środowisku Windows Server 2008 ( z wyłączeniem testów bazy Oracle). Procedury dla bazy Oracle zostały przetestowane na serwerze z systemem operacyjnym Ubuntu Server 11 także podpiętym do wydajnej macierzy. Wyniki z środowisk serwerowych były analogiczne do maszyny badawczej (relacje wyników testów dla danego RDBMS).
Powszechnie uważa się iż wyłączenie ograniczeń (np. Klucza podstawowego) podczas wstawiania dużej ilości danych pozwala zwiększyć wydajność zapisu. Opisywany eksperyment pozwala ocenić czy wskazana operacja jest wstanie przynieść realne zyski.
W celu przeprowadzenia testów potrzebne było utworzenie w każdym badanym RDBMS pustej bazy danych, również zgodnie z ustawieniami sugerowanymi przez kreatora. Dało to podstawy do osadzenia w niej procedur testujących z jednoczesnym utworzeniem struktury relacyjnej do gromadzenia wyników badań.
Test przeprowadzono dla każdego z RDBMS dla wyszczególnianych poniżej wariantów, podczas których wprowadzono do bazy 106 rekordów:
• wstawiano dane bez klucza głównego,
• wstawiano dane tworzące klucz główny z poziomu procedury
• wstawiono dane zawierające klucz główny utworzony przy użyciu mechanizmów RDBMS (autoincrement, identity, sequence).

Efekty przeprowadzonych badań zaprezentowano w formie tabelarycznej tab. 1, 2, 3 i 4. Zawierają one wielkości względne, a punktem ich odniesienia są wyniki uzyskane dla pierwszego wariantu badań. Powyższy sposób postępowania zastosowano do rezultatów uzyskiwanych dla każdego z badanych RDBMS. W przypadku MySQL 5.5.8 badania przeprowadzono dla dwóch różnych silników INNODB oraz MyISAM, dostępnych dla tego RDBMS. Natomiast niecelowym, zdaniem autorów było umieszczenie względnych wyników badań uzyskanych dla przypadku zapisu rekordów pozbawionych klucza podstawowego.

Omówienie wyników

Zaprezentowane wyniki badań zapisu danych zawierających i niezawierających klucz podstawowy niewiele się różnią się między sobą w przypadku SQL Server 2008 R2. Dotyczy to zarówno sytuacji, gdy klucz jest tworzony przez RDBMS, jak i przez algorytm zaszyty w procedurze testującej. Opóźnienie zapisu danych wyposażonych w klucz podstawowy, przy przyjęciu prędkości zapisu 5000 wierszy na sekundę oraz liczbie rekordów 106, wynosi 10 sekund. Ważnym odnotowania jest również fakt, iż w trakcie badań nie zaobserwowano spadku prędkości zapisu wierszy wraz ze wzrostem liczby rekordów zawartych w tabeli. Zbliżoną prawidłowość zauważono w przypadku MySQL 5.5.8 wyposażonego w silnik InnoDB z tym, że okazał się on bardziej wrażliwy na sposób generowania klucza podstawowego. Drugą dość zaskakującą zależnością, wynikającą z otrzymanych wyników dla tego RDBMS jest wzrost względnej prędkości zapisu danych, zawierających klucz podstawowy. Dotyczy to zarówno sytuacji gdy klucz główny jest generowany przez system bazodanowy, jak i procedurę. Równoległym faktem, wymagającym wyjaśnienia są zerowe wartości prędkości minimalnej uzyskiwane w trakcie badań MySQL 5.5.8[5]. Skutkowało to znacznym wzrostem odchylenia standardowego oraz tym, że minimalna prędkość względna przyjmowała wartości nieokreślone.
Tego rodzaju prawidłowości nie stwierdzono przy użyciu wcześniejszego silnika MyISAM MySQL 5.5.8 [4]. Odnotowane w tym przypadku tendencje, będę konsekwencjami pomiarów są zgodne z rezultatami uzyskanymi dla Oracle XE, które potwierdzają dotychczasowe przekonanie, że wprowadzenie klucza spowalnia zapis nowych wierszy do tabeli. Należy jednak przypomnieć o istotnych wadach silnika MyISAM, jakim jest brak możliwość tworzenia transakcji i definiowania więzów integralności referencyjnej [6].
Podsumowanie

Współczesne RDBMS bardzo się różnią pod względem implementacji modelu relacyjnego, co utrudniania formułowanie uogólnień i reguł dotyczących wydajności tych systemów informatycznych. W pewnych wypadkach rozwiązania intuicyjne, czy będące efektem dotychczasowych doświadczeń z RDBMS mogą się okazać nieefektywne przy pracy z nowymi systemami bazodanowymi, dlatego autorzy zalecają testowanie proponowanych rozwiązań zwłaszcza przed rozpoczęciem prac rozwojowych tworzonego oprogramowania (zwłaszcza w przypadku tworzenia struktur OLAP). Przeprowadzone badania wraz z dokonaną analizą wyników, z użyciem trzech różnych RDBMS skłoniły autorów do sformułowania następujących uwag i wniosków:
• Wyposażenie danych w klucz podstawowy ogranicza z reguły wydajność zapisu rekordów w każdym badanym RDBMS z wyłączaniem MySQL 5.5 zawierającego silnik InnoDB. Spadek tej wydajności jest zróżnicowany lecz z perspektywy SQL Server 2008R2 jest on mało znaczący.
• Mechanizm generowania klucza podstawowego wbudowany w RDBMS z perspektywy wydajności zapisu nowych wierszy jest generalnie rozwiązaniem lepszym od własnych rozwiązań programistycznych zaszytych w procedurach.
• Wskazanym wydaje się podjęcie dalszych wysiłków poznawczych, zmierzających do wyjaśnienia nietypowego zachowania się MySQL 5.5.8 z silnikiem InnoDB z perspektywy prędkości zapisu danych pozbawianych i wyposażonych w klucz podstawowy.
• Uzyskane całkowite czasy zapisy danych w analizowanych wariantach i w różnych RDBMS i przy zaproponowanej metodyce badań wskazują, że z perspektywy aplikacji OLTP korzyści wydajnościowe, wynikające z niestosowania jednoelementowego klucza podstawowego są mało znaczące.
Bibliografia

[1] Beynon-Davies – Database Systems 2003
[2] Joe Celko – SQL for Smarties, Advanced SQL Programming, 3 Edition 2005
[3] MySQL Reference Manual – http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html 2011
[4] MySQL MyISAM Storage Engine Manual – http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html
[5] MySQL InnoDB – http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html
[6] MySQL refman – http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html
[7] Paulraj Ponniah – Data Warehousing Fundamentals for IT Professionals 2010
[8] SQL Server perfomance – http://www.sql-server-performance.com/articles/dev/sequence_sql_server_2011_p1.aspx 2011
[9] Chris Todman – Designing a data warehouse: supporting customer relationship management 2003

Instancja bazy danych

Instancja bazy danych to byt nadrzędny utożsamiony z oprogramowaniem zarządzającym przetwarzanymi danymi. W przypadku Oracle instancja utożsamiana jest z jedną logiczną bazą danych. Instancja bazy danych Oracle składa się ze struktur pamięciowych, do których dostęp mają wszystkie procesy ją obsługujące oraz ze struktur prywatnych, dostępnych tylko dla procesów, które te struktury za-alokowały.
Można powiedzieć że Instancja Oracle składa się z struktur pamięciowych i procesów systemu operacyjnego obsługujących bazę danych. W przypadku SQL Server Instancja może obsługiwać wiele logicznych baz danych. Podobnie jak w SQL Server również MySQL pozwala na przechowywanie wielu logicznych baz danych. Konsekwencją takiego podejścia wspomnianych RDBMS jest możliwość zarządzania uprawnieniami i polityką kopii bezpieczeństwa na nieco innym poziomie (logiki danych) niż w przypadku Oracle.
W kontekście zbiorów danych instancją obiektu możemy nazwać jego wystąpienie. W przypadku baz danych dość częstym przypadkiem(niekoniecznie prawidłowym) jest fakt zapisu tego samego obiektu w różnych stanach(instancjach) mimo iż poprawność logiczna danych jest zachowana na poziomie jednostkowym może ona później zaburzać procesy analityczne.

Klucz Obcy -teoria

Klucze obce łączą dane przechowywane w różnych tabelach. Klucz obcy jest kolumną lub grupą kolumn z jednej tabeli, która czerpie swoje wartości z tej samej dziedziny co klucz główny tabeli powiązanej z nią w bazie danych. W systemach relacyjnych wprowadzamy specjalną wartość, aby wskazać niepełną lub nieznaną informację – wartość „null”.
Ta wartość, różna od zera i spacji, jest szczególnie użyteczna przy powiązaniu kluczy głównego i obcego.

Z pojęciem klucza głównego i obcego wiąże się pojęcie integralności:

Integralność referencyjna dotyczy kluczy obcych. Reguła integralności referencyjnej mówi, że każda wartość klucza obcego może się znajdować tylko w jednym z dwóch stanów.
Normalnie wartość klucza obcego odwołuje się do wartości klucza głównego w tabeli w bazie danych. Czasami wartość klucza obcego może być null i fakt ten zależy od zasad związanych z przedsięwzięciem. W tym wypadku jawnie stwierdzamy, że nie ma związku z reprezentowanymi obiektami w bazie danych albo że ten związek jest nieznany [Beynon-Davies 2003].

Oczywiście oprócz integralności referencyjnej istnieje integralność encji ( która w sumie powinna być pierwsza opisana):

Integralność encji dotyczy kluczy głównych. Jest ona również regułą integralności, która mówi, że każda tabela musi mieć klucz główny oraz że kolumna lub kolumny wybrane jako klucz główny powinny być jednoznaczne i nie zawierać wartości null.

Dla każdego związku między tabelami w naszej bazie danych powinniśmy określić, jak mamy postępować z usuwaniem docelowych i powiązanych kartotek. Na przykład, jeżeli usuniemy wiersz z tabeli w bazie danych, to musimy zdecydować, co powinno się stać z powiązanymi wierszami w tabeli korzystającej z klucza obcego wskazującego na tabele, w której usuwamy wiersz.

Są trzy (a właściwie cztery) możliwości:

graniczone usuwanie (ang. restricted delete). Jest to ostrożne podejście. Oznacza to, że zabraniamy usunąć wiersz np. z danymi wykładowcy dopóty, dopóki nie będą usunięte wszystkie wiersze informacji o zajęciach prowadzonych przez tego wykładowcę.

Kaskadowe usuwanie (ang. cascades delete). Jest to ufne podejście. Jeżeli usuwamy wiersz np. nagłówka dokumentu, to są jednocześnie usuwane wszystkie powiązane z nim pozycje tego dokumentu.

Wstaw null przy usuwaniu (ang. nullifies delete). Jest to wyważone podejście. Jeżeli usuwamy wiersz wykładowcy, to numery pracowników w powiązanych wierszach przedmiotów ustawiamy na null.

Istnieje także możliwość wymuszenia braku akcji
(ang. no action). Jest to podejście wysoce nie rozważne, co uargumentowałem we wstępie do integralności referencyjnej. Jeżeli usuniemy wiersz klucza głównego (wykładowcy) to wartości klucza obcego (np. prowadzonych przez niego przedmiotów nie ulegną zmianie).

Klucz główny – teoria

Zgodnie z założeniami modelu relacyjnego tabele (encje) powinny mieć klucz główny. Niestety nie wszyscy producenci baz danych zaimplementowali swoje produkty w sposób jaki był zakładany przez autora modelu relacyjnego ( E. Codd’a)

Każda relacja musi(powinna) mieć klucz główny. Dzięki temu możemy zapewnić, niepowtarzalność wierszy w relacji. Klucz główny to jedna lub więcej kolumn tabeli, w której wartości jednoznacznie identyfikują każdy wiersz w encji.

Reasumując:
Klucz główny (primary key) oznacza wybrany minimalny zestaw atrybutów relacji,
jednoznacznie identyfikujący każdy wiersz encji.

Klucz główny można założyć w momencie zapewnienia unikalności danych
oraz niedopuszczenia do wartości nieokreślonej na kolumnie (kolumnach)
klucza głównego.

Czym jest baza danych – teoria

Czym właściwie jest baza danych? Poniżej przedstawiam moją definicję opracowaną na podstawie lektury książek (w szczególnośći: Systemy baz danych Beynon-Davies 2003).

Baza danych jest pewnym modelem wycinka rzeczywistości w danej organizacji
(np. firmie, uczelni). Tę rzeczywistość nazywamy obszarem analizy (ang. Uniwerse Of
Discourse, UOD).

Bazę danych możemy uważać za zbiór danych (faktów), których zadaniem jest
reprezentowanie określonego UOD. Jednostka danych, jest symbolem, którego używamy, aby
reprezentować jakąś rzecz. Chcąc by fakty były użyteczne, muszą być one zinterpretowane.
Dane zinterpretowane to informacje. Natomiast informacje to dane w otoczeniu (kontekście)
nadającym im znaczenie. Zbiór faktów lub pozytywnych asercji na temat obszaru analizy
tworzą bazę danych. Zazwyczaj fakty negatywne, np. które przedmioty nie są zaliczane przez
studenta, nie są przechowywane [Beynon-Davies 2003].

jak działają transakcje – systemy OLTP

By zapewnić dostępność danych w systemie transakcyjnym (czyli takim w którym użytkownicy modyfikuj dane), należy porozmyślać trochę nad samym systemem. Załóżmy że pracujemy na systemie przy którym równolegle na różnych stanowiskach pracuje 5 fakturzystek. Jest to system do wypisywania faktur i obsługi stanów magazynowych. Dla pełni obrazu ustalmy model iż klient najpierw przychodzi składa zamówienie na podstawie którego powstaje faktura. Klient z fakturą udaje się na magazyn gdzie towar jest kompletowany i wydawany. Model ten jest dużym uproszczeniem, ale pozwoli przedstawić idee transakcji.

Patrząc atomowo na proces wypisywania faktury:
musimy wystawić nagłówek i zacząć dodawać pozycje.
Dodanie pozycji to wpisanie jej na fakturę plus zmniejszenie stanów magazynowych + sprawdzenie linii kredytowej klienta (przekroczenie linii powoduje niemożność dodania kolejnej pozycji do faktury).

Na dodaniu pozycji się skupimy. Wyraźnie widać że musimy przeprowadzić kilka operacji za jednym zamachem.
Musimy sprawdzić stan, jeśli jest wystarczający pobrać go na fakturę i zmniejszyć na magazynie itd…

Kilka operacji na bazie które stają się integralnym procesem powinno przeprowadzić się w transakcji. Transakcja jest rozliczana zero jedynkowo (sukces / porażka), w przypadku porażki baza powraca do stanu sprzed uruchomienia transakcji(mówimy o poziomie modyfikowanych danych). Dodatkowo transakcja alokuje nam zasoby a w wielu przypadkach wręcz je blokuje (o czym dokładniej się rozpiszę za chwilę). Najważniejsze jest jednak że transakcja nas chroni. Ochrona ta polega na tym iż wykonując krokowo te czynności może w trakcie dojść do styuacji gdy pójdzie coś nie tak. Zdejmiemy stan ale nigdzie go nie wstawimy. Patrząc z perspektywy banku i przelewu zdejmiemy pieniądze z jednego konta ale ich nigdzie nie przelejemy, lub na odwrót wstawimy pieniądze na konto i ich gdzieś nie zdejmiemy. Takie sytuacje są bardzo niebezpieczne. Dlatego też jeśli w jednej operacji korzystamy z więcej niż jednego zapytania musimy to objąć w transakcje.
W większości systemów wygląda to tak:

begin transaction
update tabela1 set pole1=23 where id=23;
insert into tabela2 set pol2=34 where id=324435;
commit;
end transaction;

Np w Oracle zaczynając pierwszą operację DML ustanawiamy transakcję i będzie ona trwać do momentu
wydania polecenia commit/rollback. Dla nowych użytkowników jest to dość zaskakujące i może się skończyć zlokowaniem tabel. W momencie gdy w Oracle aktualizujemy dane (otwieramy transakcje) pozostałe transakcje dla tej tabeli są wstrzymane (czekają w kolejce). Pozostali użytkownicy mogą w tym czasie pobierać dane (robić selecty) jednak ujrzą dane sprzed otwarcia transakcji. Objawem otwartje transakcji jest np dodanie pozycji do faktury które zawiesza program, albo wykonanie zapytania które wykonuje się w ułamek sekundy (normalnie), a tym razem się wykonuje bez końca. W Oracle są odpowiednie widoki, które pozwalają przejrzeć locki na tabelach. W przypadku MSSQL sprawa wygląda zupełnie inaczej. Musimy napisać sami że chcemy coś zapiąć w transakcję. Jeśli tego nie zrobimy, czekają nas 2 poważne konsekwencje. Z jednej strony brak gwarancji iż całość przebiegnie poprawnie. Z drugiej strony nie możliwości cofnięcia transakcji. gdy zmiany się dokonają i efekt nas nie zadowala to musimy napisać zapytania które przywrócą stann poprzedni co nie zawsze jest możliwe. Kolejną różnicą jest fakt iż w MSSQL abele są blokowane już przy pobieraniu danych. Więc tworząc raport który wykonuje np 10 min. Na te 10 min blokujemy tabele z których tworzy się raport. Na szczęście czasem MSSQL wykorzystuje bazę tymczasową gdzie wrzuca kolejne kroki zapytań zwalniając zasoby. Tak więc operując na wielu tabelach możemy skrócić czas blokowania tabel. Dodatkowym bonusem od Billa jest składnia with (nolock).

select * from tabela with (nolock)

Pozwala to na wykonanie zapytania które nie zablokuje tabeli. Niestety musimy liczyć się z tym iż w wyniku dostaniemy nie aktualne dane. Jednak np dla raportów za minione okresy jest to stosunkowo dobre rozwiązanie.

Ew. Gdy zależy nam na jakiś konkretnych danych mozemy tylko część tabel zrobić z with nolock, a najważniejsze dane pobać bez tego parametru:

select * from tabela1 t
inner join tabela t2 with(nolock) on
t1.id = t2.id;

Jak się ma sprawa w MYSQL? To już zupełnie inna historia. W mysql mamy do wyboru różne silniki. Istnieją silniki które nie wspierają transakcji. W przypadku wsparcia dla transakcji nie wiem jaki jest ich wpływ na locki na tabelach.

Istnieje także pojęcie transakcji rozproszonych na różnych maszynach. W przypadku linku 2 mssqli lub nawet 2 baz na jednej instancji, jesteśmy w stanie przeprowadzić sensowną transakcję. Wymaga to pewnej prekonifguracji ale jest to osiągalne. W przypadku Oracle na zdalnym serwerze (linkowanym) Nie ma możliwości przeprowadzenia DML.
Jeśli chodzi o środowiska heterogeniczne (MSSQL i ORACLE). W przypadku korzystania z OpenQuery nie jesteśmy w stanie przeprowadzić sensownej transakcji. Openquery wymusza commit po każdym zapytaniu. Można skorzystać z dodatkowych pól w tabelach do oznaczania statusów ale to już zabawa.

W oracle mamy jeszcze transakcje autonomiczne. Jest to transakcja w transakcji. Przydatne zwłaszcza do logowanai błędów. Cofamy główną transakcję a pod spodem zapisujemy informacje o błędzie. Bardzo przydatna sprawa!

Istnieje jeszcze pojęcie stopnia izolacji transakcji ale to już zagłębianie się w szczegóły.

DML – insert, wstawianie danych

Aby wstawić dane do bazy należy skorzystać z następującej składni:

insert into tabela (nazwy_kolumn... ) values (wartosc);

Na chwilę obecną załóżmy że działamy na przykładowej tabeli przyjaciele i tylko my mamy do niej dostęp. Kwestia współdzielonego dostępu i konsekwencji transakcji zostaną omówione później. W tej części chciałbym się skupić na samej technice ‘wkładania danych’.

tak więc załóżmy że chcemy dodać przyjaciela do naszej tabeli.

insert into przyjaciele (imie,zarobki,plec,kolor) values ('janek','4500','1','5');

Jeśli pracujemy na bazach Mysql,Mssql to wystarczy by wstawić dane. W przypadku bazy Oracle należy wykonać commit;

Commit jest potwierdzeniem zmodyfikowania danych, jeśli coś nam się nie spodoba możemy użyć polecenia Rollback;Te dwa zagadnienia zostaną dokładnie omówione później.

Możemy dane wstawiać także za pomocą zapytań pobierających dane.

insert into przyjaciele (imie,zarobki,plec,kolor)
select 'janek','4500',1,5 from dual

dual jest tabelą oraclową, wyimaginowanym Tworem do zabaw związanych z pobieraniem danych nie z tabel.
W mssql i mysql nie ma tego tworu więc wystarczy:

insert into przyjaciele (imie,zarobki,plec,kolor)
select 'janek','4500',1,5;

można też z innej tabeli:

insert into przyjaciele (imie,zarobki,plec,kolor)
select imie,wynagrodzenie,plec,kolor from starz_przyjaciele;

We wstępie wspomniałem o konieczności wykorzystania kopii zapasowej. Jak najszybciej stworzyc kopie zapasowa tabeli z przyjaciółmi?

select * into kopia_przyjaciol from przyjaciele
na bazie mssql

lub na oracle:

create table kopia_przyjaciol as select * from przyjaciele;

W poprzednich rozdziałach korzystaliśmy z tabeli przyjaciele o takiej definicji:

id int, — unikalny id – pole numeryczne
imie varchar2(255), — pole tekstowe
plec int, — 0 dziewczyna 1 facet — wartosc numeryczna
wiek int, — wiek w latach — wartosc liczbowa
zarobki int, — zarobki w tysiacach — wartosc liczbowa
kolor int); — kolor skory 0 jasny 10 ciemny – skala — wartosc liczbowa

poprzednie zapytania pomijały część kolumn. Tak Jeśli wartość w kolumnie nie jest wymagana w pomienięciu kolumny w insercie powstanie wartość null. O wymagalności wartości w kolumnie napiszę w rozdziale o tworzeniu baz danych.

oczywiście w trakcie insertów możemy korzystać z funkcji podzapytań itd. Wszystkie zasady logiki z pobierania danych muszą zostać spełnione przy insertowaniu.

Istnieje też coś takiego jak wartość domyślna, u nas polem do takiego opisu może być kolumna id.
W większości baz jest to pole które zostanie automatycznie wypełnione wartością przy czym
W zależności od bazy możemy spotkać się z różnymi zachowaniami i nazwami:
Dla mssql słowem wytrych jest autoidentity – przypisanie w insercie wartosci do takiej kolumny zwraca błąd istnieje parametr dzięki któremu możemy chwilowo wyłączyć autoidentity i zainsertować dane. Jest to przydatne przy większych operacjach kopiowania fragmentów baz danych… Autoidentity jest raczej gap free, znaczy się nie powstają tam dziury w numeracji (do momentu usunięcia wiersza).

W mysql – słowem klucz jest Autoincrement. Tutaj w zależności od silnika jaki wykorzystujemy zachowania mogą być różne. Nie zajmuje się Mysql na codzień więc nie będę sie tutaj bawił w eksperta ale mogę odesłać do archiwum grup dyskusyjnych o bazach danych i o języku php słowa kluczowe to Innodb i MyISAM

W oracle sytuacja jest najbardziej zakręcona – Słowo klucz to sekwencja. Jednakże sekwencja musi być powiązana z triggerem(wyzwalaczem). Każdy wyzwalacz jest pisany indiwidualnie więc może się różnie zachowywać. Ja moje wyzwalacze piszę tak że podmieniają bezwzględnie wartość pola id na numer podany przez sekwencję. Omawiane twory nie są gap free (tworzą dziury w numeracji). Plusem jest ich uniwersalność i miła dostępność z poziomu kodu
do właściwości.

Kilka słów odnośnie wyzwalaczy które są już obecne w najpopularniejszych bazach (Mysql > 5.0,Mssql,Oracle).

Wyzwalacz -> Trigger – jest to kod który reaguje na operacje DML (zmiany danych). Triggery nie działają dla selectów). Głównie triggery się wykorzystuje do kontrolowania poprawności i integralności danych, Zapisywania nadpisanych danych oraz w celu rozszerzenia zabezpieczeń.

Triggery mogą w locie nadpisywać wartości które insertujemy, więc insertując np wynagrodzenie 4500
w bazie może zapisać się 2250.

Przed wykonaniem insertów powinniśmy mieć dobrze rozpoznaną bazę na której pracujemy.
Dość miła informacją jest fakt iż jeśli podamy coś źle w zapytaniu mamy nikłe szanse na destrukcje w pryzpadku inserta. Dobrze ustawiona baza nie pozwoli zapisać danych które są niespójne. Przy insertach mamy dość niską szansę na zepsucie czegokolwiek. Oczywiście do momentu jak nie zmieniamy ustawień constraintów czyli wiezów integralności.

Teraz kilka słów o transakcjach. Jest to generalnie temat rzeka, ale kilka podstawowych rzeczy należy wiedzieć. Niestety w każdej bazie jest to realizowane w nieco inny sposób ale poniżej opiszę główne założenia a w szczegóły zagłębię sie przy innej okazji.

Załóżmy że mamy dwóch użytkowników. którzy jednocześnie chcą uzyskać dostęp do danych. Wszystko jest ok
ale jeśli jeden z użytkowników zaczyna modyfikować dane co się dzieje? Wtedy w mniej lub bardziej świadomy sposób otwiera się transakcja. I w zależności od bazy albo użytkownik który chce tylko zapytać o dane dostanie dane niezaktualizowane lub dostanie czerwone światło i będzie musiał poczekać na zakończenie transakcji.

Wstawianie spójnych danych do wielu tabel powinno być świadomie wsadzone w jedną transakcję. O ile jedno zapytanie w większości przypadków tworzy spójną transakcję o tyle 2 zapytania już takiego tworu nie tworzą (przynajmniej w MSSQL i MYSQL). Tak więc insertując np fakturę jako nagłówek i pozycję koniecznie musimy zamknąć to w jedną transakcję(w przypadku gdy mamy gotowe dane). Kolejną sprawą jest minimalizowanie czasu trwania transakcji. Nie można dopuścić do sytuacji gdy transakcja zostaje otwarta i oczekuje na jakiś wybór użytkownika. W takim wypadku należy sprawę rozbić na bardziej atomowe transakcje i zaimplementować mechanizmy zachowujące spójność danych.