Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: Помогите понять информацию по блокировке..  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31326
MAULER
Но когда начинаю анализировать блокировку (предварительно поймав её), spid блокировки остаётся прежним, ведь она ещё висит!

а вот blocked (id блокирующего процесса) может уже 20 раз измениться. И вроде бы я смотрю правильный spid в sysprocesses, а данные в нём уже совсем от другой активности.
А, понятно.
У вас в джобе регулярно выполняется некое удаление. Разумеется, оно часто блокируется, как же иначе? Но блокируется на короткое время, что не является проблемрой, а специально так задумано, во имя обеспечения целостности и атомарности БД.

Просто у вас неправильный скрипт выявления блокировок. Нужно выявлять длительные блокировки, это действительно тревожный признак (хотя иногда это неизбежно), а не короткие.

Я то думал, что у вас висят процессы, всё заблокировано надолго, а в итоге оказался прав Гавриленко Сергей Алексеевич :-)
Гавриленко Сергей Алексеевич
А с чего вы вообще взяли, что в вашей ситуации что-то не так?
1 ноя 18, 16:12    [21721721]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
MAULER
Member

Откуда:
Сообщений: 882
Владислав Колосов,

Блокировка не исчезает, а может висеть сколь угодно долго.
1 ноя 18, 16:12    [21721724]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
MAULER
Member

Откуда:
Сообщений: 882
alexeyvg
MAULER
Но когда начинаю анализировать блокировку (предварительно поймав её), spid блокировки остаётся прежним, ведь она ещё висит!

а вот blocked (id блокирующего процесса) может уже 20 раз измениться. И вроде бы я смотрю правильный spid в sysprocesses, а данные в нём уже совсем от другой активности.
А, понятно.
У вас в джобе регулярно выполняется некое удаление. Разумеется, оно часто блокируется, как же иначе? Но блокируется на короткое время, что не является проблемрой, а специально так задумано, во имя обеспечения целостности и атомарности БД.

Просто у вас неправильный скрипт выявления блокировок. Нужно выявлять длительные блокировки, это действительно тревожный признак (хотя иногда это неизбежно), а не короткие.

Я то думал, что у вас висят процессы, всё заблокировано надолго, а в итоге оказался прав Гавриленко Сергей Алексеевич :-)
Гавриленко Сергей Алексеевич
А с чего вы вообще взяли, что в вашей ситуации что-то не так?


Так мой скрипт, и выявляет длительную блокировку, как раз она может висеть очень долго. Просто у блокировки, среди прочих полей, есть два поля: spid и blocked. Значения у них разные.

Разработчики запросили код не блокируемой транзакции (spid) а блокирующей (я так понимаю, что это blocked?!)
1 ноя 18, 16:17    [21721729]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31326
MAULER
Так мой скрипт, и выявляет длительную блокировку, как раз она может висеть очень долго. Просто у блокировки, среди прочих полей, есть два поля: spid и blocked. Значения у них разные.
Вы же пишите, что "а вот blocked (id блокирующего процесса) может уже 20 раз измениться"

С чего же вы взяли, что она длительная?
Этот блокируемый процесс тыщу раз заблокировали-отпустили, пока вы смотрели.

Длительная - это когда как минимум spid и blocked не меняются, причём даже это не гарантирует, потому что в промежутке они могли разблокироваться, поработать, и снова заблокироваться. Нужно смотреть где то в sys.dm_tran_locks
1 ноя 18, 17:14    [21721782]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
MAULER
Member

Откуда:
Сообщений: 882
alexeyvg,

Абсолютно точно spid не меняется. Запись висит в блокировках в мониторе и в

SELECT * from [master].[dbo].[sysprocesses]
WHERE blocked > 0


с одним и тем же spid-ом.
1 ноя 18, 17:33    [21721794]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
MAULER
Member

Откуда:
Сообщений: 882
в sys.dm_tran_locks
висит та же самая с тем же spid что и в sysprocesses
1 ноя 18, 17:34    [21721795]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7641
MAULER,

какая-то путаница в показаниях... А это о чем Вы написали?
Проблема в том, что если смотреть процесс в dbo.sysprocesses по spid = @blocked (из первого запроса)
то он (как я понимаю первопричина) - не заблокирован.
и INPUTBUFFER такого процесса постоянно меняется!


то меняется, то не меняется.
1 ноя 18, 17:37    [21721800]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
MAULER
Member

Откуда:
Сообщений: 882
Владислав Колосов,

Ну а как я найду содержимое INPUTBUFFER зная blocked?! (т.е. блокирующего процесса)

Я зайду в sysprocesses и найду там запись с spid = blocked (из первой записи)
1 ноя 18, 17:53    [21721812]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
MAULER
Member

Откуда:
Сообщений: 882
потом этот spid подставлю в DBCC INPUTBUFFER и получу запрос который был причиной блокировки. spid которого потом попал в blocked заблокированного процесса.
1 ноя 18, 17:56    [21721814]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
invm
Member

Откуда: Москва
Сообщений: 9263
MAULER
Проблема в том, что если смотреть процесс в dbo.sysprocesses по spid = @blocked (из первого запроса)
то он (как я понимаю первопричина) - не заблокирован.
и INPUTBUFFER такого процесса постоянно меняется!
Вполне вероятно, что в блокирующей сессии есть незакрытая или долгоиграющая транзакция.
Проверить это можно так
select * from sys.dm_tran_session_transactions where session_id = @blocking_session_id;
1 ноя 18, 17:56    [21721815]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
MAULER
Member

Откуда:
Сообщений: 882
invm
MAULER
Проблема в том, что если смотреть процесс в dbo.sysprocesses по spid = @blocked (из первого запроса)
то он (как я понимаю первопричина) - не заблокирован.
и INPUTBUFFER такого процесса постоянно меняется!
Вполне вероятно, что в блокирующей сессии есть незакрытая или долгоиграющая транзакция.
Проверить это можно так
select * from sys.dm_tran_session_transactions where session_id = @blocking_session_id;


Вот очень на это похоже!!
1 ноя 18, 17:59    [21721820]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
архивариус
Member

Откуда:
Сообщений: 150
MAULER,

USE master;

select
t1.resource_type as [lock type]
,db_name(resource_database_id) as [database]
,t1.resource_associated_entity_id as [blk object]
,t1.request_mode as [lock req] -- lock requested
,t1.request_session_id as [waiter sid] -- spid of waiter
,t2.wait_duration_ms as [wait time]
,(select text from sys.dm_exec_requests as r --- get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as waiter_batch
,(select substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2)
from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) as waiter_stmt --- this is the statement executing right now
,t2.blocking_session_id as [blocker sid] -- spid of blocker
,(select text from sys.sysprocesses as p --- get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id) as blocker_stmt
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address;
GO


может поможет чем
1 ноя 18, 18:00    [21721821]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
MAULER
Member

Откуда:
Сообщений: 882
архивариус
MAULER,

USE master;

select
t1.resource_type as [lock type]
,db_name(resource_database_id) as [database]
,t1.resource_associated_entity_id as [blk object]
,t1.request_mode as [lock req] -- lock requested
,t1.request_session_id as [waiter sid] -- spid of waiter
,t2.wait_duration_ms as [wait time]
,(select text from sys.dm_exec_requests as r --- get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as waiter_batch
,(select substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2)
from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) as waiter_stmt --- this is the statement executing right now
,t2.blocking_session_id as [blocker sid] -- spid of blocker
,(select text from sys.sysprocesses as p --- get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id) as blocker_stmt
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address;
GO


может поможет чем


Благодарю. Завтра отпишусь!
1 ноя 18, 18:04    [21721826]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7641
MAULER,

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

CREATE VIEW dbo.ViewBlocks
AS
WITH tbl1
	 AS (SELECT t1.spid,
				t1.blocked,
				SQLtext.text
		 FROM [master].sys.sysprocesses t1
		 CROSS APPLY sys.dm_exec_sql_text(t1.sql_handle) SQLtext
		),
	 tbl2
	 AS (
	 SELECT tbl1.spid,
			tbl1.blocked,
			tbl1.spid head_session_id,
			tbl1.text
	 FROM tbl1 tbl1
	 WHERE tbl1.blocked = 0
	 UNION ALL
	 SELECT tbl1.spid,
			tbl1.blocked,
			tbl2.head_session_id,
			tbl1.text
	 FROM tbl1 tbl1
	 INNER JOIN tbl2 ON tbl2.spid = tbl1.blocked)
	 SELECT tbl2.spid,
			tbl2.blocked,
			case WHEN tbl2.head_session_id = tbl2.spid then 0 else tbl2.head_session_id END head_session_id,
			tbl2.text
	 FROM tbl2 tbl2;
1 ноя 18, 18:38    [21721853]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
MAULER
Member

Откуда:
Сообщений: 882
Доброе утро.

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

DECLARE deleteCursor CURSOR FAST_FORWARD FOR 
 select name from @tblList order by level, name

  OPEN deleteCursor

 FETCH NEXT FROM deleteCursor 
  INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

....
....

В блоке try-catch происходит:
1) "Конфликт инструкции DELETE с ограничением REFERENCE"
2) по ветке catch происходит генерация RAISEERROR...
3) и return

....
....

    FETCH NEXT FROM deleteCursor 
	INTO @name
	
END 

CLOSE deleteCursor
DEALLOCATE deleteCursor


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

Я так понимаю, что что-то происходит с переменной @@FETCH_STATUS, которая всегда = 0...
Или я неправильно понимаю ситуацию?
2 ноя 18, 08:45    [21722110]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31326
MAULER
Я так понимаю, что что-то происходит с переменной @@FETCH_STATUS, которая всегда = 0...
В документации указано, что @@FETCH_STATUS в таком случае неопределён. Может, это и 0, почему бы и нет?
2 ноя 18, 09:10    [21722123]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31326
MAULER
т.е. освобождения курсора не происходит.
Вообще нужно всегда использовать курсор типа LOCAL, другие варианты ИМХО экзотика.
2 ноя 18, 09:15    [21722132]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7641
Глобальные курсоры используются в очень специфических случаях, посему измените настройку базы по умолчанию на LOCAL курсор и спите спокойно.
2 ноя 18, 11:27    [21722309]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Владислав Колосов,

LOCAL И STATIC это деволт при не указанном явно
2 ноя 18, 11:30    [21722319]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7641
TaPaK,

вот эта настройка:

ALTER DATABASE [db1] SET CURSOR_DEFAULT LOCAL WITH NO_WAIT
2 ноя 18, 11:41    [21722327]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Владислав Колосов
TaPaK,

вот эта настройка:

ALTER DATABASE [db1] SET CURSOR_DEFAULT LOCAL WITH NO_WAIT

это понятно, кто-то меняет на иное?
2 ноя 18, 11:43    [21722330]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7641
TaPaK,

по умолчанию новая база создаётся с GLOBAL настройкой, насколько я вижу.
2 ноя 18, 11:50    [21722348]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7641
Может быть у Вас этот парамер изменён в базе model?
2 ноя 18, 11:53    [21722357]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Владислав Колосов
TaPaK,

по умолчанию новая база создаётся с GLOBAL настройкой, насколько я вижу.

день прожит не зря :)
2 ноя 18, 12:01    [21722370]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять информацию по блокировке..  [new]
MAULER
Member

Откуда:
Сообщений: 882
alexeyvg,
Я так понимаю, что "рвать по живому" при помощи "return" не стоит?!
Возможно, правильнее, в моём случае, присваивать какой-нибудь переменной "RolbackStatus" значение 1 после отката транзакции. Затем сравнивать её в While вместе с @@FETCHSTATUS ?
2 ноя 18, 12:18    [21722398]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить