Технология Клиент-Сервер 2010'4 |
||||||||
|
Параллелизм в отношении БД можно определить как число пользователей, способных работать с данной БД, не мешая и не пересекаясь друг с другом. Чем больше число одновременно работающих пользователей, тем выше параллелизм.
SQL Server блокирует данные для обеспечения их целостности. Это необходимо в любой БД, где данные подвергаются изменениям. Чтобы подчеркнуть это, скажу, что если БД находится в состоянии READ_ONLY, SQL Server не создает блокировок считываемых данных, поскольку они не могут быть изменены и, следовательно, защищать данные не нужно. Поэтому SQL Server только помечает объекты, к которым происходит обращение, блокировкой Intense Share (IS), чтобы исключить выполнение таких DDL-выражений, как удаление таблицы, из которой производится чтение.
Блокировки данных существуют в любой среде, где модифицируются данные (то есть БД находится в состоянии READ_WRITE, в противоположность предыдущему примеру), и это совершенно нормально. SQL Server удерживает необходимые блокировки для защиты целостности данных и освобождает данные, как только отпадает необходимость в блокировке.
Краткосрочные блокировки не оказывают негативного воздействия, они – часть нормальной работы SQL Server. Проблемы возникают, когда продолжительность блокировок становится большой, то есть в несколько секунд, и в этом случае в игру вступает sp_locks.
sp_locks – это полезное средство, помогающее в поиске и устранении проблем в сценариях с блокировками и параллелизмом.
USE [master]; SET QUOTED_IDENTIFIER ON; SET ANSI_NULLS ON; IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Locks]') AND type IN (N'P', N'PC') ) DROP PROCEDURE dbo.sp_Locks; GO CREATE PROCEDURE dbo.sp_Locks ( @Mode int = 2, @Wait_Duration_ms int = 1000 /* 1 seconds */ ) /* 19/04/2008 Yaniv Etrogi http://www.sqlserverutilities.com */ AS SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --EXEC sp_Locks @Mode = 3, @Wait_Duration_ms = 1000 /* return the one result set */ IF @Mode = 1 BEGIN; SELECT t.blocking_session_id AS blocking, t.session_id AS blocked, p2.[program_name] AS program_blocking, p1.[program_name] AS program_blocked, DB_NAME(l.resource_database_id) AS [database], p2.[hostname] AS host_blocking, p1.[hostname] AS host_blocked, t.wait_duration_ms, l.request_mode, l.resource_type, t.wait_type, (SELECT SUBSTRING(st.text, (r.statement_start_offset/2) + 1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE r.statement_end_offset END - r.statement_start_offset) /2 ) + 1) FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id = l.request_session_id) AS statement_blocked, CASE WHEN t.blocking_session_id > 0 THEN (SELECT st.text FROM sys.sysprocesses AS p CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st WHERE p.spid = t.blocking_session_id) ELSE NULL END AS statement_blocking --,t.resource_description AS blocking_resource_description --,l.resource_associated_entity_id FROM sys.dm_os_waiting_tasks AS t INNER JOIN sys.dm_tran_locks AS l ON t.resource_address = l.lock_owner_address INNER JOIN sys.sysprocesses p1 ON p1.spid = t.session_id INNER JOIN sys.sysprocesses p2 ON p2.spid = t.blocking_session_id WHERE t.session_id > 50 AND t.wait_duration_ms > @Wait_Duration_ms; END; /* return the first two result sets */ IF @Mode = 2 BEGIN; SELECT spid, [status], CONVERT(CHAR(3), blocked) AS blocked, loginame, SUBSTRING([program_name] ,1,25) AS program, SUBSTRING(DB_NAME(p.dbid),1,10) AS [database], SUBSTRING(hostname, 1, 12) AS host, cmd, waittype, t.[text] FROM sys.sysprocesses p CROSS APPLY sys.dm_exec_sql_text (p.sql_handle) t WHERE spid IN (SELECT blocked FROM sys.sysprocesses WHERE blocked <> 0) AND blocked = 0; SELECT t.blocking_session_id AS blocking, t.session_id AS blocked, p2.[program_name] AS program_blocking, p1.[program_name] AS program_blocked, DB_NAME(l.resource_database_id) AS [database], p2.[hostname] AS host_blocking, p1.[hostname] AS host_blocked, t.wait_duration_ms, l.request_mode, l.resource_type, t.wait_type, (SELECT SUBSTRING(st.text, (r.statement_start_offset / 2) + 1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1 ) FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id = l.request_session_id) AS statement_blocked, CASE WHEN t.blocking_session_id > 0 THEN (SELECT st.text FROM sys.sysprocesses AS p CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st WHERE p.spid = t.blocking_session_id) ELSE NULL END AS statement_blocking FROM sys.dm_os_waiting_tasks AS t INNER JOIN sys.dm_tran_locks AS l ON t.resource_address = l.lock_owner_address INNER JOIN sys.sysprocesses p1 ON p1.spid = t.session_id INNER JOIN sys.sysprocesses p2 ON p2.spid = t.blocking_session_id WHERE t.session_id > 50 AND t.wait_duration_ms > @Wait_Duration_ms; END; /* return all three result sets */ IF @Mode = 3 BEGIN; SELECT spid, [status], CONVERT(CHAR(3), blocked) AS blocked, loginame, SUBSTRING([program_name], 1, 25) AS program, SUBSTRING(DB_NAME(p.dbid), 1, 10) AS [database], SUBSTRING(hostname, 1, 12) AS host, cmd, waittype, t.[text] FROM sys.sysprocesses p CROSS APPLY sys.dm_exec_sql_text (p.sql_handle) t WHERE spid IN (SELECT blocked FROM sys.sysprocesses WHERE blocked <> 0) AND blocked = 0; SELECT t.blocking_session_id AS blocking, t.session_id AS blocked, SUBSTRING(p2.[program_name], 1, 25) AS program_blocking, SUBSTRING(p1.[program_name], 1, 25) AS program_blocked, DB_NAME(l.resource_database_id) AS [database], p2.[hostname] AS host_blocking, p1.[hostname] AS host_blocked, t.wait_duration_ms, l.request_mode, l.resource_type, t.wait_type, (SELECT SUBSTRING(st.text, (r.statement_start_offset/2) + 1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id = l.request_session_id) AS statement_blocked, CASE WHEN t.blocking_session_id > 0 THEN (SELECT st.text FROM sys.sysprocesses AS p CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st WHERE p.spid = t.blocking_session_id) ELSE NULL END AS statement_blocking --,t.resource_description AS blocking_resource_description --,l.resource_associated_entity_id FROM sys.dm_os_waiting_tasks AS t INNER JOIN sys.dm_tran_locks AS l ON t.resource_address = l.lock_owner_address INNER JOIN sys.sysprocesses p1 ON p1.spid = t.session_id INNER JOIN sys.sysprocesses p2 ON p2.spid = t.blocking_session_id WHERE t.session_id > 50 AND t.wait_duration_ms > @Wait_Duration_ms; SELECT DISTINCT r.session_id AS spid, r.percent_complete AS [percent], r.open_transaction_count AS open_trans, r.[status], r.reads, r.logical_reads, r.writes, s.cpu, DB_NAME(r.database_id) AS [db_name], s.[hostname], s.[program_name], --s.loginame, --s.login_time, r.start_time, --r.wait_type, r.wait_time, r.last_wait_type, r.blocking_session_id AS blocking, r.command, (SELECT SUBSTRING(text, statement_start_offset / 2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),text)) * 2 ELSE statement_end_offset END - statement_start_offset) / 2) FROM sys.dm_exec_sql_text(r.sql_handle)) AS [statement], t.[text] --,query_plan FROM sys.dm_exec_requests r INNER JOIN sys.sysprocesses s ON s.spid = r.session_id CROSS APPLY sys.dm_exec_sql_text (r.sql_handle) t --CROSS APPLY sys.dm_exec_query_plan (r.plan_handle) WHERE r.session_id > 50 AND r.session_id <> @@spid AND s.[program_name] NOT LIKE 'SQL Server Profiler%' --AND db_name(r.database_id) NOT LIKE N'distribution' --AND r.wait_type IN ('SQLTRACE_LOCK', 'IO_COMPLETION', 'TRACEWRITE') ORDER BY s.CPU DESC; END; GO |
В отличие от хранимой процедуры sp_helpindex2 (http://blogs.microsoft.co.il/blogs/yaniv_etrogi/archive/2010/04/11/sp-helpindex2.aspx), выбирающей информацию, связанную с контекстом текущей БД, sp_locks выбирает информацию уровня сервера, и поэтому она не должна быть помечена как системная хранимая процедура. Однако использование префикса sp_ позволяется воспользоваться тем, что SQL Server сперва ищет все объекты с таким префиксом в БД master. Это дает возможность вызвать процедуру из любой БД, не используя имя из трех частей (например, database.schema.object), что удобно.
Процедура возвращает до трех наборов результатов, что регулируется входным параметром @Mode.
Первый набор результатов возвращает информацию о Lead Blocker-процессе, если таковой существует, и нужен в ситуациях наличия высокой активности блокировок, приводящей к появлению цепочек блокировок.
Цепочка блокировок – это ситуация, когда есть много процессов, блокирующих другие процессы, которые, в свою очередь, блокируют данные или другие процессы. Во многих случаях это требует ручного вмешательства для разрешения конфликта, обычно это вмешательство принимает форму команды, завершающей исполнение процесса, выступающего в роли Lead Blocker.
Lead Blocker – это кличка для процесса, являющегося главной причиной, инициатором, с которого начинается цепочка блокировок – то есть этот процесс блокирует другой процесс, который, в свою очередь, будучи заблокированным, не может снять наложенные им блокировки, и тем самым сам становится блокирующим процессом. Это может продолжаться и продолжаться, вовлекая другие процессы, и порождая цепочки блокировок.
В таком сценарии страдает параллелизм работы с БД, и это, конечно, отражается на времени отклика приложений. Так что на момент, когда вы узнаете о наличии такой ситуации, вы, конечно, хотите как можно быстрее справиться с ней, чтобы минимизировать ее влияние на систему.
Этот набор результатов чаще всего содержит одну строку, но в сценарии эскалации блокировок вы можете увидеть и несколько строк (что означает, что имеется несколько блокирующих процессов и несколько цепочек блокировок).
На рисунке 1, где показано исполнение тестового сценария, блокирующим является процесс, выдавший команду UPDATE (spid 218). Этот процесс блокирует первый процесс, (spid 193), выдающий команду SELECT (/* connection 1 */), и любой последующий SELECT блокируется этим первым выражением SELECT.
Завершение подключения, выдавшего команду UPDATE, позволит всем блокированным (ожидающим) процессам продолжить работу и разрушит цепочку блокировок.
Заметьте, что если прервать любой другой процесс, сценарий эскалации блокировок не будет прерван и цепочка блокировок сохранится.
Второй набор результатов содержит информацию о блокирующих и блокированных процессах, обращаясь к трем ключевым DMV: sys.dm_os_waiting_tasks, sys.dm_tran_locks и sys.sysprocesses.
Мы выполняем запрос sys.dm_os_waiting_tasks, поскольку именно это нас и интересует – задачи (процессы), находящиеся в состоянии ожидания, в данном случае – ожидающие возможности получить блокировку ресурсов. Приятно то, что это DMV можно соединить с sys.dm_tran_locks по колонке resource_address, которая содержит адрес в памяти ресурса, которого ожидает задача, а с другой стороны условия объединения имеется колонка lock_owner_address из sys.dm_tran_locks, являющаяся адресом в памяти внутренней структуры данных, используемой для отслеживания запросов на блокировки модулем LOCK_MANAGER SQL Server-а.
Эти два DMV дают ценную информацию о блокировках. Далее мы добавляем sys.sysprocesses, чтобы получить информацию более высокого уровня, о блокировках программ и хостов. Чтобы получить информацию о блокирующих и заблокированных процессах, мы дважды выполняем соединение с sys.sysprocesses – один экземпляр для блокирующих, а другой – для заблокированных процессов.
Это достигается соединением одного экземпляра sys.sysprocesses как p1 с sys.dm_os_waiting_tasks по колонке session_id, и второго экземпляра как p2 по колонке blocking_session_id из sys.dm_os_waiting_tasks.
Входной параметр @Wait_Duration_ms позволяет ограничить число выводимых строк теми процессами, чье время ожидание превышает число (время в миллисекундах), указанное в этом параметре. Это очень полезно, поскольку обычно вас не интересуют краткосрочные (в 2-3 секунды) ожидания (блокировки), не оказывающие отрицательного влияния на систему.
Третий набор результатов содержит информацию из sys.dm_exec_requests об активно исполняемых процессах. Этот набор результатов может быть полезным, когда нет активных блокировок, и первые два набора не содержат никаких строк, но вам все-таки нужно увидеть, что исполняется в данное время на сервере.
-- Возвратить только второй набор результатов EXEC sp_Locks @Mode = 1, @Wait_Duration_ms = 1000; -- Возвратить первый и второй наборы результатов EXEC sp_Locks @Mode = 2, @Wait_Duration_ms = 1000; -- Возвратить все три набора результатов EXEC sp_Locks @Mode = 3, @Wait_Duration_ms = 1000; |
Чтобы сохранить информацию о блокировках в таблицу, используйте скрипт Capture_blocking_info.sql, который использует выражение INSERT…EXEC с sp_locks в @Mode = 2.
/* Сохранение информации о блокировках в таблицу 19/04/2008 Yaniv Etrogi http://www.sqlserverutilities.com */ USE [tempdb]; SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Blocks]') AND type in (N'U')) DROP TABLE [dbo].[Blocks]; GO CREATE TABLE [dbo].[Blocks]( [Id] [int] identity(1,1) NOT NULL CONSTRAINT [PK_Blocks] PRIMARY KEY NONCLUSTERED ([Id]), [InsertTime] datetime CONSTRAINT [DF_Blocks_InsertTime] DEFAULT (getdate()) NOT NULL, [blocking] smallint NULL, [blocked] smallint NULL, [program_blocking] varchar(128) NULL, [program_blocked] varchar(128) NULL, [database] varchar(128) NULL, [host_blocking] varchar(128) NOT NULL, [host_blocked] varchar(128) NOT NULL, [wait_duration_ms] bigint NULL, [request_mode] varchar(60) NOT NULL, [resource_type] varchar(60) NOT NULL, [wait_type] varchar(60) NULL, [statement_blocked] varchar(max) NULL, [statement_blocking] varchar(max) NULL ); GO CREATE CLUSTERED INDEX IXC_Blocks_InsertTime ON dbo.Blocks (InsertTime); -- Бесконечный цикл до ручного прекращения исполнения -- или изменения условия WHEN SET NOCOUNT ON; DECLARE @i int; SELECT @i = 0; WHILE (@i < 1000000) BEGIN; INSERT INTO [dbo].[Blocks] ([blocking], [blocked], [program_blocking], [program_blocked], [database], [host_blocking], [host_blocked], [wait_duration_ms], [request_mode], [resource_type], [wait_type], [statement_blocked], [statement_blocking]) EXEC sp_Locks @Wait_Duration_ms = 1000, @Mode = 1; SELECT @i = @i + 1; IF @i % 100 = 0 PRINT @i; --print every 100 iterations WAITFOR DELAY '00:00:10'; --10 seconds sleep END; -- Выборка полученных данных о блокировках. SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT TOP 100 [Id], [InsertTime], [blocking], [blocked], LEFT([program_blocking], 20) AS [program_blocking], LEFT([program_blocked], 20) AS [program_blocked], [database], [host_blocking], [host_blocked], [wait_duration_ms], [request_mode], [resource_type], [wait_type], [statement_blocked], [statement_blocking] FROM [dbo].[Blocks] ORDER BY [InsertTime] DESC; |
Чтобы получить представление о блокирующих и заблокированных процессах, возвращаемых sp_locks, можно использовать следующий скрипт, детализирующий причины блокировки.
Я использую его при поиске и воспроизведении сценария блокировки. Фильтрация по spid позволяет мне видеть только нужные мне данные и получить картину выделенных ресурсов и состояния их блокировок.
SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT --TOP 100 l.request_session_id AS spid, DB_NAME(l.resource_database_id) AS [database], CASE WHEN l.resource_type = 'OBJECT' THEN OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id) WHEN l.resource_associated_entity_id = 0 THEN 'NA' ELSE OBJECT_NAME(p.object_id, l.resource_database_id) END AS [object], p.index_id, l.resource_type AS [resource], l.resource_description AS [description], l.request_mode AS [mode], l.request_status AS [status], l.resource_associated_entity_id FROM sys.dm_tran_locks l LEFT JOIN sys.partitions p ON p.hobt_id = l.resource_associated_entity_id WHERE resource_type NOT LIKE 'DATABASE' --AND l.request_session_id = @@SPID -- <---- edit spid here --AND DB_NAME(l.resource_database_id) NOT LIKE 'distribution' |
При исполнении скрипта для тестового сценария при фильтрации по имени БД я вижу все вовлеченные процессы. Это четко показывает, что блокирующий процесс, spid 218, удерживает эксклюзивную (Х) блокировку ключа индекса (кластерного индекса в данном случае), и что это блокировка обновляемой строки. Эксклюзивная блокировка строки должна быть также помечена на уровне страницы, что и делается с помощью Intense Exclusive (IX) блокировки. Можно видеть, что это страница 154 в файле данных с номером 1. IX-блокировка страницы должна быть распространена до уровня таблицы, что и делается с помощью IX-блокировки этого объекта.
USE [master]; SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'TEST') CREATE DATABASE TEST; GO USE [TEST]; IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T1]') AND type in (N'U')) DROP TABLE [dbo].[T1]; GO USE [TEST]; IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T1]') AND type in (N'U')) DROP TABLE [dbo].[T1]; GO CREATE TABLE [dbo].[T1] ( [c1] [int] IDENTITY(1,1) NOT NULL, [c2] [int] NOT NULL, PRIMARY KEY CLUSTERED([c1] ) ); INSERT T1 (c2) VALUES (10); INSERT T1 (c2) VALUES (20); INSERT T1 (c2) VALUES (30); INSERT T1 (c2) VALUES (40); INSERT T1 (c2) VALUES (50); INSERT T1 (c2) VALUES (60); /* Blocker */ -- Выполняет UPDATE и оставляет транзакцию открытой, так что эксклюзивная (X) -- блокировка, получаемая процессом, не освобождается. USE [TEST]; |
BEGIN TRAN; UPDATE T1 SET c2 = 100 WHERE c1 = 5; --ROLLBACK /* Blocked */ USE [TEST]; SELECT * FROM TEST..T1 /* connection 1 */ ; USE [TEST]; SELECT * FROM TEST..T1 /* connection 2 */ ; USE [TEST]; SELECT * FROM TEST..T1 /* connection 3 */ ; USE [TEST]; SELECT * FROM TEST..T1 /* connection 4 */ ; USE [TEST]; SELECT * FROM TEST..T1 /* connection 5 */ ; |
Все остальные процессы, пытающиеся читать данные, имеют статус WAIT и ожидают освобождения ресурса, заблокированного выражением UPDATE.
DMV (Dynamic Management Views) и DMF (Dynamic Management Function), используемые хранимой процедурой, требуют, чтобы вызывающая их сторона имела права VIEW SERVER STATE и, конечно, право на запуск процедур из БД master (EXECUTE).
/* USE [master]; GRANT VIEW SERVER STATE TO Paul; GRANT EXECUTE ON sp_locks TO Paul; */ |
Ваши предложения и комментарии мы ожидаем по адресу: mag@rsdn.ru
Copyright ©
1994-2002 Оптим.ру