Описание блокировки и блокировки

12 мин

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

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

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

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

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

Взаимоблокировка — это другой сценарий блокировки, который возникает, когда одна транзакция содержит блокировку ресурса, а другая транзакция содержит блокировку для другого ресурса. Затем каждая транзакция пытается получить блокировку ресурса, заблокированного другой транзакцией, что приводит к бесконечному ожиданию, так как ни один из транзакций не может завершиться. Подсистема SQL Server обнаруживает эти сценарии и разрешает взаимоблокировку, прерывая одну из транзакций, исходя из того, какая транзакция выполнила наименьший объём работы, который нужно отменить. Транзакция, которая завершена, называется жертвой взаимоблокировки. Взаимоблокировки записываются в сеанс расширенных событий system_health, который включен по умолчанию.

Важно понимать концепцию транзакции. Autocommit — это стандартный режим SQL Server и Базы данных SQL Azure, что означает, что изменения, внесенные в следующую инструкцию, автоматически будут записываться в журнал транзакций базы данных.

INSERT INTO DemoTable (A) VALUES (1);

Чтобы разработчики могли иметь более детальный контроль над кодом приложения, SQL Server также позволяет явно контролировать транзакции. Следующий запрос возьмет блокировку строки в таблице DemoTable, которая не будет освобождена до добавления последующей команды для подтверждения транзакции.

BEGIN TRANSACTION

INSERT INTO DemoTable (A) VALUES (1);

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

BEGIN TRANSACTION

INSERT INTO DemoTable (A) VALUES (1);

COMMIT TRANSACTION

Команда COMMIT TRANSACTION явно фиксирует запись изменений в журнале транзакций. Измененные данные в конечном итоге вставляются в файл данных асинхронно. Эти транзакции представляют собой единицу работы ядра СУБД. Если разработчик забыл выдать команду COMMIT TRANSACTION, транзакция останется открытой, и блокировки не будут выпущены. Это одна из основных причин длительных транзакций.

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

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

SELECT tst.session_id, [database_name] = db_name(s.database_id)
    , tat.transaction_begin_time
    , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
    , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
        WHEN 2 THEN 'Read-only transaction'
        WHEN 3 THEN 'System transaction'
        WHEN 4 THEN 'Distributed transaction' END
    , input_buffer = ib.event_info, tat.transaction_uow     
    , transaction_state  = CASE tat.transaction_state    
        WHEN 0 THEN 'The transaction has not been completely initialized yet.'
        WHEN 1 THEN 'The transaction has been initialized but has not started.'
        WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
        WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
        WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
        WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
        WHEN 6 THEN 'The transaction has been committed.'
        WHEN 7 THEN 'The transaction is being rolled back.'
        WHEN 8 THEN 'The transaction has been rolled back.' END 
    , transaction_name = tat.name, request_status = r.status
    , tst.is_user_transaction, tst.is_local
    , session_open_transaction_count = tst.open_transaction_count  
    , s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib
ORDER BY tat.transaction_begin_time DESC;

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

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

Чтение неуправляемого — самый низкий уровень изоляции, доступный. Разрешено грязное чтение, что означает, что одна транзакция может видеть изменения, внесенные другой транзакцией, которая еще не была зафиксирована.
Чтение зафиксировано — позволяет транзакции считывать данные, которые ранее считывались, но не изменялись другой транзакцией без ожидания завершения первой транзакции. Этот уровень также освобождает блокировки чтения сразу после выполнения операции выбора. Это уровень SQL Server по умолчанию.
Повторяемое чтение — этот уровень сохраняет блокировки чтения и записи, полученные на выбранных данных до конца транзакции.
Сериализуемость — это самый высокий уровень изоляции, при котором транзакции полностью изолированы. Блокировки чтения и записи получаются для выбранных данных и не выпускаются до конца транзакции.

SQL Server также включает два уровня изоляции, включая управление версиями строк.

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

Уровни изоляции задаются для каждого сеанса с помощью команды T-SQL SET , как показано ниже.

SET TRANSACTION ISOLATION LEVEL

 { READ UNCOMMITTED

 | READ COMMITTED

 | REPEATABLE READ

 | SNAPSHOT

 | SERIALIZABLE

 }

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

Мониторинг блокирующих проблем

Выявление блокирующих проблем может быть сложной из-за их спорадической природы. DMV sys.dm_tran_locks, в сочетании с sys.dm_exec_requests, предоставляет сведения о блокировках, удерживаемых каждым сеансом. Более эффективный способ мониторинга проблем блокировки — использовать подсистему расширенных событий на постоянной основе.

Блокирующие проблемы обычно делятся на две категории:

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

Мониторинг проблем с производительностью, связанных с блокировкой, позволяет быстро определить снижение производительности, связанное с блокировкой.

Дополнительные сведения о мониторинге блокировки см. в "Понимание и решение проблем блокировки SQL Server".

Обратная связь

Были ли сведения на этой странице полезными?