Блокировки в СУБД, влияние на работу пользователей, получение информации о блокировках в MS SQL

Довольно часто блокировки в СУБД доставляют и администраторам, и пользователям немало хлопот. Особенно часто эти неприятности проявляются при неверсионном редактировании из ArcGIS Desktop: если пользователь отредактировал объект и не сохранил изменения, объект остается заблокированным и другие пользователи не могут изменить этот объект.
Отчасти, проблема решается установкой опции «Сохранять изменения сразу после редактирования», которая находится в опциях редактирования, на закладке «Работа с версиями» (там же, где устанавливается режим неверсионного редактирования). Эта опция появилась в версии 10.2 и по умолчанию она включена. Так что если ее оставить включенной по умолчанию, то вернуть состояние данных к тому, которое было до редактирования в версии 10.2 и выше не получится. Обращайте внимание на эту особенность! Естественно, при включенной опции, при окончании сессии редактирования вопроса «Сохранить изменения?» вы не увидите.
Если опцию «Сохранять изменения сразу после редактирования» выключать, то измененные объекты остаются заблокированными пользователем до сохранения изменений или до завершения редактирования, и другие пользователи не могут их изменить. К сожалению, просмотреть идентификаторы заблокированных объектов не получилось, как средствами ArcGIS, так и (как это выяснилось) средствами MS SQL. Средства ArcGIS Desktop позволяют проконтролировать блокировки структуры данных.
Средствами СУБД можно увидеть количество заблокированных объектов. Например, это можно сделать с помощью приведенный ниже скрипта, который был практически без изменений скопирован из источника. Данный скрипт приведен на таком большом количестве сайтов, что даже непонятно, кому говорить спасибо. И почти на всех сайтах в этом скрипте есть одна маленькая ошибка, в 10 строке, в названии таблицы. Похоже, эта ошибка сделана специально, для включения мозга читателей. Я эту ошибку исправил.
Скрипт выводит список и количество блокировок в БД MS SQL.
Если обратиться к документации MS SQL, то мы увидим, что существуют различные виды блокировок:

· RID – single row lock

· KEY – a range of keys in an index

· PAG – data or index page lock

· EXT – Extent Lock

· TAB – Table Lock

· DB – Database Lock

И различные режимы блокировок:

· S – Shared lock

· U – Update Lock

· X – Exclusive lock

· IS – Intent shared

· IU – Intent Update

· IX – Intent Exclusive

· BU – Bulk update

Если проанализировать результаты работы скрипта, то можно увидеть, что RID-блокировки (то есть, блокировки на строки, то есть именно на объекты классов пространственных объектов) создаются в БД TEMPDB и их количество совпадает с количеством отредактированных (без сохранения) объектов. Блокировки в той базе, где находятся редактируемые данные накладываются на страницы данных. Таким образом получить идентификаторы заблокированных объектов не представляется возможным. По крайней мере, с помощью данного инструмента и используемой в нем хранимой процедуры sp_lock. Но тем не менее, получить информацию о блокировках в СУБД, количестве заблокированных объектов данный скрипт позволяет.

SET NOCOUNT ON
GO

— Count the locks

IF EXISTS ( SELECT Name
FROM tempdb..sysobjects
WHERE name LIKE ‘#Hold_sp_lock%’ )
—If So Drop it
DROP TABLE #Hold_sp_lock
GO

CREATE TABLE #Hold_sp_lock
(
spid INT,
dbid INT,
ObjId INT,
IndId SMALLINT,
Type VARCHAR(20),
Resource VARCHAR(50),
Mode VARCHAR(20),
Status VARCHAR(20)
)
INSERT INTO #Hold_sp_lock
EXEC sp_lock
SELECT COUNT(spid) AS lock_count,
SPID,
Type,
Cast(DB_NAME(DBID) as varchar(30)) as DBName,
mode
FROM #Hold_sp_lock
GROUP BY SPID,
Type,
DB_NAME(DBID),
MODE
Order by lock_count desc,
DBName,
SPID,
MODE

—Show any blocked or blocking processes

IF EXISTS ( SELECT Name
FROM tempdb..sysobjects
Where name like ‘#Catch_SPID%’ )
—If So Drop it
DROP TABLE #Catch_SPID
GO
Create Table #Catch_SPID
(
bSPID int,
BLK_Status char(10)
)
GO
Insert into #Catch_SPID
Select Distinct
SPID,
‘BLOCKED’
from master..sysprocesses
where blocked <> 0
UNION
Select Distinct
blocked,
‘BLOCKING’
from master..sysprocesses
where blocked <> 0