Восстановление базы данныхпредставляет собой процедуру восстановления базы данных в некоторое корректное состояние, принимаемое в случае разрушения системы.
Управление транзакциями
В функциональные возможности любой типичной СУБД должны входить три взаимосвязанных функции: механизмы поддержки транзакций, управление параллельностью и средства восстановления базы данных, – назначение которых состоит в гарантированном поддержании базы данных в достоверном и согласованном состоянии. Причем достоверность и согласованность базы данных должна сохраняться при наличии отказов оборудования или программных компонентов, а также при эксплуатации базы данных в многопользовательской среде.
Многие СУБД допускают одновременное выполнение несколькими пользователями различных операций в базе данных. Если эти операции будут осуществляться бесконтрольно, выполняемые пользователями действия будут произвольным образом влиять друг на друга, вследствие чего база данных может перейти в несогласованное состояние. Для исключения подобных явлений в каждой СУБД реализуется некоторый протокол управление параллельностью, в задачу которого входит предотвращение нежелательного влияния пользовательских процессов друг на друга.
Существует множество различных типов отказов, способных повлиять на функционирование базы данных, каждый из которых требует особых способов обработки. Они отказы влияют только на содержимое оперативной памяти, другие могут воздействовать и на вторичную память системы. Приведем некоторые причины, способные вызвать отказы:
– аварийное прекращение работы системы, вызванное ошибкой оборудования или программного обеспечения, приведшей к разрушению содержимого оперативной памяти;
– отказ носителей информации, например, разрушение магнитной головки или появление неустранимого сбоя чтения, имеющее следствием потерю части вторичной памяти системы;
– ошибка прикладных программ, например, логические ошибки в программах, получающих доступ к базе данных, послужившие причиной сбоев при выполнении одной или нескольких транзакций;
– стихийные бедствия, например, пожары, наводнения, землетрясения или отказы в сети электропитания;
– небрежное или легкомысленное отношение, послужившее причиной непреднамеренного разрушения данных или программ со стороны оператора или пользователей системы;
– диверсии и преднамеренное разрушение или уничтожение данных, оборудования или программного обеспечения.
Разрушение системы может быть вызвано как отказами оборудования, так и программными ошибками или сбоями носителей информации.
Причины могут быть различны: разрушение магнитной головки, ошибка в прикладной программе, ошибка в логике приложения, работающего с базой данных, и т. д. Кроме того, причиной может послужить непреднамеренное или преднамеренное повреждение, или уничтожение данных и программного обеспечения операторами системы или ее конечными пользователями. Любая СУБД должна иметь средства восстановления системы (независимо от конкретной причины отказа), а также возможность возврата базы данных в согласованное состояние (в случае его утраты).
Какой бы ни была причина отказа системы, существует два принципиальных следствия, которые надо учитывать: утрата содержимого оперативной памяти, в том числе буфера базы данных, и утрата копии базы данных на дисках.
Восстановление базы данныхпредставляет собой процедуру восстановления базы данных в некоторое корректное состояние, принимаемое в случае разрушения системы.
Поддержка транзакций
Концепция транзакций – неотъемлемая часть любой клиент-серверной базы данных. Поддержание механизма транзакций – показатель уровня развитости СУБД. Корректное поддержание транзакций одновременно является основой обеспечения целостности БД. Транзакции также составляют основу изолированности в многопользовательских системах, где с одной БД параллельно могут работать несколько пользователей или прикладных программ. Одна из основных задач СУБД – обеспечение изолированности, т.е. создание такого режима функционирования, при котором каждому пользователю казалось бы, что БД доступна только ему. Такую задачу СУБД принято называть параллелизмом транзакций.
Большинство выполняемых действий производится в теле транзакций. По умолчанию каждая команда выполняется как самостоятельная транзакция. При необходимости пользователь может явно указать ее начало и конец, чтобы иметь возможность включить в нее несколько команд.
Определение. Транзакция– это действие или серия действий, выполняемых одним пользователем или прикладной программой, которые осуществляют доступ или изменение содержимого базы данных.
Транзакция является логической единицей работы, выполняемой в базе данных. Простейшей транзакцией может быть модификация зарплаты работника, указанного его личным номером. Более сложная транзакция, например, предназначена для удаления сведений о работнике, заданным его учетным номером, когда удаляются все сведения, связанные с данным работником, из всех таблиц базы, данных.
Под транзакцией понимается неделимая с точки зрения воздействия на БД последовательность операторов манипулирования данными (чтения, удаления, вставки, модификации), приводящая к одному из двух возможных результатов: либо последовательность выполняется, если все операторы правильные, либо вся транзакция откатывается, если хотя бы один оператор не может быть успешно выполнен. Обработка транзакций гарантирует целостность информации в базе данных. Таким образом, транзакция переводит базу данных из одного целостного состояния в другое.
Любая транзакция всегда должна переводить базу данных из одного согласованного состояния в другое, хотя допускается, что согласованность состояния базы будет нарушаться в ходе выполнения транзакции. Таким образом, любая транзакция завершается одним из двух возможных способов. В случае успешного завершения результаты транзакции фиксируются(commit) в базе данных, и последняя переходит в новое согласованное состояние. Если выполнение транзакции завершилось неудачно, она отменяется.
В этом случае в базе данных должно быть восстановлено то согласованное состояние, в котором она находилась до начала данной транзакции. Этот процесс называется откатом(rollback) транзакции. Зафиксированная транзакция не может быть отменена. Если оказывается, что зафиксированная транзакция была ошибочной, потребуется выполнить другую транзакцию, отменяющую действия, выполненные первой транзакцией. В некоторых случаях эту транзакцию называют компенсирующей. Следует отметить, что отмененная транзакция может быть еще раз запущена позже и, в зависимости от причин предыдущего отказа, вполне успешно завершена и зафиксирована в базе данных.
ACID-свойства транзакций
Существуют некоторые свойства, которыми должна обладать любая из транзакций, так называемые ACID – аббревиатура, составленная из первых букв английских названий:
При выполнении транзакции система управления базами данных должна придерживаться определенных правил обработки набора команд, входящих в транзакцию. В частности, разработано четыре правила, известные как требования ACID, они гарантируют правильность и надежность работы системы:
– атомарность(atomicity). Это свойство типа «все или ничего». Любая транзакция представляет собой неделимую единицу работы, которая может быть либо выполнена вся целиком, либо не выполнена вовсе;
– согласованность (consistency). Каждая транзакция должна переводить базу данных из одного согласованного состояния в другое согласованное состояние;
– изолированность (isolation). Все транзакции выполняются независимо одна от другой. Другими словами, промежуточные результаты незавершенной транзакции не должны быть доступны другим транзакциям;
– продолжительность (durability). Результаты успешно завершенной (зафиксированной) транзакции должны сохраняться в базе данных постоянно и не должны быть утеряны в результате последующих сбоев.
Менеджер транзакций осуществляет координацию работы транзакций, выполняемых прикладными программами. Он взаимодействует с планировщиком, отвечающим за реализацию выбранной стратегии управления параллельностью. В некоторых случаях планировщик называют менеджером блокировки, если используемый протокол управления параллельностью строится на основе системы блокировок. Цель работы планировщика состоит в достижении максимально возможного уровня параллельности при условии исключения влияния параллельно выполняющихся транзакций друг на друга, поскольку это может послужить источником нарушения согласованности базы данных. Если в процессе выполнения транзакции происходит отказ, то база данных может оказаться в несогласованном состоянии. Задачей менеджера восстановления является предоставление гарантий того, что в подобном случае база данных будет автоматически возвращена в то состояние, в котором она находилась до начала данной транзакции и, следовательно, останется согласованной. Менеджер буферов отвечает за передачу данных между основной памятью компьютера и вторичной дисковой памятью.
Блокировки
Повышение эффективности работы при использовании небольших транзакций связано с тем, что при выполнении транзакциисервер накладывает на данные блокировки.
Блокировкой называется временное ограничение на выполнение некоторых операций обработки данных. Блокировка может быть наложена как на отдельную строку таблицы, так и на всю базу данных. Управлением блокировками на сервере занимается менеджер блокировок, контролирующий их применение и разрешение конфликтов. Транзакции и блокировки тесно связаны друг с другом. Транзакции накладывают блокировки на данные, чтобы обеспечить выполнение требований ACID. Без использования блокировок несколько транзакций могли бы изменять одни и те же данные.
Блокировка представляет собой метод управления параллельными процессами, при котором объектБД не может быть модифицирован без ведома транзакции, т.е. происходит блокирование доступа к объекту со стороны других транзакций, чем исключается непредсказуемое изменение объекта. Различают два вида блокировки:
· блокировка записи – транзакция блокирует строки в таблицах таким образом, что запрос другой транзакции к этим строкам будет отменен;
· блокировка чтения – транзакция блокирует строки так, что запрос со стороны другой транзакции на блокировку записи этих строк будет отвергнут, а на блокировку чтения – принят.
Если в системе управления базами данных не реализованы механизмы блокирования, то при одновременном чтении и изменении одних и тех же данных несколькими пользователями могут возникнуть следующие проблемы одновременного доступа:
· проблема последнего изменения возникает, когда несколько пользователей изменяют одну и ту же строку, основываясь на ее начальном значении; тогда часть данных будет потеряна, т.к. каждая последующая транзакция перезапишет изменения, сделанные предыдущей. Выход из этой ситуации заключается в последовательном внесении изменений;
· проблема "грязного" чтения возможна в том случае, если пользователь выполняет сложные операции обработки данных, требующие множественного изменения данных перед тем, как они обретут логически верное состояние. Если во время изменения данных другой пользователь будет считывать их, то может оказаться, что он получит логически неверную информацию. Для исключения подобных проблем необходимо производить считывание данных после окончания всех изменений;
· проблема неповторяемого чтения является следствием неоднократного считывания транзакцией одних и тех же данных. Во время выполнения первой транзакции другая может внести в данные изменения, поэтому при повторном чтении первая транзакция получит уже иной набор данных, что приводит к нарушению их целостности или логической несогласованности;
· проблема чтения фантомов появляется после того, как одна транзакция выбирает данные из таблицы, а другая вставляет или удаляет строки до завершения первой. Выбранные из таблицы значения будут некорректны.
Для решения перечисленных проблем в специально разработанном стандарте определены четыре уровня блокирования. Уровень изоляциитранзакции определяет, могут ли другие (конкурирующие) транзакции вносить изменения в данные, измененные текущей транзакцией, а также может ли текущая транзакция видеть изменения, произведенные конкурирующими транзакциями, и наоборот. Каждый последующий уровень поддерживает требования предыдущего и налагает дополнительные ограничения:
· уровень 0 – запрещение "загрязнения" данных. Этот уровень требует, чтобы изменять данные могла только одна транзакция; если другой транзакции необходимо изменить те же данные, она должна ожидать завершения первой транзакции;
· уровень 1 – запрещение "грязного" чтения. Если транзакция начала изменение данных, то никакая другая транзакция не сможет прочитать их до завершения первой;
· уровень 2 – запрещение неповторяемого чтения. Если транзакция считывает данные, то никакая другая транзакция не сможет их изменить. Таким образом, при повторном чтении они будут находиться в первоначальном состоянии;
· уровень 3 – запрещение фантомов. Если транзакция обращается к данным, то никакая другая транзакция не сможет добавить новые или удалить имеющие строки, которые могут быть считаны при выполнении транзакции. Реализация этого уровня блокирования выполняется путем использования блокировок диапазона ключей. Подобная блокировка накладывается не на конкретные строки таблицы, а на строки, удовлетворяющие определенному логическому условию.
В СУБД используют протокол доступа к данным, позволяющий избежать проблемы параллелизма. Его суть заключается в следующем:
· транзакция, результатом действия которой на строку данных в таблице является ее извлечение, обязана наложить блокировку чтения на эту строку;
· транзакция, предназначенная для модификации строки данных, накладывает на нее блокировку записи;
· если запрашиваемая блокировка на строку отвергается из-за уже имеющейся блокировки, то транзакция переводится в режим ожидания до тех пор, пока блокировка не будет снята;
· блокировка записи сохраняется вплоть до конца выполнения транзакции.
Решение проблемы параллельной обработки БД заключается в том, что строки таблиц блокируются, а последующие транзакции, модифицирующие эти строки, отвергаются и переводятся в режим ожидания. В связи со свойством сохранения целостности БДтранзакции являются подходящими единицами изолированности пользователей. Действительно, если каждый сеанс взаимодействия с базой данных реализуется транзакцией, то пользователь начинает с того, что обращается к согласованному состоянию базы данных – состоянию, в котором она могла бы находиться, даже если бы пользователь работал с ней в одиночку.
Управление транзакциями
Под управлением транзакциями понимается способность управлять различными операциями над данными, которые выполняются внутри реляционной СУБД. Прежде всего, имеется в виду выполнение операторов INSERT, UPDATE и DELETE. Например, после создания таблицы (выполнения оператора CREATE TABLE ) не нужно фиксировать результат: создание таблицы фиксируется в базе данных автоматически. Точно так же с помощью отмены транзакции не удастся восстановить только что удаленную оператором DROP TABLE таблицу.
После успешного выполнения команд, заключенных в тело одной транзакции, немедленного изменения данных не происходит. Для окончательного завершения транзакции существуют так называемые команды управления транзакциями, с помощью которых можно либо сохранить в базе данных все изменения, произошедшие в ходе ее выполнения, либо полностью их отменить.
Существуют три команды, которые используются для управления транзакциями:
· COMMIT – для сохранения изменений;
· ROLLBACK – для отмены изменений;
· SAVEPOINT – для установки особых точек возврата.
После завершения транзакции вся информация о произведенных изменениях хранится либо в специально выделенной оперативной памяти, либо во временной области отката в самой базе данных до тех пор, пока не будет выполнена одна из команд управления транзакциями. Затем все изменения или фиксируются в базе данных, или отбрасываются, а временная область отката освобождается.
Команда COMMIT предназначена для сохранения в базе данных всех изменений, произошедших в ходе выполнения транзакции. Она сохраняет результаты всех операций, которые имели место после выполнения последней команды COMMIT или ROLLBACK.
Команда ROLLBACK предназначена для отмены транзакций, еще не сохраненных в базе данных. Она отменяет только те транзакции, которые были выполнены с момента выдачи последней команды COMMIT или ROLLBACK .
Команда SAVEPOINT (точка сохранения) предназначена для установки в транзакции особых точек, куда в дальнейшем может быть произведен откат (при этом отката всей транзакции не происходит).Команда имеет следующий вид:
SAVEPOINT имя_точки_сохраненияОна служит исключительно для создания точек сохранения среди операторов, предназначенных для изменения данных. Имя точки сохранения в связанной с ней группе транзакций должно быть уникальным.
Для отмены действия группы транзакций, ограниченных точками сохранения, используется командаROLLBACK со следующим синтаксисом:
ROLLBACK TO имя_точки_сохраненияПоскольку с помощью команды SAVEPOINT крупное число транзакций может быть разбито на меньшие и поэтому более управляемые группы, ее применение является одним из способов управления транзакциями.
SQL Server предлагает множество средств управления поведением транзакций. Пользователи в основном должны указывать только начало и конец транзакции, используя команды SQL или API (прикладного интерфейса программирования).
Транзакция определяется на уровне соединения с базой данных и при закрытии соединения автоматически закрывается. Если пользователь попытается установить соединение снова и продолжить выполнение транзакции, то это ему не удастся.
Когда транзакция начинается, все команды, выполненные в соединении, считаются телом одной транзакции, пока не будет достигнут ее конец.
Явные транзакции
Явные транзакции требуют, чтобы пользователь указал начало и конец транзакции, используя следующие команды:
начало транзакции: в журнале транзакций фиксируются первоначальные значения изменяемых данных и момент начала транзакции ;
BEGIN TRAN[SACTION][имя_транзакции |@имя_переменной_транзакции[WITH MARK [‘описание_транзакции’]]]конец транзакции: если в теле транзакции не было ошибок, то эта команда предписывает серверу зафиксировать все изменения, сделанные в транзакции, после чего в журнале транзакций помечается, что изменения зафиксированы и транзакция завершена;
COMMIT [TRAN[SACTION] [имя_транзакции | @имя_переменной_транзакции]]создание внутри транзакции точки сохранения: СУБД сохраняет состояние БД в текущей точке и присваивает сохраненному состоянию имя точки сохранения;
SAVE TRAN[SACTION] {имя_точки_сохранеия | @имя_переменной_точки_сохранения}прерывание транзакции; когда сервер встречает эту команду, происходит откат транзакции, восстанавливается первоначальное состояние системы и в журнале транзакций отмечается, что транзакция была отменена.
Приведенная ниже команда отменяет все изменения, сделанные в БД после оператора BEGIN TRANSACTION или отменяет изменения, сделанные в БД после точки сохранения, возвращая транзакцию к месту, где был выполнен оператор SAVE TRANSACTION.
ROLLBACK [TRAN[SACTION] [имя_транзакции |@имя_переменной_транзакции | имя_точки_сохранения |@имя_переменной_точки_сохранения]]Функция @@TRANCOUNT возвращает количество активных транзакций.Функция @@NESTLEVEL возвращает уровень вложенности транзакций.
BEGINTRANSAVETRANSACTIONpoint1Вложенные транзакции
Вложенными называются транзакции, выполнение которых инициируется из тела уже активной транзакции.
Для создания вложенной транзакции пользователю не нужны какие-либо дополнительные команды. Он просто начинает новую транзакцию, не закрыв предыдущую. Завершение транзакции верхнего уровня откладывается до завершения вложенных транзакций. Если транзакция самого нижнего (вложенного) уровня завершена неудачно и отменена, то все транзакции верхнего уровня, включая транзакцию первого уровня, будут отменены. Кроме того, если несколько транзакций нижнего уровня были завершены успешно (но не зафиксированы), однако на среднем уровне (не самая верхняя транзакция) неудачно завершилась другая транзакция, то в соответствии с требованиями ACID произойдет откат всех транзакций всех уровней, включая успешно завершенные. Только когда все транзакции на всех уровнях завершены успешно, происходит фиксация всех сделанных изменений в результате успешного завершения транзакции верхнего уровня.
Каждая команда COMMIT TRANSACTION работает только с последней начатой транзакцией. При завершении вложенной транзакции команда COMMIT применяется к наиболее "глубокой" вложенной транзакции. Даже если в команде COMMIT TRANSACTION указано имя транзакции более высокого уровня, будет завершена транзакция, начатая последней.
Если команда ROLLBACK TRANSACTION используется на любом уровне вложенности без указания имени транзакции, то откатываются все вложенные транзакции, включая транзакцию самого высокого (верхнего) уровня. В команде ROLLBACK TRANSACTION разрешается указывать только имя самой верхней транзакции. Имена любых вложенных транзакций игнорируются, и попытка их указания приведет к ошибке. Таким образом, при откате транзакции любого уровня вложенности всегда происходит откат всех транзакций. Если же требуется откатить лишь часть транзакций, можно использовать команду SAVE TRANSACTION, с помощью которой создается точка сохранения.
BEGIN TRANINSERT Товар (Название, остаток)VALUES ('v',40) BEGIN TRAN INSERT Товар (Название, остаток) VALUES ('n',50) BEGIN TRAN INSERT Товар (Название, остаток) VALUES ('m',60)ROLLBACK TRANВ качестве примера рассмотрим, что происходит, если в неявной транзакции обновляется одна строка таблицы. Представим себе простую неупорядоченную таблицу, содержащую столбец c1 с целочисленными данными и столбец c2 с символьными данными. В таблице имеется 10 000 строк, и пользователь отправляет запрос на обновление следующим образом.
UPDATE SimpleTable SET c1 = 10 WHERE c2 LIKE '%Paul%';Выполняются следующие операции.
· Страницы данных из SimpleTable считываются с диска в память (буферный пул), поэтому можно выполнять поиск соответствующих строк. Оказывается, что на трех страницах данных имеется пять строк, соответствующих предикату предложения WHERE.
· Модуль хранилищ автоматически запускает неявную транзакцию.
· Эти три страницы и пять строк данных блокируются для выполнения обновлений.
· Изменения вносятся в пять записей данных на трех страницах данных, находящихся в памяти.
· Изменения записываются также в записи журнала транзакций на диске.
· Модуль хранилищ автоматически фиксирует эту неявную транзакцию.
Уровни изоляции SQL Server
Уровень изоляции определяет степень независимости транзакций друг от друга. Наивысшим уровнем изоляции является сериализуемость, обеспечивающая полную независимость транзакций друг от друга. Каждый последующий уровень соответствует требованиям всех предыдущих и обеспечивает дополнительную защиту транзакций.
SQLServer поддерживает все четыре уровня изоляции, определенные стандартом ANSI.
Уровеньизоляцииустанавливаетсякомандой:
SETTRANSACTIONISOLATIONLEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }· READ UNCOMMITED – незавершенное чтение, или допустимо черновое чтение. Низший уровень изоляции, соответствующий уровню 0. Он гарантирует только физическую целостность данных: если несколько пользователей одновременно изменяют одну и ту же строку, то в окончательном варианте строка будет иметь значение, определенное пользователем, последним изменившим запись. По сути, для транзакции не устанавливается никакой блокировки, которая гарантировала бы целостность данных. Для установки этого уровня используется команда:
SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED· READ COMMITTED –завершенное чтение, при котором отсутствует черновое, "грязное" чтение. Тем не менее в процессе работы одной транзакции другая может быть успешно завершена и сделанные ею изменения зафиксированы. В итоге первая транзакция будет работать с другим набором данных. Это проблема неповторяемого чтения. Данный уровень изоляции установлен в SQL Server по умолчанию и устанавливается посредством команды:
SET TRANSACTION ISOLATIONLEVEL READ COMMITTED· REPEATABLE READ – повторяющееся чтение. Повторное чтение строки возвратит первоначально считанные данные, несмотря на любые обновления, произведенные другими пользователями до завершения транзакции. Тем не менее на этом уровне изоляции возможно возникновение фантомов . Его установка реализуется командой:
SET TRANSACTION ISOLATIONLEVEL REPEATABLE READ· SERIALIZABLE – сериализуемость. Чтение запрещено до завершения транзакции. Это максимальный уровень изоляции, который обеспечивает полную изоляцию транзакций друг от друга. Он устанавливается командой:
SET TRANSACTION ISOLATIONLEVEL SERIALIZABLEВ каждый момент времени возможен только один уровень изоляции.
| Таблица 1. Уровень изоляции конкурирующей транзакции принят по умолчанию (READ COMMITTED). В примере шаги 4, 6 и 8 демонстрируют черновое чтение. Шаги 9 и 10 блокируются, потому что данные захвачены конкурирующей транзакцией. | |
| Пользователь user1 Конкурирующая транзакция | Пользователь user2 Текущая транзакция |
| USE basa_user2 BEGIN TRANSACTION TRA | USE basa_user2 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRANSACTION TRB |
| 1. SELECT * FROM Товар | 2. SELECT * FROM Товар |
| 3.UPDATE Товар SET остаток=остаток+10 WHERE КодТовара=4 | 4. SELECT * FROM Товар (читает измененные неподтвержденные данные) |
| 5.DELETEFROMТоварWHEREКодТовара=4 | 6. SELECT * FROM Товар (читает измененные неподтвержденные данные) |
| 7. INSERT Товар (Название, остаток) VALUES ('SS',999) | 8. SELECT * FROM Товар (читает измененные неподтвержденные данные) |
| 12. ROLLBACK TRANSACTION TRA | 9. UPDATE Товар SET остаток=остаток+10 WHERE КодТовара=4 (блокируется до окончания конкурирующей транзакции ) |
| 10.DELETE FROM Товар WHERE КодТовара=4(блокируется до окончания конкурирующей транзакции ) | 11.INSERT Товар(Название, остаток) VALUES ('SS',999) (выполняется) |
| 13. ROLLBACK TRANSACTION TRB SELECT @@TRANCOUNT | |
| Таблица 16.2. Уровень изоляции конкурирующей транзакции принят по умолчанию (READ COMMITTED). В примере шаги 4, 6 и 8 демонстрируют блокировку данных, захваченных другой транзакцией, в то время как работа с другими данными разрешается (шаг 10). | |
| Пользователь user1 Конкурирующая транзакция | Пользователь user2 Текущая транзакция |
| USE basa_user2 BEGIN TRANSACTION TRA | USE basa_user2 SET TRANSACTION ISOLATION LEVELREAD COMMITTED BEGIN TRANSACTION TRB |
| 1. SELECT * FROM Товар | 2. SELECT * FROM Товар |
| 3. UPDATE Товар SET остаток=остаток+10 (захватывает данные) | 4. SELECT * FROM Товар WHERE КодТовара=4 (блокируется до окончания конкурирующей транзакции ) |
| 5. DELETE FROM Товар WHERE КодТовара=4 | 6. UPDATE Товар SET остаток=остаток+10 WHERE КодТовара=4 (блокируется до окончания конкурирующей транзакции ) |
| 7. UPDATE Товар SET остаток=остаток+10 WHERE КодТовара=4 (выполняется той транзакцией, которая первой захватила данные на изменение или удаление) | 8. DELETE FROM Товар WHERE КодТовара=4 (блокируется до окончания конкурирующей транзакции ) |
| 9. INSERT Товар (Название, остаток) VALUES ('SS',999) | 10. INSERT Товар(Название, остаток) VALUES ('SS',999) (выполняется) |
| 11. ROLLBACK TRANSACTION TRA SELECT @@TRANCOUNT | 12. ROLLBACK TRANSACTION TRB SELECT @@TRANCOUNT |
| Таблица 16.3. Уровень изоляции конкурирующей транзакции принят по умолчанию (READ COMMITTED). На шаге 2 транзакция захватила данные чтением и блокирует работу с ними со стороны конкурирующей транзакции (шаги 3, 5), которая может лишь добавлять записи (шаг 7). | |
| Пользователь user1 Конкурирующая транзакция | Пользователь user2 Текущая транзакция |
| USE basa_user2 BEGIN TRANSACTION TRA | USE basa_user2 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION TRB |
| 1. SELECT * FROM Товар | 2. SELECT * FROM Товар (захватывает данные) |
| 3. UPDATE Товар SET остаток=остаток+10 WHERE КодТовара=4 (блокируется) | 4. SELECT * FROM Товар (блокируется до окончания конкурирующей транзакции ) |
| 5. DELETE FROM Товар WHERE КодТовара=4 (блокируется) | 6. UPDATE Товар SET остаток=остаток+10 WHERE КодТовара=4 (выполняется, т.к. данные захвачены текущей транзакцией ) |
| 7. INSERT Товар (Название, остаток) VALUES ('SS',999) (выполняется) | 8. DELETE FROM Товар WHERE КодТовара=4 (выполняется, т.к. данные захвачены текущей транзакцией ) |
| 10. ROLLBACK TRANSACTION TRA SELECT @@TRANCOUNT | 9. INSERT Товар(Название, остаток) VALUES ('SS',999) (выполняется) |
| 11. ROLLBACK TRANSACTION TRB SELECT @@TRANCOUNT | |
| Таблица 16.4. Уровень изоляции конкурирующей транзакции принят по умолчанию (READ COMMITTED). Пример демонстрирует, что текущая транзакция захватила данные чтением (шаг 2) и блокирует любые действия с ними со стороны конкурирующей транзакции вплоть до вставки данных (шаг 7). | |
| Пользователь user1 Конкурирующая транзакция | Пользователь user2 Текущая транзакция |
| USE basa_user2 BEGIN TRANSACTION TRA | USE basa_user2 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION TRB |
| 1. SELECT * FROM Товар | 2. SELECT * FROM Товар (захватывает данные) |
| 3. UPDATE Товар SET остаток=остаток+10 WHERE КодТовара=4 (блокируется) | 4. SELECT * FROM Товар (выполняется) |
| 5. DELETE FROM Товар WHERE КодТовара=4 (блокируется) | 6. UPDATE Товар SET остаток=остаток+10 WHERE КодТовара=4 (выполняется, т.к. данные захвачены текущей транзакцией ) |
| 7. INSERT Товар (наименование, остаток) VALUES ('SS',999) (блокируется) | 8. DELETE FROM Товар WHERE КодТовара=4 (выполняется, т.к. данные захвачены текущей транзакцией ) |
| 10. ROLLBACK TRANSACTION TRA SELECT @@TRANCOUNT | 9. INSERT Товар(Название, остаток) VALUES ('SS',999) (выполняется) |
| 11. ROLLBACK TRANSACTION TRB SELECT @@TRANCOUNT |
Использование sp_lock
Прежде чем показать запрос, автоматизирующий обнаружение проблемных запросов, хотелось бы поговорить о другой важной характеристике запросов с низкой производительностью, а именно, о безудержной трате ресурсов.
Sp_who2 показывает хорошую картину процессов, которые могут блокировать другие процессы, и некоторое начальное представление об использовании ресурсов типа CPU и I/O, но не говорит ничего о различных блокировках, наложенных для исполнения процесса.
Рисунок 1. После KILL в Important_Data нет записей.
Блокировка – это «нормальное» действие SQL Server, то есть это механизм, посредством которого SQL Server управляет параллельным доступом к данному ресурсу нескольких конкурирующих процессов. Однако как DBA вы должны распознавать поведение блокировок, явно указывающее, что что-то не так.
Распространенные типы блокировок:
§ RID – блокировка одной строки
§ KEY – диапазон ключей в индексе
§ PAG – блокировка страницы данных или индекса
§ EXT – блокировка экстента
§ TAB – блокировка таблицы
§ DB – блокировка БД
В дополнение к типам блокировок, относящимся к ресурсам или объектам, у SQL Server есть общие режимы блокировок:
§ S – Совмещаемая (или разделяемая, Shared) блокировка
§ U – блокировка обновления (Update)
§ X – монопольная (Exclusive) блокировка
§ Intent-блокировки (IS, IU, IX) – используются для создания иерархии блокировок.
§ BU – используется при массовой заливке данных в таблицу
§ Sch-S и Sch-M – блокировки схемы.
Из режимов и типов блокировок, приведенных выше, можно создавать комбинации. Так, например, можно создать блокировку таблицы (TAB) в режиме «Х», то есть эксклюзивном. Это значит, что процесс запрашивает или получает эксклюзивную блокировку таблицы. Естественно, удержание такой блокировки на существенное время может привести к проблемам с блокировками.
В SQL Server есть хранимая процедура sp_lock, предоставляющая массу полезной для DBA информации о количестве и типах блокировок, запрошенных процессом.
Примечание: В SQL Server 2005 и выше эквивалентом sp_lock является компонент SQL ServerDatabaseEngine- динамическое административное представление sys.dm_tran_locks.
На рисунке 9 показан результат выполнения sp_lock для SPID 51, Плохого Запроса.
Рисунок 9. Блокировки Плохого Запроса.
Здесь вы можете видеть, что наложено много блокировок, в основном эксклюзивных блокировок уровня строки, как показывают режим "X" и тип "RID". Когда один SPID, накладывающий такое количество блокировок- что-то точно идет не так, как должно.
Часто простого подсчета блокировок и, что важнее, типов блокировок от отдельного SPID, достаточно, чтобы помочь обнаружить плохо выполняющийся запрос, даже если нет явного блокирования работы. Запрос блокировок, так же как и запрос подключений, расходуют память, и даже разделяемые блокировки, которые могут и не блокировать доступ к данным, иногда могут иметь большое влияние на производительность из-за нагрузки на память и другие ресурсы.
Листинг 1. Блокировки в базе данных при уровне изоляции Зафиксированное чтение (CommittedRead)
Выходные данные процедуры sp_lock:
/Заголовки столбцов таблицы/
Spid - идентификатор процесса
Dbid - идентификатор базы данных
Objid - идентификатор объекта
Indid - индивидуальный идентификатор
Тип - тип ресурса (степень детализации)
Ресурс - идентификатор ресурса
Режим - режим блокирования
Статус - статус блокировки
Листинг 2. Блокировки ключей и разделяемые блокировки при уровне изоляции Повторяемое чтение (RepeatableRead)
USE PUBS SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT * FROM authors WHERE au_lname = `Ringer` EXEC sp_lock @@spid COMMIT TRANЛистинг 3. Блокировки диапазона ключа при уровне изоляции Упорядочиваемый (Serializable)
USE PUBS SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT * FROM authors WHERE au_lname = `Ringer` EXEC sp_lock @@spid COMMIT TRANЛистинг 4. Исключающие блокировки при уровне изоляции Зафиксированное чтение (ReadCommitted)
USE PUBS SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN UPDATE authors SET contract = 0 WHERE au_lname = `Ringer` EXEC sp_lock @@spid COMMIT TRANЛистинг 5. Блокировки строк при уровне изоляции Зафиксированное чтение (ReadCommitted)
SQL Batch: USE PUBS SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN updatenewTitles SET price = 3.99 WHERE type = `business` EXEC sp_lock @@spid ROLLBACK TRANУправление параллельностью
Определение. Управление параллельностью – это процесс организации одновременного выполнения в базе данных различных операций, гарантирующий исключение их взаимного влияния друг на друга.
Важнейшей целью создания баз данных является организация параллельного доступа многих пользователей к общим данным, используемым ими совместно. Обеспечить параллельный доступ относительно несложно, если все пользователи будут только читать данные, помещенные в базу. В этом случае работа каждого из них не оказывает никакого влияния на работу остальных пользователей. Однако, если два и более пользователей одновременно обращаются к базе данных и хотя бы один из них обновляет хранимую в базе информацию, возможно взаимное влияние процессов друг на друга, способное привести к несогласованности данных.
Данная задача аналогична задачам, стоящим перед любой многопользовательской компьютерной системой. В этом случае несколько пользователей получают возможность одновременно выполнять операции благодаря использованию концепции мультипрограммирования, позволяющей двум и более программам (или транзакциям) выполняться в одно и то же время. Например, многи<