Msg 547, Level 16, State 1, Line 1

ALTER TABLE statement conflicted with COLUMN CHECK constraint ckWage . The conflict occurred in database FastFood, table Employees, column HourlyRate.

После этого достаточно применить простой оператор SELECT для проверки наличия значений меньше 6,75 и убедиться в том, что такое значение действительно имеется. Но все же остается непонятным, как такое может случиться. Ведь был предусмотрен оператор UPDATE, который должен был исключить подобную возможность. Действительно, оператор был выполнен и его выполнение прошло вполне успешно, а дело лишь в том, что появился фантом.

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

Единственный способ предотвращения возможности возникновения подобных обстоятельств состоит в использовании значения уровня изоляции транзакции, равного SERIALIZABLE. При использовании этого значения любые другие операторы обновления таблицы должны выполняться в соответствии с условиями, которые не противоречат условиям, заданным в конструкции WHERE текущего оператора, так как в противном случае они будут заблокированы.

Потерянные обновления

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

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

Предположим, что в торговой компании решается вопрос о кредитовании крупной покупки. Аналитик отдела кредитования этой компании получил заяв1су с информацией о том, что заказчик X уже получил кредит на сумму, приближающуюся к лимиту кредитования, но хотел бы взять дополнительный кредит. Аналитик извлекает из базы данных информацию об этом заказчике, чтобы ознакомиться с ней. Обнаруживается, что лимит кредита составляет 5 тыс. долл., но этот заказчик всегда погашает кредит вовремя.

Между тем другой сотрудник отдела кредитования извлекает строку с данными заказчика X, чтобы внести изменения в поле с его адресом. В полученной строке также содержится значение лимита кредитования, равное 5 тыс. долл.

К этому времени первый сотрудник принимает решение удовлетворить запрос и повысить лимит кредитования заказчика X до 7500 долл. Он вводит это значение и нажимает клавишу <Enter>. С данного момента в базе данных находится значение лимита кредитования для заказчика X, равное 7500 долл.

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

Напомним, что в форме редактирования второго сотрудника находилось значение лимита кредитования, равное 5 тыс. долл. Итак, в базе данньгх снова появилось значение лимита кредитования для заказчика X, равное 5 тыс. долл. Обновление, выполненное первым сотрудником, было потеряно.

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

Блокируемые ресурсы

в СУБД SQL Server предусмотрено шесть различных типов блокируемых ресурсов, которые образуют иерархию. Чем выше уровень блокировки, тем крупнее ее степень детализации (иными словами, при повышении уровня блокировки происходит выбор все большего и большего количества блокируемых объектов, на которые может распространяться любое каскадно выполняемое действие, даже лишь потому, что заблокирован сам объект, к которому относится это действие). Блокируемые ресурсы, расположенные в порядке возрастания степени детализации, описаны ниже.

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

Таблица. Если выбрана степень детализации блокировки на уровне таблицы, то блокируется вся таблица. К этому относятся все объекты данных, связанные с самой таблицей, в том числе действительные строки с данными (все строки без исключения) и все ключи, определенные на всех индексах, связанных с рассматриваемой таблицей.

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

Страница. Применение степени детализации блокировки на уровне страницы приводит к тому, что блокируются все данные или ключи индекса на этой странице.

Ключ. Блокировка со степенью детализации на уровне ключа представляет собой блокиров1су конкретного ключа или рада ключей в индексе. Другие ключи, заданные на той же странице индекса, могут оказаться незатронутыми.

Строка или идентификатор строки (Row Identifier - RID). Формально при использовании блокировки со степенью детализации на уровне строки считается, что блокировка распространяется на идентификатор строки (это - внут ренняя конструкция СУБД SQL Server), но по существу блокируется вся строка.

Процесс эскалации блокировок и влияние блокировок на производительность

Стремление использовать более низкий уровень детализации блокировки (допустим, поддерживать блокировку на уровне строки, а не страницы) оправдано, если количество блокируемых элементов невелико. Но по мере увеличения количества блокируемых элементов издержки на поддержание всех этих блокировок возрастают настолько значительно, что производительность начинает заметно снижаться. Это означает, что блокировки в связи с уменьшением скорости обработки приходится оставлять на более продолжительное время, а это приводит к увеличению конкуренции за ресурсы, ведь чем дольше строка остается заблокированной, тем выше вероятность того, что эта строка потребуется кому-то другому. Чтобы выйти из этой ситуации, можно заменить несколько блокировок с низким уровнем детализации одной блокировкой с более высоким уровнем детализации; такой процесс объединения блокировок называется эскалацией блокировок. При выборе оптимальной степени детализации блокировок необходимо найти компромисс, не допуская чрезмерного увеличения количества блокировок и, с другой стороны, не применяя блокировки, которые охватывают слишком крупные объекты. Если же в ходе работы количество блокировок существенно увеличивается, то в диспетчере блокировок применяется эскалация для перехода на следующий уровень.

После того как количество поддерживаемых блокировок достигает определенного порогового значения, происходит эскалация блокировок и переход на следующий, более высокий уровень детализации, что позволяет уменьшить объем работы, связанный с обслуживанием блокировок более низкого уровня (в результате освобождаются ресурсы, скорость обработки данных повышается и конкуренция за ресурсы становится менее острой).

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

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