7. Управління паралельним доступом до даних

До цього часу ми використовували таблиці, єдиними користувачами яких були ми самі. На практиці, на багатокористувацькій машині, дані найчастіше є спільними для різних користувачів. Тоді постає питання: хто може використовувати ту чи іншу таблицю і в якому режимі (перегляд, вставка, видалення, додавання, ...)?

7.1. Створення користувачів Firebird

Під час роботи з IB-Expert ми увійшли в систему як користувач SYSDBA. Цю інформацію можна знайти у властивостях відкритого з’єднання з SGBD:

Праворуч видно, що підключений користувач — [SYSDBA]. Чого не видно, так це його пароля [masterkey]. [SYSDBA] — це особливий користувач Firebird: він має всі права на всі об’єкти, якими керує SGBD. За допомогою IBExpert можна створювати нових користувачів, використовуючи опцію [Tools / User Manager] або таку піктограму:

Відкриється вікно управління користувачами:

Кнопка [Add] дозволяє створювати нових користувачів:

Отже, створимо таких користувачів:

ім’я	пароль
ADMIN1	admin1
ADMIN2	admin2
SELECT1	select1
SELECT2	select2
UPDATE1	update1
UPDATE2	update2

7.2. Надання прав доступу користувачам

База даних належить тому, хто її створив. Бази даних, які ми створили до цього моменту, належали користувачеві [SYSDBA]. Щоб проілюструвати поняття прав, створимо (Database / Create Database) нову базу даних під ім’ям [ADMIN1, admin1]:

і зареєструємо її під псевдонімом DBACCES (ADMIN1). Використання псевдонімів дозволяє відкривати з’єднання з однією й тією ж базою даних, надаючи їм різні ідентифікатори, що полегшує їх пошук у оглядачі баз даних IBExpert:

Тепер створимо дві такі таблиці: TA та TB:

Таблиця TA

Таблиця TB

Ці таблиці не пов'язані між собою.

За допомогою IB-Expert створимо друге з’єднання з базою даних [DBACCES], цього разу під назвою [ADMIN2 / admin2]. Для цього використовуємо опцію [Database / Register Database]:

Перейдемо до DBACCES (ADMIN2) і відкриємо редактор SQL (Shift + F12):

Ми матимемо можливість використовувати різні підключення до однієї й тієї ж бази даних [DBACCES]. Для кожного з них у нас буде редактор SQL. У [1] редактор SQL вказує псевдонім підключеної бази даних. Використовуйте цю інформацію, щоб дізнатися, в якому редакторі SQL ви перебуваєте. Це матиме важливе значення, оскільки ми будемо створювати з’єднання, які матимуть різні права доступу до об’єктів бази даних.

Виведемо вміст таблиці TA:

Ми отримуємо таке повідомлення про помилку:

Що це означає? База даних [DBACCESS] була створена користувачем [ADMIN1] і, отже, є його власністю. Тільки він має доступ до різних об’єктів цієї бази даних. Він може надавати права доступу іншим користувачам за допомогою команди SQL GRANT. Ця команда має різні синтаксиси. Один із них такий:

syntaxe	GRANT привілей1, привілей2, ...| ALL PRIVILEGES ON table/vue TO користувач1, користувач2, ...| PUBLIC [ WITH GRANT OPTION ]
action	надає права доступу privilègei або всі права (ALL PRIVILEGES) на table або vue користувачам utilisateuri або всім користувачам ( PUBLIC ). Умова WITH GRANT OPTION дозволяє користувачам, які отримали ці привілеї, у свою чергу передавати їх іншим користувачам.

Серед привілеїв privilègei, які можуть бути надані, є такі:

DELETE	право використовувати команду DELETE для таблиці або подання.
INSERT	право використовувати команду INSERT для таблиці або подання
SELECT	право на використання команди SELECT для таблиці або подання
UPDATE	право на використання команди UPDATE для таблиці або подання. Це право можна обмежити певними стовпцями за допомогою такого синтаксису: GRANT update ( col1, col2, ...) ON таблиця/представлення TO користувач1, користувач2, ...| PUBLIC [ WITH GRANT OPTION ]

Надамо користувачеві [ADMIN2] право SELECT на таблицю TA. Лише власник таблиці може надати це право, c.a.d. У даному випадку це [ADMIN1]. Перейдемо до з’єднання DBACCES (ADMIN1) і відкриємо новий редактор SQL (Shift+F12):

Далі ми будемо переходити з одного редактора SQL до іншого. Щоб не заплутатися, можна скористатися опцією [Windows] у меню:

Вище показано два редактори SQL, кожен з яких прив’язаний до конкретного користувача. Повернімося до редактора SQL (ADMIN1) і виконаємо таку команду:

Потім підтвердимо її командою COMMIT:

Після цього перейдемо до редактора користувача ADMIN2, щоб повторити операцію SELECT, яка завершилася невдачею:

Ми отримуємо таке повідомлення про помилку:

Користувач [ADMIN2] досі не має прав на перегляд таблиці [TA]. Насправді, схоже, що права користувача завантажуються в момент його входу в систему. Отже, [ADMIN2], ймовірно, все ще має ті самі права, що й на початку сеансу, тобто жодних. Перевіримо це. Вийдемо з системи користувача [ADMIN2]:

• перейдіть до його сеансу
• запросити відключення, клацнувши правою кнопкою миші на з’єднанні та вибравши опцію [Deconnect from database] або (Shift + Ctrl + D)

Якщо у вікні з’явиться запит на [COMMIT], введіть [COMMIT]. Потім знову підключімо користувача [ADMIN2], вибравши опцію [Reconnect], зазначену вище. Зробивши це, повернімося до редактора SQL (ADMIN2) і повторімо запит SELECT, який завершився невдачею:

У результаті ми отримаємо такий результат:

Цього разу ADMIN2 може переглядати таблицю TA завдяки праву SELECT, яке йому надав його власник ADMIN1. Зазвичай це єдине право, яке він має. Перевіримо це. Знову в редакторі SQL (ADMIN2):

На екрані праворуч видно, що ADMIN2 не має права DELETE щодо таблиці TA.

Повернемося до редактора SQL (ADMIN1), щоб надати більше прав користувачеві ADMIN2. Послідовно виконуємо дві наступні команди:

• перша команда надає користувачеві ADMIN2 усі права доступу до таблиці [TA], а також можливість самостійно надавати права (WITH GRANT OPTION)
• друга команда підтверджує попередню

Зробивши це, як і раніше, поновимо з'єднання користувача [ADMIN2] (Deconnect / Reconnect), а потім у редакторі SQL (ADMIN2) введемо такі команди:

ADMIN2 видалив усі рядки з таблиці TA. Скасуємо це видалення за допомогою команди ROLLBACK:

Перевіримо, чи ADMIN2, у свою чергу, може надати права на таблицю TA.

Тепер відкриємо з’єднання з базою даних [DBACCES] (Database / Register database) під іменем [SELECT1 / select1] — одним із раніше створених користувачів — а потім двічі клацнемо на створеному таким чином посиланні в [Database Explorer]:

Перейдемо до цього нового з’єднання та відкриємо новий редактор SQL (Shift + F12), щоб ввести туди такі команди:

Користувач SELECT1 дійсно має право SELECT на таблиці TA. Чи може він передати це право користувачеві SELECT2?

Операція завершилася невдало, оскільки користувач SELECT1 не отримав права передавати право SELECT, яке він отримав від користувача ADMIN2. Для цього було б необхідно, щобкористувач ADMIN2 використав у своєму наказі SQL GRANT. Правила передачі прості:

• користувач може передавати лише ті права, які він отримав, і не більше
• він може передавати їх лише в тому випадку, якщо отримав їх із привілеєм [WITH GRANT OPTION]

Наданий дозвіл можна скасувати за допомогою команди REVOKE:

syntaxe	REVOKE привілей1, привілей2, ...| ALL PRIVILEGES ON table/vue FROM користувач1, користувач2, ...| PUBLIC
action	скасовує права доступу privilègei або всі права (ALL PRIVILEGES) на table або vue для користувачів utilisateuri або для всіх користувачів ( PUBLIC ).

Спробуємо. Повернімося до редактора SQL з ADMIN2, щоб скасувати право SELECT, яке ми надали користувачеві SELECT1:

Відключімо, а потім знову підключімо користувача SELECT1. Потім у редакторі SQL (SELECT1) запитаємо вміст таблиці TA:

Користувач SELECT1 дійсно втратив право на читання таблиці TA. Слід зазначити, що саме ADMIN2 надав йому це право, а ADMIN2 його відібрав. Якщо ADMIN1 спробує його відібрати, помилка не буде зафіксована, але згодом можна буде помітити, що SELECT1 зберіг своє право SELECT.

Право можна надати всім за допомогою такого синтаксису: GRANT право(а) ON таблиця / представлення TO PUBLIC. Отже, надамо право SELECT на таблицю TA усім. Для цього можна використати ADMIN1 або ADMIN2. Ми використовуємо ADMIN2:

Створимо з’єднання з базою даних під ім’ям користувача USER1 / user1:

За допомогою з'єднання DBACCES (USER1) відкриємо новий редактор SQL (Shift + F12) і введемо такі команди:

Користувач USER1 дійсно має право SELECT на таблицю TA.

7.3. Транзакції

7.3.1. Рівні ізоляції

Тепер ми відходимо від проблеми прав доступу до об’єктів бази даних, щоб розглянути проблему паралельного доступу до цих об’єктів. Двоє користувачів, які мають достатні права доступу до об’єкта бази даних, наприклад, до таблиці, хочуть використовувати його одночасно. Що відбувається?

Кожен користувач працює в рамках транзакції. Транзакція — це послідовність команд SQL, яка виконується «атомарно»:

• або всі операції виконуються успішно
• або одна з них завершується невдало, і тоді всі попередні операції скасовуються

У підсумку операції транзакції або всі були успішно застосовані, або жодна не була застосована. Коли користувач сам керує транзакцією (що має місце у всьому цьому документі), він підтверджує транзакцію командою COMMIT або скасовує її командою ROLLBACK.

Кожен користувач працює у власній транзакції. Зазвичай розрізняють чотири рівні ізоляції між різними користувачами:

• Uncommitted Read
• Закріплене читання
• Повторюване читання
• Серіалізоване

Нефіксоване читання

Цей режим ізоляції також називають «Dirty Read». Ось приклад того, що може статися в цьому режимі:

1. користувач U1 починає транзакцію з таблицею T
2. користувач U2 починає транзакцію з цією ж таблицею T
3. користувач U1 змінює рядки таблиці T, але ще не підтверджує ці зміни
4. користувач U2 «бачить» ці зміни та приймає рішення на основі того, що бачить
5. користувач скасовує свою транзакцію за допомогою ROLLBACK

Бачимо, що в пункті 4 користувач U2 прийняв рішення на основі даних, які згодом виявляться помилковими.

Committed Read

Цей режим ізоляції дозволяє уникнути описаної вище проблеми. У цьому режимі користувач U2 на кроці 4 не «побачить» змін, внесених користувачем U1 до таблиці T. Він побачить їх лише після того, як U1 завершить свою транзакцію.

У цьому режимі, який також називають «Unrepeatable Read», можуть траплятися такі ситуації:

1. користувач U1 починає транзакцію з таблицею T
2. користувач U2 починає транзакцію в тій самій таблиці T
3. Користувач U2 створює SELECT для обчислення середнього значення стовпця C у рядках таблиці T, що відповідають певній умові
4. користувач U1 змінює (UPDATE) певні значення у стовпці C таблиці T та підтверджує їх (COMMIT)
5. користувач U2 повторює ту саму операцію SELECT, що й у пункті 3. Він виявить, що середнє значення стовпця C змінилося внаслідок змін, внесених користувачем U1.

Тепер користувач U2 бачить лише зміни, «підтверджені» користувачем U1. Але, залишаючись у тій самій транзакції, дві однакові операції (3 і 5) дають різні результати. Цю ситуацію позначає термін «Unrepeatable Read». Це неприємна ситуація для того, хто прагне отримати стабільне зображення таблиці T.

Повторюване читання

У цьому режимі ізоляції користувач має гарантію, що отримає однакові результати при читанні даних з бази, доки залишається в межах однієї транзакції. Він працює з «знімком», у який ніколи не відображаються зміни, внесені іншими транзакціями, навіть підтвердженими. Він побачить ці зміни лише тоді, коли сам завершить свою транзакцію командою COMMIT або ROLLBACK.

Однак цей режим ізоляції ще не є досконалим. Після операції 3, описаної вище, рядки, до яких звертається користувач U2, блокуються. Під час операції 4 користувач U1 не зможе змінити (UPDATE) значення стовпця C у цих рядках. Однак він може додавати нові рядки (INSERT). Якщо деякі з доданих рядків задовольняють умову, перевірену в кроці 3, операція 5 дасть середнє значення, відмінне від того, що було отримано в кроці 3, через додані рядки.

Щоб вирішити цю нову проблему, потрібно перейти в режим ізоляції «Serializable».

Serializable

У цьому режимі ізоляції транзакції повністю ізольовані одна від одної. Він гарантує, що результат двох транзакцій, виконаних одночасно, буде таким самим, якби вони виконувалися одна за одною. Щоб досягти цього результату, під час операції 4, коли користувач U1 намагається додати рядки, які змінять результат транзакції SELECT користувача U1, йому буде це заборонено. Повідомлення про помилку вкаже йому, що вставка неможлива. Вона стане можливою, коли користувач U2 підтвердить свою транзакцію.

Чотири рівні ізоляції транзакцій SQL доступні не у всіх SGBD. Firebird надає такі рівні ізоляції:

• snapshot: режим ізоляції за замовчуванням. Відповідає режиму «Repeatable Read» стандарту SQL.
• committed read: відповідає режиму «committed read» стандарту SQL

Цей рівень ізоляції встановлюється командою SET TRANSACTION:

syntaxe

SET TRANSACTION [READ WRITE | READ ONLY] [WAIT|NOWAIT] ISOLATION LEVEL [SNAPSHOT | READ COMMITTED]

fonctionnement

підкреслені ключові слова є значеннями за замовчуванням READ WRITE: транзакція може читати та записувати READ ONLY: транзакція може лише читати WAIT: у разі конфлікту між двома транзакціями та, яка не змогла виконати свою операцію, очікує, поки інша транзакція буде підтверджена. Вона більше не може надсилати команди SQL. NOWAIT: транзакція, яка не змогла виконати свою операцію, не блокується. Вона отримує повідомлення про помилку і може продовжувати роботу. ISOLATION LEVEL [SNAPSHOT | READ COMMITTED]: рівень ізоляції

Спробуємо. У редакторі SQL(ADMIN1) вводимо таку команду SQL:

Бачимо, що її не було дозволено. Невідомо, чому...

IB-Expert дозволяє встановити режим ізоляції іншим способом. Клацнімо правою кнопкою миші на з’єднанні DBACCES(ADMIN1), щоб вибрати опцію [Database Registration Info]:

На екрані праворуч видно наявність опції [Transactions]. Вона дозволить нам встановити рівень ізоляції транзакцій. Тут ми встановлюємо його на [snapshot]. Те саме робимо з підключенням DBACCES (ADMIN2).

7.3.2. Режим знімка

Розглянемо рівень ізоляції snapshot, який є режимом ізоляції за замовчуванням у Firebird. Коли користувач починає транзакцію, створюється знімок бази даних. Далі користувач працюватиме з цим знімком. Таким чином, кожен користувач працює з власним знімком бази даних. Якщо він вносить до нього зміни, інші користувачі їх не бачать. Вони побачать їх лише тоді, коли користувач, який їх вніс, підтвердить їх за допомогою COMMIT.

Можна розглянути два випадки:

• один користувач читає таблицю (select), тоді як інший її змінює (insert, update, delete)
• обидва користувачі хочуть одночасно змінити таблицю

7.3.2.1. Принцип узгодженого читання

Нехай є два користувачі U1 та U2, які працюють з однією й тією ж таблицею TAB:

      --------+----------+--------+-------+----------------------
              T1a       T2a      T1b     T2b 

Транзакція користувача U1 починається в момент часу T1a і закінчується в момент часу T1b.

Транзакція користувача U2 починається в момент часу T2a і закінчується в момент часу T2b.

U1 обробляє фотографію TAB, зроблену в момент часу T1a. Між T1a та T1b він редагує TAB. Інші користувачі отримають доступ до цих змін лише в момент часу T1b, коли U1 створить COMMIT.

U2 працює над фотографією TAB, зробленою в момент часу T2a, тобто з тією самою фотографією, яку використовував U1 (якщо інші користувачі тим часом не змінили оригінал). Він не «бачить» змін, які міг внести користувач U1 у TAB. Він зможе їх побачити лише у момент часу T1b.

Проілюструємо це на прикладі нашої бази [DBACCES]. Ми дозволимо одночасно працювати двом користувачам: [ADMIN1] та [ADMIN2]. Перейдемо до з’єднання DBACCES (ADMIN1) і в редакторі SQL користувача ADMIN1 виконаємо такі операції:

ADMIN1 змінив рядок № 2 таблиці TA, але ще не підтвердив (COMMIT) свою операцію. Тоді користувач ADMIN2 виконує операцію SELECT над таблицею TA (перехід у редактор SQL з ADMIN2). Ми знаходимося до моменту часу T2a з прикладу.

Повернення до редактора SQL з ADMIN1, який підтверджує додавання:

Повернення до редактора SQL з ADMIN2 для повторного створення SELECT:

ADMIN2 бачить зміни, внесені ADMIN1. У режимі знімка транзакція не бачить змін, внесених іншими транзакціями, доки останні не завершаться.

7.3.2.2. Одночасне редагування одним і тим самим об’єктом бази даних двома транзакціями

Розглянемо приклад з бухгалтерського обліку: U1 та U2 працюють з рахунками. U1 списує з рахунку comptex суму S і зараховує ту саму суму на рахунок comptey. Це відбуватиметься у кілька етапів:

      --------+----------+--------+-------+----------------------
              T1a       T1b     T1c      T1d 

U1 розпочинає транзакцію в момент часу T1a, списує кошти з рахунку comptex у момент часу T1b, зараховує кошти на рахунок comptey у момент часу T1c та підтверджує обидві операції в момент часу T1d. Припустимо також, що U2 хоче зробити те саме, починає свою транзакцію в момент часу T2a і завершує її в момент часу T2d згідно з такою схемою:

      --------+----------+----+----+-------+------+-----+-------+---------
              T1a       T1b  T2a   T1c     T2b   T1d   T2c    T2d

У момент часу T2 створюється знімок таблиці рахунків для U2. Він є узгодженим згідно з принципом snapshot. U2 бачить початковий стан рахунків comptex та comptey, оскільки U1 ще не підтвердив свої операції.

Припустимо, що comptex має початковий залишок у розмірі 1000 € і що кожен із користувачів U1 та U2 бажає списати з нього по 100 €.

• У момент часу T1b U1 зменшує баланс comptex на 100 євро, таким чином, баланс останнього становить 90 євро. Ця операція буде підтверджена лише в момент часу T1d.
• У момент часу T2b U2 бачить, що comptex має 1000 €, (принцип когерентного читання) і зменшує його на 100 €, таким чином змінюючи значення на 90 €.
• у підсумку, у момент часу T2d, коли все буде підтверджено, на рахунку comptex залишиться 90 € замість очікуваних 80 €.

Вирішенням цієї проблеми є заборона U2 змінювати comptex, доки U1 не завершить свою транзакцію. Таким чином, U2 буде заблоковано до моменту часу T1d. Цей механізм забезпечує режим snapshot.

Проілюструємо це на прикладі бази даних DBACCES. ADMIN1 розпочинає транзакцію у своєму редакторі SQL (ADMIN1):

Спочатку ми створили COMMIT, щоб переконатися, що розпочинаємо нову транзакцію. Потім ми видалили рядок № 4. Транзакція ще не була затверджена.

ADMIN2, у свою чергу, розпочинає транзакцію у своєму редакторі SQL (ADMIN2):

На екрані праворуч видно, що ADMIN2 намагався змінити рядок № 4. Йому було повідомлено, що це неможливо, оскільки хтось інший уже змінив цей рядок, але ще не підтвердив цю зміну.

Повернемося до редактора SQL (ADMIN1), щоб створити COMMIT:

Повернімося до редактора SQL (ADMIN2), щоб повторно виконати команду UPDATE:

Операція UPDATE проходить успішно, хоча рядок № 4 вже не існує, як показує наступний SELECT. Саме в цей момент ADMIN2 виявляє, що рядок більше не існує.

7.3.2.3. Режим Repeatable Read

Тепер проілюструємо режим «Repeatable Read». Цей рівень ізоляції забезпечується режимом «snapshot». Він гарантує, що транзакція завжди отримуватиме однаковий результат під час читання бази даних.

Почнемо з роботи з редактором SQL для ADMIN2:

Тепер перейдемо до редактора SQL з ADMIN1:

Користувач ADMIN1 додав два рядки та підтвердив свою транзакцію. Тепер повернемося до редактора SQL (ADMIN2), щоб повторити операцію SELECT SUM:

Видно, що ADMIN2 не бачить доданих рядків з ADMIN1, хоча вони були підтверджені за допомогою COMMIT. SELECT SUM дає той самий результат, що й до додавання рядків. Це принцип «повторного читання» (Repeatable Read).

Тепер, залишаючись у редакторі SQL (ADMIN2), підтвердимо транзакцію за допомогою COMMIT, а потім повторно виконаємо SELECT SUM:

Рядки, додані ADMIN1, тепер враховуються.

7.3.3. Режим Committed Read

Тепер розглянемо режим «Committed Read». Цей рівень ізоляції аналогічний рівню snapshot, за винятком режиму «Repeatable Read».

Спочатку змінимо рівень ізоляції транзакцій для обох з’єднань.

• відключаємо обох користувачів ADMIN1 та ADMIN2
• змінюємо рівень ізоляції їхніх транзакцій

• ми знову підключаємо користувачів ADMIN1 та ADMIN2

Тепер повернемося до попереднього прикладу, що ілюстрував «Repeatable Read», щоб показати, що поведінка більше не є такою самою. Почнемо з роботи з редактором SQL для ADMIN2:

Тепер перейдемо до редактора SQL з ADMIN1:

Користувач ADMIN1 додав два рядки та підтвердив свою транзакцію. Тепер повернімося до редактора SQL (ADMIN2), щоб повторити операцію SELECT SUM:

SELECT SUM не дає того самого результату, що й до внесення змін ADMIN1. У цьому полягає різниця між режимами snapshot та read committed.