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

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

Подскажите, пожалуйста, как разрулить блокировку indexed view в достаточно простой ситуации:

Есть таблица в которую пишутся операции:

PK - int
Код товара - varchar
Количество - int

Есть indexed view которое суммирует количества в рамках каждого из товаров.

Есть несколько процессов которые параллельно стараются писать данные в эту таблицу. При этом 2 и далее процесс блокируется 1-м если вставляется товар кот. еще нет в таблице, либо если 2 процесс вставляет тот же товар, что и первый.

Вопрос: причина возникновения данной блокировки и есть ли какие-либо способы позволить моим процессам писать в такую таблицу одновременно?
20 июн 16, 13:47    [19314267]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
aleks2
Guest
1. Тупо и прямолинейно with(tablockx).
2. Изучит понятие "критическая секция" и применить sp_getapplock/
20 июн 16, 13:51    [19314282]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
msLex
Member

Откуда:
Сообщений: 9273
aleks2
1. Тупо и прямолинейно with(tablockx).
2. Изучит понятие "критическая секция" и применить sp_getapplock/

Так ТС хочет параллельно вставлять записи с неуникальным кодом товара в таблицу, которая аггрегируется до этого кода товара через индексированное представление.
20 июн 16, 14:00    [19314319]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
msLex,

https://www.sql.ru/forum/1208382-a/pro-indeksirovannye-vu-i-dedloki
20 июн 16, 14:26    [19314519]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
MSCH
При этом 2 и далее процесс блокируется 1-м если вставляется товар кот. еще нет в таблице
Очень сомнительно, что причина в индексированном представлении.
MSCH
если 2 процесс вставляет тот же товар, что и первый
Да, так и должно быть. Множество процессов не могут одновременно обновлять данные в одной и той же строке.
20 июн 16, 14:44    [19314618]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
msLex
Member

Откуда:
Сообщений: 9273
invm
MSCH
При этом 2 и далее процесс блокируется 1-м если вставляется товар кот. еще нет в таблице
Очень сомнительно, что причина в индексированном представлении.

Ничего удивительного, все так и есть.

При добавлении несуществующего ранее ключа в индексированную вью вешается range блокировка.
Если предположить, что код товара монотонно возрастающий, и первые записи в таблице по ним появляются примерно в том же порядке, то после добавления первой записи по очередному товару, блокируется вставка всех новых товаров с кодом больше добавленного.
20 июн 16, 16:42    [19315261]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
msLex
Member

Откуда:
Сообщений: 9273
a_voronin
msLex,

https://www.sql.ru/forum/1208382-a/pro-indeksirovannye-vu-i-dedloki

причем здесь ваш дидлок при апдейте полей из ключа кластреного индекса?
у ТС-а, по описанию, обычные ожидания на блокировках.
20 июн 16, 16:45    [19315276]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
msLex
При добавлении несуществующего ранее ключа в индексированную вью вешается range блокировка.
Да. Только это RangeI-N, которая сама с собой совместима. И которая затем преобразуется в обычную X.
Так что не должна конкурентная вставка блокироваться.
20 июн 16, 17:06    [19315371]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
нужно, чтобы ТС скрипты таблички и вью выложил
вот это там хочу увидеть:
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
20 июн 16, 17:09    [19315378]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
msLex
Member

Откуда:
Сообщений: 9273
invm
msLex
При добавлении несуществующего ранее ключа в индексированную вью вешается range блокировка.
Да. Только это RangeI-N, которая сама с собой совместима. И которая затем преобразуется в обычную X.
Так что не должна конкурентная вставка блокироваться.

Небольшой тест дает мне RangeS-U, несовместимую саму с собой.
Как следствие происходит зависание на wait RangeS-U ключа FFFFF.... при вставке второго нового кода товара до завершения транзакции вставки первого нового товара.
Возможно есть нюансы.
20 июн 16, 19:37    [19315982]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
msLex
Возможно есть нюансы.
Возможно.
Мой тест на условиях ТС'а дает RangeI-N.
20 июн 16, 19:39    [19315993]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
msLex
Member

Откуда:
Сообщений: 9273
invm
msLex
Возможно есть нюансы.
Возможно.
Мой тест на условиях ТС'а дает RangeI-N.

действительно странно, я пытался точно воспроизвести картину у ТС-а

вот, что я делал

таблица
CREATE TABLE [dbo].[test_table](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[code] [int] NOT NULL,
	[cnt] [int] NOT NULL
) ON [PRIMARY]


представление
CREATE view [dbo].[test_view] 
with schemabinding
as	
	select 
		code
		, cnt = sum(cnt)
		, cnt_big = count_big(*)
	from dbo.test_table
	group by 
		code

GO
CREATE UNIQUE CLUSTERED INDEX [ixuc__code] ON [dbo].[test_view]
(
	[code]
)
GO


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

insert dbo.test_table(
code
, cnt
)
select 
	1
	, 1



добавляем код товара "2" в первой транзакции, и оставляем ее открытой

begin tran

insert dbo.test_table(
code
, cnt
)
select 
	2
	, 1

-- проверяем наложенные блокировки 
sp_lock @@spid 
/*
 и, помимо прочего, видим 
spid	dbid	ObjId	IndId	Type	Resource	Mode	Status
56	6	933578364	1	KEY	(ffffffffffff)                  	RangeS-U		GRANT
*/



пытаемся добавить товар с кодом "3" во второй транзакции

begin tran

insert dbo.test_table(
code
, cnt
)
select 
	3
	, 1


она зависает и среди блокировок этой транзакции видим

spid	dbid	ObjId	IndId	Type	Resource	Mode	Status
58	6	933578364	1	KEY	(ffffffffffff)                  	RangeS-U		WAIT


можете выложит условия своего эксперимента, интересно в чем отличие.
20 июн 16, 19:58    [19316042]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
msLex
действительно странно, я пытался точно воспроизвести картину у ТС-а
Вы правы.
Действительно должна накладываться RangeS-U. Как в merge при serializable. Потому что по сути это и есть merge.
Но не всегда. Вот такой скрипт
+
use tempdb;
go

create table dbo.t (id int identity primary key, g int not null, v int not null);
go

create view dbo.vt
with schemabinding
as
select
 g, count_big(*) as c, sum(v) as sv
from
 dbo.t
group by
 g;
go

create unique clustered index IX_vt__g on dbo.vt (g);
go

begin tran;
insert into dbo.t values (-10, 1);
exec sp_lock @@spid;
rollback;
go

select 'dbo.vt', ghost_record_count from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.vt', 'V'), 1, default, 'detailed');
go

begin tran;
insert into dbo.t values (-10, 1);
exec sp_lock @@spid;
rollback;
go

select name, object_id(name) from (values ('dbo.t'), ('dbo.vt')) t(name);
go

drop view dbo.vt;
drop table dbo.t;
go
Дает результат
+
(1 row(s) affected)
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
82 2 879055359 1 PAG 1:2460 IX GRANT
82 2 815055131 1 PAG 1:2456 IX GRANT
82 2 815055131 1 KEY (8194443284a0) X GRANT
82 2 415053706 0 TAB IX GRANT
82 1 1131151075 0 TAB IS GRANT
82 2 815055131 0 TAB IX GRANT
82 2 879055359 0 TAB IX GRANT
82 2 879055359 1 KEY (486fa6644a60) X GRANT
82 2 879055359 1 KEY (ffffffffffff) RangeS-U GRANT

ghost_record_count
------ --------------------
dbo.vt 1

(1 row(s) affected)


(1 row(s) affected)
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
82 2 815055131 1 KEY (61a06abd401c) X GRANT
82 2 879055359 1 PAG 1:2460 IX GRANT
82 2 815055131 1 PAG 1:2456 IX GRANT
82 1 1131151075 0 TAB IS GRANT
82 2 815055131 0 TAB IX GRANT
82 2 879055359 0 TAB IX GRANT
82 2 879055359 1 KEY (486fa6644a60) X GRANT

name
------ -----------
dbo.t 815055131
dbo.vt 879055359

(2 row(s) affected)

Т.е. при наложении блокировок учитываются все строки индекса, даже фантомные.

В общем, мой ляп - схалтурил при тестировании.
20 июн 16, 23:39    [19316881]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
msLex
Member

Откуда:
Сообщений: 9273
invm
Т.е. при наложении блокировок учитываются все строки индекса, даже фантомные.

Забавная оптимизация, накладывать X lock на ghost рекорду вместо range на следующую "живую"
21 июн 16, 00:08    [19316984]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
MSCH
Member

Откуда:
Сообщений: 8
Коллеги, я правильно понимаю из ваших постов что при наличии такой ситуации (когда суммирующая вьюшка прицеплена к таблице) ни о каких параллельных процессах не стоит мечтать? Как же тогда на SQL строят системы с тысячами пользователей? Может есть какой-то обходной путь?
21 июн 16, 10:26    [19317692]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
msLex
Забавная оптимизация
Это даже не оптимизация - вариантов других нет.
Нельзя вставить повторяющийся ключ, поэтому приходится реанимировать труп. Попутно это еще и выгоднее :)
21 июн 16, 10:35    [19317737]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
MSCH
ни о каких параллельных процессах не стоит мечтать?
При определнных условиях, перечисленных выше, да, не стоит.
MSCH
Как же тогда на SQL строят системы с тысячами пользователей
Нормально строят.
Такое поведение будет на любом сервере, где агрегирующее материализованное представление обновляется синхронно.
Делайте транзакции короче. Или вставляйте в буферную таблицу, затем асинхронно перемещайте в основную.
Задачу опишите конкретнее, может еще варианты будут.
21 июн 16, 10:48    [19317802]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
aleks2
Guest
MSCH
Коллеги, я правильно понимаю из ваших постов что при наличии такой ситуации (когда суммирующая вьюшка прицеплена к таблице) ни о каких параллельных процессах не стоит мечтать? Как же тогда на SQL строят системы с тысячами пользователей? Может есть какой-то обходной путь?


Плохо учат нынешних программиЗдов.

Критическая секция и все писатели встают в очередь.
Читатели почти ничего не теряют.

ЗЫ. ЭТО делают даже системы реального времени.
21 июн 16, 12:01    [19318222]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
MSCH
Member

Откуда:
Сообщений: 8
aleks2
MSCH
Коллеги, я правильно понимаю из ваших постов что при наличии такой ситуации (когда суммирующая вьюшка прицеплена к таблице) ни о каких параллельных процессах не стоит мечтать? Как же тогда на SQL строят системы с тысячами пользователей? Может есть какой-то обходной путь?


Плохо учат нынешних программиЗдов.

Критическая секция и все писатели встают в очередь.
Читатели почти ничего не теряют.

ЗЫ. ЭТО делают даже системы реального времени.


Подскажите, пожалуйста, путь как это можно сделать для примера MsLex. Заранее благодарен.
21 июн 16, 14:25    [19318947]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
MSCH
Есть indexed view которое суммирует количества в рамках каждого из товаров.


вы так и не показали код своей View
21 июн 16, 14:28    [19318964]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
MSCH
Member

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

MsLex отлично воспроизвел мой пример (см выше)
21 июн 16, 15:04    [19319150]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
MSCH
Есть таблица в которую пишутся операции:

PK - int
Код товара - varchar
Количество - int

Есть indexed view которое суммирует количества в рамках каждого из товаров.


вы что-то скрываете явно, у вас совсем другой вариант. а в описании вью я вообще вижу ерунду или она у вас один в один повторяет вашу таблицу
21 июн 16, 15:07    [19319159]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
MSCH
Подскажите, пожалуйста, путь как это можно сделать для примера MsLex
Да никак этого не сделать. aleks2 просто не понял, что вам нужно исключить блокирование писателя писателем.

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

В случае с конкурентной вставкой строк с новыми кодами товара вам уже озвучили причину - каждый новый код больше предыдущего.
Чтобы уменьшить негативный эффект этого, вам нужно рандомизировать код товара в представлении.
Например, добавив в таблицу вычисляемый столбец
str(rand([код товара]) * 1000000000000000, 16) + [код товара]
И представление делать уже на основе этого столбца.
21 июн 16, 15:23    [19319230]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Поправка:
str(rand(checksum([код товара])) * 1000000000000000, 16) + [код товара]


ЗЫ: Следует понимать, что такая рандомизация приведет к расщеплениям страниц при вставке.
21 июн 16, 15:27    [19319255]     Ответить | Цитировать Сообщить модератору
 Re: indexed view  [new]
aleks2
Guest
invm
MSCH
Подскажите, пожалуйста, путь как это можно сделать для примера MsLex
Да никак этого не сделать. aleks2 просто не понял, что вам нужно исключить блокирование писателя писателем.


Это ты ничо не понял.
Критическая секция - рулеззз.
21 июн 16, 17:22    [19319741]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить