Проблеми спільного доступу до даних в Oracle

Олег Елманов

Чим більше кількість одночасно працюють з базою даних користувачів, тим більша ймовірність конфлікту одночасного редагування однієї і тієї ж рядка. Що робити сервера, якщо два користувача одночасно намагаються оновити одну й ту ж запис? Можна прийняти те зміна, яке прийшло пізніше, але тоді один з користувачів буде бачити у себе некоректні дані. А гірше за все - він буде думати, що все в порядку. Як вирішити подібні проблеми?

Припустимо, що два користувачі відкрили для редагування форму з одним і тим же документом. Перший користувач змінює важливі параметри, ціни, кількість і натискає «зберегти». Інший користувач не бачить цих змін, тому що він отримав дані раніше. При цьому він змінює якийсь незначний параметр, наприклад, дату, і теж натискає збереження. Незначна зміна другу користувача перезаписує важливі зміни першим.

В багатокористувацьких додатках до програмування можна чинити декількома способами:

1. Хто останній, той і правий. У цьому випадку ви просто реалізуєте логіку програми і не турбуєтеся про те, що два користувачі можуть одночасно змінювати будь-то дані. Прав буде той, хто трохи пізніше натисне кнопку оновлення.

2. Спробувати реалізувати «одночасну» роботу власними засобами, з допомогою журналів - якщо в журналі є запис, що хтось відкрив документ, але не закрив, то не дозволяти повторне відкриття іншим користувачам. Може бути, де-то це буде зручно і швидко, але в Oracle реалізовані хороші вбудовані кошти блокувань, які працюють швидше, ефективніше і надійніше, тож даний метод ми не рекомендуємо до використання.

3. Блокувати записи, які користувач збирається змінювати, засобами бази даних. Заблоковану запис неможливо змінити, тому всі запити на редагування будуть відхилятися. Цей підхід є більш правильним і саме йому присвячена ця стаття.

Заблоковані засобами Oracle запису може змінити тільки той користувач, який встановив блокування. Інші можуть тільки переглядати дані і не можуть виконувати UPDATE або DELETE.

Блокування

Блокування-це механізм бази даних, за допомогою якого сервер утримує певні ресурси за певним користувачем. Всі користувачі можуть тільки читати заблоковані дані. Oracle досить інтелектуальний і блокує дані на необхідному рівні. Якщо зміни піддається тільки один рядок, то тільки вона і буде утримана (інші бази даних можуть блокувати дані цілими сторінками, а в одній сторінці може бути кілька рядків, і всі вони стають недоступними для редагування).

Блокування бувають явними і неявними. Неявні створюються сервером без нашої участі при кожній зміні даних таблиці або структури і знімаються по завершенню виконання оператора. Такі блокування існують тільки під час виконання операції модифікації даних. Явні блокування задаються користувачем і можуть бути створені на етапі вибору даних. У даному випадку ви явно вказуєте, що певний ресурс повинен бути закріплений за вами до тих пір, поки ви його не відпустіть.

Не варто боятися блокувань, тому що в Oracle вони ніяк не позначаються на продуктивності системи. Вони лише говорять про те, що якісь дані взяті певним користувачем для редагування.

FOR UPDATE

Коли ми просто використовуємо оператор SELECT для вибірки даних, сервер виконує наш запит без блокування будь-яких записів. Але якщо необхідна вибірка даних безпосередньо для редагування, то ми повинні повідомити сервер про блокування. Для цього в кінець запиту необхідно додати FOR UPDATE. Наприклад, наступний запит вибирає всі записи з таблиці Users для редагування:

SELECT * FROM Users FOR UPDATE

Цей запит жахливий, але він є лише прикладом. Справа в тому, що запит вибирає всі записи з таблиці, а значить, всі вони будуть заблоковані для інших користувачів. Ніколи так не робіть. Якщо вам необхідно змінити всю таблицю, то можете відразу виконувати оператор UPDATE в певній транзакції,-вибирати дані тут не має сенсу. Якщо хоча б один рядок виявиться закріпленої за якимось користувачем, то оператор UPDATE не пройде і блокування не допоможе. Заблокувати таблицю можна ще за допомогою оператора LOCKTABLE, але краще все ж вибирати з допомогою запиту SELECT тільки ті дані, які потрібні, і при цьому вказувати ключові слова FOR UPDATE. Найчастіше робота з даними побудована за принципом «вікно реєстру-вікно редагування». Наприклад, у вас є вікно реєстру документів, де користувачі можуть переглядати рахунки, накладні і т.д. за певний період часу. У цьому вікні відбувається тільки перегляд, тому для вибірки даних тут не слід використовувати блокування, інакше це призведе до проблем при багато користувачів роботі. Якщо один користувач вибере всі документи за місяць, то інші не зможуть відкрити дані за той же період.

Лістинг 1

rocedure TSorneDocurnent. ForrnShow (Sender: TObject); var

oldSql : String; begin

// зберігаємо запит і додаємо оператори блокування

oldSql: = odsDocs. SQL. Text;

OdsDocs.SQL.AddO FOR UPDATE NOWAIT');

try

// намагаємося відкрити набір даних

odsDocs.open;

odsDocs.Readonly : = false;

// перевіряємо, чи знайдений документ

if odsDocs.RecordCount = 0 then

begin

Showmes sage ( 'Документ не знайдений, поки ви думали, його вже видалили');

Close;

exit;

end;

except

// документ заблоковано, тому відкриваємо його тільки для читання

odsDocs.SQL.Text: = oldSql;

Showmessage ( 'Документ заблоковано іншим користувачем, відкриваємо тільки для читання');

odsDocs.open;

odsDocs.Readonly: = true;

end; end;

Коли користувач вирішить відредагувати будь-який документ, слід відкрити окреме вікно, в якому буде обрано саме цей документ і на нього буде встановлена блокування. Наприклад:

SELECT *

FROM Docs

WHERE PrimaryKey = 10

FOR UPDATE

В цьому прикладі ми обираємо і блокуємо запис з таблиці Docs з первинним ключем, рівним 10. Блокування буде поставлено лише на один запис і цей документ більше ніхто не зможе відкрити. Так як у вікні реєстру документів виконується запит SELECT без FOR UPDATE, то він продовжить працювати, і решта користувачі зможуть його переглядати та відкривати для редагування інші незаблокірованние документи.

Не чекайте!

А що станеться, якщо користувач спробує відкрити документ, який вже заблокований іншим користувачем? Відповідь проста-запит зависне в очікуванні звільнення ресурсів. Якщо у вашій програмі не передбачено можливості переривання запитів, а блокування виявилася мертвою, то програма зависне навічно. Завершити роботу можна буде тільки перериванням процесу. Самое страшне, якщо якийсь користувач відкрив вікно і пішов на обід. Ресурс виявляється заблокованим надовго, і це заважає роботі інших користувачів.

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

Щоб сесія не зависла через нескінченного очікування заблокованих даних, я рекомендую додавати ще опцію NOWAIT:

SELECT *

FROM Docs

WHERE PrimaryKey = 10

FOR UPDATE NOWAIT

Такий запит спробує отримати дані і встановити на них блокування, але якщо це неможливо, то очікування не буде. Сервер просто поверне помилку з номером ORA-00054:

ORA-00054 Resource busy and acquire

with NOWAIT specified

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

Приклад

Давайте подивимося, як реалізувати можливість відкриття картки редагування з використанням блокувань на Delphi. Припустимо, у нас є форма TSomeDocument для редагування і дані вибираються за допомогою компонента TOracleDataSet (назвемо його odsDocs) зі складу DOA (Direct Oracle Access, прямий доступ до Oracle). У компоненті odsDocs прописаний запит на вибірку даних без будь-яких блокувань. За події OnShow для форми пишемо код, показаний в лістингу! Розберемо вміст представленого лістингу. Спочатку зберігаємо запит, який прописаний в компоненті, а потім додаємо до запиту опції FOR UPDATE NOWAIT. Тепер відкриваємо набір даних всередині блоку try ... except. Якщо код відпрацював нормально, то ресурс вільний і вже заблокований нами. Потрібно тільки перевірити кількість записів на 0. А раптом, поки ми працювали з вибіркою в реєстрі документів, цей документ вже хтось вилучив?

Якщо під час відкриття набору даних сталася помилка через блокування, то виконання програми переходить на блок except. Тут повертаємо збережений запит в компонент odsDocs, повідомляємо користувачеві, що дані неможливо відкрити для редагування, і відкриваємо набір даних, але вже без опції FOR UPDATE NOWAIT.

Це досить простий, але ефективний спосіб блокування документів.

Блокування в пов'язаних запитах

Припустимо, що у нас є дві таблиці Docs і Users. У таблиці Docs є поле UserlD, де зберігається первинний ключ з таблиці Users. Таким чином, кожен документ прив'язаний до певного користувачеві, наприклад, що створив, відповідального або кому-то еще. Подивимося, як буде виглядати запит на вибірку даних для редагування:

SELECT *

FROM Docs d, Users u WHERE d.PrimaryKey = 10 AND d.UserlD = u.PrimaryKey FOR UPDATE

В результаті блокування буде встановлена не тільки на обраний документ під номером 10, але і на запис в таблиці Users, яка пов'язана з даними документом. Це дуже погано. Тепер, якщо хтось інший спробує відкрити на редагування інший документ, але теж пов'язаний з цим користувачем, то сервер не дасть цього зробити. Всі документи користувача будуть заблоковані, а це неправильно. Блокуватися повинен тільки певний документ, а таблиця користувачів не буде редагуватися (з неї тільки вибирається запис), та її сервер не повинен чіпати.

Як повідомити Oracle, що записи в Users блокувати не можна? Для цього потрібно явно вказати таблицю, а краще-первинний ключ в цій таблиці: FOR UPDATE OF ім'я поля. Після ключового слова OF вказується поле, по якому сервер дізнається, яку запис з пов'язаних таблиць потрібно заблокувати. Отже, наш запит повинен виглядати наступним чином:

SELECT *

FROM Docs d, Users u WHERE d.PrimaryKey = 10 AND d.UserlD = u.PrimaryKey FOR UPDATE OF d.PrimaryKey

Ось тепер буде заблокована тільки один запис документа і тільки з таблиці Docs.

Тривалість

Щоб Вам не блокували дані надовго, при відкритті форми можна запускати таймер і через п'ять хвилин запитувати підтвердження продовження роботи. Якщо користувач не підтвердить, то форма повинна закритися і звільнити ресурс. Це допоможе в тих випадках, якщо хто-небудь забудькуватий піде на обід чи додому, залишивши запущену програму і відкриті ресурси. Якщо у вас багато віконному система і передбачена можливість відкриття одразу безлічі документів, то користувач може забувати закривати вікна редагування, що знов-таки призведе до зайвих, а головне - невиправданим блокувань. Не завадило б засіб, відключає таймер нате випадки, коли користувач дійсно хоче працювати з даними довго і повинен робити це усвідомлено.

Система

Тепер поговоримо про системні уявленнях, за допомогою яких ви можете керувати і контролювати блокування. Всі блокування можна отримати за допомогою подання v $ lock:

SELECT * FROM v $ lock

Результат не дуже зручний, тому що містить якісь адреси та цифри, та й записів дуже багато. У полі sid знаходитися ідентифікатор сесії, а в полі Турі можна побачити тип блокування. Коли викликається SELECT FOR UPDATE, то створюється блокування транзакції, а в полі Турі можна побачити ТХ. Існують і інші види блокування, наприклад, блокування сервера, зміна структури таблиць і т.д. Більш докладно про це можна прочитати в документації по Oracle.

Виходячи з вищесказаного, більш інформативним буде наступний запит:

SELECT s.username, 1 .* FROM v $ lock 1, v $ session s WHERE l.TYPE = 'TX' and l.sid = s.sid

Тут ми зв'язалися з поданням v $ session, що повертає сесії, і тепер в результат попадає ім'я користувача, який утримує блокування. З подання v $ session можна отримати багато корисної інформації. Просто виконайте наступний запит, щоб визначитися, які ще поля можна включити до запит, який було показано вище:

SELECT *

FROM v $ session

Поки все добре, але за отриманими даними ми до цих пір не можемо визначити, хто ж саме заблокував певну рядок. Воттутя б порекомендував створити налаштовувану таблицю журналу з наступних пів їй:

1. Ідентифікатор документа;

2. Ідентифікатор користувача;

3. Дата.

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

Журнал дозволить вам уникнути багатьох дзвінків з питаннями, хто і що заблокував. Якщо нещасливого користувача немає, то тоді вже будуть звертатися до вас, а ви з допомогою таблиць v $ lock і v $ session зможете відшукати блокування і зняти.

LOCK TABLE

Припустимо, що нам потрібно провести декілька дій по зміні даних у всій таблиці (або в більшості її записів), і всі ці зміни неможливо укласти в один єдиний запит UPDATE, та й сама робота з даними забере не п'ять хвилин. При виконанні однієї операції UPDATE блокувати таблицю не має сенсу, але при серйозних змінах це просто необхідно. Якщо після виконання деяких дій хтось заблокує хоча б один запис, подальші ваші дії будуть паралізовані. Такий трюк може призвести до порушення цілісності даних, тому перед великою кількістю змін краще заблокувати всю таблицю. Для блокування всієї таблиці краще використовувати не SELECT FOR UPDATE, a LOCK TABLE IN EXCLUSIVE MODE. Цей оператор блокує всю таблицю відразу, а не кожну рядок окремо.

Заблокувавши всю таблицю, ви можете не поспішаючи модифіковані дані, і ніхто інший не відірве вас від цього цікавого заняття.

Разом

Блокування - Дуже потужний і зручний засіб для розрахованих на багато додатків. Використовуйте їх і ви позбудетеся від безлічі проблем. Головне-дотримуватися правилами:

1. Намагайтеся блокувати мінімально необхідну кількість записів у таблиці.

2. Не забудьте після закриття форми зберегти або відкинути зміни і закрити набір даних, щоб звільнити ресурс.

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

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

Список літератури

IT спец № 07 ЛИПЕНЬ 2007