Как узнать, что блокирует мои таблицы?

Ответ 1

Взгляните на следующие системные хранимые процедуры, которые вы можете запустить в SQLServer Management Studio (SSMS):

• sp_who
• sp_lock

Кроме того, в SSMS вы можете просматривать блокировки и процессы по-разному:

Различные версии SSMS помещают монитор активности в разные места. Например, SSMS 2008 и 2012 имеют это в контекстном меню, когда вы щелкаете правой кнопкой мыши на сервере node.

Ответ 2

Чтобы перейти непосредственно к тому, "кто заблокирован/заблокирован", я объединил/сократил sp_who и sp_lock в один запрос, который дает хороший обзор того, у кого какой объект заблокирован до какого уровня.

--Create Procedure WhoLock
--AS
set nocount on
if object_id('tempdb..#locksummary') is not null Drop table #locksummary
if object_id('tempdb..#lock') is not null Drop table #lock
create table #lock (    spid int,    dbid int,    objId int,    indId int,    Type char(4),    resource nchar(32),    Mode char(8),    status char(6))
Insert into #lock exec sp_lock
if object_id('tempdb..#who') is not null Drop table #who
create table #who (     spid int, ecid int, status char(30),
            loginame char(128), hostname char(128),
            blk char(5), dbname char(128), cmd char(16)
            --
            , request_id INT --Needed for SQL 2008 onwards
            --
         )
Insert into #who exec sp_who
Print '-----------------------------------------'
Print 'Lock Summary for ' + @@servername  + ' (excluding tempdb):'
Print '-----------------------------------------' + Char(10)
Select     left(loginame, 28) as loginame, 
    left(db_name(dbid),128) as DB,
    left(object_name(objID),30) as object,
    max(mode) as [ToLevel],
    Count(*) as [How Many],
    Max(Case When mode= 'X' Then cmd Else null End) as [Xclusive lock for command],
    l.spid, hostname
into #LockSummary
from #lock l join #who w on l.spid= w.spid
where dbID != db_id('tempdb') and l.status='GRANT'
group by dbID, objID, l.spid, hostname, loginame

Select * from #LockSummary order by [ToLevel] Desc, [How Many] Desc, loginame, DB, object

Print '--------'
Print 'Who is blocking:'
Print '--------' + char(10)
SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, p.loginame
, convert(char(12), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM      master..sysprocesses p
JOIN      master..sysdatabases d ON p.dbid =  d.dbid
WHERE     EXISTS (  SELECT 1
          FROM      master..sysprocesses p2
          WHERE     p2.blocked = p.spid )

Print '--------'
Print 'Details:'
Print '--------' + char(10)
Select     left(loginame, 30) as loginame,  l.spid,
    left(db_name(dbid),15) as DB,
    left(object_name(objID),40) as object,
    mode ,
    blk,
    l.status
from #lock l join #who w on l.spid= w.spid
where dbID != db_id('tempdb') and blk <>0
Order by mode desc, blk, loginame, dbID, objID, l.status

(Что означают сокращения уровня блокировки, см., Например, https://technet.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx)

Скопировано из: sp_WhoLock - хранимый процесс T-SQL, объединяющий sp_who и sp_lock...

Примечание: столбец [Xclusive lock for command] может вводить в заблуждение - он показывает текущую команду для этого spid; но блокировка X могла быть вызвана более ранней командой в транзакции.

Ответ 3

У меня есть хранимая процедура, которую я собрал вместе, которая касается не только блокировок и блокировок, но и того, что работает на сервере. Я поместил его в мастера. Я поделюсь им с вами, код ниже:

USE [master]
go


CREATE PROCEDURE [dbo].[sp_radhe] 

AS
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


-- the current_processes
-- marcelo miorelli 
-- CCHQ 
-- 04 MAR 2013 Wednesday

SELECT es.session_id AS session_id
,COALESCE(es.original_login_name, '') AS login_name
,COALESCE(es.host_name,'') AS hostname
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,es.status
,COALESCE(er.blocking_session_id,0) AS blocked_by
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,coalesce(db_name(er.database_id),'No Info') as dbid
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,sql_text=st.text
,transaction_isolation =
CASE es.transaction_isolation_level
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'Read Uncommitted'
    WHEN 2 THEN 'Read Committed'
    WHEN 3 THEN 'Repeatable'
    WHEN 4 THEN 'Serializable'
    WHEN 5 THEN 'Snapshot'
END
,COALESCE(es.cpu_time,0) 
    + COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0) 
    + COALESCE(es.writes,0) 
    + COALESCE(er.reads,0) 
+ COALESCE(er.writes,0) AS physical_io
,COALESCE(er.open_transaction_count,-1) AS open_tran
,COALESCE(es.program_name,'') AS program_name
,es.login_time
FROM sys.dm_exec_sessions es
LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id
LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
where es.is_user_process = 1 
  and es.session_id <> @@spid
  and es.status = 'running'
ORDER BY es.session_id

end 

GO

эта процедура сделала очень хорошо для меня в последние пару лет. для запуска просто введите sp_radhe

Что касается размещения sp_radhe в основной базе данных

Я использую следующий код и делаю его системной хранимой процедурой

exec sys.sp_MS_marksystemobject 'sp_radhe'

как вы можете видеть по ссылке ниже

Создание собственных хранимых процедур системы SQL Server

Что касается уровня изоляции транзакций

Вопросы об уровнях изоляции транзакций T-SQL, которые вы слишком стеснялись спросить

Джонатан Кехайяс

После изменения уровня изоляции транзакции он изменяется только тогда, когда область действия завершается в конце процедуры или обратный вызов, или если вы меняете его явно снова, используя УРОВЕНЬ ИЗОЛЯЦИИ SET TRANSACTION.

Кроме того, УРОВЕНЬ ИЗОЛЯЦИИ ОПЕРАЦИИ ДОЛЖЕН только хранимую процедуру, поэтому вы можете иметь несколько вложенных хранимых процедур которые выполняются на своих собственных уровнях изоляции.

Ответ 4

exec sp_lock

Этот запрос должен предоставить вам существующие блокировки.

exec sp_who SPID -- will give you some info

Имея spids, вы можете проверить мониторинг активности (вкладка процессов), чтобы выяснить, какие процессы блокируют таблицы ( "подробности" для получения дополнительной информации и "процесс уничтожения", чтобы убить его).

Ответ 5

Вы также можете использовать sp_who2, который дает больше информации

Вот некоторая информация http://dbadiaries.com/using-sp_who2-to-help-with-sql-server-troubleshooting

Ответ 6

Это должно дать вам все детали существующих замков. Я получил часть креста, но я забыл, где она. Если вы заметили похожий пост, отредактируйте этот ответ и добавьте ссылку для меня. Спасибо.

DECLARE @tblVariable TABLE(SPID INT, Status VARCHAR(200), [Login] VARCHAR(200), HostName VARCHAR(200), 
    BlkBy VARCHAR(200), DBName VARCHAR(200), Command VARCHAR(200), CPUTime INT, 
    DiskIO INT, LastBatch VARCHAR(200), ProgramName VARCHAR(200), _SPID INT, 
    RequestID INT)

INSERT INTO @tblVariable
EXEC Master.dbo.sp_who2

SELECT v.*, t.TEXT 
FROM @tblVariable v
INNER JOIN sys.sysprocesses sp ON sp.spid = v.SPID
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS t
ORDER BY BlkBy DESC, CPUTime DESC

Затем вы можете с осторожностью убить SPID, который блокирует вашу таблицу.

kill 104 -- Your SPID

Ответ 7

Поворот сюжета!

У вас могут быть потерянные распределенные транзакции с эксклюзивными блокировками, и вы их не увидите, если ваш сценарий предполагает, что с транзакцией связан сеанс (нет!). Запустите приведенный ниже сценарий, чтобы определить эти транзакции:

;WITH ORPHANED_TRAN AS (
SELECT
    dat.name,
    dat.transaction_uow,
    ddt.database_transaction_begin_time,
    ddt.database_transaction_log_bytes_reserved,
    ddt.database_transaction_log_bytes_used
FROM
    sys.dm_tran_database_transactions ddt,
    sys.dm_tran_active_transactions dat,
    sys.dm_tran_locks dtl
WHERE
    ddt.transaction_id = dat.transaction_id AND
    dat.transaction_id = dtl.request_owner_id AND
    dtl.request_session_id = -2 AND
    dtl.request_mode = 'X'
)
SELECT DISTINCT * FROM ORPHANED_TRAN

После того как вы определили транзакцию, используйте столбецaction_uow, чтобы найти ее в MSDTC и решить, следует ли ее прервать или зафиксировать. Если транзакция помечена как сомнительная (с вопросительным знаком рядом с ней), вы, вероятно, захотите ее прервать.

Вы также можете убить единицу работы (UOW), указав транзакцию_uow в команде KILL:

KILL '<transaction_uow>'

Ссылки:

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/kill-transact-sql?view=sql-server-2017#arguments

https://www.mssqltips.com/sqlservertip/4142/how-to-kill-a-blocking-negative-spid-in-sql-server/

Ответ 8

Согласно официальным документам sp_lock помечен как устаревший:

Эта функция находится в режиме обслуживания и может быть удалена в будущей версии Microsoft SQL Server. Избегайте использования этой функции в новых разработках и планируйте модифицировать приложения, которые в настоящее время используют эту функцию.

и вместо этого рекомендуется использовать sys.dm_tran_locks. Этот динамический объект управления возвращает информацию о текущих активных ресурсах менеджера блокировок. Каждая строка представляет текущий активный запрос к администратору блокировки для блокировки, которая была предоставлена или ожидает, чтобы быть предоставленной.

Как правило, он возвращает больше деталей в более удобном для пользователя синтаксисе, чем sp_lock.

Процедура whoisactive, написанная Адамом Мачаничем, очень хороша для проверки текущей активности в вашей среде и просмотра того, какие типы ожидания/блокировки замедляют ваши запросы. Вы можете очень легко найти то, что блокирует ваши запросы и множество другой полезной информации.

Например, допустим, у нас есть следующие запросы, работающие на уровне изоляции SQL Server по умолчанию - Read Committed. Каждый запрос выполняется в отдельном окне запроса:

-- creating sample data
CREATE TABLE [dbo].[DataSource]
(
    [RowID] INT PRIMARY KEY
   ,[RowValue] VARCHAR(12)
);

INSERT INTO [dbo].[DataSource]([RowID], [RowValue])
VALUES (1,  'samle data');

-- query window 1
BEGIN TRANSACTION;

    UPDATE [dbo].[DataSource]
    SET [RowValue] = 'new data'
    WHERE [RowID] = 1;

--COMMIT TRANSACTION;

-- query window 2
SELECT *
FROM [dbo].[DataSource];

Затем выполните sp_whoisactive (sp_whoisactive только часть столбцов):

Вы можете легко увидеть сеанс, который блокирует SELECT и даже его код T-SQL. Процедура имеет много параметров, поэтому вы можете проверить документы для более подробной информации.

Если мы sys.dm_tran_locks представление sys.dm_tran_locks мы увидим, что один из сеансов ожидает блокировки ресурса, который имеет эксклюзивную блокировку другим сеансом: