Обнаружение и разрешение конфликтов блокировок
В любой многопользовательской среде неизбежно ситуация, когда два пользователя захотят работать с одними и теми же данными в одно и то же время. База данных должна обеспечивать отсутствие такой возможности. Принцип изоляции из ACID теста гласит что база данных должна гарантировать что сессии не должны видеть результаты выполнения транзакций других сессий до тех пор, пока транзакция не завершена. Для обеспечения такого результата БД должна упорядочить параллельный доступ к данным; т.е. убедиться, что даже если сессии затребовали доступ к одним и тем же строкам, в реальности запросы выстраиваются в очередь и ждут своей очереди.
Упорядочивание доступа происходит за счёт работы механизма табличных и строчных блокировок. Блокировка в Oracle происходит полностью автоматически. Грубо говоря, проблемы возникают только если программа пытается взаимодействовать с механизмом блокировок с помощью плохо написанного кода, или если бизнес аналитики и бизнес-модель разработана неправильно где сессии будут пересекаться.
Общие и исключительные блокировки
Стандартный уровень блокировок в Oracle гарантирует максимальный уровень параллелизма. Это значит что если сессия обновляет одну строку – блокируется одна строка, ничего больше. Более того, строка блокируется только для изменения – остальные сессии могут считывать эту строку. Блокировка существует пока транзакция не завершена, либо COMMIT либо ROLLBACK. Это исключительная (exclusive) блокировка: первая сессия затребовала блокировку строки на изменение, остальные сессии которые хотят заблокировать строку для изменения должны ждать. Доступ для чтения разрешён – несмотря на то что строка изменяется сессией заблокировавшей строку, операции чтения будут использовать данные отката чтобы гарантировать недоступность данных неподтверждённых транзакций.
Только одна сессия может блокировать строку или таблицу исключительной блокировкой – но общие (shared) блокировки могут накладываться на один объект многими сессиями. Нет никакого смысла использовать общую блокировку для строки, так как единственной целью блокировки стрки это получение уникального доступа к данным для изменения. Общие блокировки накладываются на всё таблицу, и многие сессии могут наложить общую блокировку на одну и ту же таблицу. Общие блокировки нужны для того, чтобы предотвратить исключительную блокировку таблицы другими сессииями: вы не можете получить исключительную блокировку если уже существует общая блокировка. Эксклюзивная блокировка таблицы нужна для выполнения DDL команд. Вы не можете выполнить запрос которые изменяет объект (к примеру удалить столбец из таблицы) если хотя бы одна другая сессия заблокировала таблицу общей блокировкой.
Для выполнения DML команда над строками, сессия должна получить исключительные блокировки всех строк которые будут изменяться и общую блокировку для таблицы. Если другая сессия уже наложила исключительную блокировку на строки, сессия будет висеть пока блокировки не будут убраны командой COMMIT или ROLLBACK. Если другая сессия заблокировала таблицу общей блокировкой но исключительная блокировка наложена на другие строки – то всё в порядке оба запроса могут работать. Исключительная блокировка для таблицы может быть, но по умолчанию механизм блокировок не блокирует всю таблицу пока это не необходимо для DDL команды.
Все DML команды требуют минимум две блокировки: исключительная блокировка строки которая изменяется и общая блокировка для таблицы содержащей строку. Исключительная блокировка предотвращает взаимодействие других сессий с изменяемой строкой а общая блокировка предотвращает другие сессии от изменения таблицы с помощью DDL команды. Эти блокировки запрашиваются автоматически. Если DML запрос не может получить необходимые блокировки – сессия будет висеть до момента получения этих блокировок.
Для выполнения DDL команды – необходимо исключительная блокировка всего объекта. Эта блокировка не может быть получена пока не завершены все DML транзакции к таблице и не освобождена исключительные блокировки строк и общие блокировки таблицы. Исключительная блокировка необходимая для DDL команды запрашивается автоматически, но если нельзя заблокировать объект в текущий момент – обычно потому что другие сессии наложили общую блокировку – то запрос прекратит выполнение с ошибкой вместо ожидания.
Механизм размещения в очереди
Запросы на блокировку помещаются в очередь. Если сессия запросила блокировку и не может получить её так как другая сессия уже заблокировала строку или объект, сессия будет ждать. Может случить что несколько сессий ждут доступа для одной и той же строки или объекта – в этом случае, Oracle будет отслеживать порядок в котором сессии запрашивали блокировку. Когда сессия заблокировавшая объек или строку освобождает его – блокировка разрешается следующей сессии и так далее. Это называется механизм размещения в очереди (enqueue mechanism).
Если вы не хотите чтобы сессия ждала в очереди если нет возможности заблокировать объект, то единственным способом избежать этого будет использование WAIT или NOWAIT директивы в команде SELECT … FOR UPDATE. Обычный SELECT всегда выполнится успешно так как SELECT не требует каких-либо блокировок – но DML команда будет висеть. Команда SELECT… FOR UPDATE вернёт набор строк и заблокирует их исключительной блокировкой. Если строки уже заблокированы – команда будет ждать в очереди пока не освободятся блокировки, как обычная DML команда. Для того чтобы избежать зависания сессии можно использовать SELECT… FOR UPDATE NOWAIT или SELECT… FOR UPDATE WAIT <n> где n это количетсво секунд ожидания. После получения блокировок с помощью команды SELECT FOR UPDATE вы можете выполнять DML команды без возможности зависания сессии.
TIP
Возможно добавить директиву SKIP LOCKED к команде SELECT FOR UPDATE, тогда запрос вернёт только те строки которые не заблокированы другими сессиями. Эта команда существовала и раньше но поддерживается только с версии 11g
Конкуренция блокировок
Когда сессия запрашивает блокировку для строки или объекта и не может её получить потому что объекты уже заблокированы другой сессией – сессия зависает. Это конкуренция блокировок, и она может плохо сказыватьяс на производительности базы данных так как все сессии в очереди ожидают блокировки. Некоторые блокировки могут быть необходимыми: природа приложения предполагает доступ разными пользователями к одним и тем же данным в один и тот же момент времени в результате работы. Некоторые же параллельные блокировки могут быть вызваны неправильно написанной программой.
Oracle предоставляет способ для обнаружения конкурирующих блокировок и также возможность разрешить проблему конкуренции в случае необходимости. Отдельным видом конкурирующих транзакций является deadlock. В случае возникновения таких ситуаций БД устраняет их автоматически.
Причины конкурирующих транзакций
Иногда бывает что бизнес-пользователям необходимо изменять данные в одних и тех же строках одновременно. Если это тормозит производительность системы, единственных выходом будет разработка новой бизнес-модели и новых бизнес-процессов. Но несмотря на то, что часть конкурирующих блокировок это правильное поведение системы – иногда конкуренция возникает из-за плохо разработанного приложения, что усугубляет проблему.
Длительные транзакции всегда будут вызывать проблемы. Типичным примером является случай, когда пользователь обновляет строку и не подтверждает транзакцию. Возможно пользователь даже ушёл на обед и не подтвердил транзакцию. Вы не сможете контролировать это если пользователи работают с помощью таких инструментов как SQL *Plus, но этого никогда не должно происходить в хорошо продуманном приложении. Программа должна учитывать что блокировка должна запрашиваться непосредственно перед изменением данных и освобождаться немедленно после завершения работы.
Некоторые программы запрашивают больше блокировок чем им необходимо. Например в некоторых программах для разработки всегда используется SELECT.. FOR UPDATE чтобы избежать необходимости перечитывать данные и проверять их на изменения. Некоторые инструменты для разработки не используют блокировку строки: если пользователь хочет обновить всего одну строку – компонент запрашивает блокировку для нескольких десятков или даже сотен строк. Если ваша программа написана с использованием таких компонентов – база данных Oracle будет именно то что вы сказали ей делать: блокировть много строк вместо одной, что не необходимо с точки зрения бизнес-логики.
Обнаружение и разрешение проблем вызванных конкурирующими транзакциями
Существуют представления в которых хранится информация о блокировках в базе данных, но это один из тех случаев когда даже очень опытные администраторы обычно предпочитают использовать инструменты с графическим интерфейсом. Чтобы попасть в окно управления блокировками в Database Control перейдите на вкладку Performance из домашней страницы а затем перейдите по ссылке Instance Lock в секции Additional Monitoring. На рисунке 8-6 отображено окно Instance Locks, с выбранной секцией Blocking Locks. Может быть любое количество блокировок в базе данных, но нас интересут только запросы на блокировки которые не могут быть выполнены (сессии висят). Такие блокировки известны как заблокированные блокировки (bBlocking locks).
На рисунке 8-6 видны две проблемы. Сессия с номером 116 подключенная к аккаунту SCOTT заблокировала одну или более строк в таблице HR.EMPLOYEES. Эта сессия не висит – она работает в обычном режиме. Но сессия 129, подключенная как пользователь MPHO заблокирована – она ждёт освобождения блокировок сессией 116. Сессия 129 висит в данный момент и продолжит работу когда сессия 116 освободит блокировки выполнив команду ROLLBACK или COMMIT. Вторая проблема хуже: пользователь JON блокирует две сессии – ISAAC и ROOP.
Конкуренция блокировок это обычное последствие одновременного доступа разных пользователей к одним и тем же данным. Эта проблема может усиливаться из-за плохого приложения, но в принципе конкуренция блокировок это часть нормальной работы базы данных. Поэтому DBA не может решить эту проблему полностью – он может только указать что существует проблема, и предложить учитывать эту информацию при проектировании системы: разработке структур данных и процессов.
Если блокировки становятся проблемой, как на рисунке 8-6, то необходимо изучить причину. Database Control может предоставить необходимую информацию. Нажав на значения в столбец SQL ID вы сможете увидеть SQL запрос, выполнение которого вызвало конкурирующую блокировку. Столбец “ROWID” можно использовать чтобы найти конкретную строку за которую конкурируют сессии. Вы не можете посмотреть строку в этом окне Database Control но вы можете использовать значение и выполнять запросы в отдельной сессии (которая не требует блокировок). Когда запросы и данные известны, можно обсудить решение проблемы с программистами и бизнес-аналитиками.
В экстренных случаях, DBA может разрешить конкретную ситуацию – отключив сессию, или сессии которые задерживают много блокировок слишком долго. Когда сессия отключается принудительно – все блокировки вызванные этой сессией освобождаются и транзакция отменяется. Заблокированные сессии смогут продолжать работу. Для отключения сессии можно использовать Database Control или команду ALTER SYSTEM KILL SESSION. В нашем примере если мы решим отключить пользователя SCOTT, можно выбрать эту строку и нажать кнопку KILL SESSION.
Deadlock
Возможно возникновение ситуации, когда две сессии блокируют друг друга таким образом что обе будут висеть вечно, каждая будет ожидать освобождения блокировок другой. Это называется deadlock. Deadlock-и это не проблема DBA: они могут быть вызваны только плохой архитектурой системы и разрешаются автоматически базой данных. Информация о возникновении deadlock-ов записываетя в системный журнал, с подробным описание в файле трассировки – частью обычного мониторинга базы данных является проверка на возникновение deadlock-ов и предоставление разработчиками подробной информации при каких обстоятельствах это произошло.
Если возникает deadlock – обе сессии зависнут, но лишь на короткое время. Одна из сессий обнаружит возникновение deadlock-а и произойдёт отмена запроса вызывающего проблему с ошибкой “ORA-00060 Deadlock detected”. Это сообщение должно отслеживаться программистами.
Вы должны понимать что deadlock-и возникают только по вине неправильно спроектированной программы. Код пытается выполнить что-то, что логически невозможно. Хорошо написанное приложени всегда вызывает блокировки последовательно таким образом, чтобы исключить возникновение deadlock-ов или будет проверять наличие несовместимых блокировок перед тем как их запрашивать.