НОВЫЙ КУРС БД 2013

действия! Тогда как возвращаемый хранимой процедурой набор данных нужно предварительно поместить в таблицу (чаще всего временную). Общий синтаксис пользовательской функции:

CREATE FUNCTION <имя функции>

(

@<параметр 1 > <тип данных> [= значение по умолчанию 1], @<параметр 2 > <тип данных> [= значение по умолчанию 2],

…

@<параметр N > <тип данных> [= значение по умолчанию N]

)

RETURNS <тип данных> |

RETURNS TABLE [WITH ENCRYPTION | SHECNABINDING] |

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT | EXECUTE AS {CALLER | SELF | OWNER | ‘ИМЯ ОЛЬЗОВАТЕЛЯ’}

[AS] [EXTERNAL NAME <имя внешнего метода в dll>] BEGIN

[КОД TSQL]

RETURN <скалярное значение> | RETURN (запрос с SELECT)

END

Разберем этот довольно большой код:

Вначале, как всегда стоит оператор CREATE, создающий объект БД, затем идет имя функции и список параметров с указанием их типов, и если необходимо значений по умолчанию. После этого идет ключевое слово RETURNS, совместно с которым могут применяться несколько вариантов. Тип возвращаемого значения зависит от того, что мы хотим получить в итоге скалярную величину или таблицу. Дальше все как у хранимых процедур, однако, слово RETURN здесь является обязательным.

Давайте напишем простую процедуру, возвращающую скалярное значение. Начнем мы именно со скалярных значений, поскольку большинство встроенных функций возвращают именно его. Например, функция GETDATE() возвращает текущую дату, функция USER() возвращает имя пользователя, под которым ведется работа с базой в текущий момент. Если вы работаете от имени системного администратора, то функция вернет dbo – data base owner, т.е. владелец базы данных. Как было сказано выше, процедуры могут возвращать практически любые типы данных и даже пользовательские типы. В качестве возвращаемого значения нельзя использовать только курсоры и BLOB поля. Итак, наша первая функция:

CREATE FUNCTION dbo.GetDateOnly (@D datetime) RETURNS varchar(12)

AS BEGIN

Return CONVERT(varchar(12),@D,104)

--104 – это специальный параметр, который указывает формат даты

--в данном случае будет возвращено значение в виде дд.мм.гггг

END

Теперь мы можем выполнить запрос с использованием нашей функции, но прежде опишем задачу, для которой писалась эта функция:

Пусть есть такая таблица:

ID	Man	DateInsert
1	12	2012-01-14 14:12:22.740
2	2	2013-01-12 14:00:36.020

В нее вставляются данные, и при вставке данных, в поле DateInsert вставляется текущая дата через функцию Getdate(). Если мы попробуем выбрать строки, вставленные сегодня, используя такой запрос:

SELECT * FROM Mans WHERE DateInsert = GETDATE()

91

Мы всегда будем получать пустой результат! Такой итог обусловлен тем, что GETDATE возвращает дату с точностью миллисекунды. И любой вызов функции GETDATE вернет дату позже, чем дата вставки. Для того чтобы этот запрос вернул строки с сегодняшней датой нужно соответствующим образом ее отформатировать. Например так:

SELECT * FROM Mans

WHERE CONVERT(varchar(12), DateInsert,104)=CONVERT(varchar(12), GETDATE(),104)

Такая организация запроса, создает определенные трудности при его восприятии. Для того чтобы его упростить мы и написали нашу функцию dbo.GetDateOnly (@D datetime). Давайте ее применим:

SELECT * FROM Mans

WHERE dbo.GetDateOnly(DateInsert) = dbo.GetDateOnly(GetDate())

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

	Orders
ID	OrderDate	Price
1	2012-01-14 14:12:22.740	2304.23
2	2012-01-15 15:32:62.765	123.00
…	…	…
79	2013-01-14 11:19:21.340	34678.67

Напишем функцию, которая будет возвращать среднюю цену заказа за определенный интервал времени, а следующая функция будет возвращать разницу с указанной ценой:

CREATE FUNCTION dbo.AVGPrice (@DateStart datetime, @DateEnd datetime) RETURNS money

AS BEGIN

RETURNS (Select AVG(Price) FROM Orders

WHERE OrderDate Between @DateStart AND @DateEnd) END

GO

CREATE FUNCTION dbo.PriceDIFF (@Price money, @DateStart datetime, @DateEnd datetime) RETURNS money

AS BEGIN

RETURNS @Price – dbo.AVGPrice(@DateStart, @DateEnd)

END

GO

Вызвать нашу функцию очень просто:

DECLARE @avgprice money

SET @avgprice = (Select ‘Средняя цена за октябрь 2012’ = dbo.AVGPrice(

CONVERT(datetime,’01.10.2012’,104),

CONVERT(datetime,’31.10.2012’,104))

Select ‘Разница со средней ценой сентября и октября 2012’ = dbo.PriceDIFF

(@avgprice, CONVERT(datetime,’01.09.2012’,104), CONVERT(datetime,’30.09.2012’,104))

92

Функция вернет два значения – первое средняя цена за октябрь 2012 и разницу средней цены за октябрь и сентябрь 2012. Можно обойтись и без использования функций, написав подзапросы и используя другие средства, однако функции делают жизнь проще.

Теперь мы рассмотрим функцию, которая возвращает таблицу:

CREATE FUNCTION dbo.TMansList RETURNS TABLE

AS BEGIN

RETURNS (Select job, surname + ‘ ‘ + name + ‘ ‘ + patronymic as fio, DATEPART(yy, birthdate)

FROM Mans)

END

Функция просто возвращает таблицу с отформатированными специальным образом столбцами. Но мы можем использовать эту функцию прямо в запросах и работать с результатами этой функции как с обычной таблицей:

Select * from dbo.TMansList()

Select * from dbo.TMansList() as TML inner join Job as J On TML.job = J.id

Подводя итог по пользовательским функциям, хочется выделить то, что их использование улучшает быстродействие запросов. А возможность использовать функции, написанные на других языках программирования (.NET совместимых и не только) делает использование функций очень удобным в плане повторного использования кода. В данном пункте не применялась технология подключения библиотек. Я рекомендую посетить сайт, на котором есть очень хороший пример по созданию и подключению DLL, написанной на языке C#. На этом сайте также производится создание пользовательских функций уже на TSQL, которые ссылаются на функции из библиотеки.

Адрес сайта: http://www.t-sql.ru/post/CompressFileCLR.aspx

4.5 Транзакции и блокировки

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

4.5.1 Основные понятия о транзакциях

Начнем рассмотрение темы по традиции с определения основных понятий:

 Транзакция – последовательность команд TSQL, работающая по принципу «все или ничего». Поясним немного данное определение – принцип все или ничего означает, выполняется либо

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

Например, процесс перевода студента из одной группы в другую подразумевает исключение его из одной группы и зачисление его в другую группу. Пусть у нас студент по фамилии Иванов переводится из группы с именем «Иб-111» в группу «Иб-112». Предположим также, что у нас есть база данных, в которой храниться информация о группах студента (допустимо, чтобы студент учился в нескольких группах на разных формах обучения):

	Студенты					Группы студента				Группы
ID	Фамилия	Имя	Отчество		ID	Студент	Группа	Статус		Имя группы
…	…	…	…		…	…	…	…		Иб-111
23456	Иванов	Петр	Олегович		28	23456	Иб-111	учится		Иб-112
…	…	…	…		…	…	…	…		…

93

Для того, чтобы перевести студента из одной группы в другую, нужно просто обновить и добавить соответствующие записи в таблице группы студента:

UPDATE [Группы студента] SET [Статус] = «отчислен»

WHERE ([Студент] = 23456) AND ([Группа] = «Иб-111»)

INSERT INTO [Группы студента] ([Студент],[ Группа],[Статус])

VALUES (23456, «Иб-112», «учится»)

Этот довольно простой сценарий содержит очень большую опасность – он может выполниться частично (по разным причинам), т.е. если первая строка выполнилась, а вторая нет! В этом случае мы просто потеряем информацию о студенте, отчислив его из группы «Иб-111», но, не добавив его в группу «Иб-112» И в итоге наша база данных перестанет отражать актуальную информацию о студенте. Очевидно, что эти операции связаны и должны выполняться парой. Можно конечно вести отслеживание ошибок при выполнении каждого запроса, анализировать количество строк затронутых запросом и другие системные переменные, но согласитесь не всегда это возможно и в целом такой подход очень затратен по времени. Здесь на помощь нам и придут транзакции – включив обе эти операции в одну транзакцию, мы обеспечим отмену всех операций в этой транзакции, если одна из операций этой транзакции была завершена неудачно.

Давайте теперь посмотрим синтаксис определения транзакций:

BEGIN TRAN [имя транзакции]

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

COMMIT TRAN [имя транзакции]

Этот оператор фиксирует все данные в базе данных физически. Т.е. все то, что было сделано в рамках транзакции, сначала будет записано в кеш и никак не влияет на реальные данные в базе данных, операция COMMIT TRAN записывает все изменения из кеша уже в базу данных. Если имя транзакции не задано будет закрыта последняя открытая транзакция.

SAVE TRAN [имя точки восстановления]

Этот оператор создает точку восстановления данных – промежуточная копия состояния данных в момент выполнения транзакции. Каждая точка уникально определяется заданным именем и затем, используя это имя можно выполнить откат к любой точке восстановления с помощью оператора ROLLABACK TRAN, о котором ниже.

ROLLBACK TRAN [имя транзакции] | [имя точки восстановления]

Этот оператор отменяет все то, что было сделано транзакцией, и приводит данные к моменту, как будто ни один оператор транзакции не выполнялся. Также, если была создана точка восстановления, описанная выше, то откат можно произвести к конкретной точке, используя ее имя. Однако после выполнения операции ROLLBACK TRAN все созданные точки уничтожаются, независимо к какой из них был сделан откат, при этом можно создавать новые точки восстановления. В результате этих действий могут возникать довольно запутанные ситуации, поэтому применять откат к точкам следует с осторожностью. Если имя транзакции не задано будет отменена последняя открытая транзакция.

Итак, давайте напишем нашу первую транзакцию, опираясь на новые знания:

BEGIN TRAN TranslateStud

UPDATE [Группы студента] SET [Статус] = «отчислен»

WHERE ([Студент] = 23456) AND ([Группа] = «Иб-111»)

INSERT INTO [Группы студента] ([Студент],[ Группа],[Статус]) VALUES (23456, «Иб-112», «учится»)

IF @@ERROR = 0 COMMIT TRAN TranslateStud ELSE ROLLBACK TRAN TranslateStud

94

4.5.2 Основные понятия о блокировках

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

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

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

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

4.5.3. Проблемы, решаемые с помощью блокировок

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

1)Чтение незафиксированных данных

2)Неповторяемое чтение

3)Фантомы

4)отерянные обновления

Вданном пункте затрагивается вопрос об уровне изоляции транзакций, информация об этом будет дана в следующем пункте, а пока рассмотрим каждую проблему детально:

Чтение незафиксированных данных

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

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

95

Неповторяемое чтение

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

Неповторяемое чтение - эта ситуация происходит, когда строка транзакции считывается дважды, а между моментами считывания происходит ее модификация в другой транзакции. В качестве примера снова обратимся к книге Виейры – Программирование баз данных:

Итак, проблема состоит в том, что в транзакции 1 хранится несуществующее значение 125, поскольку строка с этим числом уже изменена во второй транзакции на 75 и вторая транзакция завершилась! Это означает, что первая транзакция работает с устаревшими данными, фактически в таблице уже хранится значение 75, а работа идет со значением 125. И в итоге мы получаем отрицательное число, хотя логика в первой транзакции верна и должна исключать отрицательных чисел. Выходом из сложившейся ситуации является задание уровня изоляции транзакции равным REPEATED READ или SERIALIZABLE. Можно еще задать ограничение CHECK на появление отрицательных чисел и затем обрабатывать возникающие ошибки, но я за превентивный подход – т.е. устранить проблему до ее появления.

Фантомы

Фантомы это строки, которые появляются и исчезают, как будто не подчиняясь законам операторов UPDATE и DELETE. Типичный пример появления фантомных строк приведен ниже:

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

Salary

ID	Должность	Оклад
1	Менеджер	13000
2	Программист	25000
3	Бухгалтер	22000

96

Фирма работает довольно успешно, и руководство решает поднять зарплату сотрудников, установив ее минимум в 20000 рублей. Программист пишет такой код, и не думая об какой-либо ошибке:

Update Salary

Set Оклад = 20000 Where Оклад < 20000

ALTER TABLE Salary ADD CONSTRAINT chSalary CHECK (Оклад >=20000) GO

Каково же будет удивление программиста, когда SQL вернет ему ошибку с кодом 547, т.е. нарушение ограничения. Получается, что существую строки с окладом меньше 20000! Но как такое возможно, если задано ограничение! Такая ситуация встречается крайне редко, однако в момент после того как все строки были обновлены, но еще не было задано ограничение другой пользователь создал должность с окладом меньше 20000. Единственный способ предотвратить возникновение таких ситуаций – это использовать уровень изоляции SERIALIZABLE. Подробнее об уровнях изоляции в следующем пункте.

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

Эта довольно необычная ситуация возникает когда результаты обновления одной транзакции перекрывают обновления другой. Например, одновременно два сотрудника открыли форму редактирования сотрудника, один редактирует данные паспорта и сохраняет информацию. Другой редактирует номер ИНН, и тоже сохраняет информацию, при этом, не затрагивая паспортные данные. Однако в форме второго человека находятся устаревшие паспортные данные и если логика приложения задана неверно, то старые данные паспорта вновь будут записаны в таблицу. Таким образом, работа первого человека будет полностью потеряна. Даная ситуация имеет единственный путь решения – в коде клиентского приложения определять какие поля были изменены и изменять только затронутые поля, а не строку целиком.

4.5.4. Уровни изоляции транзакций

Чтобы понять, какова связь между транзакциями и блокировками, необходимо уяснить, как выполняются транзакции в условиях применения блокировок. По умолчанию любая блокировка модификации данных, после ее создания сохраняется в течение всей продолжительности выполнения транзакции. Из этого следует, что чем более продолжительное время выполняется транзакция, тем больше по времени будет заблокирован объект(ы), на который эта транзакция направлена. А это в свою очередь влечет очень большие последствия, в виде длительного ожидания отклика от системы, при этом будет сведена на нет вся параллельность работы. Кроме этого в ходе программы могут возникать так называемые взаимные блокировки (deadlock) транзакций, - это такая ситуация когда транзакция X1 не выполняется, а ждет пока освободится объект O1, занятый транзакцией X2, которая, в свою очередь, ждет освобождения объекта O2, который занят транзакцией X1. Таким образом, получаем ситуацию бесконечного ожидания транзакций. Есть еще одна разновидность бесконечного ожидания – это хождение по кругу, называемой постоянной блокировкой. При такой блокировке система не «застревает», как в обычной взаимной блокировке, а занимается бесполезной работой, её состояние постоянно меняется — но, тем не менее, она «зациклилась» и не производит никакой полезной работы. В жизни такую ситуацию можно сравнить с таким событием, когда двое людей встречаются лицом к лицу, и каждый из них пытается посторониться, но они не расходятся, а несколько секунд сдвигаются в одну и ту же сторону.

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

97

READ COMMITTED (применяется по умолчанию)

READ UNCOMMITED

REPEATABLE READ

SERIALIZABLE

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

SET TRANSACTION ISOLATION LEVEL

<READ COMMITTED> | <READ UNCOMMITED> | <REPEATABLE READ> | <SERIALIZABLE>

ровень READ COMMITTED (применяется по умолчанию)

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

ровень READ UNCOMMITTED

Этот уровень изоляции напротив позволяет транзакции считывать данные, модифицируемые в других транзакциях. Иными словами это полная противоположность READ COMMITTED. Этот вариант изоляции наиболее опасный, однако, он обеспечивает самую высокую производительность. Если выбран этот уровень изоляции, блокировки для объекта использоваться не будут! Используя данный уровень можно «на себе» прочувствовать все проблемы блокировок, описанные ранее. По своему опыту скажу, что надобность в установке данного уровня изоляции возникает крайне редко, поскольку результаты выполнения практически всех транзакций ставятся под сомнение и что толку в том, что все они будут выполнены быстро, если их результаты окажутся не верны. Данный уровень можно применять лишь в одном случае – для сбора статистики и построения отчетов, где все транзакции работают только на выборку, и нет ни одной транзакции модифицирующей данные.

ровень REPEATABLE READ

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

ровень SERIALIZABLE

Это самый строгий уровень изоляции, он позволяет избежать всех описанных проблем, кроме потерянного обновления. Установка этого уровня равносильна указанию, что любые операторы модификации данных таблицы (UPDATE, DELETE, INSERT), указанные в любой транзакции будут отвергнуты, пока не будет завершена работа с этими же строками этой таблицы в другой транзакции. Иными словами если некая транзакция выполняет действия со строками 1,3 и 5 некой таблицы, то эти строки будут заблокированы, пока эта транзакция не выполнится, а любые другие транзакции желающие изменить эти же строки будут ожидать в очереди и затем выполняться последовательно! Т.е. фактически при определенных обстоятельствах мы исключаем возможность параллельной работы пользователей, все запросы у нас начинают работать по принципу очереди, что естественно скажется на скорости работы транзакций и работы системы в целом.

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

98

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

4.5.5 Уровни блокировок и эскалация блокировок.

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

данных, неповторяемое чтение, фантомы.

С каждой из проблем связано множество неприятностей, решение которых состоит в правильном выборе режима изоляции объекта. В SQL Server предусмотрено шесть различных уровней блокировки или изоляции объекта:

1)ровень базы данных. Это самый высокий уровень. Происходит блокировка всех объектов базы данных. Т.е. ни один пользователь не сможет обратиться ни к одному объекту базы. Такой уровень обычно применяется при изменении структуры базы данных.

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

3)ровень экстента. Экстент – единица памяти, используемая при распределении пространства для таблиц и индексов. Экстент занимает 64 КБ и состоит из восьми смежных страниц, каждая из которых объемом 8 Кбайт. Это означает, что в одном мегабайте базы данных SQL Server содержится 128 страниц или 16 экстентов. Информацию об организации хранения данных можно посмотреть на сайте Microsoft в разделе MSDN (например, здесь - http://msdn.microsoft.com/ru-ru/library/ms190969(v=sql.105).aspx). А кратко скажу, что в SQL Server используется довольно специфичный подход к выделению памяти под данные в таблицах: при вставке данных в таблицу в оперативной памяти выделяется место именно под новый экстент, а не под новую строку, что было бы логичнее на первый взгляд. Т.е. если данные не убираются в один экстент, то происходит выделение нового экстента, при этом одна строка СУБД может храниться в разных экстентах! И место под новый экстент выделяется, даже когда не хватает всего 1 байта! Вообще процесс распределения памяти в SQL Server это тема для отдельного разговора, это довольно сложный и не всегда оптимальный процесс и я не буду вдаваться в его детали в данном курсе. Вернемся к блокировке на уровне экстента. На этом уровне происходит блокирование восьми страниц данных таблицы, т.е. всего экстента. Данный уровень в основном применяется при создании новых таблиц, а также при увеличении размера базы данных в целом. Выставляется он сервером блокировок автоматически, чтобы не затронуть смежные данные, находящиеся в других таблицах. Как правило, данный уровень блокировки вручную не выставляется.

4)ровень страницы. Страница, как было сказано выше, также является единицей хранения данных, ее размер – 8 КБ. На данном уровне блокируется страница с данными, находящимися на ней, это может быть часть строк таблицы и индекса. Т.е. блокируются все строки, входящие в одну страницу. Этот тип блокировок также в основном применятся SQL Server для того, чтобы не затронуть сохраненные данные при вставке новых данных. Он используется диспетчером блокировок очень часто, но практически никогда вручную.

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

6)ровень идентификатора строки (Row Identifier – RID). RID-уровень распространяется на всю строку в таблице. Т.е. блокируется все столбцы таблицы в конкретной строке. Наиболее часто употребляемый уровень изоляции.

По умолчанию SQL Server управляет уровнем блокировок автоматически в зависимости от уровня изоляции транзакции, однако при желании мы можем изменить уровень блокировки с помощью специальных подсказок – хинтов. Однако делать это нужно очень осторожно и только

99

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

MSDN. Информация о подсказках для SQL приведена на следующей странице http://msdn.microsoft.com/ru-ru/library/ms187373(v=sql.90).aspx

Как уже было сказано, SQL Server сам выбирает наиболее оптимальный объект для блокировки и для большинства операций вначале пытается использовать только блокировки на уровне строки (RID-уровень). Если запрос на чтение или изменение данных касается большого количества записей, то для экономии ресурсов SQL Server может принять решение об использовании блокировок более высокого уровня. Важно отметить тот факт, что SQL переходит к уровню KEY, при случае, когда общее количество блокировок в системе на заданном объекте превышают определенный предел (настраивается администратором) и\или начинают занимать слишком большой объем памяти, при этом все блокировки уровня RID снимаются, таким образом, экономится память. Если количество блокировок уровня KEY также превышает определенный уровень, диспетчер блокировок переходит на уровень таблицы, освобождая все KEY-блокировки, при этом пропускаются уровни страницы и экстента! Уровни изоляции страниц и экстента применяются при добавлении данных, а не при модификации данных. Повышение уровня блокировки называется эскалацией блокировок (lock escalation).

Чем меньше детализация блокировки (строка – самая высокая степень детализации), тем ниже ее стоимость, но ниже и возможность параллельной работы пользователей. Если выбирать минимальную степень детализации, запросы на выборку и обновление данных будут исполняться очень быстро, но другие пользователи при этом должны будут ожидать завершения транзакции. Степень параллелизма можно увеличить путем повышения уровня детализации, однако блокировка – вполне конкретный ресурс SQL Server’а, для ее создания, поддержания и удаления требуется время и память. Блокировка занимает 96 байт. Общее количество блокировок может варьироваться от 5000 до 2 147 483 647. Конкретное значение можно задать с помощью хранимой процедуры sp_configure с параметром locks.

Внешне механизм эскалации блокировок выглядит очень логичным и эффективным, однако на практике с ним возникают проблемы. При выполнении операций, которые должны быть выполнены с большим количеством записей в таблице, SQL Server пытается вначале использовать блокировки уровня записи. В результате на установку и последующее снятие таких блокировок расходуется значительное количество системных ресурсов. Этого можно было бы избежать, если сразу применить для выполнения операции нужный уровень блокировок (Ключа или Таблицы). Данную проблему можно решить настроив уровень изоляции транзакции самостоятельно. Вторая, более важная проблема, заключается в том, что SQL Server применяет эскалацию блокировок, в том числе и на мощных серверах, с которыми одновременно работает большое число пользователей. Типичная ситуация выглядит таким образом: в базе данных есть большая таблица, с которой постоянно работают пользователи (назовем ее главной таблицей). За счет эскалации блокировок количество записей, которые одновременно блокируют пользователи, автоматически увеличивается, в результате чего другие пользователи не могут получить к ним доступ. Таким образом, при достижении определенного количества пользователей работа с этой таблицей резко затрудняется. Для решения подобных проблем в зависимости от ситуации можно использовать разные способы. Я приведу некоторые наиболее употребимые способы:

1)Вручную выставлять уровни блокировок и степени изоляции транзакций. Как уже было сказано, эта возможность ориентирована на опытных разработчиков.

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

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

100