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

Откуда:
Сообщений: 1446
Есть таблица:

CREATE TABLE dbo.[charge](
	id [bigint] NULL,
	[spid] [smallint] NOT NULL
) ON [data]

GO

ALTER TABLE dbo.[charge] ADD  CONSTRAINT [df_charge_spid]  DEFAULT (@@spid) FOR [spid]
GO


Кластерный индекс (только один):

/****** Object:  Index [idx_charge_internal_spid]    Script Date: 28.06.2013 14:22:34 ******/
CREATE CLUSTERED INDEX [idx_charge_spid] ON dbo.[charge]
(
	[spid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) 
go



И представление:
CREATE view dbo.[charge_processing] 
as
select id
  from dbo.charge with (rowlock)
  where spid = @@spid



Из каждого сеанса к этому представлению обращаются пользователи с запросами, на вставку, выборку, удаление, изменение. Вьюха должна фильтровать польщователей по @@spid.
Так вот, включил профайлер, запустил в нескольких окнах хранимые процедуры, которые используют эту вьюху и он мне выдал взаимоблокировку (см. картинку ниже).

Т.е. когда один процесс делает select из вьюхи, а второй, например, insert, то происходит взаимоблокировка. Почему?

Уровень изоляции транзакций ReadComitted.
Версия:
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

К сообщению приложен файл. Размер - 75Kb
28 июн 13, 14:30    [14497480]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
_ч_
Member

Откуда:
Сообщений: 1446
Повторить такую взаимоблокировку пока не удается
28 июн 13, 14:39    [14497552]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
gwergwerg
Guest
_ч_,

+1
28 июн 13, 14:47    [14497608]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
rgwergwerg
Guest
_ч_,

полный тест кейс в студию
28 июн 13, 14:48    [14497618]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3422
_ч_,

Если файл с описанием дэдлока открыть в любом вьюере XML, то там будут точные запросы с обеих сторон, и вообще много всего интересного. Смотрели?
28 июн 13, 14:52    [14497657]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
_ч_
Member

Откуда:
Сообщений: 1446
Если верить профайлеру, то зависает, если запустить два запроса так, чтобы они выполнялись одновременно:

update c1
set
	r = case when c1.r + c2.r > 0 then c1.r + c2.r
				else	0
				end
	,delta = isnull(c1.delta, 0) + case when c1.r + c2.r > 0 then -c2.r
				else 0
				end             
from dbo.[charge_processing] c1
inner join dbo.[charge_processing] c2 on c2.b_id <> c1.b_id
			and c1.r_id = c2.r_id and c2.r < 0
where c1.r > 0
option
	(maxdop 1) 



Полностью таблица:

CREATE TABLE dbo.[charge](
	[b_id] [bigint] NULL,
	[r_id] [uniqueidentifier] NULL,
	[r] [decimal](23, 10) NOT NULL,
	[start_date] [datetime] NOT NULL,
	[finish_date] [datetime] NOT NULL,
	[spid] [smallint] NOT NULL,
	[delta] [decimal](23, 10) NULL
) 

Ну и соотв-венно во вьюхе транслируются все эти поля, кроме spid
28 июн 13, 18:08    [14498762]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
_ч_
Member

Откуда:
Сообщений: 1446
Пытаюсь воспроизвести с незакрытой транзакцией - не воспроизводится
28 июн 13, 18:09    [14498767]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
1d0
Member

Откуда: инфа100%
Сообщений: 2521
два апдейта одновременно - неудивительно возникновение блокировок
1 июл 13, 15:07    [14505633]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
Crimean
Member

Откуда:
Сообщений: 13147
1d0
два апдейта одновременно - неудивительно возникновение блокировок


+1 и with (xlock) решает, если не разбираться особо
1 июл 13, 15:29    [14505745]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
Denis Reznik
Member

Откуда: Киев
Сообщений: 156
_ч_,

Попробуйте два таких запроса запустить в разных окнах в цикле WHILE. Упадёт дедлок если он падал на таких запросах.

Crimean
+1 и with (xlock) решает, если не разбираться особо


Не факт что поможет. Уйдут Shared локи, но общая схема дедлока не поменяется.

_ч_,

Можете показать план запроса? есть ли какие-то ещё на таблице индексы или внешние ключи?
1 июл 13, 16:31    [14506108]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
_ч_
Member

Откуда:
Сообщений: 1446
1d0
два апдейта одновременно - неудивительно возникновение блокировок


Ой ли? При rowlock и при том, что в условии вьюхи фильтрация по @@spid? А он у каждой сессии свой.


Crimean, кардинальное у Вас решение проблемы )


Denis Reznik, я кажется уже победил эту блокировку.
1 июл 13, 17:58    [14506651]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Denis Reznik,

а тут, как обычно, 50/50! дедлоки от конвертации S > X - уйдут, первратившись в ожидания. дедлоки от того, что несколько записей в разном порядке в транзакции меняются - останутся, но тут хинтами и не полечить, надо код менять это тс успеет всегда. так что попробовать хинт в проблемный запрос - стоит. не секрет же, что UPDATE внутри транзакции дедлочит сам с сабой на ровном месте
1 июл 13, 18:00    [14506662]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
_ч_
Member

Откуда:
Сообщений: 1446
План во вложении

К сообщению приложен файл (1.xml - 26Kb) cкачать
1 июл 13, 18:01    [14506667]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
_ч_
Member

Откуда:
Сообщений: 1446
план

К сообщению приложен файл. Размер - 15Kb
1 июл 13, 18:02    [14506684]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
Crimean
Member

Откуда:
Сообщений: 13147
_ч_
Crimean, кардинальное у Вас решение проблемы )


почему "кардинальное"? ситуация-то букварная. S>X / S>X. план не тот выбирается. вот и привет. как по мне лучше через XLOCK лечить, чем индексы хинтовать. ибо все равно в результате UPDATE будут X блокировки наложены. мы просто их чуть раньше повесим. в чем "кардинальность"-то?
1 июл 13, 18:03    [14506689]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
_ч_
Member

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

т.е. вы считаете, что лучше\выгоднее написать везде в такого рода update подсказку xlock, чем написать хинт index во вьюхе?
А чем лучше?
1 июл 13, 18:09    [14506731]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
Crimean
Member

Откуда:
Сообщений: 13147
_ч_
Crimean,

т.е. вы считаете, что лучше\выгоднее написать везде в такого рода update подсказку xlock, чем написать хинт index во вьюхе?
А чем лучше?


у вас в результате UPDATE все равно "повиснут" X блокировки. так? тогда в чем проблема "запросить" их "сразу" и исключить этап развешивания S, которые потом все равно захотят стать X, на чем и дедлок? и не "везде", а только для дедлочащего стейтмента
а хинтование может быть болезненное, если план запроса не является параметро - независимым, а то и данные - независимым
1 июл 13, 19:40    [14507111]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
_ч_
UPDATE	C2
SET	 r	=                      CASE WHEN C2.r > -C1.r THEN C2.r + C1.r ELSE 0 END
	,delta	= IsNull(C2.delta,0) + CASE WHEN C2.r > -C1.r THEN      - C1.r ELSE 0 END
FROM	dbo.charge_processing	C1
JOIN	dbo.charge_processing	C2 ON C1.r_id  = C2.r_id
				  AND C1.b_id != C2.b_id
WHERE	C1.r < 0
AND	C2.r > 0
Неа, с другой стороны другой апдейт.
Если с обоих один и тот же запрос, то не должно, ибо изменяются только строки R > 0, на основании строк R < 0.
Т.е. не может приводить к взаимно-блокировкам никак.

Приводите реальный стек вызовов. У вас там в транзакции больше апдейтов.
Или бажную версию пользуете, что врятли.
2 июл 13, 01:05    [14507840]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
Crimean
Member

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

так было ш уже вроде? один и тот же апдейт, но завернутый в транзакцию, дедлочит сам с собой )
2 июл 13, 02:50    [14507878]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
Александр52
Member

Откуда: Кокосовые острова ส็็็็็
Сообщений: 5136
Как вариант, в зависимости от важности данных, попробовать - WITH (NOLOCK)
2 июл 13, 11:48    [14509384]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Александр52
Как вариант, в зависимости от важности данных, попробовать - WITH (NOLOCK)


для UPDATE? а поможет?
2 июл 13, 11:56    [14509425]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
Александр52
Member

Откуда: Кокосовые острова ส็็็็็
Сообщений: 5136
Crimean

для UPDATE? а поможет?


Если я правильно понимаю, при NOLOCK - не будут затронуты заблокированные данные, т.е. они будут просто пропущены.
Исходя из этого - почему нет)
2 июл 13, 11:59    [14509440]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Александр52,

The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.
2 июл 13, 12:05    [14509480]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
Glory
Member

Откуда:
Сообщений: 104751
Александр52
Если я правильно понимаю, при NOLOCK - не будут затронуты заблокированные данные, т.е. они будут просто пропущены.
Исходя из этого - почему нет)

Т.е. такой UPDATE пропустит часть записей, которые должны быть обновлены ?

READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.
2 июл 13, 12:07    [14509493]     Ответить | Цитировать Сообщить модератору
 Re: Объясните почему происходит блокировка  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
Crimean
_ч_
Crimean,

т.е. вы считаете, что лучше\выгоднее написать везде в такого рода update подсказку xlock, чем написать хинт index во вьюхе?
А чем лучше?


у вас в результате UPDATE все равно "повиснут" X блокировки. так? тогда в чем проблема "запросить" их "сразу" и исключить этап развешивания S, которые потом все равно захотят стать X, на чем и дедлок? и не "везде", а только для дедлочащего стейтмента
а хинтование может быть болезненное, если план запроса не является параметро - независимым, а то и данные - независимым



Он абсолютно прав.
2 июл 13, 12:17    [14509561]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить