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

Откуда: Donetsk->Emerald City
Сообщений: 358
Здравствуйте.

Версия сервера:

Microsoft SQL Server 2014 - 12.0.4100.1 (X64) 
Apr 20 2015 17:29:27
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Столкнулся со следующим поведением при выполнении скрипта ниже:

CREATE TABLE test(ac VARCHAR(20), ai INT)

CREATE PARTITION FUNCTION [part_fn_test](VARCHAR(20))
AS RANGE RIGHT FOR VALUES ('ABC', 'DEF', 'GHI')

CREATE PARTITION SCHEME [part_sch_test] AS PARTITION [part_fn_test] ALL TO ([PRIMARY])

CREATE CLUSTERED INDEX idx_1 ON [test](ac) 
ON [part_sch_test](ac)

ALTER TABLE test SET (LOCK_ESCALATION = AUTO)
GO

INSERT INTO test SELECT TOP 1 'ABC', number FROM [master].dbo.spt_values AS sv WHERE sv.[type] = 'P' ORDER BY sv.number
INSERT INTO test SELECT TOP 1 'DEF', number FROM [master].dbo.spt_values AS sv WHERE sv.[type] = 'P' ORDER BY sv.number
INSERT INTO test SELECT TOP 1 'GHI', number FROM [master].dbo.spt_values AS sv WHERE sv.[type] = 'P' ORDER BY sv.number

BEGIN TRANSACTION

DELETE FROM test WHERE ac = 'ABC'

EXEC sp_lock 65

ROLLBACK


при удалении сервер накладывает блокировки намерения на все партиции.

spid dbid ObjId IndId Type Resource Mode Status
65 2 2099048 1 HBT IX GRANT
65 2 2099048 1 HBT IX GRANT
65 2 2099048 1 HBT IX GRANT
65 2 2099048 1 HBT IX GRANT
65 2 2099048 0 TAB IX GRANT
65 2 2099048 1 PAG 1:22820 IX GRANT
65 1 1467152272 0 TAB IS GRANT
65 2 2099048 1 KEY (003d11da587a) X GRANT
65 32767 -571204656 0 TAB Sch-S GRANT


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

Вопрос: можно ли избежать данного поведения (а именно, накладывания эксклюзивных блокировок намерения на секции, в которых нет данных, модифицируемых данной сессией), или лучше реализовать удаление через перемещение секций? Возможно также, что где-то есть ошибка в определении секционирования, но я не могу ее найти.
18 фев 16, 21:26    [18838032]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки на секционированной таблице  [new]
Balbidon
Member

Откуда: Donetsk->Emerald City
Сообщений: 358
Похоже, что переключение секций в параллельных транзакциях тоже не сработает, т.к. до конца транзакции держится Sch-M на таблице и на всех партициях.
18 фев 16, 21:52    [18838117]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки на секционированной таблице  [new]
invm
Member

Откуда: Москва
Сообщений: 9830
Balbidon
т.к. другому процессу придется ждать освобождения эксклюзивной блокировки на своей секции.
Не придется. X на секцию нет - есть IX, а IX совместимы между собой.

Про Sch-M не понятно - DML не ставит Sch-M.
18 фев 16, 23:25    [18838357]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки на секционированной таблице  [new]
Balbidon
Member

Откуда: Donetsk->Emerald City
Сообщений: 358
Прошу прощения за некорректный пример. Если мы возьмем вместо 1 строки каждого типа, скажем по 100 тысяч строк:

INSERT INTO test SELECT TOP 100000 'ABC', sv.number FROM [master].dbo.spt_values AS sv, [master].dbo.spt_values AS sv2 WHERE sv.[type] = 'P' ORDER BY sv.number
INSERT INTO test SELECT TOP 100000 'DEF', sv.number FROM [master].dbo.spt_values AS sv, [master].dbo.spt_values AS sv2 WHERE sv.[type] = 'P' ORDER BY sv.number
INSERT INTO test SELECT TOP 100000 'GHI', sv.number FROM [master].dbo.spt_values AS sv, [master].dbo.spt_values AS sv2 WHERE sv.[type] = 'P' ORDER BY sv.number


и получим эскалацию блокировки до уровня секции:

spid dbid ObjId IndId Type Resource Mode Status
65 2 2099048 0 TAB IX GRANT
65 1 1467152272 0 TAB IS GRANT
65 2 2099048 1 HBT IX GRANT
65 32767 -571204656 0 TAB Sch-S GRANT
65 2 2099048 1 HBT X GRANT
65 2 2099048 1 HBT IX GRANT
65 2 2099048 1 HBT IX GRANT


то в параллельной сессии:

BEGIN TRANSACTION

DELETE FROM test WHERE ac = 'DEF'

ROLLBACK


имеем:

spid dbid ObjId IndId Type Resource Mode Status
59 2 2099048 0 TAB IX GRANT
59 2 2099048 1 HBT Sch-S GRANT
59 2 2099048 1 HBT IX CNVT
59 2 2099048 1 HBT IX GRANT


И вот эта конверсия с IX в X будет ждать.
19 фев 16, 00:35    [18838587]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки на секционированной таблице  [new]
Balbidon
Member

Откуда: Donetsk->Emerald City
Сообщений: 358
invm
Про Sch-M не понятно - DML не ставит Sch-M.


Это я про другой вариант:

CREATE TABLE test(ac VARCHAR(20), ai INT)
SELECT TOP 0 * into test2 FROM test

CREATE PARTITION FUNCTION [part_fn_test](VARCHAR(20))
AS RANGE RIGHT FOR VALUES ('ABC', 'DEF', 'GHI')

CREATE PARTITION SCHEME [part_sch_test] AS PARTITION [part_fn_test] ALL TO ([PRIMARY])

CREATE CLUSTERED INDEX idx_1 ON [test](ac) 
WITH (DROP_EXISTING = ON, DATA_COMPRESSION = NONE)
ON [part_sch_test](ac)

CREATE CLUSTERED INDEX idx_1 ON [test2](ac) 
WITH (DROP_EXISTING = ON, DATA_COMPRESSION = NONE)
ON [part_sch_test](ac)

ALTER TABLE test SET (LOCK_ESCALATION = AUTO)
GO
ALTER TABLE test2 SET (LOCK_ESCALATION = AUTO)
GO

INSERT INTO test SELECT TOP 100000 'ABC', sv.number FROM [master].dbo.spt_values AS sv, [master].dbo.spt_values AS sv2 WHERE sv.[type] = 'P' ORDER BY sv.number
INSERT INTO test SELECT TOP 100000 'DEF', sv.number FROM [master].dbo.spt_values AS sv, [master].dbo.spt_values AS sv2 WHERE sv.[type] = 'P' ORDER BY sv.number
INSERT INTO test SELECT TOP 100000 'GHI', sv.number FROM [master].dbo.spt_values AS sv, [master].dbo.spt_values AS sv2 WHERE sv.[type] = 'P' ORDER BY sv.number

BEGIN TRANSACTION

ALTER TABLE test SWITCH PARTITION 2 TO test2 PARTITION 2;

ROLLBACK


Здесь получается, что транзакция будет держать Sch-M блокировки на обеих test и test2 таблицах, и вторая транзакция:

BEGIN TRANSACTION

ALTER TABLE test SWITCH PARTITION 3 TO test2 PARTITION 3;

ROLLBACK


будет ждать возможности наложить Sch-S.
19 фев 16, 00:45    [18838610]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки на секционированной таблице  [new]
Balbidon
Member

Откуда: Donetsk->Emerald City
Сообщений: 358
В качестве АПа...

Подобное поведение удается побороть запретив эскалацию, но некоторые таблицы могут содержать сотни тысяч строк на секцию, что приводит к чрезмерному количеству блокировок и ненужному расходу ресурсов. Кроме того, потенциально возможно возникновение взаимоблокировок (deadlocks), что еще более нежелательно. Любые мысли по данному поводу, включая изменение логики, приветствуются.
19 фев 16, 20:27    [18843155]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки на секционированной таблице  [new]
Balbidon
Member

Откуда: Donetsk->Emerald City
Сообщений: 358
И еще один АП.

CREATE TABLE test(ac VARCHAR(20), ai INT)

CREATE PARTITION FUNCTION [part_fn_test](VARCHAR(20))
AS RANGE RIGHT FOR VALUES ('ABC', 'DEF', 'GHI')

CREATE PARTITION SCHEME [part_sch_test] AS PARTITION [part_fn_test] ALL TO ([PRIMARY])

CREATE CLUSTERED INDEX idx_1 ON [test](ac) 
ON [part_sch_test](ac)

CREATE INDEX idx_2 ON [test](ai) ON [part_sch_test](ac)

ALTER TABLE test SET (LOCK_ESCALATION = AUTO)
GO

INSERT INTO test SELECT TOP 100000 'ABC', sv.number FROM [master].dbo.spt_values AS sv, [master].dbo.spt_values AS sv2 WHERE sv.[type] = 'P' ORDER BY sv.number
INSERT INTO test SELECT TOP 100000 'DEF', sv.number FROM [master].dbo.spt_values AS sv, [master].dbo.spt_values AS sv2 WHERE sv.[type] = 'P' ORDER BY sv.number
INSERT INTO test SELECT TOP 100000 'GHI', sv.number FROM [master].dbo.spt_values AS sv, [master].dbo.spt_values AS sv2 WHERE sv.[type] = 'P' ORDER BY sv.number

BEGIN TRANSACTION

DELETE FROM test WHERE ac = 'ABC'

EXEC sp_lock 65

ROLLBACK


Если мы добавим некластерный индекс idx_2, то в нем IX не накладывается на секции, из которых данные не удаляются.

spiddbidObjIdIndIdTypeResourceModeStatus
65114671522720TAB ISGRANT
65219346299352HBT XGRANT
65219346299350TAB IXGRANT
65219346299351HBT IXGRANT
65219346299351HBT IXGRANT
65219346299351HBT XGRANT
65219346299351HBT IXGRANT
6532767-5712046560TAB Sch-SGRANT


Есть ли возможность избавиться от этих, на первый взгляд, избыточных IX-блокировок на секции кластерного индекса. Или хотя бы понять, почему серверу так необходимо их накладывать.
26 фев 16, 21:27    [18869643]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки на секционированной таблице  [new]
invm
Member

Откуда: Москва
Сообщений: 9830
Balbidon
Или хотя бы понять, почему серверу так необходимо их накладывать.
Запрос
DELETE FROM test WHERE ac = 'ABC'
автопараметризуется.
Соответственно, план строится универсальный с позиционированием на все секции. Поэтому и столько IX на HOBT.

Если, например, переписать запрос так:
DELETE FROM test WHERE ac = 'ABC' option (recompile)
или так
DELETE FROM test WHERE ac = (select 'ABC')
то автопараметризации не будет и заблокируется только одна секция.
26 фев 16, 23:39    [18870145]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки на секционированной таблице  [new]
Balbidon
Member

Откуда: Donetsk->Emerald City
Сообщений: 358
Bingo!

Спасибо большое.
27 фев 16, 00:20    [18870254]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки на секционированной таблице  [new]
Владислав Колосов
Member

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

секционирование "не работает" если тип данных фильтра запроса отличается от типа аргумента функции секционирования.

Поэтому
DELETE FROM test WHERE ac = cast(('ABC' as varchar(20))


даст желаемый результат.
Вообще полезно завести привычку явно указывать тип данных констант.
27 фев 16, 17:24    [18871822]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки на секционированной таблице  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8811
Скобка лишняя.
27 фев 16, 17:25    [18871828]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки на секционированной таблице  [new]
Balbidon
Member

Откуда: Donetsk->Emerald City
Сообщений: 358
Владислав Колосов,

Оригинальный сценарий подразумевал функцию типа:

CREATE FUNCTION test_fn() RETURNS VARCHAR(20) 
AS
BEGIN
<... некая логика ...>
RETURN 'ABC'
END
GO


и, соответственно, оператор удаления выглядел:

DELETE FROM test WHERE ac = dbo.test_fn()


т.е., вроде бы, типы были приведены верно. Впрочем, насчет полного приведения констант к типу - ценный совет. Ранее я всегда обращал внимание на численные и Unicode, будем учитывать и размер типов VARCHAR на будущее.

В итоге сейчас конечный результат выглядит как:

DELETE FROM test WHERE ac = (SELECT dbo.test_fn()) OPTION (RECOMPILE)
29 фев 16, 00:25    [18875469]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить