Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Дедлок из-за экскалации X Key в X PAGE  [new]
Rankatan
Member

Откуда:
Сообщений: 250
Всем привет!

У нас несколько раз происходил дедлок (xdl в приложении). Дедлок происходит в 1 ХП (sp_createobject)
CREATE TABLE [dbo].table2
(
	clm1ID NUMERIC(35,0) NOT NULL,
	clm2ID NUMERIC(35,0) NOT NULL,
	val1 NUMERIC(35,0) NOT NULL,
	val2 NUMERIC(35,0) NULL,
	val3 VARCHAR(255)  NULL,
	val4 NUMERIC(35,0) NULL,
	val5 NUMERIC(35,0) NULL,
	PRIMARY KEY CLUSTERED 
	(
		clm1ID ASC,
		clm2ID ASC
	) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
	,FOREIGN KEY(val1) REFERENCES [dbo].[tblval1] (val1)
	,FOREIGN KEY(val2) REFERENCES [dbo].[tblval2] (val2)
	,FOREIGN KEY(val4) REFERENCES [dbo].[Image] (ImageID)
	,FOREIGN KEY(val5) REFERENCES [dbo].[Image] (ImageID)
	,FOREIGN KEY(clm1ID, clm2ID) REFERENCES [dbo].[ObjectTbl] (clm1ID, clm2ID)
)

GO
CREATE TABLE dbo.table1(
	clm1ID NUMERIC(35,0) NOT NULL,
	clm2ID NUMERIC(35,0) NOT NULL,
	clm3 NUMERIC(1,0) NOT NULL,
	clm4 NUMERIC(1,0) NULL,
	clm5 NUMERIC(1,0) NULL,
	clm6 NUMERIC(1,0) NULL,
	clm7 NUMERIC(1,0) NULL,
	clm8 NUMERIC(1,0) NULL,
	clm9 NUMERIC(1,0) NULL,
	CONSTRAINT [PK_Bio_ChipSignCheck] PRIMARY KEY CLUSTERED 
	(
		clm1ID ASC,
		clm2ID ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
	,FOREIGN KEY(clm1ID, clm2ID) REFERENCES [dbo].table2 (clm1ID, clm2ID)
) 
GO


CREATE PROCEDURE sp_createobject (.....)
AS
	.....
	BEGIN TRAN
		.....

		INSERT INTO dbo.table2(clm1ID, clm2ID, val1, val2, val3, val4, val5)
		VALUES (@clm1ID, @clm2ID, @val1, @val2, @val3, @val4, @val5)

		INSERT INTO dbo.table1(clm1ID, clm2ID, clm3, clm4, clm5, clm6, clm7, clm8, clm9)
		select @clm1ID, @clm2ID, dim_tbl1.Val,dim_tbl2.Val,dim_tbl3.Val,dim_tbl4.Val,dim_tbl5.Val,dim_tbl6.Val,dim_tbl7.Val
		from DimMain dim_tbl1
			left outer join DimMain dim_tbl2 on dim_tbl2.dmclm1ID=22 and dim_tbl2.dmclm2ID=2 and dim_tbl2.dmclm3ID=@inst
			left outer join DimMain dim_tbl3 on dim_tbl3.dmclm1ID=22 and dim_tbl3.dmclm2ID=3 and dim_tbl3.dmclm3ID=@inst
			inner join DimMain dim_tbl4 on dim_tbl4.dmclm1ID=22 and dim_tbl4.dmclm2ID=4 and dim_tbl4.dmclm3ID=@inst
			left outer join DimMain dim_tbl5 on dim_tbl5.dmclm1ID=22 and dim_tbl5.dmclm2ID=5 and dim_tbl5.dmclm3ID=@inst
			left outer join DimMain dim_tbl6 on dim_tbl6.dmclm1ID=22 and dim_tbl6.dmclm2ID=6 and dim_tbl6.dmclm3ID=@inst
			left outer join DimMain dim_tbl7 on dim_tbl7.dmclm1ID=22 and dim_tbl7.dmclm2ID=7 and dim_tbl7.dmclm3ID=@inst
			left outer join DimMain dim_tbl8 on dim_tbl8.dmclm1ID=22 and dim_tbl8.dmclm2ID=8 and dim_tbl8.dmclm3ID=@inst
		where dim_tbl1.dmclm1ID=22 and dim_tbl1.dmclm2ID=1 and dim_tbl1.dmclm3ID=@inst

		.....
	COMMIT


В table1 всегда вставляется одна запись, т.е. всегда должна накладываться блокировка X на KEY. В обычной ситуации (>99%) именно так и происходит: при insert в table1 накладывается X на KEY и IX на PAGE. Но пару раз происходил дедлок и судя по дедлоку блокировка X была на PAGE. + при проверке FK c table1 на table2 накладывалась блокировка S на PAGE (по дедлоку видно), в обычной ситуации должна накладываться IS на PAGE.
Как может происходить такая эскалация, что при вставке 1 строки накладывается X блокировка на PAGE, а не на KEY?

Доп. инфо:
#Во всех индексах IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON и эти настройки не менялись
#Во время дедлоков никаких обслуживающих операций не проводилось
#Нигде хинтов типа WITH(PAGLOCK) не используется

К сообщению приложен файл (deadlock.xdl - 3Kb) cкачать
23 июн 16, 13:10    [19325981]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок из-за экскалации X Key в X PAGE  [new]
Rankatan
Member

Откуда:
Сообщений: 250
sql server 2005

скриншот дедлока

К сообщению приложен файл. Размер - 36Kb
23 июн 16, 13:11    [19325990]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок из-за экскалации X Key в X PAGE  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
INSERT INTO dbo.table2 (rowlock)
23 июн 16, 13:13    [19326005]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок из-за экскалации X Key в X PAGE  [new]
Rankatan
Member

Откуда:
Сообщений: 250
Гавриленко Сергей Алексеевич
INSERT INTO dbo.table2 (rowlock)

Как победить ясно. Хочу понять почему сервер делает эскалацию. + У нас нежелательно использовать хинты.
23 июн 16, 13:18    [19326046]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок из-за экскалации X Key в X PAGE  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Rankatan
Гавриленко Сергей Алексеевич
INSERT INTO dbo.table2 (rowlock)

Как победить ясно. Хочу понять почему сервер делает эскалацию. + У нас нежелательно использовать хинты.
Ну, запретите паглоки на индекс. Сервер волен выбирать изначально любой уровень гранулярности, если ему это не запрещено.

Сообщение было отредактировано: 23 июн 16, 13:21
23 июн 16, 13:21    [19326063]     Ответить | Цитировать Сообщить модератору
 Re: Дедлок из-за экскалации X Key в X PAGE  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 205
Rankatan
Хочу понять почему сервер делает эскалацию.

Есть вот такое мнение:
Mike Hotek - Microsoft SQL Server 2008 Implementation and Maintenance
Каждый режим блокировки может применяться к строке, странице или таблице. Диспетчер блокировок определяет тип блокировки на основании очень
жесткого ограничения ресурсов, которое обычно называется правилом двух
процентов, цель которого — сокращение до минимума числа блокировок, которые нужно создавать и управлять, потому что каждая блокировка потребляет
память. Если SQL Server решает, что потребуется доступ к больше чем двум
процентам строк на странице, такая страница блокируется. Аналогично, если
требуется доступ к более чем двум процентам страниц в таблице, такая таблица блокируется.
Определяя тип блокировки, Диспетчер блокировок использует статистику
распределения, такую же, что применяется Оптимизатором запросов. Поскольку статистика распределения не всегда точна или не всегда доступна, в Диспетчере блокировок есть механизм, который называется укрупнение блокировок (lock escalation) и позволяет повышать уровень блокировки. SQL Server
может укрупнить блокировку строки до блокировки таблицы или блокировку
страницы — до блокировки таблицы.


А вообще если не нужна эскалация, можете ее отключить для таблиц(ы)
23 июн 16, 23:43    [19328715]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить