УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ Транзакция - это последовательность операций над БД, рассматриваемых СУБД как единое целое. Либо транзакция успешно выполняется, - презентация

1 УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ Транзакция - это последовательность операций над БД, рассматриваемых СУБД как единое целое. Либо транзакция успешно выполняется, и СУБД фиксирует (COMMIT) изменения БД, произведенные этой транзакцией, во внешней памяти, либо ни одно из этих изменений никак не отражается на состоянии БД. Понятие транзакции необходимо для поддержания логической целостности БД. Если вспомнить наш пример информационной системы с файлами СОТРУДНИКИ и ОТДЕЛЫ, то единственным способом не нарушить целостность БД при выполнении операции приема на работу нового сотрудника является объединение элементарных операций над файлами СОТРУДНИКИ и ОТДЕЛЫ в одну транзакцию. Таким образом, поддержание механизма транзакций является обязательным условием даже однопользовательских СУБД (если, конечно, такая система заслуживает названия СУБД). Но понятие транзакции гораздо более важно в многопользовательских СУБД. То свойство, что каждая транзакция начинается при целостном состоянии БД и оставляет это состояние целостным после своего завершения, делает очень удобным использование понятия транзакции как единицы активности пользователя по отношению к БД. При соответствующем управлении параллельно выполняющимися транзакциями со стороны СУБД каждый из пользователей может в принципе ощущать себя единственным пользователем СУБД (на самом деле, это несколько идеализированное представление, поскольку в некоторых случаях пользователи многопользовательских СУБД могут ощутить присутствие своих коллег). С управлением транзакциями в многопользовательской СУБД связаны важные понятия сериализации транзакций и сериального плана выполнения смеси транзакций. Под сериализаций параллельно выполняющихся транзакций понимается такой порядок планирования их работы, при котором суммарный эффект смеси транзакций эквивалентен эффекту их некоторого последовательного выполнения. Сериальный план выполнения смеси транзакций - это такой план, который приводит к сериализации транзакций. Понятно, что если удается добиться действительно сериального выполнения смеси транзакций, то для каждого пользователя, по инициативе которого образована транзакция, присутствие других транзакций будет незаметно (если не считать некоторого замедления работы по сравнению с однопользовательским режимом). Существует несколько базовых алгоритмов сериализации транзакций. В централизованных СУБД наиболее распространены алгоритмы, основанные на синхронизационных захватах объектов БД. При использовании любого алгоритма сериализации возможны ситуации конфликтов между двумя или более транзакциями по доступу к объектам БД. В этом случае для поддержания сериализации необходимо выполнить откат (ликвидировать все изменения, произведенные в БД) одной или более транзакций. Это один из случаев, когда пользователь многопользовательской СУБД может реально (и достаточно неприятно) ощутить присутствие в системе транзакций других пользователей.

2 УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ u 1, c 1 результат обновления i 2 утрачен u 2, c 2 редактирование Отказ в доступе u 1, c 1 u 2, c 2 редактирование Блокировка L I снята ожидание редактирование Блокировка L 2 снята Блокировка L 2 Блокировка L I

3 УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ u 1, c 1 редактирование Блокировка L 2 редактирование u 2, c 2 Отказ в доступе ожидание Отказ в доступе Блокировка L I снята Блокировка L I u 1, c 1

4 ТРАНЗАКЦИЯ (ОПРЕДЕЛЕНИЕ) Транзакция группа последовательных операций, которая представляет из себя логическую единицу работы с данными. Транзакция может быть выполнена целиком либо успешно, соблюдая целостность данных и независимо от параллельно идущих других транзакций, либо не выполнена вообще и тогда она не должна произвести никакого эффекта.

5 ПРИМЕР ТРАНЗАКЦИИ Начать транзакциюНачать транзакцию прочесть баланс на счету номер 5прочесть баланс на счету номер 5 уменьшить баланс на 10 денежных единицуменьшить баланс на 10 денежных единиц сохранить новый баланс счёта номер 5сохранить новый баланс счёта номер 5 прочесть баланс на счету номер 7прочесть баланс на счету номер 7 увеличить баланс на 10 денежных единицувеличить баланс на 10 денежных единиц сохранить новый баланс счёта номер 7сохранить новый баланс счёта номер 7 Окончить транзакциюОкончить транзакцию

6 ИСПОЛЬЗОВАНИЕ В SQL SERVER begin transaction [transaction name] commit transaction [transaction name] rollback transaction [transaction name]

7 ПРИМЕР НЕОБХОДИМОСТИ ИЗОЛЯЦИИ ТРАНЗАКЦИЙ Процедура «продать товар» Проверить количество товара на складеПроверить количество товара на складе Если недостаточно то завершитьЕсли недостаточно то завершить Уменьшить количество товараУменьшить количество товара

8 УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ Уровни изоляции призваны обеспечить в СУБД правила параллелизма и последовательности работы с данными. Когда устанавливается уровень изоляции, множество пользователей, работающих с одними и теми же значениями данных в столбцах и строках таблицы, устанавливают блокировки или следуют основанным на установленном уровне изоляции правилам. Дадим краткий обзор существующих уровней изоляции транзакций в MS SQL Server 2000.

9 УРОВНИ ИЗОЛЯЦИИ ТРАНЗАКЦИИ 0 Неподтверждённое чтение (Read Uncommitted) 1 Подтверждённое чтение (Read Committed) 2 Повторяемое чтение (Repeatable Read) 3 Упорядоченный (Serializable)

10 УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ Когда установлен уровень Read Uncommited, пишущая транзакция не блокирует читающие, а читающие не блокируют запись. Таким образом, Вы имеете возможность составить запрос таким образом, что получите грязные данные, которые ещё не сохранены в базе данных, и этим будет нарушен принцип последовательности.

11 УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ Read Committed, изоляция устанавливается по умолчанию, и эта установка действует в рамках сеанса. Основной принцип состоит в том, что пишущая транзакция всегда блокирует читающие транзакции, если они имеют уровни изоляции выше её, исключая Read Uncommited.

12 УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ Когда установлен Read Committed, прочитать можно только сохранённые данные. Но как только читающая транзакция завершит процесс чтения данных, даже если сама транзакция к этому моменту ещё не завершена, её блокировка уже не будет препятствовать изменениям в этих данных.

13 УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ При использовании Repeatable Read, когда в одной транзакции читаются порции данных, одни и те же данные будут считываться каждый раз, когда происходит чтение в этой транзакции. Поэтому другие транзакции не смогут изменять данные, но они смогут осуществлять вставки новых данных в таблицу или в диапазоны данных, которые в этот момент не блокированы.

14 УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ Уровень Serializable идёт на шаг дальше по отношению Repeatable Read и защищает все другие блоки данных от вставок. Это называется предотвращением фантомных чтений.

15 УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ Уровни изоляции призваны обеспечить в СУБД правила параллелизма и последовательности работы с данными. Когда устанавливается уровень изоляции, множество пользователей, работающих с одними и теми же наборами данных (одни и те же значения данных в столбцах и строках таблицы), устанавливают блокировки или следуют основанным на установленном уровне изоляции правилам. По умолчанию, устанавливается изоляция Read Committed, и эта установка действует в рамках сеанса. Основной принцип состоит в том, что пишущая транзакция всегда блокирует читающие транзакции, если они имеют уровни изоляции выше её, исключая Read Uncommited. Когда установлен уровень Read Uncommited, пишущая транзакция не блокирует читающие, а читающие не блокируют запись. Таким образом, Вы имеете возможность составить запрос таким образом, что получите грязные данные, которые ещё не сохранены в базе данных, и этим будет нарушен принцип последовательности. Когда установлен Read Committed, прочитать можно только сохранённые данные. Но как только читающая транзакция завершит процесс чтения данных, даже если сама транзакция к этому моменту ещё не завершена, её блокировка уже не будет препятствовать изменениям в этих данных. При использовании Repeatable Read, когда в одной транзакции читаются порции данных, одни и те же данные будут считаться каждый раз, когда происходит чтение в этой транзакции. Поэтому, даже в моменты, когда чтение данных не выполняется, другие транзакции не смогут изменять данные, но они смогут осуществлять вставки новых данных в таблицу или в диапазоны данных, которые в этот момент не блокированы. Уровень Serializable идёт на шаг дальше по отношению Repeatable Read и защищает все другие блоки данных от вставок. Это называется предотвращением фантомных чтений.

16 Snapshot - изоляция Кроме Read Uncommited, во всех других уровнях изоляции запись блокирует чтения. Не существует опций на сеансовом уровне, которые бы предписывали запрет установки блокировки пишущей транзакцией для читающих транзакций (кроме использования NOLOCK). Когда транзакция читает данные, иногда допустимо обращаться к уже сохранённым данным, без учёта последних или текущих изменений данных, но зато при этом избежать блокировок. Чтобы реализовать такую возможность, введён уровень Snapshot - изоляции. Когда для сеанса установлен этот уровень изоляции, читающие транзакции получают предыдущую копию данных.

17 Snapshot - изоляция

18 Транзакция 2 (Snapshot Isolation) SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT y FROM tst WHERE x = SELECT y FROM tst WHERE x = COMMIT TRAN SELECT y FROM tst WHERE x= Транзакция 1 BEGIN TRAN UPDATE tst SET y =-1 WHERE x =1 COMMIT TRAN Транзакция 3 (RCSI) BEGIN TRAN SELECT y FROM tst WHERE x = SELECT y FROM tst WHERE x = COMMIT TRAN SELECT c2 FROM t1 WHERE c1 = Snapshot - изоляция Пример ВРЕМЯ

19 Транзакция 2 (Snapshot Isolation) SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT y FROM tst WHERE x = UPDATE tst SET y = 4 WHERE x = 2 УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ Транзакция 1 BEGIN TRAN UPDATE tst SET y = -2 WHERE x =2 COMMIT TRAN Транзакция 3 (RCSI) BEGIN TRAN SELECT y FROM tst WHERE x = UPDATE tst SET y = 3 WHERE x = 2 COMMIT TRAN SELECT y FROM tst WHERE x = Обнаружение конфликта обновления эта инструкция не пройдет из-за обнаружения конфликта и автоматически произойдет откат транзакции ВРЕМЯ

20 УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ Read-Committed Snapshot IsolationRead-Committed Snapshot Isolation –Процессы чтения видят зафиксированные значения на момент выполнения инструкции Snapshot IsolationSnapshot Isolation –Процессы чтения видят зафиксированные значения на момент выполнения транзакции –Предотвращается проблема потерянных обновлений –Требуется изменение кода Написание исключений для выявления конфликтовНаписание исключений для выявления конфликтов Установка на уровне сессии SET TRANSACTION ISOLATION LEVEL SNAPSHOTУстановка на уровне сессии SET TRANSACTION ISOLATION LEVEL SNAPSHOT –Нагрузка гораздо больше по сравнению c RCSI Версии хранятся в tempdbВерсии хранятся в tempdb –Обеспечьте свободное пространство и производительность дискового массива

21 УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ Использовать версионностьИспользовать версионность Не использовать длинные транзакцииНе использовать длинные транзакции –Могут быть ситуации, когда транзакцию невозможно сделать короткой Уменьшить уровень изоляции до Read UncommittedУменьшить уровень изоляции до Read Uncommitted –Возможно грязное чтение

22 Snapshot Isolation Как это сделано Уровни изоляции, основанные на моментальных снимках, используют версионность данных на уровне строкУровни изоляции, основанные на моментальных снимках, используют версионность данных на уровне строк –Операции Update/Delete генерируют версии строк –Непротиворечивость чтения достигается прохождением по версиям строки в обратном хронологическом порядке Каждой транзакции назначается транзакционный последовательный номер (XSN)Каждой транзакции назначается транзакционный последовательный номер (XSN) –Каждая новая или измененная строка помечается этим номером –Предыдущая версия помещается в хранилище версий –Каждая строка указывает на связный список предыдущих версий этой строки Версии строки содержатся в базе данных tempdbВерсии строки содержатся в базе данных tempdb

23 Read-Committed Snapshot IsolationRead-Committed Snapshot Isolation –Новое поведение read committed (неблокирующее) –Изоляция на уровне инструкций –Получение последних зафиксированных данных на момент начала выполнения инструкции Snapshot IsolationSnapshot Isolation –Новый уровень изоляции транзакций –Изоляция на уровне транзакций –Получение последних зафиксированных данных на момент начала выполнения транзакции

24 Snapshot Isolation Транзакционная согласованность БД на момент начала транзакции Требуется установка на уровне БД ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON Требуется установка на уровне сессии SET TRANSACTION ISOLATION LEVEL SNAPSHOT SET TRANSACTION ISOLATION LEVEL SNAPSHOT Чтение не блокирует данные Уменьшается количество тупиковых блокировок ценой конфликтов при одновременной записи несколькими транзакциями Состояние Snapshot Isolation включено по умолчанию для master и msdb

25 Read-Committed Snapshot Требуется включение на уровне БД ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT ON Процессы чтения видят зафиксированные значения на момент выполнения инструкции –Процессы записи не блокируют процессы чтения –Процессы чтения не блокируют процессы записи Значительное уменьшение операций блокирования ресурсов и появления тупиковых блокировок без изменения кода приложений

26 Явное определение транзакции УСТАНОВКА УРОВНЯ ИЗОЛЯЦИИ SET TRANSACTION ISOLATION LEVEL READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE | SNAPSHOT

27 Явное определение транзакции УСТАНОВКА УРОВНЯ ИЗОЛЯЦИИ READ COMMITTED Накладывается блокировка, запрещающая изменение одних и тех же данных разными транзакциями. Данные изменённые одной транзакцией могут быть прочитаны другой транзакцией. Проблемы грязного, неповторяемого и чтения фантомов не решены.

28 Явное определение транзакции УСТАНОВКА УРОВНЯ ИЗОЛЯЦИИ READ UNCOMMITTED На изменяемые данные налагается блокировка, запрещающая не только изменение но и чтение данных. Не решает проблемы неповторяемого чтения и чтения фантомов. ИСПОЛЬЗУЕТСЯ В MS SQL SERVER ПО УМОЛЧАНИЮ

29 Явное определение транзакции УСТАНОВКА УРОВНЯ ИЗОЛЯЦИИ REPEATABLE READ Решает проблему неповторяемого чтения.

30 Явное определение транзакции УСТАНОВКА УРОВНЯ ИЗОЛЯЦИИ SERIALIZABLE Высший уровень изоляции. Решает проблему чтения фантомов. Используется блокировка индексов, т.е. в отношении не могут быть вставлены или удалены кортежи, соответствующие логическому условию, используемому для выборки данных.

31 Явное определение транзакции НАЧАЛО ТРАНЗАКЦИИ BEGIN TRAN[saction] [transaction_name [WITH MARK [description]]] WITH MARK – маркировка для журнала Description – краткое описание транзакции

32 MS SQL SERVER 2005 УРОВНИ ИЗОЛЯЦИИ Уровни изоляции Грязное чтение Неповторяемое чтение Фантомы Конфликт обновления Блокирование ресурсов Read Uncommitted ДаДаДаНет Read Committed 1 Locking 2 Snapshot Нет Нет Да Да Нет Нет Пессимист. Оптимист. Repeatable Read НетНетДаНетПессимист. SnapshotНетНетНетДаОптимист. SerializableНетНетНетНетПессимист. Возможные аномалии Обнаружен и сделан откат Нет потерянных обновлений! Обнаружен и сделан откат Нет потерянных обновлений!

33 БЛОКИРОВКИ «Пессимистичная» блокировка гарантирует целостность данных, но несовместима с многоуровневыми системами, рассчитанными на неустойчивые канал связи и тонкого клиента, и не позволяет достичь высокой масштабируемости«Пессимистичная» блокировка гарантирует целостность данных, но несовместима с многоуровневыми системами, рассчитанными на неустойчивые канал связи и тонкого клиента, и не позволяет достичь высокой масштабируемости «Оптимистичные» блокировки приходится реализовывать вручную – или добавляя timestamp (номер версии) в каждую таблицу, или при обновлении в предложение WHERE подставлять условие равенства старым значениям колонок«Оптимистичные» блокировки приходится реализовывать вручную – или добавляя timestamp (номер версии) в каждую таблицу, или при обновлении в предложение WHERE подставлять условие равенства старым значениям колонок

34 БЛОКИРОВКИ Пессимистичные блокировки сводятся к тому, что информация при чтении приложений блокируется до завершения некой транзакции. Это порождает большую длительность блокировки информации в базе данных, что препятствует работе других пользователей. Оптимистичная блокировка. Эта блокировка реализуется вручную, когда мы считываем информацию с базы, какие-либо строки из таблиц, они блокируются, то есть другие пользователи могут их спокойно считывать, видоизменять, но непосредственно перед изменением данных необходимо проверить, были ли они изменены с момента загрузки. То есть, например, пользователь загрузил какой-то документ, поменял его, нажал ok, он принял решение, исходя из информации в базе на момент загрузки. Вот чтобы всё было правильно, когда он нажал ok, и всё это ушло на сервер приложений, вот там необходимо начать транзакцию, проверить, что информация не изменена, и если она не изменена, тогда сохранить в базу данных изменённый данным пользователем документ.

35 ТУПИКИ - DEAD LOCKИ Обычно проявляются в большом количестве при высокой нагрузкеОбычно проявляются в большом количестве при высокой нагрузке Являются «убийцами» масштабируемости – добавляют задержки, уменьшают количество успешно выполняемых операцийЯвляются «убийцами» масштабируемости – добавляют задержки, уменьшают количество успешно выполняемых операций Устраняются аккуратным (и дисциплинированным) программированием операций базы данных – за счет выполнения всех операций в одной последовательности прохода по таблицамУстраняются аккуратным (и дисциплинированным) программированием операций базы данных – за счет выполнения всех операций в одной последовательности прохода по таблицам Легко детектируются Performance MonitorомЛегко детектируются Performance Monitorом Конкретное условие возникновения можно определить с помощью trace flag 1204Конкретное условие возникновения можно определить с помощью trace flag 1204

36 ТУПИКИ - DEAD LOCKИ Dead lock – это ситуация, когда две транзакции ждут друг друга, как два человека, столкнувшиеся в дверях, и никто не желает отступить. Как в таких случаях ведёт себя Sequel сервер? Sequel сервер в течение некоторого времени соображает, потом на основе стоимости выполнения транзакции пытается принять решение, которую из них он откатит автоматически. На принятие этого решения уходит некоторое время. Что уменьшает масштабируемость системы? Масштабируемость реально уменьшается не только за счёт того, что дидлоки создают задержки в базе данных, но и за счёт того, что уменьшается процент выполнения успешных транзакций. Вот есть пример у нас живой: это мы занимались оптимизацией системы, построенной для одного из наших заказчиков, подвергали её нагрузке. Нагрузка мерилась от 100 до нескольких тысяч пользователей. Так вот уже на 200 пользователей уже 5%транзакций завершалось с ошибкой по дидлокам. Когда мы доводили нагрузку до 1000 пользователей, там получалось огромное время выполнения запросов, но плюс к этому процент транзакций с дидлоками доходил до 30-40%, то есть, никакой речи о масштабируемости данной системы идти не может. Её пришлось значительно корректировать. Как устраняются дидлоки? Они устраняются, прежде всего, дисциплиной в программировании операций с базой данных, это заключается в том, что когда у вас есть транзакции, модифицирующие более чем одну таблицу, все транзакции должны модифицировать эту таблицу в одной последовательности, тогда у вас никогда не будет дидлоков. Ну, естественно это правило не всегда возможно соблюсти, но надо к этому стремиться, и вот, как раз, если вы применяете компоненты доступа к данным, которые пишет обычно один человек, хорошо разбирающийся в серверах баз данных, наверное, в этом случае он запрограммирует последовательность операций так, что все операции будут выполняться в одной последовательности. Немного о детекции. Как обнаружить факт о наличие дидлоков в системе? Прежде всего, это Performance Monitor. Вы запускаете вашу систему под нагрузкой, и среди счётчиков Sequel сервера, с которыми умеет работать Performance Monitor, есть количество дидлоков, то есть, если дидлоков нет, значит, вы про них забываете, если дидлоки есть, вам нужно определить место их возникновения, то есть на каких SQL командах происходят дидлоки. Для этого можно установить Trace Flag Sequel сервера, тогда он в RR-lock будет писать информацию о дидлоках, будет приводить оба SQL statement, на которых возник дидлок и кучу дополнительной информации. Это можно посмотреть в Books online SQL Server, набрать там Trace Flag , и там будет несколько строк описания всего этого.

37 Snapshot Isolation - Пример CREATE TABLE t1 (c1 int unique, c2 int) INSERT INTO t1 VALUES (1, 5) Транзакция 2 (Snapshot Isolation) SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT c2 FROM t1 WHERE c1 = 1 -- SQL Server возвратит 5 -- SQL Server возвратит 5 SELECT c2 FROM t1 WHERE c1 = 1 -- SQL Server возвратит 5 COMMIT TRAN SELECT c2 FROM t1 WHERE c1 = 1 -- SQL Server возвратит 9 SELECT c2 FROM t1 WHERE c1 = 1 -- SQL Server возвратит 9 Транзакция 1 BEGIN TRAN UPDATE t1 SET c2 = 9 WHERE c1 =1 COMMIT TRAN Транзакция 3 (RCSI) BEGIN TRAN SELECT c2 FROM t1 WHERE c1 = 1 -- SQL Server возвратит 5 -- SQL Server возвратит 5 SELECT c2 FROM t1 WHERE c1 = 1 -- SQL Server возвратит 9 COMMIT TRAN SELECT c2 FROM t1 WHERE c1 = 1 SELECT c2 FROM t1 WHERE c1 = 1 -- SQL Server возвратит 9 -- SQL Server возвратит 9 Время

38 Snapshot Isolation: Обязательное выявление конфликтов Возникает только для Snapshot IsolationВозникает только для Snapshot Isolation Возникает когда данные, изменяющиеся внутри snapshot-транзакции, также изменяются другой транзакциейВозникает когда данные, изменяющиеся внутри snapshot-транзакции, также изменяются другой транзакцией Обнаружение происходит автоматическиОбнаружение происходит автоматически Вызывает автоматический откат snapshot- транзакцииВызывает автоматический откат snapshot- транзакции Обнаружение конфликтов предотвращает проблему потерянных обновленийОбнаружение конфликтов предотвращает проблему потерянных обновлений

39 Обнаружение конфликта обновления - Пример CREATE TABLE t1 ( c1 int unique, c2 int) INSERT INTO t1 VALUES (1,5) INSERT INTO t1 VALUES (2,5) Транзакция 2 (Snapshot Isolation) SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT c2 FROM t1 WHERE c1 = 1 -- SQL Server возвратит 5 эта инструкция не пройдет из-за обнаружения конфликта и автоматически произойдет откат транзакции UPDATE t1 SET c2 = 15 WHERE c1 = 1 эта инструкция не пройдет из-за обнаружения конфликта и автоматически произойдет откат транзакции Транзакция 1 BEGIN TRAN UPDATE t1 SET c2 = 9 WHERE c1 = 1 COMMIT TRAN Время Транзакция 3 (RCSI) BEGIN TRAN SELECT c2 FROM t1 WHERE c1 = 1 -- SQL Server возвратит 5 UPDATE t1 SET c2 = 11 WHERE c1 = 1 COMMIT TRAN SELECT a FROM t WHERE c1 = 1 -- SQL Server возвратит 11

40 Минимизация конфликтов обновления Использовать в SELECT хинт UpdateИспользовать в SELECT хинт Update –Уменьшает шанс возникновения конфликтов обновления в продолжительных snapshot- транзакциях –Никто не сможет изменить эти данные после того как они будут заблокированы Все таблицы, даже самые маленькие, должны быть проиндексированыВсе таблицы, даже самые маленькие, должны быть проиндексированы –Одновременные обновления даже разных строк на неиндексированной таблице вызывает конфликт

41 Минимизация конфликтов обновления - Пример CREATE TABLE t1 ( c1 int unique, c2 int) INSERT INTO t1 VALUES (1,5) Транзакция 1 (Snapshot Isolation) SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN …. SELECT c2 FROM t1 with (UPDLOCK) WHERE c1 = 1 // вернется значение (5) UPDATE t1 SET c2 = 15 WHERE c1 = 1 эта инструкция пройдет успешно commit Транзакция 2 BEGIN TRAN UPDATE t1 SET c2 = 9 WHERE c1 = 1 -- транзакция 2 заблокирована -- транзакция 2 разблокирована COMMIT TRAN Время

42 Явное определение транзакции ТОЧКА СОХРАНЕНИЯ SAVE TRAN[saction] {savepoint_name

43 Явное определение транзакции ОТКАТ ТРАНЗАКЦИИ ROLLBACK TRAN[saction] ROLLBACK [WORK] ROLLBACK TRAN[saction] [transaction_name | save_point_name Если выполнена команда ROLLBACK транзакция будет завершена

44 Явное определение транзакции ФИКСИРОВАНИЕ ТРАНЗАКЦИИ COMMIT TRAN[saction] Если выполнена команда COMMIT транзакция будет завершена

45 Управление блокировками на уровне команд SELECT title FROM books WITH (ROWLOCK) WHERE prise>35; SELECTINSERTUPDATEDELETE ROWLOCK PAGLOCK TABLOCK TABLOCKX HOLDLOCK UPDLOCK NOLOCK READUNCOMMITED READCOMMITED REPEATABLEREAD SERIALIZABLE READPAST

46 ТУПИКОВАЯ БЛОКИРОВКА УСТАНОВКА ТАЙМАУТА SET LOCK_TIMEOUT timeout_period timeout_period – задаётся в миллисекундах. Значение по умолчанию – 1 что значит бесконечное ожидание разблокирования.

47 ТУПИКОВАЯ БЛОКИРОВКА УСТАНОВКА ТАЙМАУТА SET DEADLOCK_PRIORITY LOW | NORMAL LOW – транзакцией можно пожертвовать в первую – может принимать целые значения. 3 соответствует LOW, 6 соответствует NORMAL.

48 Сравнение уровней изоляции транзакций в MS SQL Server и Oracle Oracle SQL Server Read Committed (по умолчанию) Read-Committed Snapshot Isolation Serializable Snapshot Isolation используется с логикой обязательного обнаружения конфликтов Read Only Snapshot Isolation

49 Существуют две основные стратегии поддержания ссылочной целостности. RESTRICT (ОГРАНИЧИТЬ) - не разрешать выполнение операции, приводящей к нарушению ссылочной целостности. CASCADE (КАСКАДНОЕ ИЗМЕНЕНИЕ) - разрешить выполнение требуемой операции, но внести при этом необходимые изменения в связанных таблицах так, чтобы не допустить нарушения ссылочной целостности и сохранить все имеющиеся связи. Изменение начинается в родительской таблице и каскадно выполняется в дочерних таблицах. В реализации этой стратегии имеется одна тонкость, заключающаяся в том, что дочерние таблицы сами могут быть родительскими для некоторых третьих таблиц. При этом может дополнительно потребоваться выполнение какой-либо стратегии и для этой связи и т.д. Если при этом какая-либо из каскадных операций (любого уровня) не может быть выполнена, то необходимо отказаться от первоначальной операции и вернуть базу данных в исходное состояние. Это сложная стратегия, но она не нарушает связей между родительскими и дочерними таблицами. Эти стратегии являются стандартными и присутствуют во всех СУБД, в которых имеется поддержка ссылочной целостности. Существуют две основные стратегии поддержания ссылочной целостности. RESTRICT (ОГРАНИЧИТЬ) - не разрешать выполнение операции, приводящей к нарушению ссылочной целостности. CASCADE (КАСКАДНОЕ ИЗМЕНЕНИЕ) - разрешить выполнение требуемой операции, но внести при этом необходимые изменения в связанных таблицах так, чтобы не допустить нарушения ссылочной целостности и сохранить все имеющиеся связи. Изменение начинается в родительской таблице и каскадно выполняется в дочерних таблицах. В реализации этой стратегии имеется одна тонкость, заключающаяся в том, что дочерние таблицы сами могут быть родительскими для некоторых третьих таблиц. При этом может дополнительно потребоваться выполнение какой-либо стратегии и для этой связи и т.д. Если при этом какая-либо из каскадных операций (любого уровня) не может быть выполнена, то необходимо отказаться от первоначальной операции и вернуть базу данных в исходное состояние. Это сложная стратегия, но она не нарушает связей между родительскими и дочерними таблицами. Эти стратегии являются стандартными и присутствуют во всех СУБД, в которых имеется поддержка ссылочной целостности. ОСНОВНЫЕ СТРАТЕГИИ ПОДДЕРЖАНИЯ ССЫЛОЧНОЙ ЦЕЛОСТНОСТИ

50 IGNORE (ИГНОРИРОВАТЬ) - разрешить выполнять операцию без проверки ссылочной целостности. В этом случае в дочерней таблице могут появляться некорректные значения внешних ключей, вся ответственность за целостность базы данных ложится на программиста или пользователя. SET NULL (ЗАДАТЬ ЗНАЧЕНИЕ NULL) - разрешить выполнение требуемой операции, но все возникающие некорректные значения внешних ключей изменять на null-значения. Эта стратегия имеет два недостатка. Во-первых, для нее требуется разрешение на использование null-значений. Во-вторых, записи дочерней таблицы теряют связь с записями родительской таблицы. Установить, с какой записью родительской таблицы были связаны измененные записи дочерней таблицы, после выполнения операции уже нельзя. SET DEFAULT (ЗАДАТЬ ЗНАЧЕНИЕ ПО УМОЛЧАНИЮ) - разрешить выполнение требуемой операции, но все возникающие некорректные значения внешних ключей изменять на некоторое значение, принятое по умолчанию. Достоинство этой стратегии по сравнению с предыдущей в том, что она позволяет не пользоваться null-значениями. Установить, с какими записями родительской таблицы были связаны измененные записи дочерней таблицы, после выполнения такой операции тоже нельзя. IGNORE (ИГНОРИРОВАТЬ) - разрешить выполнять операцию без проверки ссылочной целостности. В этом случае в дочерней таблице могут появляться некорректные значения внешних ключей, вся ответственность за целостность базы данных ложится на программиста или пользователя. SET NULL (ЗАДАТЬ ЗНАЧЕНИЕ NULL) - разрешить выполнение требуемой операции, но все возникающие некорректные значения внешних ключей изменять на null-значения. Эта стратегия имеет два недостатка. Во-первых, для нее требуется разрешение на использование null-значений. Во-вторых, записи дочерней таблицы теряют связь с записями родительской таблицы. Установить, с какой записью родительской таблицы были связаны измененные записи дочерней таблицы, после выполнения операции уже нельзя. SET DEFAULT (ЗАДАТЬ ЗНАЧЕНИЕ ПО УМОЛЧАНИЮ) - разрешить выполнение требуемой операции, но все возникающие некорректные значения внешних ключей изменять на некоторое значение, принятое по умолчанию. Достоинство этой стратегии по сравнению с предыдущей в том, что она позволяет не пользоваться null-значениями. Установить, с какими записями родительской таблицы были связаны измененные записи дочерней таблицы, после выполнения такой операции тоже нельзя. ДОПОЛНИТЕЛЬНЫЕ СТРАТЕГИИ ПОДДЕРЖАНИЯ ССЫЛОЧНОЙ ЦЕЛОСТНОСТИ