Sql уровни изоляции транзакций. Поведение при различных уровнях изолированности

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

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

Проблемы одновременного конкурентного доступа

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

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

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

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

Компонент Database Engine и уровни изоляции

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

READ UNCOMMITTED

Уровни изоляции READ UNCOMMITTED, REPEATABLE READ и SERIALIZABLE доступны только в пессимистической модели одновременного конкурентного доступа, тогда как уровень SNAPSHOT доступен только в оптимистической модели одновременного конкурентного доступа. Уровень изоляции READ COMMITTED доступен в обеих моделях. Далее рассмотрены четыре уровня изоляции, которые доступны только в пессимистической модели, а уровень SNAPSHOT описан в следующей статье.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Установка и редактирование уровня изоляции

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

параметр TRANSACTION ISOLATION LEVEL инструкции SET;

подсказки уровня изоляции.

Параметру TRANSACTION ISOLATION LEVEL можно присвоить пять постоянных значений, которые имеют такие же имена и смысл, как и только что рассмотренные уровни изоляции. Предложение FROM инструкции SELECT также поддерживает эти подсказки уровней изоляции. Задание уровня изоляции в предложении FROM инструкции SELECT перекрывает текущее его значение, установленное инструкцией SET TRANSACTION ISOLATION LEVEL.

Инструкция DBCC USEROPTIONS возвращает информацию о текущих значениях параметров инструкции SET, включая значение уровня изоляции, которое возвращается в параметре ISOLATION LEVEL.

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

Проблемы параллельного доступа с использованием транзакций

Транзакция 1	Транзакция 2
UPDATE tbl1 SET f2=f2+1 WHERE f1=1;
COMMIT;
	SELECT f2 FROM tbl1 WHERE f1=1;

В транзакции 2 выбирается значение поля f2, затем в транзакции 1 изменяется значение поля f2. При повторной попытке выбора значения из поля f2 в транзакции 2 будет получен другой результат. Эта ситуация особенно неприемлема, когда данные считываются с целью их частичного изменения и обратной записи в базу данных.

Фантомное чтение

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

Предположим, имеется две транзакции, открытые различными приложениями, в которых выполнены следующие SQL-операторы:

Транзакция 1	Транзакция 2
	SELECT SUM(f2) FROM tbl1;
INSERT INTO tbl1 (f1,f2) VALUES (15,20);
COMMIT;
	SELECT SUM(f2) FROM tbl1;

В транзакции 2 выполняется SQL-оператор, использующий все значения поля f2. Затем в транзакции 1 выполняется вставка новой строки, приводящая к тому, что повторное выполнение SQL-оператора в транзакции 2 выдаст другой результат. Такая ситуация называется фантомным чтением. От неповторяющегося чтения оно отличается тем, что результат повторного обращения к данным изменился не из-за изменения/удаления самих этих данных, а из-за появления новых (фантомных) данных.

Уровни изоляции

Под «уровнем изоляции транзакций » понимается степень обеспечиваемой внутренними механизмами СУБД (то есть не требующей специального программирования) защиты от всех или некоторых видов вышеперечисленных несогласованностей данных, возникающих при параллельном выполнении транзакций. Стандарт SQL-92 определяет шкалу из четырёх уровней изоляции: Read uncommitted, Read committed, Repeatable read, Serializable. Первый из них является самым слабым, последний - самым сильным, каждый последующий включает в себя все предыдущие.

Read uncommitted (чтение незафиксированных данных)

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

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

Read committed (чтение фиксированных данных)

Большинство промышленных СУБД, в частности, Microsoft SQL Server , PostgreSQL и Oracle , по умолчанию используют именно этот уровень. На этом уровне обеспечивается защита от чернового, «грязного» чтения, тем не менее, в процессе работы одной транзакции другая может быть успешно завершена и сделанные ею изменения зафиксированы. В итоге первая транзакция будет работать с другим набором данных.

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

Блокирование читаемых и изменяемых данных. Заключается в том, что читающая транзакция блокирует читаемые данные в разделяемом (shared) режиме, в результате чего параллельная транзакция, пытающаяся изменить эти данные, приостанавливается, а пишущая транзакция блокирует изменяемые данные для читающих транзакций, работающих на уровне read committed или более высоком, до своего завершения, препятствуя, таким образом, «грязному» чтению. Сохранение нескольких версий параллельно изменяемых строк. При каждом изменении строки СУБД создаёт новую версию этой строки, с которой продолжает работать изменившая данные транзакция, в то время как любой другой «читающей» транзакции возвращается последняя зафиксированная версия. Преимущество такого подхода в том, что он обеспечивает бо́льшую скорость, так как предотвращает блокировки. Однако он требует, по сравнению с первым, существенно бо́льшего расхода оперативной памяти, которая тратится на хранение версий строк. Кроме того, при параллельном изменении данных несколькими транзакциями может создаться ситуация, когда несколько параллельных транзакций произведут несогласованные изменения одних и тех же данных (поскольку блокировки отсутствуют, ничто не помешает это сделать). Тогда та транзакция, которая зафиксируется первой, сохранит свои изменения в основной БД, а остальные параллельные транзакции окажется невозможно зафиксировать (так как это приведёт к потере обновления первой транзакции). Единственное, что может в такой ситуации СУБД - это откатить остальные транзакции и выдать сообщение об ошибке «Запись уже изменена».

Конкретный способ реализации выбирается разработчиками СУБД, а в ряде случае может настраиваться. Так, по умолчанию MS SQL использует блокировки, но (в версии 2005 и выше) при установке параметра READ_COMMITTED_SNAPSHOT базы данных переходит на стратегию версионности, Oracle исходно работает только по версионной схеме. В Informix можно предотвратить конфликты между читающими и пишущими транзакциями, установив параметр конфигурации USELASTCOMMITTED (начиная с версии 11.1), при этом читающая транзакция будет получать последние подтвержденные данные

Repeatable read (повторяемость чтения)

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

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

Serializable (упорядочиваемость)

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

Поддержка изоляции транзакций в реальных СУБД

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

Так, Oracle в принципе не поддерживает нулевой уровень, так как его реализация транзакций исключает «грязные чтения», и формально не позволяет устанавливать уровень Repeatable read, то есть поддерживает только Read committed (по умолчанию) и Serializable. При этом на уровне отдельных команд он, фактически, гарантирует повторяемость чтения (если команда SELECT в первой транзакции выбирает из базы набор строк, и в это время параллельная вторая транзакция изменяет какие-то из этих строк, то результирующий набор, полученный первой транзакцией, будет содержать неизменённые строки, как будто второй транзакции не было). Также Oracle поддерживает так называемые READ-ONLY транзакции, которые соответствуют Serializable, но при этом не могут сами изменять данные.

Microsoft SQL Server поддерживает все четыре стандартных уровня изоляции транзакций, а дополнительно - уровень SNAPSHOT, находящийся между Repeatable read и Serialized. Транзакция, работающая на данном уровне, видит только те изменения данных, которые были зафиксированы до её запуска, а также изменения, внесённые ею самой, то есть ведёт себя так, как будто получила при запуске моментальный снимок данных БД и работает с ним.

Поведение при различных уровнях изолированности

«+» - предотвращает, «-» - не предотвращает.

Напишите отзыв о статье "Уровень изолированности транзакций"

Примечания

Отрывок, характеризующий Уровень изолированности транзакций

– Андрея Николаевича? мы мимо проедем, я вас проведу к нему.
– Что ж левый фланг? – спросил Пьер.
– По правде вам сказать, entre nous, [между нами,] левый фланг наш бог знает в каком положении, – сказал Борис, доверчиво понижая голос, – граф Бенигсен совсем не то предполагал. Он предполагал укрепить вон тот курган, совсем не так… но, – Борис пожал плечами. – Светлейший не захотел, или ему наговорили. Ведь… – И Борис не договорил, потому что в это время к Пьеру подошел Кайсаров, адъютант Кутузова. – А! Паисий Сергеич, – сказал Борис, с свободной улыбкой обращаясь к Кайсарову, – А я вот стараюсь объяснить графу позицию. Удивительно, как мог светлейший так верно угадать замыслы французов!
– Вы про левый фланг? – сказал Кайсаров.
– Да, да, именно. Левый фланг наш теперь очень, очень силен.
Несмотря на то, что Кутузов выгонял всех лишних из штаба, Борис после перемен, произведенных Кутузовым, сумел удержаться при главной квартире. Борис пристроился к графу Бенигсену. Граф Бенигсен, как и все люди, при которых находился Борис, считал молодого князя Друбецкого неоцененным человеком.
В начальствовании армией были две резкие, определенные партии: партия Кутузова и партия Бенигсена, начальника штаба. Борис находился при этой последней партии, и никто так, как он, не умел, воздавая раболепное уважение Кутузову, давать чувствовать, что старик плох и что все дело ведется Бенигсеном. Теперь наступила решительная минута сражения, которая должна была или уничтожить Кутузова и передать власть Бенигсену, или, ежели бы даже Кутузов выиграл сражение, дать почувствовать, что все сделано Бенигсеном. Во всяком случае, за завтрашний день должны были быть розданы большие награды и выдвинуты вперед новые люди. И вследствие этого Борис находился в раздраженном оживлении весь этот день.
За Кайсаровым к Пьеру еще подошли другие из его знакомых, и он не успевал отвечать на расспросы о Москве, которыми они засыпали его, и не успевал выслушивать рассказов, которые ему делали. На всех лицах выражались оживление и тревога. Но Пьеру казалось, что причина возбуждения, выражавшегося на некоторых из этих лиц, лежала больше в вопросах личного успеха, и у него не выходило из головы то другое выражение возбуждения, которое он видел на других лицах и которое говорило о вопросах не личных, а общих, вопросах жизни и смерти. Кутузов заметил фигуру Пьера и группу, собравшуюся около него.
– Позовите его ко мне, – сказал Кутузов. Адъютант передал желание светлейшего, и Пьер направился к скамейке. Но еще прежде него к Кутузову подошел рядовой ополченец. Это был Долохов.
– Этот как тут? – спросил Пьер.
– Это такая бестия, везде пролезет! – отвечали Пьеру. – Ведь он разжалован. Теперь ему выскочить надо. Какие то проекты подавал и в цепь неприятельскую ночью лазил… но молодец!..
Пьер, сняв шляпу, почтительно наклонился перед Кутузовым.
– Я решил, что, ежели я доложу вашей светлости, вы можете прогнать меня или сказать, что вам известно то, что я докладываю, и тогда меня не убудет… – говорил Долохов.
– Так, так.
– А ежели я прав, то я принесу пользу отечеству, для которого я готов умереть.
– Так… так…
– И ежели вашей светлости понадобится человек, который бы не жалел своей шкуры, то извольте вспомнить обо мне… Может быть, я пригожусь вашей светлости.
– Так… так… – повторил Кутузов, смеющимся, суживающимся глазом глядя на Пьера.
В это время Борис, с своей придворной ловкостью, выдвинулся рядом с Пьером в близость начальства и с самым естественным видом и не громко, как бы продолжая начатый разговор, сказал Пьеру:
– Ополченцы – те прямо надели чистые, белые рубахи, чтобы приготовиться к смерти. Какое геройство, граф!
Борис сказал это Пьеру, очевидно, для того, чтобы быть услышанным светлейшим. Он знал, что Кутузов обратит внимание на эти слова, и действительно светлейший обратился к нему:
– Ты что говоришь про ополченье? – сказал он Борису.
– Они, ваша светлость, готовясь к завтрашнему дню, к смерти, надели белые рубахи.
– А!.. Чудесный, бесподобный народ! – сказал Кутузов и, закрыв глаза, покачал головой. – Бесподобный народ! – повторил он со вздохом.
– Хотите пороху понюхать? – сказал он Пьеру. – Да, приятный запах. Имею честь быть обожателем супруги вашей, здорова она? Мой привал к вашим услугам. – И, как это часто бывает с старыми людьми, Кутузов стал рассеянно оглядываться, как будто забыв все, что ему нужно было сказать или сделать.
Очевидно, вспомнив то, что он искал, он подманил к себе Андрея Сергеича Кайсарова, брата своего адъютанта.
– Как, как, как стихи то Марина, как стихи, как? Что на Геракова написал: «Будешь в корпусе учитель… Скажи, скажи, – заговорил Кутузов, очевидно, собираясь посмеяться. Кайсаров прочел… Кутузов, улыбаясь, кивал головой в такт стихов.
Когда Пьер отошел от Кутузова, Долохов, подвинувшись к нему, взял его за руку.
– Очень рад встретить вас здесь, граф, – сказал он ему громко и не стесняясь присутствием посторонних, с особенной решительностью и торжественностью. – Накануне дня, в который бог знает кому из нас суждено остаться в живых, я рад случаю сказать вам, что я жалею о тех недоразумениях, которые были между нами, и желал бы, чтобы вы не имели против меня ничего. Прошу вас простить меня.
Пьер, улыбаясь, глядел на Долохова, не зная, что сказать ему. Долохов со слезами, выступившими ему на глаза, обнял и поцеловал Пьера.
Борис что то сказал своему генералу, и граф Бенигсен обратился к Пьеру и предложил ехать с собою вместе по линии.
– Вам это будет интересно, – сказал он.
– Да, очень интересно, – сказал Пьер.
Через полчаса Кутузов уехал в Татаринову, и Бенигсен со свитой, в числе которой был и Пьер, поехал по линии.

Бенигсен от Горок спустился по большой дороге к мосту, на который Пьеру указывал офицер с кургана как на центр позиции и у которого на берегу лежали ряды скошенной, пахнувшей сеном травы. Через мост они проехали в село Бородино, оттуда повернули влево и мимо огромного количества войск и пушек выехали к высокому кургану, на котором копали землю ополченцы. Это был редут, еще не имевший названия, потом получивший название редута Раевского, или курганной батареи.
Пьер не обратил особенного внимания на этот редут. Он не знал, что это место будет для него памятнее всех мест Бородинского поля. Потом они поехали через овраг к Семеновскому, в котором солдаты растаскивали последние бревна изб и овинов. Потом под гору и на гору они проехали вперед через поломанную, выбитую, как градом, рожь, по вновь проложенной артиллерией по колчам пашни дороге на флеши [род укрепления. (Примеч. Л.Н. Толстого.) ], тоже тогда еще копаемые.
Бенигсен остановился на флешах и стал смотреть вперед на (бывший еще вчера нашим) Шевардинский редут, на котором виднелось несколько всадников. Офицеры говорили, что там был Наполеон или Мюрат. И все жадно смотрели на эту кучку всадников. Пьер тоже смотрел туда, стараясь угадать, который из этих чуть видневшихся людей был Наполеон. Наконец всадники съехали с кургана и скрылись.
Бенигсен обратился к подошедшему к нему генералу и стал пояснять все положение наших войск. Пьер слушал слова Бенигсена, напрягая все свои умственные силы к тому, чтоб понять сущность предстоящего сражения, но с огорчением чувствовал, что умственные способности его для этого были недостаточны. Он ничего не понимал. Бенигсен перестал говорить, и заметив фигуру прислушивавшегося Пьера, сказал вдруг, обращаясь к нему:
– Вам, я думаю, неинтересно?
– Ах, напротив, очень интересно, – повторил Пьер не совсем правдиво.
С флеш они поехали еще левее дорогою, вьющеюся по частому, невысокому березовому лесу. В середине этого
леса выскочил перед ними на дорогу коричневый с белыми ногами заяц и, испуганный топотом большого количества лошадей, так растерялся, что долго прыгал по дороге впереди их, возбуждая общее внимание и смех, и, только когда в несколько голосов крикнули на него, бросился в сторону и скрылся в чаще. Проехав версты две по лесу, они выехали на поляну, на которой стояли войска корпуса Тучкова, долженствовавшего защищать левый фланг.
Здесь, на крайнем левом фланге, Бенигсен много и горячо говорил и сделал, как казалось Пьеру, важное в военном отношении распоряжение. Впереди расположения войск Тучкова находилось возвышение. Это возвышение не было занято войсками. Бенигсен громко критиковал эту ошибку, говоря, что было безумно оставить незанятою командующую местностью высоту и поставить войска под нею. Некоторые генералы выражали то же мнение. Один в особенности с воинской горячностью говорил о том, что их поставили тут на убой. Бенигсен приказал своим именем передвинуть войска на высоту.
Распоряжение это на левом фланге еще более заставило Пьера усумниться в его способности понять военное дело. Слушая Бенигсена и генералов, осуждавших положение войск под горою, Пьер вполне понимал их и разделял их мнение; но именно вследствие этого он не мог понять, каким образом мог тот, кто поставил их тут под горою, сделать такую очевидную и грубую ошибку.
Пьер не знал того, что войска эти были поставлены не для защиты позиции, как думал Бенигсен, а были поставлены в скрытое место для засады, то есть для того, чтобы быть незамеченными и вдруг ударить на подвигавшегося неприятеля. Бенигсен не знал этого и передвинул войска вперед по особенным соображениям, не сказав об этом главнокомандующему.

Князь Андрей в этот ясный августовский вечер 25 го числа лежал, облокотившись на руку, в разломанном сарае деревни Князькова, на краю расположения своего полка. В отверстие сломанной стены он смотрел на шедшую вдоль по забору полосу тридцатилетних берез с обрубленными нижними сучьями, на пашню с разбитыми на ней копнами овса и на кустарник, по которому виднелись дымы костров – солдатских кухонь.
Как ни тесна и никому не нужна и ни тяжка теперь казалась князю Андрею его жизнь, он так же, как и семь лет тому назад в Аустерлице накануне сражения, чувствовал себя взволнованным и раздраженным.
Приказания на завтрашнее сражение были отданы и получены им. Делать ему было больше нечего. Но мысли самые простые, ясные и потому страшные мысли не оставляли его в покое. Он знал, что завтрашнее сражение должно было быть самое страшное изо всех тех, в которых он участвовал, и возможность смерти в первый раз в его жизни, без всякого отношения к житейскому, без соображений о том, как она подействует на других, а только по отношению к нему самому, к его душе, с живостью, почти с достоверностью, просто и ужасно, представилась ему. И с высоты этого представления все, что прежде мучило и занимало его, вдруг осветилось холодным белым светом, без теней, без перспективы, без различия очертаний. Вся жизнь представилась ему волшебным фонарем, в который он долго смотрел сквозь стекло и при искусственном освещении. Теперь он увидал вдруг, без стекла, при ярком дневном свете, эти дурно намалеванные картины. «Да, да, вот они те волновавшие и восхищавшие и мучившие меня ложные образы, – говорил он себе, перебирая в своем воображении главные картины своего волшебного фонаря жизни, глядя теперь на них при этом холодном белом свете дня – ясной мысли о смерти. – Вот они, эти грубо намалеванные фигуры, которые представлялись чем то прекрасным и таинственным. Слава, общественное благо, любовь к женщине, самое отечество – как велики казались мне эти картины, какого глубокого смысла казались они исполненными! И все это так просто, бледно и грубо при холодном белом свете того утра, которое, я чувствую, поднимается для меня». Три главные горя его жизни в особенности останавливали его внимание. Его любовь к женщине, смерть его отца и французское нашествие, захватившее половину России. «Любовь!.. Эта девочка, мне казавшаяся преисполненною таинственных сил. Как же я любил ее! я делал поэтические планы о любви, о счастии с нею. О милый мальчик! – с злостью вслух проговорил он. – Как же! я верил в какую то идеальную любовь, которая должна была мне сохранить ее верность за целый год моего отсутствия! Как нежный голубок басни, она должна была зачахнуть в разлуке со мной. А все это гораздо проще… Все это ужасно просто, гадко!

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

Стандарт описывает следующие особые условия, недопустимые для различных уровней изоляции:

«грязное» чтение

Транзакция читает данные, записанные параллельной незавершённой транзакцией. неповторяемое чтение

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

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

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

Таблица 13.1. Уровни изоляции транзакций

Уровень изоляции	«Грязное» чтение	Неповторяемое чтение	Фантомное чтение	Аномалия сериализации
Read uncommited (Чтение незафиксированных данных)	Допускается, но не в PG	Возможно	Возможно	Возможно
Read committed (Чтение зафиксированных данных)	Невозможно	Возможно	Возможно	Возможно
Repeatable read (Повторяемое чтение)	Невозможно	Невозможно	Допускается, но не в PG	Возможно
Serializable (Сериализуемость)	Невозможно	Невозможно	Невозможно	Невозможно

В Postgres Pro вы можете запросить любой из четырёх уровней изоляции транзакций, однако внутри реализованы только три различных уровня, то есть режим Read Uncommitted в Postgres Pro действует как Read Committed. Причина этого в том, что только так можно сопоставить стандартные уровни изоляции с реализованной в Postgres Pro архитектурой многоверсионного управления конкурентным доступом.

В этой таблице также показано, что реализация Repeatable Read в Postgres Pro не допускает фантомное чтение. Стандарт SQL допускает возможность более строгого поведения: четыре уровня изоляции определяют только, какие особые условия не должны наблюдаться, но не какие обязательно должны . Поведение имеющихся уровней изоляции подробно описывается в следующих подразделах.

Для выбора нужного уровня изоляции транзакций используется команда SET TRANSACTION .

Важно

Поведение некоторых функций и типов данных Postgres Pro в транзакциях подчиняется особым правилам. В частности, изменения последовательностей (и следовательно, счётчика в столбце, объявленному как serial) немедленно видны во всех остальных транзакциях и не откатываются назад, если выполнившая их транзакция прерывается. См. Раздел 9.16 и Подраздел 8.1.4 .

13.2.1. Уровень изоляции Read Committed

Read Committed - уровень изоляции транзакции, выбираемый в Postgres Pro по умолчанию. В транзакции, работающей на этом уровне, запрос SELECT (без предложения FOR UPDATE/SHARE) видит только те данные, которые были зафиксированы до начала запроса; он никогда не увидит незафиксированных данных или изменений, внесённых в процессе выполнения запроса параллельными транзакциями. По сути запрос SELECT видит снимок базы данных в момент начала выполнения запроса. Однако SELECT видит результаты изменений, внесённых ранее в этой же транзакции, даже если они ещё не зафиксированы. Также заметьте, что два последовательных оператора SELECT могут видеть разные данные даже в рамках одной транзакции, если какие-то другие транзакции зафиксируют изменения после запуска первого SELECT , но до запуска второго.

Команды UPDATE , DELETE , SELECT FOR UPDATE и SELECT FOR SHARE ведут себя подобно SELECT при поиске целевых строк: они найдут только те целевые строки, которые были зафиксированы на момент начала команды. Однако к моменту, когда они будут найдены, эти целевые строки могут быть уже изменены (а также удалены или заблокированы) другой параллельной транзакцией. В этом случае запланированное изменение будет отложено до фиксирования или отката первой изменяющей данные транзакции (если она ещё выполняется). Если первая изменяющая транзакция откатывается, её результат отбрасывается и вторая изменяющая транзакция может продолжить изменение изначально полученной строки. Если первая транзакция зафиксировалась, но в результате удалила эту строку, вторая будет игнорировать её, а в противном случае попытается выполнить свою операцию с изменённой версией строки. Условие поиска в команде (предложение WHERE) вычисляется повторно для выяснения, соответствует ли по-прежнему этому условию изменённая версия строки. Если да, вторая изменяющая транзакция продолжают свою работу с изменённой версией строки. Применительно к командам SELECT FOR UPDATE и SELECT FOR SHARE это означает, что изменённая версия строки блокируется и возвращается клиенту.

Похожим образом ведёт себя INSERT с предложением ON CONFLICT DO UPDATE . В режиме Read Committed каждая строка, предлагаемая для добавления, будет либо вставлена, либо изменена. Если не возникнет несвязанных ошибок, гарантируется один из этих двух исходов. Если конфликт будет вызван другой транзакцией, результат которой ещё не видим для INSERT , предложение UPDATE подействует на эту строку, даже несмотря на то, что эта команда обычным образом может не видеть никакую версию этой строки.

При выполнении INSERT с предложением ON CONFLICT DO NOTHING строка может не добавиться в результате действия другой транзакции, эффект которой не виден в снимке команды INSERT . Это опять же имеет место только в режиме Read Committed.

Вследствие описанных выше правил, изменяющая команда может увидеть несогласованное состояние: она может видеть результаты параллельных команд, изменяющих те же строки, что пытается изменить она, но при этом она не видит результаты этих команд в других строках таблиц. Из-за этого поведения уровень Read Committed не подходит для команд со сложными условиями поиска; однако он вполне пригоден для простых случаев. Например, рассмотрим изменение баланса счёта в таких транзакциях:

BEGIN; UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534; COMMIT;

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

В более сложных ситуациях уровень Read Committed может приводить к нежелательным результатам. Например, рассмотрим команду DELETE , работающую со строками, которые параллельно добавляет и удаляет из множества, определённого её условием, другая команда. Например, предположим, что website - таблица из двух строк, в которых website.hits равны 9 и 10:

BEGIN; UPDATE website SET hits = hits + 1; -- выполняется параллельно: DELETE FROM website WHERE hits = 10; COMMIT;

Команда DELETE не сделает ничего, даже несмотря на то, что строка с website.hits = 10 была в таблице и до, и после выполнения UPDATE . Это происходит потому, что строка со значением 9 до изменения пропускается, а когда команда UPDATE завершается и DELETE получает освободившуюся блокировку, строка с 10 теперь содержит 11 , а это значение уже не соответствует условию.

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

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

13.2.2. Уровень изоляции Repeatable Read

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

Этот уровень отличается от Read Committed тем, что запрос в транзакции данного уровня видит снимок данных на момент начала первого оператора в транзакции (не считая команд управления транзакциями), а не начала текущего оператора. Таким образом, последовательные команды SELECT в одной транзакции видят одни и те же данные; они не видят изменений, внесённых и зафиксированных другими транзакциями после начала их текущей транзакции.

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

Команды UPDATE , DELETE , SELECT FOR UPDATE и SELECT FOR SHARE ведут себя подобно SELECT при поиске целевых строк: они найдут только те целевые строки, которые были зафиксированы на момент начала транзакции. Однако к моменту, когда они будут найдены, эти целевые строки могут быть уже изменены (а также удалены или заблокированы) другой параллельной транзакцией. В этом случае транзакция в режиме Repeatable Read будет ожидать фиксирования или отката первой изменяющей данные транзакции (если она ещё выполняется). Если первая изменяющая транзакция откатывается, её результат отбрасывается и текущая транзакция может продолжить изменение изначально полученной строки. Если же первая транзакция зафиксировалась и в результате изменила или удалила эту строку, а не просто заблокировала её, произойдёт откат текущей транзакции с сообщением

ОШИБКА: не удалось сериализовать доступ из-за параллельного изменения

так как транзакция уровня Repeatable Read не может изменять или блокировать строки, изменённые другими транзакциями с момента её начала.

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

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

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

Примечание

До версии 9.1 в PostgreSQL при запросе режима Serializable поведение системы в точности соответствовало вышеописанному. Таким образом, чтобы сейчас получить старое поведение Serializable, нужно запрашивать режим Repeatable Read.

13.2.3. Уровень изоляции Serializable

Уровень Serializable обеспечивает самую строгую изоляцию транзакций. На этом уровне моделируется последовательное выполнение всех зафиксированных транзакций, как если бы транзакции выполнялись одна за другой, последовательно, а не параллельно. Однако, как и на уровне Repeatable Read, на этом уровне приложения должны быть готовы повторять транзакции из-за сбоев сериализации. Фактически этот режим изоляции работает так же, как и Repeatable Read, только он дополнительно отслеживает условия, при которых результат параллельно выполняемых сериализуемых транзакций может не согласовываться с результатом этих же транзакций, выполняемых по очереди. Это отслеживание не привносит дополнительных препятствий для выполнения, кроме тех, что присущи режиму Repeatable Read, но тем не менее создаёт некоторую добавочную нагрузку, а при выявлении исключительных условий регистрируется аномалия сериализации и происходит сбой сериализации .

Например, рассмотрим таблицу mytab , изначально содержащую:

Class | value -------+------- 1 | 10 1 | 20 2 | 100 2 | 200

Предположим, что сериализуемая транзакция A вычисляет:

SELECT SUM(value) FROM mytab WHERE class = 1;

а затем вставляет результат (30) в поле value в новую строку со значением class = 2 . В это же время сериализуемая транзакция B вычисляет:

SELECT SUM(value) FROM mytab WHERE class = 2;

получает результат 300 и вставляет его в новую строку со значением class = 1 . Затем обе транзакции пытаются зафиксироваться. Если бы одна из этих транзакций работала в режиме Repeatable Read, зафиксироваться могли бы обе; но так как полученный результат не соответствовал бы последовательному порядку, в режиме Serializable будет зафиксирована только одна транзакция, а вторая закончится откатом с сообщением:

ОШИБКА: не удалось сериализовать доступ из-за зависимостей чтения/записи между транзакциями

Это объясняется тем, что при выполнении A перед B транзакция B вычислила бы сумму 330, а не 300, а при выполнении в обратном порядке A вычислила бы другую сумму.

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

Для полной гарантии сериализуемости в Postgres Pro применяются предикатные блокировки , то есть блокировки, позволяющие определить, когда запись могла бы повлиять на результат предыдущего чтения параллельной транзакции, если бы эта запись выполнялась сначала. В Postgres Pro эти блокировки не приводят к фактическим блокировкам данным и следовательно никоим образом не могут повлечь взаимоблокировки транзакций. Они помогают выявить и отметить зависимости между параллельными транзакциями уровня Serializable, которые в определённых сочетаниях могут приводить к аномалиям сериализации. Транзакции Read Committed или Repeatable Read для обеспечения целостности данных, напротив, должны либо блокировать таблицы целиком, что помешает пользователям обращаться к этим таблицам, либо применять SELECT FOR UPDATE или SELECT FOR SHARE , что не только заблокирует другие транзакции, но и создаст дополнительную нагрузку на диск.

Предикатные блокировки в Postgres Pro , как и в большинстве других СУБД, устанавливаются для данных, фактически используемых в транзакции. Они отображаются в системном представлении pg_locks со значением mode равным SIReadLock . Какие именно блокировки будут затребованы при выполнении запроса, зависит от плана запроса, при этом детализированные блокировки (например, блокировки строк) могут объединяться в более общие (например, в блокировки страниц) в процессе транзакции для экономии памяти, расходуемой для отслеживания блокировок. Транзакция READ ONLY может даже освободить свои блокировки SIRead до завершения, если обнаруживается, что конфликты, которые могли бы привести к аномалии сериализации, исключены. На самом деле для транзакций READ ONLY этот факт чаще всего устанавливается в самом начале, так что они обходятся без предикатных блокировок. Если же вы явно запросите транзакцию SERIALIZABLE READ ONLY DEFERRABLE , она будет заблокирована до тех пор, пока не сможет установить этот факт. (Это единственный случай, когда транзакции уровня Serializable блокируются, а транзакции Repeatable Read - нет.) С другой стороны, блокировки SIRead часто должны сохраняться и после фиксирования транзакции, пока не будут завершены другие, наложившиеся на неё транзакции.

При правильном использовании сериализуемые транзакции могут значительно упростить разработку приложений. Гарантия того, что любое сочетание успешно зафиксированных параллельных сериализуемых транзакций даст тот же результат, что и последовательность этих транзакций, выполненных по очереди, означает, что если вы уверены, что единственная транзакция определённого содержания работает правильно, когда она запускается отдельно, вы можете быть уверены, что она будет работать так же правильно в любом сочетании сериализуемых транзакций, вне зависимости от того, что они делают, либо же она не будет зафиксирована успешно. При этом важно, чтобы в среде, где применяется этот подход, была реализована общая обработка сбоев сериализации (которые можно определить по значению SQLSTATE "40001"), так как заведомо определить, какие именно транзакции могут стать жертвами зависимостей чтения/записи и не будут зафиксированы для предотвращения аномалий сериализации, обычно очень сложно. Отслеживание зависимостей чтения-записи неизбежно создаёт дополнительную нагрузку, как и перезапуск транзакций, не зафиксированных из-за сбоев сериализации, но если на другую чашу весов положить нагрузку и блокирование, связанные с применением явных блокировок и SELECT FOR UPDATE или SELECT FOR SHARE , использовать сериализуемые транзакции в ряде случаев окажется выгоднее.

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

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

Объявляйте транзакции как READ ONLY , если это отражает их суть.

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

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

Не оставляйте соединения «простаивающими » в транзакции дольше, чем необходимо.

Исключите явные блокировки, SELECT FOR UPDATE и SELECT FOR SHARE там, где они не нужны благодаря защите, автоматически предоставляемой сериализуемыми транзакциями.

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

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

С приходом master-master репликаций остро встает вопрос о целостность с достоверностью базы данных.

• Целостность базы данных - соответствие имеющейся в базе данных информации её внутренней логике, структуре и всем явно заданным правилам.

• Достоверность (или истинность) - соответствие фактов, хранящихся в базе данных, реальному миру

При изменении данных, БД переходит от одного состояния к другому, при этом в процессе обновления данных возможны ситуации, когда состояние целостности или достоверности нарушается.

Например:

Прерванный перевод денег со счета на счет, посредством последовательного исполнения двух команд UPDATE, приведет к нарушению целостности:

Чтобы избежать подобные ситуации ввели понятие транзакции — атомарного действия над базой, переводящего ее из одного целостного состояния в другое. По сути это последовательность SQL-инструкций, которые должны быть выполнены целиком или отменены.

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

Одновременное чтение одним клиентом и запись другим клиентом одной и той же строки таблицы с большой вероятностью приведет к сбою или чтению некорректных данных. Механизмы блокировок позволяют избежать ситуаций одновременного доступа к данным, регламентируя порядок взаимодействия пользователей между собой. Способы реализации механизма блокировок в СУБД различных производителей могут существенно отличаться, однако суть примерно одинаковая:

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

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

• Если клиент хочет читать данные, то другие клиенты тоже могут читать данные, но никто не может записывать, пока первый клиент не закончит чтение (read lock ).
• Если клиент хочет записать данные, то другие клиенты не должны ни читать ни писать эти данные пока первый клиент не закончит (write lock ).

Блокировка может быть наложена явно или неявно .

Если клиент не назначает блокировку, MySQL сервер неявно устанавливает необходимый тип блокировки на время выполнения выражения или транзакции. В случае выполнения оператора SELECT сервер установит READ LOCK, а в случае UPDATE - WRITE LOCK. При неявной блокировке уровень блокировки зависит от типа хранилища данных: для MyISAM, MEMORY и MERGE блокируется вся таблица, для InnoDB - только используемые в выражении строки (в случае, если набор этих строк может быть однозначно определен - иначе, блокируется вся таблица).

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

Изоляция транзакций

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

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

1) Потерянное обновление (англ. lost update)

При одновременном изменении одного блока данных разными транзакциями, одно из изменений теряется;

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

Транзакция 1	Транзакция 2
UPDATE tbl1 SET f2=f2+20 WHERE f1=1;	UPDATE tbl1 SET f2=f2+25 WHERE f1=1;

В обеих транзакциях изменяется значение поля f2, при этом одно из изменений теряется. Так что, f2 будет увеличено не на 45, а только на 20 или 25.

Причина :

1. Первая транзакция прочитала текущее состояние поля.
2. Вторая транзакция сделала свои изменения, основываясь на своих сохраненных в память данных.
3. Первая делает обновление поля, используя свои «старые» данные.

2) «Грязное» чтение (англ. dirty read)

Чтение данных, добавленных или изменённых транзакцией, которая впоследствии не подтвердится (откатится);

Транзакция 1	Транзакция 2
	SELECT f2 FROM tbl1 WHERE f1=1;
ROLLBACK WORK;

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

3) Неповторяющееся чтение (англ. non-repeatable read)

При повторном чтении в рамках одной транзакции, ранее прочитанные данные оказываются изменёнными.

Предположим, имеются две транзакции, открытые различными приложениями, в которых выполнены следующие SQL-операторы:

Транзакция 1	Транзакция 2
SELECT f2 FROM tbl1 WHERE f1=1;	SELECT f2 FROM tbl1 WHERE f1=1;
UPDATE tbl1 SET f2=f2+1 WHERE f1=1;
COMMIT;
	SELECT f2 FROM tbl1 WHERE f1=1;

В транзакции 2 выбирается значение поля f2, затем в транзакции 1 изменяется значение поля f2. При повторной попытке выбора значения из поля f2 в транзакции 2 будет получен другой результат. Эта ситуация особенно неприемлема, когда данные считываются с целью их частичного изменения и обратной записи в базу данных.

4) Фантомное чтение (англ. phantom reads)

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

Транзакция 1	Транзакция 2
	SELECT SUM(f2) FROM tbl1;
INSERT INTO tbl1 (f1,f2) VALUES (15,20);
COMMIT;
	SELECT SUM(f2) FROM tbl1;

В транзакции 2 выполняется SQL-оператор, использующий все значения поля f2. Затем в транзакции 1 выполняется вставка новой строки, приводящая к тому, что повторное выполнение SQL-оператора в транзакции 2 выдаст другой результат. Такая ситуация называется фантомным чтением. От неповторяющегося чтения оно отличается тем, что результат повторного обращения к данным изменился не из-за изменения/удаления самих этих данных, а из-за появления новых (фантомных) данных.

Уровни изоляции

Serializable (упорядочиваемость)

Самый высокий уровень изолированности. Транзакции полностью изолируются друг от друга. Только на этом уровне параллельные транзакции не подвержены эффекту «фантомного чтения».

Repeatable read (повторяемость чтения)

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

Read committed (чтение фиксированных данных)

Завершенное чтение, при котором отсутствует черновое, «грязное» чтение (то есть чтение одним пользователем данных, которые не были зафиксированы в БД командой COMMIT). Тем не менее в процессе работы одной транзакции другая может быть успешно завершена и сделанные ею изменения зафиксированы. В итоге первая транзакция будет работать с другим набором данных. Это проблема неповторяемого чтения.

Read uncommitted (чтение незафиксированных данных)

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

Поведение при различных уровнях изолированности