Kurs Oracle SQL. Blokowanie tabel i wierszy

Blokowanie tabel

Blokada pozwala zachować spójność danych w przypadku modyfikacji tych samych danych przez wiele transakcji.
Tabele mogą być blokowane przez serwer Oracle lub przez nas samych. Blokada automatycznie zakładana jest na te wiersze w tabeli które są aktualnie modyfikowane. 

Blokowanie wierszy przez użytkownika

Zdarzają się sytuacje, dotyczy to zwłaszcza danych krytycznych, że chcemy mieć pewność że przez pewien czas kiedy realizujemy jakieś update na tabeli nic nie zostanie w niej zmienione przez innych użytkowników. Generalnie dążymy do tego by blokować jak najmniej danych, a jednocześnie nikt nam nie „namieszał”. W takiej sytuacji możemy zablokować tylko te dane które chcemy zmieniać. Jeśli przykładowo chcemy updatować dane w taki sposób:



UPDATE JOBS SET MIN_SALARY=3000 WHERE JOB_TITLE LIKE '%Manager%';

możemy zablokować te wiersze przy pomocy nieco zmodyfikowanego selecta który wyciągnie nam dane do modyfikacji:


SELECT MIN_SALARY FROM JOBS WHERE JOB_TITLE LIKE '%Manager%' for update;


Dodanie klauzuli „for update” sprawi że wiersze te zostaną zablokowane dla innych użytkowników do czasu, aż nie zatwierdzimy transakcji (COMMIT), lub jej nie wycofamy (ROLLBACK).
Po klauzuli „for update” można wymienić tylko te kolumny które będziemy updatować. W przypadku blokady na jednej tabeli i tak zostaną zablokowane całe wiersze. Jeśli po FOR UPDATE wymienimy kolumny, ale zapytanie oparte jest o więcej niż jedną tabelę, zablokowane zostaną wiersze tylko w tej tabeli której kolumny wymienimy po FOR UPDATE.


SELECT MIN_SALARY FROM JOBS WHERE JOB_TITLE LIKE '%Manager%' for update MIN_SALARY;


W czasie trwania blokady inny użytkownik po wprowadzeniu instrukcji DML uzyska taki efekt:












Jego sesja zostanie niejako „zawieszona”. Zapytanie będzie oczekiwało na wykonanie do momentu kiedy przez użytkownika blokującego dane nie zostaną odblokowane.
Blokada nie dotyczy poleceń odczytu danych.

 

Blokowanie tabel przez użytkownika (LOCK TABLE)

Możemy również zablokować dostęp do całej tabeli, a nie tylko pojedynczych wierszy.
Ogólna konstrukcja polecenia blokady wygląda tak:



LOCK TABLE NAZWA_TABELI IN NAZWA_TRYBU MODE;



Blokować możemy na dwa sposoby:

SHARE

LOCK TABLE JOBS IN SHARE MODE;



Jest to blokada która umożliwia zakładanie innych blokad z wyjątkiem blokady EXCLUSIVE. Stosuje się ją najczęściej przy operacjach nie ingerujących w konstrukcję tabeli, układ zależności tabel. Inne blokady, w tym blokady zakładane automatycznie w poleceniach DML będą oczekiwały na zwolnienie blokady.

EXCLUSIVE

LOCK TABLE JOBS IN EXCLUSIVE MODE;

Ta blokada uniemożliwia założenie jakichkolwiek innych blokad, w tym blokad zakładanych automatycznie przy poleceniach DML. Stosuje się ją najczęściej gdy ingerujemy w konstrukcję tabeli lub układ zależności tabel – np. przy stosowaniu poleceń ALTER, DROP, zakładaniu constraintów.

NOWAIT

 

Jeśli zastosujemy klauzulę NOWAIT :
LOCK TABLE JOBS IN SHARE MODE NOWAIT;


nie będziemy oczekiwali „zawieszeni” na zwolnienie blokady, a otrzymamy informację że w tej chwili założenie blokady nie jest możliwe.