|
|
Опубликовано 20.07.2016 12:37:53
|
|
|
Обзор шлюзов
1. Зачем вводить замки
Когда несколько пользователей одновременно выполняют операции в базе данных, возникают следующие несоответствия данных:
Отсутствующие обновления
Два пользователя, A и B, читают одни и те же данные и изменяют их, и результат изменения одного пользователя уничтожает результат другой, например, системы бронирования билетов
Грязное чтение
Пользователь A изменяет данные, затем пользователь B их читает, но пользователь A по какой-то причине отменяет изменение данных, и данные возвращаются к исходному значению
Не читайте снова и снова
Пользователь А читает данные, затем пользователь B читает их и изменяет
Основной метод контроля параллелизма — это блокировка, то есть запрет пользователям выполнять определённые операции в течение определённого времени, чтобы избежать несоответствия данных
2. Классификация шлюзов
Существует два подразделения на категории шлюзов:
1 . С точки зрения системы баз данных: она разделена на эксклюзивные замки (то есть эксклюзивные замки), общие замки и замки обновления
MS - SQL Server использует следующие шаблоны блокировки ресурсов.
Описание режима замка
Share(s) используются для операций, которые не изменяют и не обновляют данные (операции только для чтения), например, операторы SELECT.
Обновление (U) используется в обновляемых ресурсах. Предотвращает распространённые формы тупиков при чтении, блокировке нескольких сессий и, возможно, обновлении ресурсов.
Exclusive (X) используется для операций модификации данных, таких как INSERT, UPDATE или DELETE. Убедитесь, что не выполняется несколько обновлений на одном и том же ресурсе одновременно.
Замки намерения используются для формирования иерархии замков. Типы замков намерения: Intent Shared (IS), Intent Exclusive (IX) и Intent Exclusive (SIX).
Схемные блокировки используются при выполнении операций, зависящих от схемы таблицы. Типы схемных замков включают: модификацию схемы (Sch -M) и устойчивость схемы (Sch -S).
Массовые обновления (BU) используются, когда большие объемы данных копируются в таблицу и задаётся подсказка TABLOCK.
Общие замки
Общий блокировка позволяет одновременным транзакциям считывать (SELECT) ресурс. Когда существует общий (S) блок на ресурсе, ни одна другая транзакция не может изменить данные. Снимите общий (S) блокировку ресурса сразу после того, как данные были прочитаны, если только уровень изоляции транзакции не установлен на повторяемый или выше, или если общий (S) блок не сохраняется с подсказкой на время жизни транзакции.
Замок обновления
Обновление (U) блокировки предотвращают тупиковые блокировки в их обычном виде. Типичный шаблон обновления состоит из транзакции, которая считывает запись, получает общий (S) блок для ресурса (страницы или строки), а затем изменяет строку, требующую преобразования блокировки в эксклюзивный (X) блок. Если две транзакции получают блокировку общего режима на ресурсе и пытаются одновременно обновить данные, одна из транзакций пытается преобразовать блокировку в эксклюзивный (X) блок. Переход от общего режима к эксклюзивному блокировке должен подождать некоторое время, поскольку эксклюзивная блокировка одной транзакции несовместима с блокировкой общего режима другой; Происходит ожидание замка. Вторая транзакция пытается получить эксклюзивный (X) замок для обновления. Тупиковая блокировка возникает, потому что обе транзакции конвертируются в эксклюзивные (X) блокировки, и каждая транзакция ждёт, пока другая транзакция освободит блокировку общего режима.
Чтобы избежать этой потенциальной проблемы с тупиком, используйте обновлённый (U) замок. Только одна транзакция за раз может получить обновлённый (U) блок для ресурса. Если транзакция изменяет ресурс, обновляемый (U) замок преобразуется в эксклюзивный (X) блок. В противном случае замок преобразуется в общий замок.
Эксклюзивные замки
Эксклюзивные (X) блокировки препятствуют одновременным транзакциям доступу к ресурсам. Другие транзакции не могут читать или изменять данные, заблокированные эксклюзивным блокировкой (X).
Интенциональный замок
Блокировка намерения означает, что SQL Server должен получить общий (S) блок или эксклюзивный (X) блок на некоторые из базовых ресурсов иерархии. Например, блокировка намерений на долевом деле, установленная на уровне таблицы, указывает, что транзакция намерена разместить блокировку share(S) на странице или строке в таблице. Установка блокировки намерений на уровне таблицы предотвращает получение другой транзакции эксклюзивного (X) блокировки на таблице, содержащей эту страницу. Блокировки намерения могут повысить производительность, поскольку SQL Server проверяет блокировку намерения только на уровне таблицы, чтобы определить, может ли транзакция безопасно получить блокировку на этой таблице. Вместо того чтобы проверять блокировки в каждой строке или странице таблицы, чтобы определить, может ли транзакция заблокировать всю таблицу.
Замки по намерению включают Intent Sharing (IS), Intent Exclusive (IX) и Intent Exclusive Sharing (SIX).
Описание режима замка
Совместное использование намерений (IS) указывает, что намерение транзакции — это часть, а не все базовые ресурсы в иерархии чтения, размещая S-блокировки на каждом ресурсе.
Intent Exclusive (IX) указывает, что цель транзакции — изменить некоторые, но не все базовые ресурсы в иерархии, установив X-lock на каждый ресурс. IX — это надмножество ИС.
Эксклюзивное совместное использование с намерением (SIX) означает, что цель транзакции — прочитать все базовые ресурсы в иерархии и изменить некоторые, но не все, путём установки IX блокировок на каждый ресурс. Разрешите одновременные блокировки IS-объектов на ресурсах верхнего уровня. Например, блокировка SIX в таблице размещает блокировку SIX на таблице (позволяя параллельные блокировки IS) и блокировку IX на текущей изменённой странице (блокировка X на изменённой строке). Хотя каждый ресурс может иметь только одну блокировку SIX на определённый период, чтобы предотвратить обновление ресурса другими транзакциями, другие транзакции могут считывать базовые ресурсы в иерархии, получая блокировки IS на уровне таблицы.
Эксклюзивный замок: Только программа, выполняющая операцию блокировки, имеет право использовать его, и любые другие операции на нём не принимаются. Когда вы выполняете команду обновления данных, SQL Server автоматически использует эксклюзивный блок. Когда на объекте существуют другие замки, вы не можете добавить к нему эксклюзивный замок.
Общий замок: Ресурс, заблокированный общим блокировкой, может быть прочитан другими пользователями, но другие пользователи не могут его изменять.
Блокировка обновления: когда SQL Server готов обновить данные, он сначала блокирует объект данных, чтобы данные нельзя было изменить, но можно было прочитать. Когда SQL Server определяет, что хочет обновить данные, он автоматически заменяет блокировку обновления на эксклюзивную блокировку и не может добавить блокировку обновления, если на объекте существуют другие блокировки.
2 . С точки зрения программиста: он делится на оптимистичную и пессимистичную блокировку.
Optimism Lock: полностью зависит от базы данных для управления работой замка.
Пессимистичные замки: программисты сами управляют обработкой замков на данных или объектах.
MS - SQLSERVER использует блокировки для реализации пессимистического контроля параллелизма между несколькими пользователями, которые одновременно вносят изменения в базу данных
3. Размер частицы замка
Гранулярность блокировки — это размер заблокированной цели, малая блокирующая гранулярность — высокая параллельность, но накладные расходы большие, а большая блокирующая гранулярность — низкая параллельность, но накладные расходы малы
SQL Server поддерживает детализацию блокировки для строк, страниц, ключей, диапазонов ключей, индексов, таблиц или баз данных
Описание ресурса
Идентификатор строки RID. Раньше запирали ряд в столе отдельно.
Блокировка строки ключей в индексе. Используется для защиты диапазона ключей в сериализируемых транзакциях.
8 килобайт (КБ) страниц данных или индексных страниц.
Расширенный диск Набор из восьми соседних страниц данных или индексных страниц.
Таблица Вся таблица, включая все данные и индексы.
База данных данных.
4. Продолжительность времени заключения
Длительность удержания замка — это время, необходимое для защиты ресурса на запрошённом уровне.
Время удержания общего блокировки, используемого для защиты операций чтения, зависит от уровня изоляции транзакций. При стандартном уровне изоляции транзакций READ COMMED, общий блокировка управляется только на время чтения страницы. При сканировании замок не освобождается, пока замок не будет получен на следующей странице сканирования. Если указать запрос HOLDLOCK или установить уровень изоляции транзакции на REPEATABLE READ или SERIALIZABLE, блокировка не освобождается до завершения транзакции.
В зависимости от настройки параллелизма курсора курсор может получить блокировку свитка в совместном режиме для защиты экстракции. Когда требуется блокировка прокрутки, она не отпускается до следующего момента извлечения или закрытия курсора, в зависимости от того, что произойдёт раньше. Однако если указать HOLDLOCK, он не освобождается до конца транзакции.
Эксклюзивная блокировка, использованная для защиты обновления, не будет освобождена до окончания сделки.
Если соединение пытается получить замок, который конфликтует с блокировкой, контролируемой другим соединением, то соединение, пытающееся получить замок, будет заблокировано до тех пор:
Конфликтный замок освобождается, и соединение получает запрошенный замок.
Тайм-аут соединения истёк. По умолчанию нет интервала тайм-аута, но некоторые приложения устанавливают интервалы тайм-аута, чтобы избежать бесконечного ожидания
Пять кастомизировок замков в SQL Server
1 Обрабатывайте тупиковые блокировки и устанавливайте приоритеты в тупиках
Тупик — это бесконечное ожидание, вызванное подачей заявок на разные блокировки несколькими пользователями, потому что заявитель имеет часть права на блокировку и ожидание частичной блокировки, принадлежащей другим пользователям
Вы можете использовать DEADLOCK_PRIORITY SET, чтобы контролировать, как сессия реагирует в случае заблокировки. Если оба процесса блокируют данные, и каждый процесс не может снять свою блокировку до тех пор, пока другой процесс не откроет свой блокировку, возникает тупиковая ситуация.
2 Обрабатывайте тайм-ауты и устанавливайте длительность блокировки.
@@LOCK_TIMEOUT Возвращает текущую настройку тайм-аута для текущей сессии в миллисекундах
Настройка SET LOCK_TIMEOUT позволяет приложению установить максимальное время ожидания оператора для блокировки ресурса. Когда время ожидания оператора превышает параметр LOCK_TIMEOUT, система автоматически отменяет блокирующий оператор и возвращает приложению сообщение об ошибке 1222 о превышении времени тайм-аута запроса блокировки
пример
В следующем примере период тайм-аута блокировки установлен на 1 800 миллисекунд.
SET LOCK_TIMEOUT1800
3) Установите уровень изоляции транзакций.
4) Используйте подсказки по блокировке на уровне таблицы для операторов SELECT, INSERT, UPDATE и DELETE.
5) Настройте фиксирующую гранулярность индекса
Вы можете использовать sp_indexoption системных сохранённых процедурах для установки детализации блокировки для индексации
6. Просмотр информации о замке
1 Выполнить EXEC SP_LOCK сообщить информацию о замке
2 Нажмите Ctrl + 2 в анализаторе запросов, чтобы увидеть информацию о замке
7. Меры предосторожности при использовании
Как избежать тупиков
1. При использовании транзакций старайтесь сократить логический процесс обработки транзакций и отправлять или откатить транзакции досрочно.
2 Установите параметр тайм-аута тупика в разумном диапазоне, например: 3 минуты - 10 минут; После этого операция автоматически прекращается, чтобы избежать зависания процесса;
3. Оптимизировать программу, проверить и избежать явления тупика;
4. Тщательно протестируйте все скрипты и SP перед точной версией.
5 Все SP должны иметь обработку ошибок (через @error)
6 Не изменяйте стандартный уровень транзакций SQL SERVER. Принудительная блокировка не рекомендуется
Решите проблему Как заблокировать базу данных таблицы строк
8. Несколько вопросов о замках
1 Как зафиксировать строку таблицы
УСТАНОВИТЕ УРОВЕНЬ TRANSACTIONISOLATION READUNCOMMITTED
ВЫБРАТЬ *ИЗ таблицы rowlock, где id = 1
2 Заблокировать таблицу в базе данных
ВЫБЕРИТЕ *ИЗ ТАБЛИЦЫ С (HOLDLOCK)
Заявление о блокировке:
sybase:
Обновление таблицы COL1 = COL1, где 1= 0 ;
MSSQL:
Выберите COL1 из таблицы (Tablockx), где 1= 0 ;
oracle:
СТОЛ ДЛЯ ЗАБЛОКИРОВКИ В ЭКСКЛЮЗИВНОМ РЕЖИМЕ;
После того как замок заблокирован, никто другой не может им управлять, пока пользователь не разблокирует его, и он не разблокируется с помощью коммита или откаты.
Несколько примеров помогают вам углубить впечатление
Поставить стол 1(A,B,C)
А Б В
A1 B1 C1
A2 B2 C2
a3 b3 c3
1) Эксклюзивный замок
Создайте два новых соединения
Выполните следующее сообщение в первом соединении
Начать транс
Обновление таблицы1
множество A= ' aa '
где B= ' b2'
Ждите задержки' 00:00:30' --подождите 30 секунд
Commit tran
Выполните следующее сообщение во втором соединении
Начать транс
Выберите *из таблицы1
где B= ' b2'
Commit tran
Если два вышеуказанных оператора выполняются одновременно, запрос select должен дождаться выполнения обновления, то есть 30 секунд
2) Общий замок
Выполните следующее сообщение в первом соединении
Начать транс
выбрать *из таблицы 1 Holdlock — Блокировка искусственно добавляется к замку
где B= ' b2'
Ждите задержки' 00:00:30' --подождите 30 секунд
Commit tran
Выполните следующее сообщение во втором соединении
Начать транс
выберите A,C из таблицы1
где B= ' b2'
Обновление таблицы1
множество A= ' aa '
где B= ' b2'
Commit tran
Если два вышеуказанных оператора выполняются одновременно, можно выполнить запрос select во втором соединении
Обновление должно дождаться, пока первая транзакция освободит общий блокировку и превратит его в эксклюзивный, прежде чем можно будет выполнить, то есть ждать 30 секунд
3) Тупик
Добавлена таблица 2(D,E)
Д Е
d1 e1
d2 e2
Выполните следующее сообщение в первом соединении
Начать транс
Обновление таблицы1
множество A= ' aa '
где B= ' b2'
Ждите задержки 00:00:30
Обновление таблицы 2
множество D= ' d5'
где E= 'e1'
Commit tran
Выполните следующее сообщение во втором соединении
Начать транс
Обновление таблицы 2
множество D= ' d5'
где E= 'e1'
ждать задержки' 00:00:10'
Обновление таблицы1
множество A= ' aa '
где B= ' b2'
Commit tran
В то же время система обнаруживает тупик и прекращает процесс
Добавлю:
Подсказки по блокировке на уровне таблицы, поддерживаемые SQL Server 2000
HOLDLOCK удерживает общий блокировку до завершения всей транзакции и должен быть освобождён, как только заблокированный объект не нужен, что соответствует уровню изоляции СЕРИАЛИЗИРУЕМОЙ транзакции
Оператор NOLOCK выполняется без выдачи общего блокировки, что позволяет делать грязные чтения, что соответствует уровню изоляции READ UNCOMMITTED транзакций
PAGLOCK использует несколько блокировок страницы, при этом один стольный замок
READPAST позволяет sql-серверу пропускать любые заблокированные строки и выполнять транзакции, а для уровней изоляции READ некомментированных транзакций пропускать только блокировки RID, а не блокировки страниц, зон и таблицы
ROWLOCK обеспечивает использование rowlock
TABLOCKX обеспечивает использование эксклюзивной блокировки на уровне таблицы, которая предотвращает использование таблицы другими транзакциями во время транзакции
UPLOCK требует использования обновлений при чтении таблицы без общего блокировки
Блокировка приложения:
Блокировка приложения — это блокировка, генерируемая клиентским кодом, а не блокировка, сгенерируемая самим SQL Server
Два процесса для обработки блокировок приложений
sp_getapplock Блокировка ресурсов приложений
sp_releaseapplock Разблокировать ресурсы приложения
Примечание: Разница между блокировкой таблицы в базе данных
ВЫБРАТЬ *ИЗ ТАБЛИЦЫ С (HOLDLOCK) Другие транзакции могут читать таблицу, но не могут обновлять и удалять
ВЫБРАТЬ *ИЗ ТАБЛИЦЫ С(TABLOCKX) Другие транзакции не могут читать, обновлять и удалять таблицу
|
Предыдущий:Не было никакой конечной точки, слушающей http://localhost:111/xxx.svc этот с...Следующий:SQL блокирует NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
