Презентация на тему Управление транзакциями и блокироваками в СУБД MS SQL Server 2008

Разработка и эксплуатация информационных систем Тема занятия:Многопользовательский доступ к базам данных Преподаватель БОУ СПО ЧЭМК Игнатьева Татьяна Александровна Вопросы для повторения Перечислите операторы языка SQL для определения структуры данных CREATE ALTER DROP Вопросы для повторения Перечислите операторы языка SQL, предназначенные для манипулирования данными INSERT DELETE UPDATE SELECT Постановка задачи Создать запросы на языке SQL Изменить количество вакансий на 50 для специальности с кодом 1 Изменить количество вакансий на 40 для специальности с кодом 1 Реализация запросов в СУБД MS SQL Server Реализация запросов в СУБД MS SQL Server Организация многопользовательского доступа к БД Для исключения нарушения логической целостности данных при многопользовательском доступе, используется механизм транзакций. Понятие транзакции Транзакция — последовательность операций, выполняемая как целостная логическая единица работы. По умолчанию каждая команда выполняется как самостоятельная транзакция Можно явно указать ее начало и конец, чтобы иметь возможность включить в нее несколько команд Свойства транзакции атомарность В транзакции выполняются или все входящие в нее операции, или ни одна. Свойства транзакции атомарность согласовансть После завершении транзакции все данные должны находится в согласованном состоянии.Если хотя бы одна из них нарушит целостность данных, то все остальные могут выдать неверные результаты. Свойства транзакции атомарность согласованность изоляция Модификации, выполняемые транзакцией, должны быть изолированы от модификаций, параллельно выполняемых другими транзакциями. Свойства транзакции атомарность согласованность изоляция устойчивость По завершении транзакции ее результат должен сохраниться в системе в любом случае: фиксацией результатов или откатом. Транзакции и блокировки Транзакции накладывают блокировки на данные, чтобы обеспечить выполнение свойственных требований. Блокировка — временное ограничение на выполнение некоторых операций обработки данных. Решение «потерянных обновлений» update специальности set Вакансий=50 where код_специальности=1waitfor delay '00:00:05'select * from специальностиwhere код_специальности=1 update специальности set Вакансий=40 where код_специальности=1select * from специальностиwhere код_специальности=1 begin tran begin tran commit tran commit tran Исследование взаимовлияния транзакций При одновременном выполнении двух транзакций, обращающихся к одним и тем же данным, могут возникать следующие проблемные ситуации:«грязное чтение» — возникает в том случае, когда первая транзакция читает данные, которые обновила, но еще не зафиксировала вторая транзакция. Пример «грязного чтения» Пример «грязного чтения» Исследование взаимовлияния транзакций «неповторяемое чтение» — возникает, когда первая транзакция несколько раз обращается к одним и тем же данным, однако данные меняются вследствие того, что между обращениями вторая транзакция обновляет данные и фиксирует. Пример «неповторяемого чтения» Исследование взаимовлияния транзакций «чтение фантомов» — появляется в том случае, когда записи из диапазона записей, читаемого в первой транзакции, добавляются или удаляются второй транзакцией. Управление взаимодействиями транзакций Для управления взаимодействия транзакций между собой используется установка уровня изоляции транзакции. Уровень изоляции транзакции определяет, какие блокировки накладываются на данные. Для изменения уровня изоляции транзакции используется оператор SET TRANSACTION ISOLATION LEVEL.Синтаксис: SET TRANSACTION ISOLATION LEVEL {уровень изоляции} Уровни изоляции транзакции READ UNCOMMITTED (неподтвержденное чтение) — транзакция с этим уровнем изоляции может читать записи, которые были изменены, но еще не зафиксированы другой транзакцией. READ COMMITTED (подтвержденное чтение) — транзакция с этим уровнем изоляции может читать только те записи, которые были изменены и уже зафиксированы другой транзакцией. Уровни изоляции транзакции REPEATABLE READ (повторяемое чтение) — транзакция с этим уровнем изоляции может читать только те записи, которые были изменены и уже зафиксированы другой транзакцией, и никакая другая транзакция не может изменить записи, которые были прочитаны в рамках этой транзакции.SERIALIZABLE (упорядочение) — транзакция с этим уровнем изоляции может читать только те записи, которые были изменены и уже зафиксированы другой транзакцией; никакая другая транзакция не может изменить записи, которые были прочитаны в рамках этой транзакции и никакая другая транзакция не может добавить или удалить записи из диапазона записей, прочитанного в рамках этой транзакции. Уровни изоляции транзакции Уровень изоляции определяет, может ли транзакция считывать результаты работы других одновременно выполняемых завершенных и/или незавершенных транзакций. Уровни изоляции Потерянные обновления Грязное чтение Неповторяемое чтение Чтение фантомов READ UNCOMMITTED Нет Да Да Да READ COMMITTED Нет Нет Да Да REPEATABLE READ Нет Нет Нет Да SERIALIZABLE Нет Нет Нет Нет Решение проблемной ситуации «грязное чтение» begin tranupdate абитуриенты set статус='зачислен' where код_абитуриента=2waitfor delay '00:00:05'rollback tran set transaction isolation level select count(код_абитуриента) from абитуриенты where статус='зачислен' read committed Решение «неповторяемого чтения» Влияние уровней изоляции на транзакции Уровень изоляции Степень параллельности работы Уровень изоляции Согласованность данных снижается степень параллельности работы повышается согласованность данных При повышении уровня изоляции Литература 1) Шрайнер П. A., Котельников И. A. Распределенные информационные системы и базы данных. Режим доступа: http://msdn.microsoft.com2) Уровни изоляции и несогласованность данных. Всё об MS SQL Server 2008R2/2012 на русском. Режим доступа: www.T-SQL.ru.3) Грязное чтение и несогласованные данные. Всё об MS SQL Server 2008R2/2012 на русском. Режим доступа: www.T-SQL.ru4) Д.Э.Фуфаев, Э.В.Фуфаев. Разработка и эксплуатация автоматизированных информационных систем: учебник. - М.: Издательский центр «Академия», 2010.