SQL Transakcje

1.
2.
3.
4.
5.
Transakcje.
Współbieżność.
Widoki
Indeksy
Tworzenie i usuwanie baz danych- Plik danych i plik logów.
Transakcje to jedno z podstawowych pojęć współczesnych systemów baz danych. Umożliwiają one
współbieżny dostęp do zawartości bazy danych, dostarczając niezbędnych mechanizmów
synchronizacji.
str. 1
Istotą transakcji jest integrowanie kilku operacji w jedną niepodzielną całość.
Transakcja – zbiór operacji na bazie danych, które stanowią w istocie pewną całość i jako takie
powinny być wykonane wszystkie lub żadna z nich. Warunki jakie powinny spełniać transakcje
bardziej szczegółowo opisują zasady ACID (Atomicity, Consistency, Isolation, Durability –
Atomowość, Spójność, Izolacja, Trwałość).
Przykładem transakcji może być transakcja bankowa jaką jest przelew. Muszą tu zostać dokonane 2
operacje – zabranie pieniędzy z jednego konta oraz dopisanie ich do drugiego. W przypadku
niepowodzenia żadna z tych operacji nie powinna być zatwierdzona, gdyż zajście tylko jednej
powodowałoby nieprawidłowości w bazie danych (pojawienie się lub zniknięcie pieniędzy).
Transakcja składa się zawsze z 3 etapów:
•
•
•
rozpoczęcia
wykonania
zamknięcia
str. 2
W systemach bazodanowych istotne jest, aby transakcja trwała jak najkrócej, ponieważ równolegle
może być dokonywanych wiele transakcji i część operacji musi zostać wykonana w pewnej
kolejności. Każdy etap transakcji jest logowany, dzięki czemu w razie awarii systemu (dzięki
zawartości logów), można odtworzyć stan bazy danych sprzed transakcji, która nie została
zamknięta.
Część systemów baz danych umożliwia używanie punktów pośrednich (ang. save point), są to
zapamiętane w systemie etapy transakcji, do których w razie wystąpienia błędu można się wycofać,
bez konieczności anulowania wszystkich wykonanych działań.
W systemach baz danych realizujących standard SQL następujące polecenia dotyczą transakcji:
•
•
•
BEGIN lub BEGIN WORK – rozpoczęcie transakcji;
COMMIT – zatwierdzenie zmian wykonanych w obrębie transakcji;
ROLLBACK – odrzucenie zmian wykonanych w obrębie transakcji;
•
•
SAVEPOINT nazwa – zdefiniowanie punktu pośredniego o określonej nazwie;
RELEASE SAVEPOINT nazwa – skasowanie punktu pośredniego (nie wpływa w żaden
sposób
na stan transakcji);
•
ROLLBACK TO SAVEPOINT nazwa
– wycofanie transakcji do stanu zapamiętanego w
podanym punkcie pośrednim.
str. 3
SQL Server pozwala na rozpoczęcie transakcji w jednym z trzech trybów:
1. Jawne rozpoczęcie transakcji (ang. Explicit) — poprzez wydanie polecenia BEGIN
TRANSACTION. Explicit jest wykonywana zawsze wtedy, gdy programista w kodzie TSQL zadeklaruje chęć wykonania danego zapytania (bloku zapytań) w ramach transakcji.
2. Automatyczne rozpoczęcie i zatwierdzenie transakcji (ang. Autocommit) — poprzez
wykonanie dowolnej instrukcji języka Transact-SQL. Ten tryb jest domyślnym trybem SQL
Servera.
3. Niejawne rozpoczęcie transakcji (ang. Implicit) — poprzez włączenie opcji
IMPLICIT_TRANSACTIONS. W tym trybie wykonanie kolejnej instrukcji spowoduje
automatyczne rozpoczęcie kolejnej transakcji. Transakcje Implicit wykonywane są zawsze
wtedy, gdy co najmniej jedna z następujących komend jest wykonywana: ALTER TABLE,
CREATE, DELETE, DROP, FETCH, GRANT, OPEN, REVOKE, SELECT, TRUNCATE,
UPDATE.
WSPÓŁBIEŻNOŚĆ
Współbieżność transakcji
Współbieżność transakcji występuje w sytuacji kiedy kilka transakcji wykonuje operacje na tych samych
danych. Podczas wykonywania współbieżnych transakcji mogą wystąpić niepożądane anomalie. System
Zarządzania Baz Danych powinien dostarczać mechanizmów, które zapobiegają wystąpieniu niepożądanych
anomalii jednocześnie nie ograniczając wydajności bazy danych.
Celem wprowadzenia poziomów izolacji transakcji jest znalezienie kompromisu pomiędzy poprawnością
wykonywania transakcji a zapewnieniem jak największego stopnia współbieżności wykonywanych
transakcji. Wraz ze wzrostem poziomu izolacji transakcji maleje stopień współbieżności.
Anomalie współbieżnego dostępu
Podczas wykonywania równolegle transakcji na tych samych danych, w zależności od poziomu izolacji
transakcji mogą występować następujące anomalie:
•
brudny odczyt,
str. 4
•
•
•
utracona modyfikacja,
niepowtarzalny odczyt,
fantomy.
Aby właściwie stosować poziomy izolacji niezbędne jest zrozumienie tych anomalii.
Brudny odczyt
Brudny odczyt (ang. Dirty read / Uncommitted dependency) to sytuacja, w której transakcja odczytuje dane
zmienione przez inną transakcje, która później zostaje wycofana, a więc odczytane dane przez pierwszą
transakcje są nieprawdziwe.
Utracona modyfikacja
Utracona modyfikacja (ang. Lost update) to sytuacja, w której dwie transakcje równoległe próbują zmienić
te same dane i zmiany wprowadzane przez jedną transakcje mogą być nadpisane przez drugą transakcję.
Niepowtarzalny odczyt
Niepowtarzalny odczyt (ang. Non-repeatable read / Inconsistent Analysis) powstaje w sytuacji gdy jedna z
transakcji wykonuje kilkakrotnie te same zapytania na danych, które w między czasie są zmieniane przez
inną transakcję, co powoduje, że to samo zapytanie nie generuje tych samych rezultatów.
Fantomy
Fantomy (ang. Phantoms) to sytuacja podobna do niepowtarzalnego odczytu. Jedna transakcja wykonuje
kilkakrotnie to samo zapytanie ale otrzymuje różne zbiory wynikowe, ponieważ inna transakcja w tym
samym czasie usuwa lub dodaje nowe dane, które spełniają warunki zapytania. To powoduje, że za każdym
razem wynikiem zapytania może być inna liczba wierszy.
Poziomy izolacji
Dla każdej transakcji można ustalić w jakim stopniu ma ona być izolowana przed zmianami wykonywanymi
przez inne transakcje. Stopień izolacji determinuje, które anomalie związane z współbieżnym
wykonywaniem transakcji są dozwolone. Poziomy izolacji kontrolują:
•
•
•
czy mają być zakładane blokady na odczytywane dane i jakiego rodzaju blokady są wymagane,
na jak długo blokady odczytu są zakładane,
czy operacja związana z odczytywaniem danych z wiersza modyfikowanego przez inną transakcję:
o ma być zablokowana, aż zostanie zwolniona blokada wyłączności (typu X),
o nie jest blokowana ale odczytuje wersję danych zatwierdzoną wcześniej przed
rozpoczęciem transakcji,
o nie jest blokowana i odczytuje dane niezatwierdzone przez inną transakcję.
Algorytm blokowania dwufazowego (ang. two-phase locking – 2PL ) jest jedną z
najpopularniejszych metod implementacji transakcji. Algorytm ten należy do ogólniejszej
klasy algorytmów sterowania współbieżnością, które stosują blokowanie. Działanie takich
algorytmów w najprostszym przypadku polega na założeniu blokady na danej, która ma być
zapisana lub odczytana, a po wykonaniu operacji blokada jest zwalniana.
Algorytm blokowania dwufazowego składa się z dwóch faz: fazy wzrostu (ang. growing
phase ) oraz fazy zmniejszania (ang. shrinking phase ). W pierwszej fazie transakcja może
blokować zasoby, ale nie wolno jej zwalniać zasobów, które wcześniej już zablokowała. W
str. 5
drugiej fazie transakcja może zwalniać zasoby, lecz nie wolno jej blokować żadnych
nowych zasobów. Jeżeli proces nie chce modyfikować danych dopóki nie osiągnie momentu
przed fazą zmniejszania, to w razie niepowodzenia przy zakładaniu jakiejś blokady może on
zwolnić wszystkie blokady i rozpocząć ponownie algorytm.
Blokady współdzielone S (ang. Shared) są zakładane domyślnie na odczytywanych obiektach tylko
na czas wykonywania zapytania. Jeżeli dane zostały zablokowane w trybie S, to możliwe jest
założenie na nie blokady S przez inne procesy.
Blokady wyłączne X (ang. eXclusive) są zakładane na modyfikowanych obiektach i domyślnie
utrzymywane do zakończenia całej transakcji. Użytkownicy modyfikujący dane blokują innych
użytkowników.
Ustalenie poziomu izolacji nie wpływa na blokady, które są ustawiane aby chronić proces modyfikacji
danych. Transakcja zawsze otrzymuje blokadę wyłączności (typu X) na dane, które modyfikuje, i blokada ta
jest utrzymywana aż do momentu aż transakcja nie zostanie zakończona, niezależnie od tego jaki poziom
izolacji ma ustawiona transakcja. Dla operacji odczytu, poziomy izolacji definiują poziom zabezpieczenia
przed efektami zmian wykonywanych przez inne transakcje.
Standard SQL-92 definuje cztery poziomu izolacji transakcji (kolejność od najniższego stopnia izolacji do
najwyższego):
•
•
•
•
READ UNCOMMITTED - niezatwierdzony odczyt (poziom izolacji 0),
READ COMMITTED - odczyt zatwierdzonych danych (poziom izolacji 1),
REPEATABLE READ - powtarzalny odczyt (poziom izolacji 2),
SERIALIZABLE - uszeregowalny (poziom izolacji 3).
str. 6
W MS SQL Server stosuje się dwa dodatkowe poziomy izolacji oparte na wersjonowaniu wierszy. Jeden z
nich to nowa implementacja odczytu zatwierdzonych danych READ UNCOMMITTED z włączoną opcją
READ_COMMITTED_SNAPSHOT , a drugi to nowy poziom izolacji, wyższy od powtarzalnego odczytu ale
niższy od poziomu uszeregowalnego. Jest to poziom SNAPSHOT.
Możliwość wystąpienia anomalii związanych z współbieżnością transakcji w zależności od
poziomu izolacji
READ UNCOMMITTED - Odczyt niezatwierdzonych danych
Odczyt niezatwierdzonych danych (ang. read uncommitted) jest najniższym poziomem izolacji, która
zabezpiecza jedynie przed odczytem fizycznie uszkodzonych danych. Jest to najmniej restrykcyjne
ustawienie, które praktycznie powoduje ignorowanie założonych blokad. Ten poziom izolacji jest dostępny
tylko dla transakcji wykonywanych w trybie READ ONLY.
Transakcja wykonywana z tym poziomem izolacji nie zakłada blokad współdzielonych, w celu
uniemożliwienia innej blokadzie modyfikacji odczytywanych przez nią danych. Transakcje te nie są
blokowane przez blokady wyłączności, które chroniłyby tę transakcję przed odczytem wierszy, które były
zmodyfikowane ale nie zatwierdzone przez inną transakcję. Wartości danych mogą być zmienione, wiersze
mogą znikać lub pojawiać się nowe przed ukończeniem transakcji.
READ COMMITTED - Odczyt zatwierdzonych danych
Poziom izolacji odczyt zatwierdzonych danych (ang. read committed) zapewnia transakcji, która taki poziom
posiada, że odczytuje ona dane utworzone wyłącznie przez zatwierdzone transakcje i wartość żadnej danej,
zapisywanej przez tę transakcję nie zostanie zmieniona przez inną transakcję do momentu zakończenia tej
transakcji. Poziom ten nie gwarantuje jednak, że wartość danej odczytywanej przez tę transakcję nie
zostanie zmieniona przez inną transakcję do momentu jej zakończenia. Ten poziom izolacji nie gwarantuje
również rozwiązania anomalii fantomów i, stąd, nie gwarantuje uszeregowalności wszystkich realizacji.
W MS SQL Server jest to domyślny poziom izolacji transakcji i występuje on w dwóch wersjach, w zależności
str. 7
od tego czy włączona jest opcja READ_COMMITTED_SNAPSHOT (domyślnie jest wyłączona). Jeśli jest
wyłączona to transakcja zachowuje się tak jak to jest opisane powyżej, natomiast jeśli jest włączona to
podczas zmiany wartości w bazie w ramach transakcji jest tworzony snapshot starych danych dzięki czemu
inna transakcja może bez problemu odczytać wartość z bazy nie czekając na zatwierdzenie pierwszego
zapisu. Niesie to jednak za sobą poważne konsekwencje – z pewnością znacznie zmniejszy się liczba
deadlocków (zakleszczeń transakcji) w bazie ale taka sytuacja może nie być akceptowalna z punktu widzenia
biznesowego.
REPEATABLE READ - Powtarzalny odczyt
Poziom izolacji powtarzalny odczyt (ang. repeatable read) zapewnia, że każda transakcja, która ten poziom
ma ustawiony odczytuje dane utworzone wyłącznie przez zatwierdzone transakcje i wartość żadnej danej,
odczytywanej lub zapisywanej przez tę transakcję nie zostanie zmieniona przez inną transakcję do
momentu zakończenia transakcji. Niestety, ten poziom izolacji nie gwarantuje rozwiązania anomalii
fantomów i, stąd, nie gwarantuje uszeregowalności wszystkich realizacji.
Transakcja wykonywana na tym poziomie izolacji nie może odczytywać danych, które zostały
zmodyfikowane ale jeszcze nie zatwierdzone przez inne transakcje oraz żadne inne transakcje nie mogą
modyfikować danych, które są odczytywane przez tę transakcję dopóki transakcja się nie zakończy.
Zakładane są blokady współdzielone (typu S) na wszystkie dane odczytywane przez każde polecenie
transakcji i utrzymywane są do zakończenia transakcji. Takie blokady nie pozwalają innym transakcją
modyfikować ani wierszy, które są odczytywane przez tę transakcję. Inne transakcje mogą jednak wstawiać
nowe wiesze, które pasują do warunków wyszukiwania bieżącej transakcji, co powoduje powstawaniu
anomalii fantomów. Ponieważ blokady współdzielone trzymane są przez tę transakcje do czasu jej
ukończenia a nie po wykonaniu danego polecenia, dlatego współbieżność jest znacznie mniejsza niż przy
izolacji domyślnej READ COMMITTED, dlatego należy używać tego poziomu tylko gdy jest to konieczne.
SNAPSHOT - Migawka
Poziom izolacji SNAPSHOT - miagawka został zaimplementowany w MS SQL Server i wykorzystuje
technologię wersjonowania wierszy. Każda transakcja w momencie jej utworzenia tworzy sobie snapshot
danych i na nim pracuje do czasu jej zakończenia. Inne transakcje nie są w stanie zmodyfikować danych,
które zostały użyte. Nawet jeżeli inna transakcja zmodyfikuje dane to oryginalna transakcja cały czas
pracuje na danych z momentu jej utworzenia. Ten poziom izolacji można używać w bazie MS SQL Server
tylko jeśli włączona jest opcja ALLOW_SNAPSHOT_ISOLATION. Jeżeli transakcja na poziomie izolacji
SNAPSHOT korzysta z kilku baz danych opcja ALLOW_SNAPSHOT_ISOLATION musi być włączona na każdej z
tych baz.
Transakcja wykonywana na tym poziomie izolacji będzie wykorzystywała wersję danych, które istniały przed
rozpoczęciem się transakcji. Transakcja może tylko rozpoznać zmiany danych, które były zatwierdzone
przed rozpoczęciem transakcji. Zmiany danych wykonane przez inne transakcje po rozpoczęciu tej transakcji
nie są widoczne dla poleceń wykonywanych podczas tej transakcji. Z wyjątkiem sytuacji gdy baza danych
jest odtwarzana transakcje na poziomie izolacji SNAPSHOT nie zakładają blokad gdy czytają dane.
Transakcje te czytając dane nie blokują innych transakcji przez zmianą tych danych. Inne transakcje
zapisujące dane nie blokują transakcji SNAPSHOT przed odczytem danych.
Gdy transakcja jest cofana podczas odzyskiwania bazy danych transakcje typu SNAPSHOT będą wymagały
str. 8
zakładania blokad jeśli odczytywane dane są zablokowane przez inne transakcje, które są wycofywane.
Transakcja SNAPSHOT będzie zablokowana do czasu aż wycofywanie się nie zakończy. Blokada jest
zwalniana natychmiast po zakończeniu transakcji.
MS SQL Server nie wspiera wersjonowania metadanych, dlatego niektóre polecenia DDL nie mogą być
wykonywane na tym poziomie. Są to: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX,
DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME.
SERIALIZABLE - Uszeregowalny
Poziom izolacji uszeregowalny gwarantuje najwyży poziom izolacji. Transakcja wykonywana na tym
poziomie izolacji odczytuje dane utworzone wyłącznie przez zatwierdzone transakcje i wartość żadnej
danej, odczytywanej lub zapisywanej przez tę transakcję, nie zostanie zmieniona przez inną transakcję do
momentu zakończenia transakcji. Żadna inna transakcja nie może także dodać danych lub usunąć danych,
które spełniały by warunki wyszukiwania używane w którymkolwiek poleceniu transakcji SERIALIZABLE. Ten
poziom izolacji gwarantuje uszeregowalność wszystkich realizacji transakcji.
Blokady zakładane na zakres wartości kluczy pasujących to warunków wyszukiwania każdego polecenia
transakcji. To blokuje inne transakcje przez modyfikacją lub wstawianiem wierszy, które pasowałyby do
jakiegokolwiek zapytania wykonywanego przez tę transakcję. Oznacza to, że każde zapytanie w transakcji
wykonywane ponownie podczas tej transakcji zawsze będzie zwracało ten sam zestaw wierszy. Blokady te
są utrzymywane aż do zakończenia transakcji.
Implementacja w MS SQL Server
Do ustawiania poziomu izolacji transakcji służy polecenie SET TRANSACTION ISOLATION LEVER, które trzeba
wykonać przez rozpoczęciem transakcji.
SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE
READ | SNAPSHOT | SERIALIZABLE } ;
Poniżej przykład ustawiania poziomu izolacji dla transakcji:
USE AdventureWorks2012;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION;
GO
SELECT *
FROM HumanResources.EmployeePayHistory;
GO
SELECT *
FROM HumanResources.Department;
GO
COMMIT TRANSACTION;
str. 9
GO
Włączenie możliwości korzystania z poziomów izolacji wykorzystujących SNAPSHOT:
ALTER DATABASE NazwaBazyDanych
SET ALLOW_SNAPSHOT_ISOLATION ON
W MS SQL Server domyślnie ustawiony jest poziom izolacji READ COMMITTED, jeśli chcemy wykonać
polecenie na poziomie izolacji READ UNCOMMITTED wystarczy w zapytaniu użyć opcji NOLOCK. Poniżej
przykład:
SELECT * FROM NazwaTabeli WITH(NOLOCK)
Zakreszczenie –(ang. Deadlock)
Metody likwidacji zakleszczeń:
•
•
•
Wycofać obie transakcje
System musi pozwolić wybrać którą transakcję lepiej wycofać, a która ma się dokończyć( ilość
zasobów zarezerwowanych, czas wykonania, ile już sie wykonało transakcji)
Niedopuszczenie do zakleszczenia ( z góry założyć co będzie potrzebne do zdefiniowania transakcji)
str. 10
Zadanie1.- przykład transakcji
Współbieżność: zeskanować str 188 -194 Helion Edukacja
1.
2.
3.
4.
5.
6.
Model optymistyczny, model pesymistyczny
Blokowanie danych- blokada s, blokada x
Zakleszczenia
Izolowanie transakcji
Poziomy izolowania transakcji
Widok (ang View) to tak naprawdę zwykła tabela „wirtualna”, którą tworzymy za pośrednictwem
normalnego zapytania. Z widoku korzystamy jak ze zwykłej tabeli, możemy więc wykonywać „na
nim” dowolne kwerendy. Jest jednak pewna różnica w stosunku do dotychczas używanych tabel.
Mianowicie widoki do póki nie zostanie utworzony dla nich indeks nie mają fizycznej reprezentacji
swoich danych.
str. 11
Widok (perspektywa) to logiczny byt (obiekt), osadzony na serwerze baz danych. Umożliwia
dostęp do podzbioru kolumn i wierszy tabel lub tabeli na podstawie zapytania w języku SQL, które
stanowi część definicji tego obiektu. Przy korzystaniu z widoku jako źródła danych należy
odwoływać się identycznie jak do tabeli. Operacje wstawiania, modyfikowania oraz usuwania
rekordów nie zawsze są możliwe ( np. w sytuacji gdy widok udostępnia część kolumn dwóch tabel
tb_A oraz tb_B bez kolumny z kluczem głównym tabeli tb_B ). W niektórych SZBD widok służy
wyłącznie do pobierania wyników i ograniczania dostępu do danych.
Składnia SQL do utworzenia widoku:
CREATE VIEW nazwa_widoku [(kolumna1, kolumna2,
AS
SELECT ...;
..., kolumnaN )]
DROP VIEW – usuniecie widoku
ALTER VIEW – modyfikacja widoku
Składnia SQL do pobrania danych z widoku:
SELECT * FROM nazwa_widoku;
Np. CREATE VIEW [dbo].[V_OFERTA]
AS
id_produktu, nr_produktu, nazwa, id_kategorii, cena_za_opakowanie,
SELECT
stan_magazynu, wycofany, foto
FROM
WHERE
dbo.produkt
(wycofany = 0) AND (stan_magazynu > 0)
GO
UWAGA
Przy tworzeniu widoków nie można używać klauzuli ORDER BY! Jeżeli potrzebujemy
posortowane dane, należy operację tą wykonać posługując się już stworzonym widokiem.
Do zapamiętania:
WIDOK - tabela wirtualna, nie jest pamiętana fizycznie i jest obliczana
dynamicznie
TABELA TYMCZASOWA - jest obliczana tylko raz i jest usuwana automatycznie po
zakoñczenie sesji
- widok pozwala na prezentowanie użytkownikowi danych z jednej lub wielu tabel,
danych wyliczanych (ulatwie odczytanie danych)
str. 12
- ogranicza dostep do poufnych danych, ukrywa strukture tabel (poufność
danych)
- ulatwia zarzadzanie uprawnieniami uzytkownikow
- widok nie może dotyczyć tabel tymczasowych
- widoki nie są usuwane razem z tabelą – trzeba je usunąć oddzielnie; mo¿na
dla nich tworzyæ triggery
- widoki pamiętają ograniczenia obowiązujące w tabelach bazowych
- możliwe jest (z pewnymi ograniczeniami) modyfikowanie danych w tabelach
bazowych poprzez widok
- definicja widoku nie moze zawierac ORDER BY (chyba ze
zawiera TOP n), COMPUTE, INTO
-- CREATE, ALTER, DROP VIEW
Indeksy
Indeksy są definiowane w celu zwiększenia prędkości wykonywania operacji na tabeli. Są to
uporządkowane struktury zawierające dane z wybranych kolumn tabeli. Zaletą stosowania
indeksów jest ograniczenie ilości danych odczytywanych z bazy, przyśpieszenie wyszukiwania
informacji oraz sortowanie danych. Ich wadą jest to, że zajmują na dysk dodatkowe miejsce, muszą
być na bieżąco aktualizowane, a każde wstawienie, usunięcie lub aktualizacja danych w tabeli
wiąże się z aktualizacją wszystkich zdefiniowanych dla nich indeksów.
Indeksy można budować na etapie tworzenia tabel lub definiować je już w istniejącej tabeli. Instrukcja
tworzenia indeksu ma postać:
CREATE INDEX nazwa_indeksu ON nazwa_tabeli (nazwa_kolumny)
Tworzenie indeksu w istniejącej tabeli:
CREATE INDEX indeks_tytul ON ksiazki (tytul);
Tworzenie unikatowego indeksu ma postać:
CREATE UNIQUE INDEX nazwa_indeksu ON nazwa_tabeli (nazwa_kolumny);
Tak w zaindeksowanej kolumnie powtarzające się wartości są niedozwolone.
Instrukcja tworzenia indeksów w innych serwerach bazodanowych może różnić się od podanej.
Aktualizacja tabeli z indeksami zajmuje więcej czasu niż aktualizacja tabeli bez indeksów. Dlatego
należy tworzyć indeksy tylko dla kolumn, które będą często przeszukiwane. Przed utworzeniem
indeksów warto przeanalizować zapytania i podjąć decyzję, gdzie i jakie indeksy utworzyć. Na
pewno indeksy powinny być utworzone dla pól kluczy głównych i kluczy obcych.
Do usuwania indeksu służy instrukcja DROP INDEX.
DROP INDEX nazwa_tabeli.nazwa_indeksu
1.
2.
3.
4.
Tworzenie i usuwanie baz danych- Plik danych i plik logów.
Tworzenie i usuwanie baz danych- Plik danych i plik logów.
Tworzenie i usuwanie baz danych- Plik danych i plik logów.
Tworzenie i usuwanie baz danych- Plik danych i plik logów.
str. 13
Tworzenie i usuwanie baz danych- Plik danych
i plik logów.
Pliki z danymi1
SQL Server przechowuje dane dotyczące baz danych w zwykłych plikach dyskowych. Każda baza
danych:
•
•
•
Zawiera jeden podstawowy plik z danymi (*.mdf)
Może zawierać dodatkowe pliki z danymi (*.ndf)
Zawiera plik z dziennikiem transakcji – transactional log (*.ldf)
Wszystkie dane z baz danych takie jak: tabele wraz z zawartością, definicje procedur składowanych,
wyzwalacze, uprawnienia i inne, są trzymane w plikach z danymi. Dodatkowym plikiem jest dziennik
transakcji, zwany często potocznie logiem transakcyjnym. Wszystkie transakcje wykonywane w bazie
danych są zapisywane najpierw w dzienniku transakcji, a dopiero później zapisywane w plikach z
danymi (zasada WAL: Write-Ahead Logging). Dzięki danym z logu serwer jest w stanie między innymi
wycofywać transakcje (ROLLBACK) oraz zachować spójność danych po awarii. Dziennik transakcji
pozwala również na odtworzenie stanu bazy danych do dowolnego punktu w czasie.
1
http://wss.geekclub.pl/baza-wiedzy/kurs-transact-sql-czesc-8-bazy-danych,1710
str. 14
Zakładanie bazy danych
Aby założyć bazę danych, wystarczy w narzędziu Management Studio wybrać opcję New Database
i określić podstawowe parametry tej bazy, w tym lokalizację plików z danymi i z dziennikiem.
Na pierwszej zakładane (General) podajemy nazwę bazy i określamy listę plików i ich parametry.
Każdy z plików posiada logiczną nazwę, po której będziemy odwoływać się do niego w
przyszłości. Nazwa ta nie musi być identyczna z fizyczną nazwą pliku, pod jaką będzie on
widoczny w systemie operacyjnym. Dla każdego pliku powinniśmy określić jego rozmiar
początkowy (Initial Size) i sposób, w jaki plik będzie się rozrastał (Autogrowth). Należy rozważnie
dobrać te parametry. Rozrastanie się pliku jest procesem obciążającym serwer, więc nie powinno
zdarzać się zbyt często. Z drugiej strony, nie ma sensu na zapas rezerwować bardzo dużej ilości
miejsca. Należy więc oszacować, jaki będzie początkowy rozmiar bazy (po utworzeniu tabel i
innych obiektów naszej aplikacji i imporcie początkowych danych) oraz ile danych będzie
przybywać w pewnym okresie czasu (np. tygodniowo).
Autogrowth
Aby zezwolić na powiększanie plików bazy danych i logu transakcyjnego, przechodzimy do
właściwości bazy danych i na stronie Files wciskamy przycisk […] w pozycji Autogrowth, a
następnie zaznaczamy opcje Enable Autogrowth.
Poniżej mamy możliwość określenia czy plik będzie powiększał się procentowo (In Percent) czy
będzie powiększał się w określoną ilość megabajtów (In Megabytes).
Możemy również określić maksymalny rozmiar pliku, zaznaczając opcję Restricted File Growth i
określając ilość megabajtów. Jeśli nie chcemy określać maksymalnego rozmiaru, zaznaczamy
Unrestricted File Growth.
str. 15
Jedną z najbardziej istotnych dostępnych tu opcji jest Recovery model. Określa on, w jaki sposób
serwer będzie korzystał z dziennika transakcji:
•
•
•
Full – w dzienniku transakcji znajdą się wszystkie operacje wykonane na bazie danych i nie będą z
niego usuwane automatycznie. Jest to ustawienie zalecane dla każdej bazy, w której priorytetem
jest bezpieczeństwo danych.
Bulk-logged – do dziennika nie zostaną zapisane niektóre operacje np. SELECT ... INTO ...
czy BULK INSERT.
Simple – po zapisaniu wyniku działania transakcji do pliku z danymi informacje są usuwane z
dziennika. Serwer zapisuje w logu tylko minimalną ilość informacji. Pozwala to zaoszczędzić nieco
zasobów serwera, jednak przy tym ustawieniu nie będziemy mogli przywracać bazy do dowolnego
punktu w czasie. Jest to ustawienie zalecane do baz testowych, w których nie zależy nam na
bezpieczeństwie danych, a nie chcemy zajmować się okresowym czyszczeniem dziennika (opisane
w wykładzie 8).
Bazę danych możemy założyć również z poziomu języka SQL. Przykład polecenia:
CREATE DATABASE Baza ON (
NAME = Baza_dat,
FILENAME = 'e:\data\baza.mdf',
SIZE = 2MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5MB)
LOG ON (
NAME = 'Baza_log',
FILENAME = 'f:\data\bazalog.ldf',
SIZE = 2MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5MB)
Polecenie to założy bazę danych o nazwie "Baza" z jednym plikiem z danymi i jednym plikiem z
dziennikiem transakcji. Podane foldery muszą istnieć, aby wykonanie tego polecenia się powiodło.
str. 16
2
Usuwanie bazy danych
Niepotrzebną bazę danych możemy usunąć używając opcji Delete na wybranej bazie lub wpisując
polecenie DROP DATABASE nazwa_bazy. Usunięcie nie powiedzie się, gdy do bazy są podłączeni
użytkownicy.
Logiczna struktura bazy danych podsumowanie
2
http://etacar.put.poznan.pl/tadeusz.pankowski/11-sql-programowanie.pdf
str. 17
Transact-SQL
jest rozszerzeniem języka SQL stosowanym dla serwerów MS SQL
Serwer. T-SQL oznacza transakcyjny SQL, czyli rozszerzenie języka SQL umożliwiające tworzenie
konstrukcji takich jak pętle, instrukcje warunkowe oraz zmienne. Jest używany do tworzenia
str. 18
wyzwalaczy, procedur i funkcji składowanych w bazie. W języku T-SQL ciąg poleceń
bezpośrednio kierowany do serwera powinien kończyć się słowem kluczowym GO.
Zmienne
Zmienne deklarowane przez użytkownika są zmiennymi lokalnymi i istnieją tylko w odrąbie
skryptu. Muszą być poprzedzone znakiem @. Deklaracja zmiennych jest realizowana za pomocą
instrukcji:
DECLARE @ zmienna typ_danych
Procedura jest serią poleceń zapisaną w języku programowania baz danych, służącą do
wykonywania obsługi na elementach bazy: tabelach, formularzach, raportach czy kwerendach.
Procedura składowana (ang. stored procedure) – to jeden z elementów implementacji bazy
danych. Procedura składowana jest umiejscowiona bezpośrednio w systemie bazy danych, a nie po
stronie klienta. Pozwala to na zmniejszenie liczby kroków wymiany danych pomiędzy klientem a
systemem zarządzania bazą danych, co może przyczynić się do wzrostu wydajności systemu.
Zastosowanie procedur składowanych pozwala również wprowadzić bardziej przejrzysty
interfejs pomiędzy bazą danych a aplikacjami z niej korzystającymi. W starszych systemach baz
danych procedury były prekompilowane, co stwarzało dalszy zysk wydajnościowy.
Procedury składowane mogą być wykonywane w podany sposób: EXECUTE nazwa_procedury lub
EXEC nazwa_procedury.
Przykład:
Procedura P_DodOpinia dodaje opinie do bazy danych.
CREATE PROCEDURE [dbo].[P_DodOpinia]
(@id_produktu AS int,@ocena_punktowa AS
int ,@ocena_slowna AS nvarchar(max))
AS
BEGIN
INSERT INTO [opinia]
([id_produktu],[ocena_punktowa],[ocena_slowna] )
(SELECT @id_produktu
,@ocena_punktowa
,@ocena_slowna )
END
GO
Wywołanie procedury:
str. 19
exec P_DodOpinia 5,6,'super smak'
Rezultat wywołanej procedury przedstawia rysunek nr 24.
Rezultat wywołanej procedury P_DodOpinia
Wyzwalacz (ang. trigger) – procedura wykonywana automatycznie jako reakcja na pewne
zdarzenia w tabeli bazy danych. Wyzwalacze mogą ograniczać dostęp do pewnych danych, rejestrować
zmiany danych lub nadzorować modyfikacje danych.
Istnieje kilka typów wyzwalaczy. Wyzwalacze BEFORE - wykonywane przed instrukcją
generującą zdarzenie. Wyzwalacze AFTER są wykonane po instrukcji generującej zdarzenie. W
niektórych bazach danych są również wyzwalacze INSTEAD OF - są one wykonywane zamiast
instrukcji generującej zdarzenie.
Istnieją trzy typowe zdarzenia powodujące wykonanie wyzwalaczy:
•
•
•
dopisanie nowego rekordu do bazy danych w wyniku wykonania instrukcji INSERT,
zmiana zawartości rekordu w wyniku wykonania instrukcji UPDATE oraz
usunięcie rekordu w wyniku wykonania instrukcji DELETE.
Główne cechy wyzwalaczy to:
•
•
•
nie mogą mieć parametrów (ale mogą zapisywać dane w tabelach tymczasowych)
nie mogą zatwierdzać transakcji (COMMIT) ani ich wycofywać (ROLLBACK) ponieważ działają w
kontekście instrukcji SQL, która spowodowała ich uruchomienie
mogą generować dodatkowe błędy, jeżeli są źle napisane.
str. 20
•
•
wierszy.
str. 21