Вопрос 16: Управление транзакциями и блокировками. Базовые понятия. Уровни изоляции транзакций. Типы блокировок. Взаимные блокировки.

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

В Microsoft SQL Server поддерживаются два вида транзакций:

  • Явные
  • Неявные

В неявной транзакции (implicit transaction) каждая операция (например, INSERT DELETE, UPDATE) выполняется как транзакция. Явная транзакция (explicit transaction) объединяет несколько команд, находящихся между предложениями BEGIN TRANSACTION и COMMIT TRANSACTION.

Требования к выполнению транзакций системами управления БД (СУБД) называются требованиями ACID (Atomicity, Consistency, Isolation и Durability). Эти требования описывают, как должны обрабатываться данные и в каком состоянии они должны находиться после выполнения транзакции.

Требования к выполнению транзакций

Требования к выполнению транзакций системами управления БД (СУБД) называются требованиями ACID (Atomicity, Consistency, Isolation и Durability). Эти требования описывают, как должны обрабатываться данные и в каком состоянии они должны находиться после выполнения транзакции.

  • Атомарность (Atomicity). Все изменения данных, выполненные транзакции, рассматриваются как единый минимальный блок. Фиксируются либо все изменения, выполненные в транзакции, либо данные восстанавливаются в том состоянии, в котором они были до выполнения транзакции.
  • Согласованность (Consistency). После успешного завершения транзакции, данные должны удовлетворять всем ограничениям целостности, определенным в базе данных. Обычно транзакция состоит из нескольких команд, которые последовательно могут менять данные. При выполнении изменений могут быть нарушены ограничения целостности (constraints), наложенные на данные. Однако необходимо гарантировать, что к моменту фиксирования транзакции целостность данных не будет нарушена.
  • Изолированность (Isolation). Изменения данных, выполняемые разными транзакциями должны быть независимы друг от друга. То есть операции изменения данных, выполняемые одной транзакцией, не должны зависеть от изменений, выполняемых другой транзакцией. Транзакция не должна производить изменения, основываясь на данных, изменяемых в другой транзакции. Все транзакции должны работать независимо друг от друга, то есть быть изолированными. В противном случае нельзя предсказать,в каком состоянии могут оказаться данные. Если транзакция выбирает строки по определенному логическому условию, то никакая другая транзакция не должна изменять, добавлять или удалять строки, которые соответствуют указанному логическому условию. Такое поведение известно как «упорядоченность» или «сериализуемость» (serializability).
  • Устойчивость или долговечность (Durability). После того, как транзакция выполнит все необходимые изменения, и ее работа будет завершена, система выполнит фиксирование транзакции (COMMIT TRANSACTION). После этого система не может быть возвращена в состояние, в котором она была до начала транзакции. Даже если сразу после фиксирования транзакции произойдет неожиданный крах или останов системы, то при следующем запуске произойдет повторное фиксирование транзакции, и данные будут находиться в состоянии, в котором они должны находиться после завершения транзакции.

Управления транзакциями

    BEGIN TRAN[SACTION] [transaction_name | @tran_name_variable [WITH
    MARK [‘description’]]]

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

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

Уровень изоляции READ UNCOMMITTED.

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

Из четырех проблем одновременного конкурентного доступа к данным, описанных в предшествующем разделе, уровень изоляции READ UNCOMMITTED допускает три: грязное чтение, неповторяемое чтение и фантомы.

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

Уровень изоляции READ COMMITTED

Как уже упоминалось, уровень READ COMMITTED имеет две формы. Первая форма применяется в пессимистической модели одновременного конкурентного доступа, а вторая - в оптимистической. В этом разделе рассматривается первая форма этого уровня изоляции.

Транзакция, которая читает строку и использует уровень изоляции READ COMMITTED, выполнят проверку только на наличие монопольной блокировки для данной строки. Если такая блокировка отсутствует, транзакция извлекает строку. (Это выполняется с использованием разделяемой блокировки.) Таким образом предотвращается чтение транзакцией данных, которые не были подтверждены и которые могут быть позже отменены. После того, как данные были прочитаны, их можно изменять другими транзакциями.

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

Уровень изоляции READ COMMITTED для компонента Database Engine является уровнем изоляции по умолчанию.

Уровень изоляции REPEATABLE READ

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

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

Уровень изоляции SERIALIZABLE

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

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

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

Уровень изоляции можно установить, используя следующую команду

    SET TRANSACTION ISOLATION LEVEL
        { READ UNCOMMITTED
        | READ COMMITTED
        | REPEATABLE READ
        | SNAPSHOT
        | SERIALIZABLE
        }

Изменение уровня изоляции

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

  • SET TRANSACTION - Начинает транзакцию и определяет ее поведение.

  • COMMIT - Сохраняет изменения, внесенные транзакцией, в базе данных и завершает транзакцию.

  • SAVEPOINT - создает точки сохранения в группах транзакций.

  • ROLLBACK - Отменяет изменения, внесенные транзакцией, и завершает транзакцию.

В общем виде, синтаксис команды SQL для запуска транзакции:

    SET TRANSACTION [Access mode] [Lock Resolution]
    [Isolation Level] [Table Reservation]

Access Mode - определяет тип доступа к данным. Может принимать два значения:

  • READ ONLY - указывает, что транзакция может только читать данные и не может модифицировать их.

  • READ WRITE - указывает, что транзакция может читать и модифицировать данные. Это значение принимается по умолчанию.

Пример:

    SET TRANSACTION READ WRITE

Isolation Level - определяет порядок взаимодействия данной транзакции с другими в данной базе. Может принимать значения:

  • SNAPSHOT - значение по умолчанию. Внутри транзакции будут доступны данные в том состоянии, в котором они находились на момент начала транзакции. Если по ходу дела в базе данных появились изменения, внесенные другими завершенными транзакциями, то данная транзакция их не увидит. При попытке модифицировать такие записи возникнет сообщение о конфликте.
  • SNAPSHOT TABLE STABILITY - предоставляет транзакции исключительный доступ к таблицам, которые она использует. Другие транзакции смогут только читать данные из них.
  • READ COMMITTED - позволяет транзакции видеть текущее состояние базы.

Конфликты, связанные с блокировкой записей происходят в двух случаях:

  1. Транзакция пытается модифицировать запись, которая была изменена или удалена уже после ее старта. Транзакция типа READ COMMITTED может вносить изменения в записи, модифицированные другими транзакциями после их завершения.
  2. Транзакция пытается модифицировать таблицу, которая заблокирована другой транзакцией типа SNAPSHOT TABLE STABILITY.

Lock Resolution - определяет ход событий при обнаружении конфликта блокировки. Может принимать два значения:

  • WAIT - значение по умолчанию. Ожидает разблокировки требуемой записи. После этого пытается продолжить работу.
  • NO WAIT - немедленно возвращает ошибку блокировки записи.

Table Reservation - позволяет транзакции получить гарантированный доступ необходимого уровня к указанным таблицам. Существует четыре уровня доступа:

  1. PROTECTED READ - запрещает обновление таблицы другими транзакциями, но позволяет им выбирать данные из таблицы.
  2. PROTECTED WRITE - запрещает обновление таблицы другими транзакциями, читать данные из таблицы могут только транзакции типа SNAPSHOT или READ COMMITTED.
  3. SHARED READ - самый либеральный уровень. Читать могут все, модифицировать - транзакции READ WRITE.
  4. SHARED WRITE - транзакции SNAPSHOT или READ COMMITTED READ WRITE могут модифицировать таблицу, остальные - только выбирать данные.

Команда COMMIT — это транзакционная команда, используемая для сохранения изменений внесенных транзакцией в базу данных. Команда COMMIT сохраняет все транзакции в базе данных с момента выполнения последней команды COMMIT или ROLLBACK.

Ниже приведен пример, в котором из таблицы будут удалены клиенты с возрастом = 25, а затем эти изменения будут сохранены в базе данных.

    DELETE FROM CUSTOMERS
        WHERE AGE = 25;
    COMMIT;

Команда ROLLBACK — это транзакционная команда, используемая для отмены транзакций, которые еще не были сохранены в базе данных. Эта команда может использоваться только для отмены транзакций с момента выполнения последней команды COMMIT или ROLLBACK.

Ниже приведен пример, в котором из базы данных будут удалены все записи для которых возраст = 25, а затем эти изменения будут отменены.

    DELETE FROM CUSTOMERS
        WHERE AGE = 25;
    ROLLBACK;

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

    SAVEPOINT SAVEPOINT_NAME;

Эта команда предназначена только для создания SAVEPOINT в других транзакционных операторах. Команда ROLLBACK используется для отмены группы транзакций до точки SAVEPOINT.

Синтаксис, который используется для возврата к SAVEPOINT, показан ниже.

    ROLLBACK TO SAVEPOINT_NAME;

Команда RELEASE SAVEPOINT используется для удаления созданной точки SAVEPOINT. Синтаксис команды RELEASE SAVEPOINT следующий.

    RELEASE SAVEPOINT SAVEPOINT_NAME;

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

Блокировки

Блокировкой называется временное ограничение, накладываемое системой на использование тех или иных ресурсов. Блокировки служат для обеспечения изолированности транзакций друг от друга. Простейшим способом обеспечения изолированности транзакций было бы запрещение на любое обращение к данным, используемым другой транзакцией. По возможности система старается применить как можно менее жесткий режим блокирования. Для управления блокировками в MS SQL Server 2000 используется диспетчер блокировок. Блокировки могут накладываться на отдельную строку таблицы, на страницу, на всю таблицу целиком.

Ресурс Описание
RID Row identifier
Key Row lock within an index
Page Data page or index page
Extent Group of pages
Table Entire table
Database Entire database

Типы блокировок

  • Коллективная блокировка Shared (S) - наименее жесткий режим блокировки. Обычно применяется при выполнении операции чтения. Коллективная блокировка позволяет множеству транзакций читать одни и те же данные. При этом система гарантирует, что если транзакция установила коллективную блокировку, то ни одна другая транзакция не может изменить эти данные, то есть решается проблема неповторяемого чтения.
  • Блокировки обновления Update (U) используется при изменении данных. Блокировка обновления может быть установлена непосредственно или как результат повышения коллективной блокировки. Блокировка обновления рассматривается как подготовительный этап к непосредственному изменению данных. После установки блокировки обновления на данные запрещается накладывать коллективные блокировки и другие блокировки обновления. При установке блокировки обновления не снимаются уже установленные коллективные блокировки, а лишь ожидается их снятие. Когда все коллективные блокировки будут сняты, блокировка обновления может быть повышена до монопольной блокировки.
  • Монопольная блокировка Exclusive (X) используется при изменении данных. Установка этого типа блокировки возможна только в том случае, если на необходимые ресурсы не установлена никакая другая блокировка. Монопольная блокировка может быть установлена непосредственно или в результате повышения блокировки обновления. Если на ресурс установлена монопольная блокировка, то ни одна транзакция не сможет обратиться к данным. Это позволяет решить проблему грязного чтения.
  • Блокировка массивного обновления Bulk Update (BU) устанавливается при выполнении операций массивной вставки данных в таблицу с указанием хинта TABLOCK. Эта блокировка автоматически устанавливается для таблицы, если с помощью хранимой процедуры sp_tableoption установлен параметр table lock on bulk load. Блокировка массивного обновления запрещает обращение к таблице любым процессам, не участвующим в операциях массивной вставки данных.
  • Блокировка схемы Schema (Shm) используется при выполнении команд модификации объектов баз данных (DDL). Блокировка схемы предназначена для блокирования метаданных объектов БД. В Microsoft SQL Server 2000 реализованы два типа блокировок схемы: блокировка стабильности схемы Stability Lock (Sch-S) и блокировка изменения схемы Modification Lock (Sch-M).

Специальные виды блокировок

  • Блокировки намерений (Intent) обеспечивают пользователей дополнительными возможностями для управления ресурсами. Они предназначены для блокирования ресурсов вниз по иерархии объекта. Блокировки намерений позволяют значительно снизить затраты на установку, снятие и проверку блокировок. Существует несколько разновидностей блокировки намерений.
  • Коллективная блокировка намерений (Intent Shared - IS) используется в случае, когда предполагается читать данные вниз по иерархии объектов.
  • Монопольная блокировка намерений (Intent Exclusive - IE) предназначена для блокирования объектов, в которых предполагается выполнять множество изменений.
  • Коллективно-монопольная блокировка намерений (Shared with Intent eXxclusive - SIX) применяется, когда транзакция выполняет чтение большей части данных и частично изменяет их.

Взаимные блокировки

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

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

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

results matching ""

    No results matching ""