Введение блокировок, как решения проблемы параллельной обработки

Администрирование баз данных ЛЕКЦИЯ 4. ПАРАЛЛЕЛЬНАЯ ОБРАБОТКА С ЯВНЫМИ БЛОКИРОВКАМИ. СОДЕРЖАНИЕ ЛЕКЦИИ:  Введение блокировок, как решения проблемы параллельной обработки. Взаимная блокировка, “deadlock”.  Принцип оптимистической блокировки. Принцип пессимистической блокировки.  Уровни изоляции транзакции.  Синтаксис и типы SQL Cursors. 2 1. Введение блокировок, как решения проблемы параллельной обработки. параллельная обработка Решение SQL lock Волк В.К. Проектирование, программирование, управление и администрирование БД 3 Блокировка (lock) – принудительная блокировка элемента, подлежащего изменению, на время проведения транзакции. Элемент на это время полностью или частично блокирует доступ к себе иным пользователям кроме транзакции. 4 терминология: Неявная блокировка (implicit lock) Гранулярность блокировки (granularity) Мягкая блокировка (shared lock) Исключительная блокировка (exclusive lock) И.В. Ананченко, И.В. Козлов. Администрирование Microsoft SQL Server 5 Пользователь:1 1. Заблокировать элемент 100 2. Считать элемент 100 3. Уменьшить количество элементов на 5 4. Записать элемент 100 Пользователь: 2 1. Заблокировать элемент 100 2. Считать элемент 100 3. Уменьшить количество элементов на 3 4. Записать элемент 100 Порядок обработки на сервере БД: Заблокировать элемент 100 для П1 Считать элемент 100 для П1 Заблокировать элемент 100 для П2 -> неудача -> П2 переводится в состояние ожидания Установить количество элементов равным 5 для П1 Записать элемент 100 для П1 Снять блокировку П1 с элемента 100 Заблокировать элемент 100 для П2 Считать элемент 100 для П2 Установить количество элементов равным 2 для П2 Записать элемент 100 для П2 Снять блокировку П2 с элемента 100 6 Взаимоблокировка Взаимоблокировка (deadlock) - это особая проблема одновременного конкурентного доступа, в которой две транзакции блокируют друг друга. В частности, первая транзакция блокирует объект базы данных, доступ к которому хочет получить другая транзакция, и наоборот. (взаимоблокировка может быть вызвана несколькими транзакциями, которые создают цикл зависимостей.) 7 Взаимная блокировка “deadlock” Пользователь:1 Заблокировать бумагу Взять бумагу Заблокировать карандаши Пользователь: 2 Заблокировать карандаши Взять карандаши Заблокировать бумагу Порядок обработки на сервере БД: Заблокировать бумагу для П1 Заблокировать карандаши для П2 Обработать запрос П1, обновить данные о бумаге Обработать запрос П2, обновить данные о карандашах Перевести П1 в состояние ожидания (карандашей) Перевести П2 в состояние ожидание (бумаги) БЛОКИРОВКА 8 USE SampleDb; BEGIN TRANSACTION UPDATE Works_on SET Job = 'Менеджер' WHERE EmpId = 25348 AND ProjectNumber = 'p2' WAITFOR DELAY '00:00:10' BEGIN TRANSACTION UPDATE Employee SET DepartamentNumber = 'd2' WHERE Id = 28559 WAITFOR DELAY '00:00:10' UPDATE Employee DELETE FROM Works_on SET LastName = 'Фролова' WHERE EmpId = 25348 WHERE LastName = 'Вершинина' AND ProjectNumber = 'p2' COMMIT COMMIT Если обе транзакции в примере выше будут выполняться в одно и то же время, то возникнет взаимоблокировка и система возвратит следующее сообщение об ошибке: 9 2. Принцип оптимистической блокировки. При оптимистическом управлении пользователи не блокируют данные на ! Блокировка ставится только на транзакцию период чтения. При обновлении данных, система проверяет, вносил ли другой пользователь в них изменение после считывания. пользователь Если изменял другой данные, возникает ошибка. 10 Пример оптимистической блокировки: SELECT ТОВАР.Название, ТОВАР.Количество FROM ТОВАР WHERE ТОВАР.Название ='Карандаш' Старое_Количество = ТОВАР.Количество Set НовоеКоличество = ТОВАР.Количество - 5 {обработка транзакции - если Новое_Количество < 0, то генерируется исключение, и т. д. Если все в порядке:} LOCK ТОВАР UPDATE ТОВАР SET ТОВАР.Количество = Новое_Количество WHERE ТОВАР.Название = 'Карандаш‘ AND ТОВАР.Количество = Старое_Количество UNLOCK ТОВАР {проверяем, было ли обновление успешным; если нет, повторяем транзакцию} 11 Принцип пессимистической блокировки ! Блокируются все запросы к элементу БД, включая транзакцию. Проверки на успешность транзакции не требуется. Волк В.К. Проектирование, программирование, управление и администрирование БД 12 Пример пессимистической блокировки: LOCK ТОВАР SELECT ТОВАР.Название, ТОВАР.Количество FROM ТОВАР WHERE ТОВАР.Название ='Карандаш' Set Новое_Количество = ТОВАР.Количество - 5 {обработка транзакции - если НовоеКоличество < 0, генерируется исключение, и т. д. Если все в порядке:} UPDATE ТОВАР SET ТОВАР.Количество = Новое_Количество WHERE ТОВАР.Название = 'Карандаш' UNLOCK ТОВАР {проверка на успешность обновления не требуется} 13 Настройка блокировок подсказки блокировок (locking hints) LOCK_TIMEOUT И.В. Ананченко, И.В. Козлов. Администрирование Microsoft SQL Server 14 Описание доступных подсказок блокировок: UPDLOCK READPAST TABLOCK XLOCK ROWLOCK HOLDLOCK PAGLOCK NOLOCK 15 Параметр LOCK_TIMEOUT SET LOCK_TIMEOUT 8000 Если данный ресурс не может быть предоставлен процессу в течение этого периода времени, инструкция завершается аварийно и выдается соответствующее сообщение об ошибке. Значение LOCK_TIMEOUT равное -1 (значение по умолчанию) указывает отсутствие периода ожидания, т.е. транзакция не будет ожидать освобождения ресурса совсем. 16 Отображение информации о блокировках resource_type - указывает тип ресурса; resource_database_id - задает идентификатор базы данных, к которой принадлежит данный ресурс; request_mode - задает режим запроса; request_status - задает текущее состояние запроса 17 3. Уровни изоляции транзакции Уровни изоляции транзакций контролируют следующие параметры. •Применение и типы блокировки при чтении данных. •Время удержания блокировок чтения. •Использование операции чтения ссылок на строки, измененные другой транзакцией. • Блокировка до тех пор, пока не будет снята монопольная блокировка строки. • Извлечение зафиксированной версии строки, которая существовала в то время, когда началось выполнение инструкции или транзакции. • Считывание незафиксированного изменения данных. Д.Крёнке. Теория и практика построения БД 18 Уровни изоляции транзакции страница с примерами настроек https://arbinada.com/en/node/619 теория по настройкам https://habrahabr.ru/post/317884/ 19 4. Синтаксис и типы SQL Cursors приложения в которых Сфера применения курсоров критичным является информация о текущих изменениях в БД или наоборот – поддержание стабильности состояния данных в наборе Команды управления курсором: DECLARE, OPEN, FETCH, CLOSE и DEALLOCATE. 20 Основные действия: •создание или объявление курсора; •открытие курсора, т.е. наполнение его данными, которые сохраняются в многоуровневой памяти; •выборка из курсора и изменение с его помощью строк данных; •закрытие курсора, после чего он становится недоступным для пользовательских программ; •освобождение курсора, т.е. удаление курсора как объекта, поскольку его закрытие необязательно освобождает ассоциированную с ним память. 21 SQL Server поддерживает три вида курсоров: - курсоры SQL применяются в основном внутри триггеров, хранимых процедур и сценариев; - курсоры сервера действуют на сервере и реализуют программный интерфейс приложений для ODBC, OLE DB, DB_Library; - курсоры клиента реализуются на самом клиенте. Они выбирают весь результирующий набор строк из сервера и сохраняют его локально, что позволяет ускорить операции обработки данных за счет снижения потерь времени на выполнение сетевых операций. 22 Примеры SQL Cursors Стандартное объявление курсора: Объявление и открытие курсора: Объявление курсора через переменную: 23