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

Немного о блокировках

Чтобы понять, как диагностировать и предупреждать блокировки, информация о них и причинах их возникновения.

Наиболее характерными типами являются блокировки DML-операций, блокировки DDL-операций, а также защелки и внутренние блокировки.

• Блокировки данных DML. Они защищают данные таблиц. DML-операции (insert, update, delete и select for update) устанавливают монопольную блокировку для строк, которые обрабатываются DML-операцией, т.е. добавляются, удаляются или изменяются внутри транзакции. Всякий раз, когда DML-операция собирается модифицировать строки в таблице (например, по update или delete), Oracle автоматически устанавливает монопольную блокировку уровня строки (тип TX) на каждую модифицированную строку таблицы. Блокировки DML удерживаются Oracle до тех пор, пока транзакция не завершится, явно (операциями commit или rollback) или неявно. Неявно транзакция завершается после отсоединения пользователя от Oracle или в случае аварийного завершения процесса. Следует заметить, что наличие монопольной блокировки на строки, установленные модифицирующей транзакцией, не запрещает другим транзакциям изменять и удалять не затронутые модифицирующей транзакцией строки той же таблицы.
• Блокировки словаря DDL. Блокировки словаря защищают структуру объектов, например, определения таблиц и представлений. DDL-операции (create, drop, alter, truncate и т.д.) предохраняют объекты Oracle от изменения их структуры или от уничтожения. Так, DDL-блокировки устанавливаются для защиты структуры объекта от модификации другими DDL-операциями.
• Защелки (latch) – это блокировки, предназначенные для установки их на короткие интервалы времени, служат для защиты определенных структур памяти (например, буферного кэша блока БД или библиотечного кэша). Следует заметить, что внутренние блокировки и защелки не могут управляться пользователем.
• Внутренние блокировки – это блокировки разных типов, например, блокировки кэша словаря данных, блокировки управления файлами, блокировки табличного пространства и сегментов отката.

Блокировки можно разделить на две группы: создаваемые автоматически и явные (ручные) блокировки. К ручным блокировкам относятся созданные командами lock table, select for update, или с использованием пакета dbms_lock.

Причины возникновения блокировок и методы их предупреждения

Опыт работы с БД позволил сформулировать следующие наиболее часто встречающиеся причины блокировок:

• Отсутствие завершения транзакций по окончании DML-операции через commit (rollback), в силу чего другие транзакции оказываются заблокированными (им приходится ждать завершения транзакции).
• Отложенные во времени завершения транзакции, состоящей из DML-операций, когда транзакция завершается по commit (rollback) только после выполнения нескольких DML-операций или после автоматического завершения транзакции.
• Длительное время выполнения DML-операций в транзакции, в том числе в силу длительного выполнения запроса, участвующего в DML-операции, или длительного выполнения цикла, содержащего DML-операции.
• Отсутствие commit при модификации строк (например, по update или delete) в одной и той же таблице в цепочке процедур внутри сессии. Что приводит к блокировкам внутри сессии.
• Принудительные ручные блокировки (например, оператором lock table).
• Одновременно ввод в таблицу разными сессиями строк, содержащих одни и те же значения в первичном ключе или уникальном индексе (блокировки по insert).
• Использование битовых индексов в таблицах, подвергающихся одновременному изменению со стороны нескольких сессий.
• Блокировки, возникающие в дочерней таблице, обусловленные изменениями в родительской таблице.

В данной статье речь пойдет о блокировках при использовании неиндексированных внешних ключей. Тема известная для разработчиков, однако, на мой взгляд, существует несколько подводных камней, на которые часто не обращают внимание многие разработчики, вследствие чего могут возникать очень серьезные проблемы в многопользовательской среде.

При проектировании схемы базы данных и разработке таблиц существует очень важное правило: внешние ключи в таблицах без индекса не имеют права на существование. Иначе мы получаем полную блокировку подчиненной таблицы при выполнении изменения первичного ключа или при удалении записи из главной таблицы. Однако, что означает полная блокировка подчиненной таблицы? Как она проявляется? При детальном исследовании можно выяснить много интересного.

create table T_CLASSES

(ID number(32),

NAME varchar2(256)

alter table T_CLASSES

add constraint T_CLASSES_PK

primary key (ID)

using index pctfree 0;

create table T_TYPES

(ID number(32),

NAME varchar2(256)

alter table T_TYPES

add constraint T_TYPES_PK

primary key (ID)

using index pctfree 0;

create table T_VALUES

(ID number(32),

CLASS_ID number(32),

TYPE_ID number(32),

NAME varchar2(256)

alter table T_VALUES

add constraint T_VALUES_PK

primary key (ID)

using index pctfree 0;

alter table T_VALUES

foreign key (CLASS_ID)

references T_CLASSES;

alter table T_VALUES

foreign key (TYPE_ID)

references T_TYPES;

Заполним созданные таблицы тестовыми данными:

insert into T_CLASSES values (10,"State");

insert into T_CLASSES values (20,"Repair");

insert into T_TYPES values (1,"Type 1′);

insert into T_TYPES values (2,"Type 2′);

insert into T_VALUES values (101,10,1,"Wait");

insert into T_VALUES values (102,10,2,"Execute");

insert into T_VALUES values (201,20,1,"Capital");

insert into T_VALUES values (202,20,2,"Current");

Мы заметили, что ссылка в таблице T_VALUES на T_CLASSES оформлена в виде FK, но индекса по CLASS_ID нет. Теперь попробуем увидеть проблемы, возникающие при этой грубой ошибке в проектировании схемы данных. Используем для этого SQL*Plus.

Предположим, что мы хотим удалять записи из таблицы T_CLASSES при помощи внешней процедуры несколькими командами удаления, не давая возможности напрямую удалять записи в клиентском приложении и позволяя выполнять только нашу процедуру. Такой подход является очень хорошей практикой при разработке приложений. Но здесь в этом примере мы процедуру писать не будем, а выполним последовательность команд, которая должна находиться в теле процедуры и должна правильно удалять запись из таблицы классов. Откроем первую сессию SQL*Plus и выполним команды:

select * from T_VALUES where CLASS_ID=10 for update nowait;

delete T_VALUES where CLASS_ID=10;

delete T_CLASSES where;

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

Теперь откроем вторую сессию SQL*Plus для имитации многопользовательского доступа. Предположим, что мы хотим удалить запись из таблицы T_CLASSES с ID 20. По логике работы приложения мы вправе ожидать успешного удаления этой записи, ведь первый сеанс явно заблокировал только запись в T_CLASSES с ID 10 и записи в T_VALUES с ID 101 и 102. Попробуем удалить записи во втором сеансе. При этом мы помним, что первый сеанс удалил свои записи, но не завершил транзакцию. Выполним команды:

select * from T_VALUES where CLASS_ID=20 for update nowait;

select * from T_CLASSES where for update nowait;

delete T_VALUES where CLASS_ID=20;

Мы видим, что они прошли успешно. Нам удалось нормально заблокировать требуемые записи как в подчиненной таблице, так и в главной. Результат не очень согласуется с распространенным утверждением (впрочем, верным для версий сервер до девятой), что полностью блокируется подчиненная таблица при отсутствии индекса на внешний ключ. Мы даже успешно удалили записи в подчиненной таблице. Это объясняется тем, что сервер блокирует подчиненную таблицу целиком только в момент выполнения удаления, после этого блокировка со всей таблицы снимается. Поэтому мы и смогли заблокировать и удалить нужные нам записи во второй сессии. В версиях сервера Oracle до девятой блокировка подчиненной таблицы оставалась до окончания транзакции. В любом случае потребовались ресурсы сервера для выполнения полной блокировки подчиненной таблицы. Теперь выполним:

delete T_CLASSES where;

В результате мы получили зависание. Второй сеанс, несмотря на успешную блокировку записи в T_CLASSES с ID=20, ждет окончание транзакции первого сеанса. Выполним в первом сеансе rollback и увидим, что во втором сеансе успешно завершилось удаление записи. Как мы видим, в современных версиях сервера Oracle проблема блокировки при неиндексированных внешних ключах до конца не снята, а лишь несколько уменьшены возможные последствия. При этом несмотря на вроде бы успешную блокировку записи в T_CLASSES удалить запись не удается!

Если же мы создадим требуемый индекс:

create index T_VALUES_CLASS_FK

on T_VALUES (CLASS_ID);

и затем повторим наш тест, то увидим, что все работает так, как ожидается, без каких либо зависаний.

Мы получили интересные результаты. Конечно, отсутствие индексов на внешние ключи это принципиально плохо, однако эту ошибку разработчики повторяют раз за разом. В сложных системах с сотнями таблиц и множеством внешних ключей отсутствие таких индексов может приводить к резкому снижению производительности системы, а также к зависаниям пользовательских приложений, которые ожидают завершение транзакций других пользователей. Самое печальное, что при использовании операторов блокировки записей select .. for update nowait в приложениях может не возникнуть ожидаемое исключение resource busy, а может возникнуть неконтролируемое зависание приложения, ожидающего завершения транзакций. Кроме того, вероятность возникновения deadlock многократно повышается.

Рекомендация известна и очень проста - нельзя допускать применение внешних ключей при отсутствии индекса по этим внешним ключам. Однако, так ли тривиален этот совет в реальной жизни? Рассмотрим несколько распространенных конструкций, в которых может показаться, что индекс по внешнему ключу есть, но он не работает при блокировках.

Продолжим наш пример. Выполним rollback в обеих наших сессиях и удалим недавно созданный индекс:

drop index T_VALUES_CLASS_FK;

Затем создадим составной индекс, который будет включать в себя два внешних ключа:

create index T_VALUES_IE1

on T_VALUES (CLASS_ID,TYPE_ID);

Снова повторим описанный выше пример. Все работает? Замечательно, так и ожидалось, ведь внешний ключ CLASS_ID индексирован. А теперь в первой сессии выполним следующие команды:

select * from T_VALUES where TYPE_ID=1 for update nowait;

delete T_VALUES where TYPE_ID=1;

delete T_TYPES where;

Мы заблокировали и удалили записи из T_VALUES и T_TYPES для ID=1. Во втором сеансе выполняем команды:

select * from T_VALUES where TYPE_ID=2 for update nowait;

select * from T_TYPES where for update nowait;

delete T_VALUES where TYPE_ID=2;

delete T_TYPES where;

На четвертой команде получаем зависание, ожидающее завершение транзакции в первом сеансе. Уже становится понятно, что виновата во всем ссылка на T_TYPES, которая ведет себя как неиндексированная. Но ведь индекс по TYPE_ID присутствует в составном индексе! Но для блокировок такого индекса нет. Мы можем сделать вывод, что использование составного индекса для внешних ключей недопустимо, если этот ключ не является лидирующим в составном индексе. Хотя при выполнении запросов к таблице оптимизатор сервера сможет нормально использовать составной индекс и для CLASS_ID, и для TYPE_ID. В итоге мы получили еще один довольно интересный результат.

Некоторые разработчики используют в своих базах данных таблицы, организованные по индексу. Часто критерием для использования такого типа таблиц является уменьшение размера занимаемого дискового пространства и увеличение скорости поиска. Однако для такой таблицы обязательно должен быть указан при создании первичный ключ. И если первичный ключ состоит из нескольких полей, которые являются также ссылками на другие таблицы, то мы получим такие же проблемы при блокировках, как и в составных индексах обычных таблиц. Покажем это на примере. Создадим заново таблицу T_VALUES организованную по индексу.

drop table T_VALUES;

create table T_VALUES

(CLASS_ID number(32),

TYPE_ID number(32),

NAME varchar2(256),

constraint T_VALUES_PK primary key (CLASS_ID, TYPE_ID)

) organization index;

alter table T_VALUES

add constraint T_VALUES_CLASS_FK

foreign key (CLASS_ID)

references T_CLASSES;

alter table T_VALUES

add constraint T_VALUES_TYPE_FK

foreign key (TYPE_ID)

references T_TYPES;

Затем выполним первый тест, все пройдет нормально для внешнего ключа CLASS_ID, который лидирует в определении первичного ключа таблицы, организованной по индексу. Второй тест приведет к зависаниям, связанным с ожиданием завершения транзакции в первом сеансе. В случае, если в таблице такого типа в первичном ключе есть более одного внешнего ключа, то по этому внешнему ключу придется создавать индекс для предотвращения проблем с блокированием. В связи с этим, использование таблиц, организованных по индексу в этом случае вряд ли себя может оправдать. Кроме того, если для таких таблиц внешний ключ в составе первичного ключа таблицы только один, но он по каким-либо причинам не лидирует в определении первичного ключа, также возникнет проблема с блокировкой. Пример:

drop table T_VALUES;

create table T_VALUES

(CLASS_ID number(32),

TYPE_ID number(32),

NAME varchar2(256),

constraint T_VALUES_PK primary key (NAME, CLASS_ID)

) organization index;

alter table T_VALUES

add constraint T_VALUES_CLASS_FK

foreign key (CLASS_ID)

references T_CLASSES;

alter table T_VALUES

add constraint T_VALUES_TYPE_FK

foreign key (TYPE_ID)

references T_TYPES;

insert into T_VALUES values (10,1,"Wait");

insert into T_VALUES values (10,2,"Execute");

insert into T_VALUES values (20,1,"Capital");

insert into T_VALUES values (20,2,"Current");

В этом примере в определении первичного ключа всего одна внешняя ссылка, но она не является лидирующей в определении, следовательно, первый тест приведет к зависанию.

На основании рассмотренного выше материала можно сделать следующие выводы:

Использование неиндексированных внешних ключей является в общем случае грубейшей ошибкой при проектировании схемы базы данных. В результате очень вероятно получение блокировок подчиненных таблиц целиком при работе в многопользовательской среде, выражаемых в как бы зависании клиентских приложений, которые могут ожидать завершение транзакций других пользователей. Также очень вероятно резкое снижение производительности в многопользовательской среде из-за блокирования целых таблиц.

Резко возрастает вероятность возникновения deadlock вследствие большого объема блокируемых строк в таблицах.

Необходимо четко себе представлять, что такое неиндексированный внешний ключ. В составных индексах не лидирующие внешние ключи ведут себя при блокировках так же, как если индекса совсем нет. То же самое наблюдение относится к первичным ключам в таблицах, организованных по индексу. Здесь совет может быть следующий: всегда создавайте одиночный, не составной индекс для внешнего ключа.

Использование таблиц, организованных по индексу, должно быть четко обосновано. Всегда необходимо иметь в виду возможность возникновения проблем с блокировками таблиц целиком.

В заключение привожу известный скрипт Тома Кайта для поиска таблиц с неиндексированными внешними ключами:

select table_name, constraint_name,

cname1 // nvl2(cname2,","//cname2,null) //

nvl2(cname3,","//cname3,null) // nvl2(cname4,","//cname4,null) //

nvl2(cname5,","//cname5,null) // nvl2(cname6,","//cname6,null) //

nvl2(cname7,","//cname7,null) // nvl2(cname8,","//cname8,null)

from (select b.table_name,

b.constraint_name,

max(decode(position, 1, column_name, null)) cname1,

max(decode(position, 2, column_name, null)) cname2,

max(decode(position, 3, column_name, null)) cname3,

max(decode(position, 4, column_name, null)) cname4,

max(decode(position, 5, column_name, null)) cname5,

max(decode(position, 6, column_name, null)) cname6,

max(decode(position, 7, column_name, null)) cname7,

max(decode(position, 8, column_name, null)) cname8,

count(*) col_cnt

from (select substr(table_name,1,30) table_name,

substr(constraint_name,1,30) constraint_name,

substr(column_name,1,30) column_name,

position

from user_cons_columns) a,

user_constraints b

where a.constraint_name = b.constraint_name

and b.constraint_type = "R" -R referential integrity

group by b.table_name, b.constraint_name

where col_cnt > ALL

(select count(*)

from user_ind_columns i

where i.table_name = cons.table_name

and i.column_name in (cname1, cname2, cname3, cname4,

cname5, cname6, cname7, cname8)

and i.column_position <= cons.col_cnt

group by i.index_name

order by TABLE_NAME, CONSTRAINT_NAME, COLUMNS

Песочница

бро 9 октября 2013 в 16:52

Ловим блокировку на чтение в Oracle

• Oracle ,
• SQL

Каждый кто более или менее знаком с Oracle знает, что в этой БД нет блокировок на чтение. Достигается это за счет мультиверсионности данных. Более подробно об этом можно почитать в официальной документации.
В приведенной доке можно найти следующее утверждение:

Readers and writers of data do not block one another

В этой статье я бы хотел рассказать об одной особенности, когда ридеры будут блокировать друг друга.

Не буду тянуть и скажу сразу, ловить мы будем следующего зверя: enq: RC - Result Cache: Contention – внутренняя блокировка, использующая механизм очереди, для координации конкурентного доступа к кэшу. Для начала нам понадобится создать одну таблицу и функцию.

Предварительный скрипт

create table cached_table as select level id from dual connect by level < 5; create or replace function get_event_time return number is l_wait_time number; begin select nvl(max(se.time_waited_micro), 0) into l_wait_time from v$session_event se where se.sid = (select sid from v$mystat where rownum < 2) and se.event = "enq: RC - Result Cache: Contention"; return l_wait_time; end;

Далее в двух разных сессиях почти параллельно мы должны запустить практически один и тот же скрипт. Сначала запускаем скрипт «Сессия №1», потом переключаемся в другое окно и запускаем второй скрипт «Сессия №2». В общем, надо запустить второй скрипт до того как в первом истечет время сна.

Сессия №1

declare cursor cur is select /*+ result_cache */ * from cached_table where id between 1 and 2; rec cur%rowtype; l_old_event_wait_time number; l_new_event_wait_time number; begin l_old_event_wait_time:= get_event_time; open cur; fetch cur into rec; dbms_output.put_line(rec.id); fetch cur into rec; dbms_output.put_line(rec.id); dbms_lock.sleep(10); fetch cur into rec; close cur; l_new_event_wait_time:= get_event_time; dbms_output.put_line("wait_time: "||to_char(l_new_event_wait_time-l_old_event_wait_time)); end;

Сессия №2

declare cursor cur is select /*+ result_cache */ * from cached_table where id between 1 and 2; rec cur%rowtype; l_old_event_wait_time number; l_new_event_wait_time number; begin l_old_event_wait_time:= get_event_time; open cur; fetch cur into rec; dbms_output.put_line(rec.id); fetch cur into rec; dbms_output.put_line(rec.id); --dbms_lock.sleep(10); fetch cur into rec; close cur; l_new_event_wait_time:= get_event_time; dbms_output.put_line("wait_time: "||to_char(l_new_event_wait_time-l_old_event_wait_time)); end;

В результате должо получиться следующее:

Первая сессия выполнится чуть более чем за 10 сек и напечатает в output
1
2
wait_time: 0

Вторая сессия тоже будет выполняться около 10 сек и напечатает в output
1
2
wait_time: 8077256

Естественно, последнее число будет у всех разное, оно показывает время ожидания блокировки enq: RC - Result Cache: Contention в микросекундах. Т.е. в моем случае ожидание было 8 сек. Что же произошло?
Когда первая сессия начинает извлекать данные из курсора, она формирует кэш по этому sql-запросу. После того как 2 строки были извлечены, сессия впадает в спячку. В это время вторая сессия начинает читать те же самые строки и т.к. кэш сформирован, то сессия пытается читать результат именно оттуда. И в этот самый момент она натыкается на блокировку. Казалось бы, кэш есть, чего блокируют-то? Дело в том, что первая сессия еще «читает» данные из курсора! Хоть и обе строки уже прочитаны, но еще не стало понятно, что курсор прочитал все что должен был. Это произойдет только после третьего fetch, тогда станет ясно что курсору больше нечего выдавать и блокировка снимется, а до этого момента первая сессия формирует кэш и держит все остальные сессии.
Если инвалидировать кэш и повторить эксперимент заново, но поставить sleep после третьего fetch, то скорее всего блокировку поймать не удастся либо будет совсем маленькое значение времени ожидания, т.к. теперь первая сессия не выполняет ничего долговременного во время формирования кэша.

Механизм result_cache был представлен как новая фича в 11 версии бд. Сам механизм достаточно специфичен, если в нем хорошо разобраться, то, возможно, окажется, что не так уж и много ситуаций есть в вашем проекте, где это действительно можно использовать. Поэтому будьте осторожны прежде чем использовать что-то новое не разобравшить до конца. Недавно был релиз 12 версии бд уже с другими новыми возможностями и поэтому поводу была интересная