В MS SQL 2005 вместе с компонентом Service Broker появился новый способ наблюдения за событиями на сервере. Это объект уведомление о событиях. Данный объект создается на уровне сервера с привязкой к определенной базе данных. Объект подписывается на набор событий, о которых он получает уведомления в момент когда события происходят.
Особенность уведомлений о событиях состоит в том, что они интегрированы с Service Broker. В результате появляется возможность автоматического запуска своего кода в процедуре активации именно в тот момент, когда возникает событие. Для сохранения информации о событии такой подход гораздо удобнее чем периодическая проверка хранилища логов в файловой системе или где-то еще, как того требуют другие средства логирования (например, профайлер). Это создает сходство с ddl-триггерами, только тут нельзя делать откат. В качестве примера создадим уведомление о событии, которое позволяет автоматически сохранять в таблицах структурированные данные о графе взаимоблокировок. Для начала создадим базу данных с таблицами, в которых будут храниться наши логи.
Теперь создадим объекты брокера в базе данных. Понадобится создать только очередь и службу, так как для типов сообщений и контракта используются специальные системные объекты, имеющиеся в каждой базе данных.
1. Наполняем табличную переменную с перечнем участников взаимоблокировки.
2. Наполняем справочник значениями атрибутов участников.
3. Наполняем нормализованную таблицу dbo.DeadlockChains
4. Наполняем табличную переменную списков ресурсов с их блокировками от участников. При этом для простоты рассматриваем случай, когда ресурсы это строки или ключи.
5. Наполняем справочник атрибутами ресурсов
6. Наполняем нормализованную таблицу dbo.DeadlockRes. При этом для наполнения используем merge+output для сохранения соответствия узлов ресурсов и идентификаторов ресурсов.
7. Наполняем таблицы с владельцами и заблокированными соединениями.
Для конвертации xml-документа в таблицу используем метод nodes. Ниже приведен полный код хранимой процедуры.
Протестируем работу уведомления, создав классическую взаимоблокировку. Откроем два соединения. В первом запустим такой код:
Полный перечень наборов событий можно посмотреть в специальном системном представлении sys.event_notification_event_types. Тут есть много интересных событий. Например, можно получать уведомления при обнаружении поврежденной страницы в базе данных, нехватки памяти при операциях сортировки или построении hash-таблицы, изменении уровня потребления памяти сервером, расширении файлов базы, эскалации блокировок, изменении в доступах, настройках базы или сервера, наличии заблокированных соединений и многом другом.
Особенность уведомлений о событиях состоит в том, что они интегрированы с Service Broker. В результате появляется возможность автоматического запуска своего кода в процедуре активации именно в тот момент, когда возникает событие. Для сохранения информации о событии такой подход гораздо удобнее чем периодическая проверка хранилища логов в файловой системе или где-то еще, как того требуют другие средства логирования (например, профайлер). Это создает сходство с ddl-триггерами, только тут нельзя делать откат. В качестве примера создадим уведомление о событии, которое позволяет автоматически сохранять в таблицах структурированные данные о графе взаимоблокировок. Для начала создадим базу данных с таблицами, в которых будут храниться наши логи.
use master
go
if db_id ( N'LogStore' ) is null
begin
create database LogStore
on primary
(
name = LogStoreData,
filename = N'C:\В\DbData\LogStoreData.mdf',
size = 100 Mb,
filegrowth = 100 Mb,
maxsize = unlimited
)
log on
(
name = LogStoreLog,
filename = 'C:\В\TranLog\LogStoreLog.ldf',
size = 100 Mb,
filegrowth = 100 Mb,
maxsize = unlimited
)
end
go
use LogStore
go
-- Событие графа взаимоблокировки несет в себе информацию об участниках взаимоблокировки.
-- Каждый участник имеет набор атрибутов. Для их хранения создадим два справочника.
if object_id ( N'dbo.AttrTypes', N'U' ) is null
begin
create table dbo.AttrTypes
(
iAttrTpId smallint identity ( 1, 1 ) not null,
vcAttrTpName varchar ( 100 ) not null,
constraint PK_AttrTypes_iAttrTpId primary key clustered
( iAttrTpId asc ) on [primary],
constraint AK_AttrTypes_vcAttrTpName unique nonclustered
( vcAttrTpName asc ) on [primary]
) on [primary]
end
go
insert into dbo.AttrTypes ( vcAttrTpName )
values
( 'База данных' ),
( 'Логин' ),
( 'Хост' ),
( 'Уровень изоляции транзакций' ),
( 'Приложение' ),
( 'Файл базы данных' ),
( 'Объект' ),
( 'Индекс' ),
( 'Тип блокировки' )
go
if object_id ( N'dbo.Attrs', N'U' ) is null
begin
create table dbo.Attrs
(
iAttrId int identity ( 1, 1 ) not null,
iAttrTpId smallint not null,
vcAttrName varchar ( 100 ) not null,
constraint PK_Attrs_iAttrId primary key clustered
( iAttrId asc ) on [primary],
constraint AK_Attrs_vcAttrName_iAttrTpId unique nonclustered
( vcAttrName asc, iAttrTpId asc ) on [primary],
constraint FK_Attrs_iAttrTpId foreign key ( iAttrTpId )
references dbo.AttrTypes ( iAttrTpId ) on update cascade on delete no action
) on [primary]
end
go
-- Следующая таблица содержит одну строку на каждое событие. Здесь только идентификатор события и время.
if object_id ( N'dbo.Deadlocks', N'U' ) is null
begin
create table dbo.Deadlocks
(
iEventId int identity ( 1, 1 ) not null,
dtDate datetime not null,
constraint PK_Deadlocks_iEventId primary key clustered
( iEventId asc ) on [primary]
) on [primary]
end
go
-- Таблица с перечнем участников взаимоблокировки. В ней перечислены атрибуты участников, а также полем с признаком жертвы.
if object_id ( N'dbo.DeadlockChains', N'U' ) is null
begin
create table dbo.DeadlockChains
(
iEventId int not null,
iProcessId int identity ( 1, 1 ) not null,
vcProcessId varchar ( 20 ) not null,
bVictim bit not null,
vcResource varchar ( 100 ) not null,
iLoginId int not null,
iHostId int not null,
iIsolationId int not null,
iAppId int not null,
dtLastBatchStart datetime not null,
dtLastBathEnd datetime not null,
iLockId int not null,
vcBatch varchar ( 8000 ) not null,
constraint PK_DeadlockChains_iProcessId primary key clustered
( iProcessId asc ) on [primary],
constraint FK_DeadlockChains_iEventId foreign key ( iEventId )
references dbo.Deadlocks ( iEventId ) on update no action on delete no action,
constraint FK_DeadlockChains_iLoginId foreign key ( iLoginId )
references dbo.Attrs ( iAttrId ) on update no action on delete no action,
constraint FK_DeadlockChains_iHostId foreign key ( iHostId )
references dbo.Attrs ( iAttrId ) on update no action on delete no action,
constraint FK_DeadlockChains_iIsolationId foreign key ( iIsolationId )
references dbo.Attrs ( iAttrId ) on update no action on delete no action,
constraint FK_DeadlockChains_iAppId foreign key ( iAppId )
references dbo.Attrs ( iAttrId ) on update no action on delete no action,
constraint FK_DeadlockChains_iLockId foreign key ( iLockId )
references dbo.Attrs ( iAttrId ) on update no action on delete no action
) on [primary]
end
go
-- Здесь хранится перечень ресурсов, которые образовали цепочку.
if object_id ( N'dbo.DeadlockRes', N'U' ) is null
begin
create table dbo.DeadlockRes
(
iEventId int not null,
iResId int identity ( 1, 1 ) not null,
iDbId int not null,
iFileId int not null,
iPageId int not null,
iObjId int not null,
iIndId int not null,
iHobtId bigint not null,
constraint PK_DeadlockRes_iResId primary key clustered
( iResId asc ),
constraint FK_DeadlockRes_iEventId foreign key ( iEventId )
references dbo.Deadlocks ( iEventId ) on update cascade on delete no action,
constraint FK_DeadlockRes_iDbId foreign key ( iDbId )
references dbo.Attrs ( iAttrId ) on update no action on delete no action,
constraint FK_DeadlockRes_iFileId foreign key ( iFileId )
references dbo.Attrs ( iAttrId ) on update no action on delete no action,
constraint FK_DeadlockRes_iObjId foreign key ( iObjId )
references dbo.Attrs ( iAttrId ) on update no action on delete no action,
constraint FK_DeadlockRes_iIndId foreign key ( iIndId )
references dbo.Attrs ( iAttrId ) on update no action on delete no action
) on [primary]
end
go
-- У каждого ресурса может быть перечень владельцев.
if object_id ( N'dbo.ResOwners', N'U' ) is null
begin
create table dbo.ResOwners
(
iResId int not null,
iProcessId int not null,
iModeId int not null,
constraint PK_ResOwners_iResId_iProcessId primary key clustered
( iResId asc, iProcessId asc ) on [primary],
constraint FK_ResOwners_iResId foreign key ( iResId )
references dbo.DeadlockRes ( iResId ) on update cascade on delete no action,
constraint FK_ResOwners_iProcessId foreign key ( iProcessId )
references dbo.DeadlockChains ( iProcessId ) on update cascade on delete no action
) on [primary]
end
go
-- У каждого ресурса может быть перечень процессов, ожидающих получения блокировки на данный ресурс.
if object_id ( N'dbo.ResWaiters', N'U' ) is null
begin
create table dbo.ResWaiters
(
iResId int not null,
iProcessId int not null,
iModeId int not null,
constraint PK_ResWaiters_iResId_iProcessId primary key clustered
( iResId asc, iProcessId asc ) on [primary],
constraint FK_ResWaiters_iResId foreign key ( iResId )
references dbo.DeadlockRes ( iResId ) on update cascade on delete no action,
constraint FK_ResWaiters_iProcessId foreign key ( iProcessId )
references dbo.DeadlockChains ( iProcessId ) on update cascade on delete no action
) on [primary]
end
go
Теперь создадим объекты брокера в базе данных. Понадобится создать только очередь и службу, так как для типов сообщений и контракта используются специальные системные объекты, имеющиеся в каждой базе данных.
alter database LogStore set enable_broker with rollback immediate
go
if object_id ( N'dbo.LogDeadlockGraph', N'P' ) is null
begin
exec sp_executesql N'create proc dbo.LogDeadlockGraph as return'
end
go
if object_id ( N'dbo.Logger', N'SQ' ) is null
begin
create queue dbo.Logger
with
status = on,
retention = off,
activation
(
status = on,
procedure_name = dbo.LogDeadlockGraph,
max_queue_readers = 4,
exec as owner
)
end
go
if not exists
(
select *
from sys.services
where name = N'LogService'
)
begin
create service LogService
on queue dbo.Logger ( [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] )
end
go
if not exists
(
select *
from sys.server_event_notifications
where name = N'ServerLogger'
)
begin
create event notification ServerLogger
on server
for deadlock_graph
to service 'LogService', 'current database'
end
go
1. Наполняем табличную переменную с перечнем участников взаимоблокировки.
2. Наполняем справочник значениями атрибутов участников.
3. Наполняем нормализованную таблицу dbo.DeadlockChains
4. Наполняем табличную переменную списков ресурсов с их блокировками от участников. При этом для простоты рассматриваем случай, когда ресурсы это строки или ключи.
5. Наполняем справочник атрибутами ресурсов
6. Наполняем нормализованную таблицу dbo.DeadlockRes. При этом для наполнения используем merge+output для сохранения соответствия узлов ресурсов и идентификаторов ресурсов.
7. Наполняем таблицы с владельцами и заблокированными соединениями.
Для конвертации xml-документа в таблицу используем метод nodes. Ниже приведен полный код хранимой процедуры.
alter proc dbo.LogDeadlockGraph
as
begin
set nocount, xact_abort on
begin try
declare @xBody xml, @vcVictProcessId varchar ( 100 ), @dtEventTime datetime, @iEventId int, @vcSql nvarchar ( max ), @vcErr nvarchar ( max )
begin tran
;
receive top ( 1 ) @xBody = try_convert ( xml, message_body )
from dbo.Logger
if @@rowcount = 0
begin
return
end
declare @tblProcesses table
(
vcId varchar ( 100 ) not null,
vcResource varchar ( 100 ) not null,
vcLogin varchar ( 100 ) not null,
vcHost varchar ( 100 ) not null,
vcIsolation varchar ( 100 ) not null,
vcApp varchar ( 100 ) not null,
dtStart datetime not null,
dtEnd datetime not null,
vcBatch varchar ( 8000 ) not null,
vcLock varchar ( 100 ) not null
)
insert into @tblProcesses
(
vcId,
vcResource,
vcLogin,
vcHost,
vcIsolation,
vcApp,
dtStart,
dtEnd,
vcBatch,
vcLock
)
select
vcId = left ( isnull ( process.value ( '(@id)[1]', 'nvarchar ( max )' ), '' ), 100 ),
vcResource = left ( isnull ( process.value ( '(@waitresource)[1]', 'nvarchar ( max )' ), '' ), 100 ),
vcLogin = left ( isnull ( process.value ( '(@loginname)[1]', 'nvarchar ( max )' ), '' ), 100 ),
vcHost = left ( isnull ( process.value ( '(@hostname)[1]', 'nvarchar ( max )' ), '' ), 100 ),
vcIsolation = left ( isnull ( process.value ( '(@isolationlevel)[1]', 'nvarchar ( max )' ), '' ), 100 ),
vcApp = left ( isnull ( process.value ( '(@clientapp)[1]', 'nvarchar ( max )' ), '' ), 100 ),
dtStart = isnull ( process.value ( '(@lastbatchstarted)[1]', 'nvarchar ( max )' ), '1900-01-01' ),
dtEnd = isnull ( process.value ( '(@lastbatchcompleted)[1]', 'nvarchar ( max )' ), '1900-01-01' ),
vcBatch = left ( isnull ( process.value ( '(inputbuf)[1]', 'nvarchar ( max )' ), '' ), 8000 ),
vcLock = left ( isnull ( process.value ( '(@lockMode)[1]', 'nvarchar ( max )' ), '' ), 100 )
from @xBody.nodes ( '/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process' ) processes ( process )
;
with Attrs
as
(
select vcAttr, vcAttrTp
from
(
select distinct vcLogin, N'Логин' from @tblProcesses
union all
select distinct vcHost, N'Хост' from @tblProcesses
union all
select distinct vcIsolation, N'Уровень изоляции транзакций' from @tblProcesses
union all
select distinct vcApp, N'Приложение' from @tblProcesses
union all
select distinct vcLock, N'Тип блокировки' from @tblProcesses
) attrs ( vcAttr, vcAttrTp )
)
insert into dbo.Attrs ( iAttrTpId, vcAttrName )
select attrtp.iAttrTpId, newattrs.vcAttr
from
Attrs newattrs
inner join
dbo.AttrTypes attrtp on newattrs.vcAttrTp = attrtp.vcAttrTpName
left outer join
dbo.Attrs attrs on
attrs.iAttrTpId = attrtp.iAttrTpId and
attrs.vcAttrName = newattrs.vcAttr
where attrs.iAttrId is null
set @vcVictProcessId = @xBody.value ( '(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/@victim)[1]',
'nvarchar ( max )' )
set @dtEventTime = (
select dtEnd
from @tblProcesses
where vcId = @vcVictProcessId
)
insert into dbo.Deadlocks ( dtDate )
values ( @dtEventTime )
set @iEventId = scope_identity ()
;
with AttrData
as
(
select attrs.iAttrId, attrs.vcAttrName, attrtp.vcAttrTpName
from
dbo.Attrs attrs
inner join
dbo.AttrTypes attrtp on attrs.iAttrTpId = attrtp.iAttrTpId
)
insert into dbo.DeadlockChains
(
iEventId, vcProcessId, bVictim, vcResource, iLoginId, iHostId, iIsolationId, iAppId,
dtLastBatchStart, dtLastBathEnd, iLockId, vcBatch
)
select @iEventId, vcId, case when vcId = @vcVictProcessId then 1 else 0 end, vcResource,
attrlog.iAttrId, attrhost.iAttrId, attrlevel.iAttrId, attrapp.iAttrId, processes.dtStart, processes.dtEnd,
attrlock.iAttrId, processes.vcBatch
from
@tblProcesses processes
inner join
AttrData attrlog on processes.vcLogin = attrlog.vcAttrName and attrlog.vcAttrTpName = N'Логин'
inner join
AttrData attrhost on processes.vcHost = attrhost.vcAttrName and attrhost.vcAttrTpName = N'Хост'
inner join
AttrData attrlevel on processes.vcIsolation = attrlevel.vcAttrName and attrlevel.vcAttrTpName = N'Уровень изоляции транзакций'
inner join
AttrData attrapp on processes.vcApp = attrapp.vcAttrName and attrapp.vcAttrTpName = N'Приложение'
inner join
AttrData attrlock on processes.vcLock = attrlock.vcAttrName and attrlock.vcAttrTpName = N'Тип блокировки'
declare @tblResData table
(
iResId int not null,
iDbId int not null,
iPageId int not null,
iFileId int not null,
iHobtId bigint not null,
vcObj varchar ( 600 ) not null,
vcIndex varchar ( 200 ) not null,
vcMode varchar ( 100 ) not null,
vcOwner varchar ( 100 ) not null,
vcOwnerMode varchar ( 100 ) not null,
vcWaiter varchar ( 100 ) not null,
vcWaiterMode varchar ( 100 ) not null
)
set @vcSql = N'
select
ResData.iResId,
ResData.iDb,
ResData.iPage,
ResData.iFile,
ResData.iHobtId,
ResData.vcObj,
ResData.vcIndex,
ResData.vcMode,
vcOwner = left ( isnull ( own.value ( ''(/owner-list/owner/@id)[1]'', ''nvarchar ( max )'' ), '''' ), 100 ),
vcOwnerMode = left ( isnull ( own.value ( ''(/owner-list/owner/@mode)[1]'', ''nvarchar ( max )'' ), '''' ), 100 ),
vcWaiter = left ( isnull ( wait.value ( ''(/waiter-list/waiter/@id)[1]'', ''nvarchar ( max )'' ), '''' ), 100 ),
vcWaiterMode = left ( isnull ( wait.value ( ''(/waiter-list/waiter/@mode)[1]'', ''nvarchar ( max )'' ), '''' ), 100 )
from
(
select
iResId = row_number () over ( order by ( select 1 ) ),
iDb = isnull ( ResData.Res.value ( ''(@dbid)[1]'', ''int'' ), -1 ),
iPage = isnull ( ResData.Res.value ( ''(@pageid)[1]'', ''int'' ), -1 ),
iFile = isnull ( ResData.Res.value ( ''(@fileid)[1]'', ''int'' ), -1 ),
iHobtId = isnull ( ResData.Res.value ( ''(@hobtid)[1]'', ''bigint'' ), -1 ),
vcObj = left ( isnull ( ResData.Res.value ( ''(@objectname)[1]'', ''nvarchar ( max )'' ), '''' ), 100 ),
vcIndex = left ( isnull ( ResData.Res.value ( ''(@indexname)[1]'', ''nvarchar ( max )'' ), '''' ), 100 ),
vcMode = left ( isnull ( ResData.Res.value ( ''(@mode)[1]'', ''nvarchar ( max )'' ), '''' ), 100 ),
xOwner = ResData.Res.query ( ''owner-list'' ),
xWaiter = ResData.Res.query ( ''waiter-list'' )
from @xBody.nodes ( ' +
iif
(
@xBody.exist ( '/EVENT_INSTANCE/TextData/deadlock-list/deadlock/resource-list/ridlock' ) = 1,
'''/EVENT_INSTANCE/TextData/deadlock-list/deadlock/resource-list/ridlock''',
'''/EVENT_INSTANCE/TextData/deadlock-list/deadlock/resource-list/keylock'''
)
+ ' ) ResData ( Res )
) ResData
cross apply
ResData.xOwner.nodes ( ''.'' ) owners ( own )
cross apply
ResData.xWaiter.nodes ( ''.'' ) waiters ( wait )
'
insert into @tblResData
(
iResId,
iDbId,
iPageId,
iFileId,
iHobtId,
vcObj,
vcIndex,
vcMode,
vcOwner,
vcOwnerMode,
vcWaiter,
vcWaiterMode
) exec sp_executesql @vcSql, N'@xBody xml', @xBody = @xBody
;
with AttrData
as
(
select distinct vcAttrTpName, vcAttrName
from
(
select distinct 'База данных', isnull ( db_name ( iDbId ), '' ) from @tblResData
union all
select distinct 'Файл базы данных', isnull ( dbfil.name, '' )
from @tblResData fildata left outer join sys.master_files dbfil on
fildata.iDbId = dbfil.database_id and fildata.iFileId = dbfil.file_id
union all
select distinct 'Объект', vcObj from @tblResData
union all
select distinct 'Индекс', vcIndex from @tblResData
union all
select distinct 'Тип блокировки', vcMode from @tblResData
union all
select distinct 'Тип блокировки', vcOwnerMode from @tblResData
union all
select distinct 'Тип блокировки', vcWaiterMode from @tblResData
) attr ( vcAttrTpName, vcAttrName )
)
insert into dbo.Attrs ( iAttrTpId, vcAttrName )
select attrtp.iAttrTpId, attrdata.vcAttrName
from
AttrData attrdata
inner join
dbo.AttrTypes attrtp on attrdata.vcAttrTpName = attrtp.vcAttrTpName
left outer join
dbo.Attrs attr on
attr.iAttrTpId = attrtp.iAttrTpId and
attr.vcAttrName = attrdata.vcAttrName
where attr.iAttrId is null
declare @tblResIds table ( iResId int not null, iResNewId int not null )
;
with AttrData
as
(
select attrs.iAttrId, attrs.vcAttrName, attrtp.vcAttrTpName
from
dbo.Attrs attrs
inner join
dbo.AttrTypes attrtp on attrs.iAttrTpId = attrtp.iAttrTpId
)
merge dbo.DeadlockRes as trg
using
(
select iResId, iEventId = @iEventId, iDbId = attrdb.iAttrId, iFileId = attrfil.iAttrId,
res.iPageId, iObjId = attrobj.iAttrId, iIndId = attrind.iAttrId, res.iHobtId
from
(
select distinct
iResId,
iDbId,
iPageId,
iFileId,
iHobtId,
vcObj,
vcIndex,
vcMode
from @tblResData
) res
inner join
AttrData attrdb on attrdb.vcAttrTpName = N'База данных' and attrdb.vcAttrName = isnull ( db_name ( res.iDbId ), '' )
inner join
AttrData attrobj on attrobj.vcAttrTpName = N'Объект' and attrobj.vcAttrName = res.vcObj
inner join
AttrData attrind on attrind.vcAttrTpName = N'Индекс' and attrind.vcAttrName = res.vcIndex
left outer join
sys.master_files fil on
fil.database_id = res.iDbId and
fil.file_id = res.iFileId
inner join
AttrData attrfil on attrfil.vcAttrTpName = N'Файл базы данных' and attrfil.vcAttrName = isnull ( fil.name, '' )
) as src on 1 = 0
when not matched then insert ( iEventId, iDbId, iFileId, iPageId, iObjId, iIndId, iHobtId )
values ( src.iEventId, src.iDbId, src.iFileId, src.iPageId, src.iObjId, src.iIndId, src.iHobtId )
output src.iResId, inserted.iResId
into @tblResIds ( iResId, iResNewId )
;
insert into dbo.ResOwners ( iResId, iProcessId, iModeId )
select resids.iResNewId, pr.iProcessId, attr.iAttrId
from
@tblResData resdata
inner join
@tblResIds resids on resdata.iResId = resids.iResId
inner join
dbo.DeadlockChains pr on
pr.iEventId = @iEventId and
pr.vcProcessId = resdata.vcOwner
inner join
(
select attr.iAttrId, attr.vcAttrName
from
dbo.Attrs attr
inner join
dbo.AttrTypes attrtp on
attr.iAttrTpId = attrtp.iAttrTpId and
attrtp.vcAttrTpName = 'Тип блокировки'
) attr on resdata.vcOwnerMode = attr.vcAttrName
insert into dbo.ResWaiters ( iResId, iProcessId, iModeId )
select resids.iResNewId, pr.iProcessId, attr.iAttrId
from
@tblResData resdata
inner join
@tblResIds resids on resdata.iResId = resids.iResId
inner join
dbo.DeadlockChains pr on
pr.iEventId = @iEventId and
pr.vcProcessId = resdata.vcWaiter
inner join
(
select attr.iAttrId, attr.vcAttrName
from
dbo.Attrs attr
inner join
dbo.AttrTypes attrtp on
attr.iAttrTpId = attrtp.iAttrTpId and
attrtp.vcAttrTpName = 'Тип блокировки'
) attr on resdata.vcWaiterMode = attr.vcAttrName
commit tran
end try
begin catch
if xact_state () <> 0
begin
rollback tran
end
set @vcErr = concat ( error_message (), ' ', error_number (), ' ', error_line () )
; throw 60000, @vcErr, 1
end catch
end
go
Протестируем работу уведомления, создав классическую взаимоблокировку. Откроем два соединения. В первом запустим такой код:
create table ##data1 ( i int )
create table ##data2 ( i int )
begin tran
insert into ##data1 ( i )
values ( 1 )
begin tran
insert into ##data2 ( i )
values ( 1 )
update ##data2
set i += 1
update ##data1
set i += 1
declare @iEventId int = 1
select evn.vcProcessId, evn.bVictim, evn.vcResource,
vcLogin = attrlog.vcAttrName,
vcHost = attrhost.vcAttrName,
vcIsolation = attrisol.vcAttrName,
vcApp = attrapp.vcAttrName,
evn.dtLastBatchStart, evn.dtLastBathEnd,
vcLock = attrlock.vcAttrName,
evn.vcBatch
from
dbo.DeadlockChains evn
inner join
dbo.Attrs attrlog on evn.iLoginId = attrlog.iAttrId
inner join
dbo.Attrs attrhost on evn.iHostId = attrhost.iAttrId
inner join
dbo.Attrs attrisol on evn.iIsolationId = attrisol.iAttrId
inner join
dbo.Attrs attrapp on evn.iAppId = attrapp.iAttrId
inner join
dbo.Attrs attrlock on evn.iLockId = attrlock.iAttrId
where evn.iEventId = @iEventId
select vcDb = attrdb.vcAttrName, vcDbFile = attrfil.vcAttrName, res.iPageId, res.iHobtId,
vcObj = attrobj.vcAttrName, vcIndex = attrind.vcAttrName,
vcOwner = resowns.vcProcessId, vcOwnerLockMode = lockown.vcAttrName, vcWaiter = reswait.vcProcessId,
vcWaiterLockMode = lockwait.vcAttrName
from
dbo.DeadlockRes res
inner join
dbo.Attrs attrdb on res.iDbId = attrdb.iAttrId
inner join
dbo.Attrs attrfil on res.iFileId = attrfil.iAttrId
inner join
dbo.Attrs attrobj on res.iObjId = attrobj.iAttrId
inner join
dbo.Attrs attrind on res.iIndId = attrind.iAttrId
inner join
dbo.ResOwners own on res.iResId = own.iResId
inner join
dbo.DeadlockChains resowns on
resowns.iEventId = res.iEventId and
own.iProcessId = resowns.iProcessId
inner join
dbo.ResWaiters wait on res.iResId = wait.iResId
inner join
dbo.DeadlockChains reswait on
reswait.iEventId = res.iEventId and
wait.iProcessId = reswait.iProcessId
inner join
dbo.Attrs lockown on own.iModeId = lockown.iAttrId
inner join
dbo.Attrs lockwait on wait.iModeId = lockwait.iAttrId
where res.iEventId = @iEventId
Полный перечень наборов событий можно посмотреть в специальном системном представлении sys.event_notification_event_types. Тут есть много интересных событий. Например, можно получать уведомления при обнаружении поврежденной страницы в базе данных, нехватки памяти при операциях сортировки или построении hash-таблицы, изменении уровня потребления памяти сервером, расширении файлов базы, эскалации блокировок, изменении в доступах, настройках базы или сервера, наличии заблокированных соединений и многом другом.
