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

Откуда: г. Екатеринбург
Сообщений: 680
Добрый день.

Можно ли заставить данный запрос отработать. Суть такая в транзакции создаётся табличный тип, который в последствии используется, вот пример:

BEGIN TRANSACTION
GO

CREATE TYPE [test5] AS TABLE
( 
  [Index]					INT PRIMARY KEY
);
GO

DECLARE @T test5
INSERT INTO @T ([Index]) VALUES (589)
GO

COMMIT TRANSACTION
GO


момент "BEGIN TRANSACTION" и "COMMIT TRANSACTION" мне недоступен... вшито в программу обновления скриптов базы.
Данный код вызывает взаимоблокировку ресурсов, происходит из-за того, что табличный тип не зафиксирован в БД.
Вопрос, можно ли как-то зафиксировать часть транзакции?
10 авг 16, 11:09    [19523692]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировка ресурса, как быть  [new]
o-o
Guest
пишут, пофиксили уже баговину:
connect: Deadlock occurs when creating user-defined data type and objects that use it
но на 2008R2 SP3 воспроизводится
@@version
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (Intel X86) Aug 19 2014 12:21:07 Copyright (c) Microsoft Corporation Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)

сейчас на других версиях проверю
10 авг 16, 11:34    [19523827]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировка ресурса, как быть  [new]
o-o
Guest
на поздних версиях тоже воспроизводится.
наврали они, что пофиксили.
это же самое и в комментариях далее пишут.
вот версии серверов, где все то же самое:
автор
Microsoft SQL Server 2012 - 11.0.5569.0 (Intel X86) Jan 9 2015 11:41:41 Copyright (c) Microsoft Corporation Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)

Microsoft SQL Server 2014 - 12.0.4100.1 (Intel X86) Apr 20 2015 17:34:37 Copyright (c) Microsoft Corporation Express Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
10 авг 16, 12:09    [19524064]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировка ресурса, как быть  [new]
Denis Reznik
Member

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

Они то пофиксили, но сценарий другой в баге на коннекте:

USE tempdb
GO

BEGIN TRAN
CREATE TYPE dbo.TestType
FROM varchar(12) NOT NULL
GO
CREATE FUNCTION dbo.test_fn()
RETURNS @T TABLE(myColumn dbo.TestType NOT NULL) AS
BEGIN
RETURN
END
GO
COMMIT
10 авг 16, 12:17    [19524126]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировка ресурса, как быть  [new]
Denis Reznik
Member

Откуда: Киев
Сообщений: 156
Игорь_UUS,

автор
момент "BEGIN TRANSACTION" и "COMMIT TRANSACTION" мне недоступен...


а что вам доступно для изменения?

Похоже что заставить работать этот код как есть нельзя. При создании типа, SQL Server накладывает Sch-M блокировку на метаданные этого типа. При создании переменной типа ему требуется Sch-S блокировка на метаданные этого-же типа, для гарантии что его никто не изменит. И сейчас даже в одной сессии он не может поставить Sch-S блокировку. В принципе это можно рассматривать как баг и завести его на коннекте, но быстро его не пофиксят, если пофиксят вообще.

Что можно сделать: COMMIT после создания типа и BEGIN TRAN после это коммита, чтобы коммит в конце запроса не завалился.
10 авг 16, 12:41    [19524313]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировка ресурса, как быть  [new]
Denis Reznik
Member

Откуда: Киев
Сообщений: 156
Игорь_UUS,

и ещё вопрос к вам, это полный код или есть что-то ещё?

Почему спрашиваю, потому что после выполнения

DECLARE @T test5
INSERT INTO @T ([Index]) VALUES (589)
GO


следующая инструкция будет выполняться уже в другом батче и таблицной переменной уже не будет видно в этом запросе.
10 авг 16, 12:44    [19524338]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировка ресурса, как быть  [new]
o-o
Guest
Denis Reznik
o-o,

Они то пофиксили, но сценарий другой в баге на коннекте:

USE tempdb
GO

BEGIN TRAN
CREATE TYPE dbo.TestType
FROM varchar(12) NOT NULL
GO
CREATE FUNCTION dbo.test_fn()
RETURNS @T TABLE(myColumn dbo.TestType NOT NULL) AS
BEGIN
RETURN
END
GO
COMMIT

если они для какого-то конкретного сценария сделали,
то это еще не "пофиксили".
описание проблемы какое?
connect
When you try to create (in a single transaction) user-defined data type and any object that uses this type in a table variable a deadlock occurs.

вот это и есть у ТС.
и это НЕ пофиксили.
вот 2 графа, один с 2008-ого при создании функции:
+ gr1

Node:1
METADATA: database_id = 6 USER_TYPE(user_type_id = 257) CleanCnt:2 Mode:Sch-M Flags: 0x1
Grant List 1:
Owner:0x0502B340 Mode: Sch-M Flg:0x40 Ref:3 Life:02000000 SPID:51 ECID:0 XactLockInfo: 0x052D6C38
SPID: 51 ECID: 0 Statement Type: DECLARE Line #: 2
Input Buf: Language Event: CREATE FUNCTION dbo.test_fn() RETURNS @T TABLE(myColumn dbo.TestType NOT NULL) AS BEGIN RETURN END
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x05EBAE78 Mode: Sch-S SPID:51 BatchID:0 ECID:0 TaskProxy:(0x05422340) Value:0x502b6a0 Cost:(0/0)
NULL
Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x05EBAE78 Mode: Sch-S SPID:51 BatchID:0 ECID:0 TaskProxy:(0x05422340) Value:0x502b6a0 Cost:(0/0)
deadlock-list
deadlock victim=process2f6f390
process-list
process id=process2f6f390 taskpriority=0 logused=0 waitresource=METADATA: database_id = 6 USER_TYPE(user_type_id = 257) waittime=75 ownerId=738 transactionname=@T lasttranstarted=2016-08-10T11:45:52.827 XDES=0x5ebae78 lockMode=Sch-S schedulerid=2 kpid=20856 status=suspended spid=51 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2016-08-10T11:45:52.827 lastbatchcompleted=2016-08-10T11:45:52.827 clientapp=Microsoft SQL Server Management Studio - Query hostname=IS1P38HJ hostpid=5616 loginname=HD03\EE25989 isolationlevel=read committed (2) xactid=736 currentdb=2 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
executionStack
frame procname=adhoc line=2 sqlhandle=0x01000600778aab05f8ef6805000000000000000000000000
CREATE FUNCTION dbo.test_fn()
RETURNS @T TABLE(myColumn dbo.TestType NOT NULL) AS
BEGIN
RETURN
END
inputbuf
CREATE FUNCTION dbo.test_fn()
RETURNS @T TABLE(myColumn dbo.TestType NOT NULL) AS
BEGIN
RETURN
END
resource-list
metadatalock subresource=USER_TYPE classid=user_type_id = 257 dbid=6 id=lock5004e00 mode=Sch-M
owner-list
owner id=process2f6f390 mode=Sch-M
waiter-list
waiter id=process2f6f390 mode=Sch-S requestType=wait

второй с 2008 R2 SP3, где уже тот случай с функцией не воспроизводится, а случай ТС еще как:
+ gr2
Node:1
METADATA: database_id = 50 USER_TYPE(user_type_id = 257) CleanCnt:2 Mode:Sch-M Flags: 0x1
Grant List 0:
Owner:0x068F05E0 Mode: Sch-M Flg:0x40 Ref:6 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x061A88F0
SPID: 55 ECID: 0 Statement Type: INSERT Line #: 3
Input Buf: Language Event: DECLARE @T test5 INSERT INTO @T ([Index]) VALUES (589)
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x081A57F8 Mode: Sch-S SPID:55 BatchID:0 ECID:0 TaskProxy:(0x12010348) Value:0x68f0cc0 Cost:(0/0)
NULL
Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x081A57F8 Mode: Sch-S SPID:55 BatchID:0 ECID:0 TaskProxy:(0x12010348) Value:0x68f0cc0 Cost:(0/0)
deadlock-list
deadlock victim=process9bbab0
process-list
process id=process9bbab0 taskpriority=0 logused=0 waitresource=METADATA: database_id = 50 USER_TYPE(user_type_id = 257) waittime=4683 ownerId=2668012 transactionname=@T lasttranstarted=2016-08-10T10:16:43.567 XDES=0x81a57f8 lockMode=Sch-S schedulerid=1 kpid=13288 status=suspended spid=55 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2016-08-10T10:16:43.220 lastbatchcompleted=2016-08-10T10:16:43.220 clientapp=Microsoft SQL Server Management Studio - Query hostname=IS1P38HJ hostpid=5616 loginname=HD03\EE25989 isolationlevel=read committed (2) xactid=2667994 currentdb=50 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
executionStack
frame procname=adhoc line=3 sqlhandle=0x020000009f38c215563b1f7782f9b0bf84d4091bcba4007d
DECLARE @T test5
INSERT INTO @T ([Index]) VALUES (589)
inputbuf
DECLARE @T test5
INSERT INTO @T ([Index]) VALUES (589)
resource-list
metadatalock subresource=USER_TYPE classid=user_type_id = 257 dbid=50 id=lock692c240 mode=Sch-M
owner-list
owner id=process9bbab0 mode=Sch-M
waiter-list
waiter id=process9bbab0 mode=Sch-S requestType=wait

в чем отличие-то?
в обоиx случаях сам же себе захватил Sch-M и сам же ждет Sch-S.
ну в чем разница-то?
10 авг 16, 12:52    [19524393]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировка ресурса, как быть  [new]
Denis Reznik
Member

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

очень простая разница. Возьмите вот такой код и выполните его (это то что описано в баге и пофиксано):

BEGIN TRAN
CREATE TYPE dbo.TestType9
FROM varchar(12) NOT NULL
GO
CREATE FUNCTION dbo.test_fn9()
RETURNS @Table TABLE(myColumn dbo.TestType9 NOT NULL) 
AS
BEGIN 
	DECLARE @T TestType9 = 589
	INSERT @Table SELECT @T;
RETURN
END
GO

--SELECT * FROM dbo.test_fn9()

ROLLBACK


Теперь раскоментируйте строку перед ROLLBACK (это сценарий топикстартера)
10 авг 16, 13:15    [19524558]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировка ресурса, как быть  [new]
o-o
Guest
Denis Reznik,
словами-то можете выразить, разница в чем?
абсолютно одинаковый дедлок,
если нельзя заюзать тип в той же транзакции, где об этом в документации,
почему не валится на компиляции?
у запостившего на коннект четко сказано,
в одной транзакции создаем тип и объект, его использующий, получаем дедлок.
у ТС и идет создание типа и тут же объявление переменной с использованием этого типа.
что не так, что теперь не функция объявляет переменную этого типа, а код вне функции?
это принципиально?
10 авг 16, 14:04    [19524915]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировка ресурса, как быть  [new]
invm
Member

Откуда: Москва
Сообщений: 9687
o-o
ну в чем разница-то?
По сути разницы нет, потому что причина одна.
MS просто пофиксил частный случай, а не проблему в целом.
10 авг 16, 14:17    [19524960]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировка ресурса, как быть  [new]
Игорь_UUS
Member

Откуда: г. Екатеринбург
Сообщений: 680
Всем спасибо. Я решил пойти сложным, но гарантированным путём. Переписал скрипты)))) только ушло на это - практически день. Я почитал - это действительно баг у мелкософта, который они настойчиво не фиксят...
10 авг 16, 15:44    [19525443]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировка ресурса, как быть  [new]
Denis Reznik
Member

Откуда: Киев
Сообщений: 156
o-o
Denis Reznik,
почему не валится на компиляции?


А что в сиквеле валится на компиляции? :) Запрос, который не смог прожевать оптимизатор? Если вы по то почему дедлок а не рантайм ошибка, попробуйте запустить всю транзакцию в одном батче, получите просто ошибку что такого типа нет.

o-o
Denis Reznik,
у запостившего на коннект четко сказано,
в одной транзакции создаем тип и объект, его использующий, получаем дедлок.


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

o-o
Denis Reznik,
у ТС и идет создание типа и тут же объявление переменной с использованием этого типа.
что не так, что теперь не функция объявляет переменную этого типа, а код вне функции?
это принципиально?


Нет, это другой сценарий. Когда вы создаёте функцию (без SCHEMABINDING) вы можете использовать в теле функции таблицы (объекты, которых не существует на момент создания функции), и для содания фукнции блокировка на объекты не нужна в принципе. Вы получите ошибку выполнения если на момент вызова функции не будет соответствущей таблицы в базе. Когда вы создаёте переменную типа, SQL Server должен гарантировать что тип никто не изменит (из того что приходит в голову - статические методы класса), и поэтому он ставит SCH-S блокировку на объект-тип. Я не говорю что дедлок - это правильное поведение в этом случае, я говорю что это другая ситуация.
11 авг 16, 00:06    [19527308]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировка ресурса, как быть  [new]
o-o
Guest
Denis Reznik
А что в сиквеле валится на компиляции? :) Запрос, который не смог прожевать оптимизатор?

валится вот на таком:
if object_id('dbo.t') is not null drop table dbo.t;
create table dbo.t(id int);
insert dbo.t(id) values(1);
go
begin try
	alter table dbo.t add col int;
	update dbo.t
	set col= 2
	where id = 1
end try
begin catch
	print 'catch block'
end catch

блок catch не ловит, потому что это ошибка компиляции.
и это документированное поведение:
BOL
A table cannot be changed and then the new columns referenced in the same batch.

Batches
где что-то подобное про объявление типа и невозможность его использования в том же бэтче/транзакции?

Denis Reznik
o-o
Denis Reznik,
у запостившего на коннект четко сказано,
в одной транзакции создаем тип и объект, его использующий, получаем дедлок.


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

детский сад какой-то.
у него дано четкое словесное описание проблемы.
и одно репро.
что, 10 репро надо постить?
автор
претензия: ваши матрасы кривые, размер указан один, а на деле другой,
не влазят в заявленного размера кровать.
вот я купил матрас 120х190 клмн-45 белый в звездочку, вот моя кровать, вот не лезет
ответ: мы пофиксили.
приходит следующий на тот же сайт и пишет: где ж вы пофиксили-то, у меня снова не лезет,
вот мое репро, купил матрас 120х190 ёпрст-80 белый в синий горошек, не лезет!
ответ продавца: вы что, не видите, мы пофиксили для репро "120х190 клмн-45 белый в звездочку

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

Denis Reznik
o-o
Denis Reznik,
у ТС и идет создание типа и тут же объявление переменной с использованием этого типа.
что не так, что теперь не функция объявляет переменную этого типа, а код вне функции?
это принципиально?


Нет, это другой сценарий. Когда вы создаёте функцию (без SCHEMABINDING) вы можете использовать в теле функции таблицы (объекты, которых не существует на момент создания функции)...

deffered name resolution?
у вас при переводе слово выпало, но ничего, я всегда читаю оригинал.
только таблицы могут не существовать, но никак не типы
BOL
Deferred name resolution can only be used when you reference nonexistent table objects.
All other objects must exist at the time the stored procedure is created.
For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.

create proc dbo.usp_test2
as
  declare @t MyTableType;

поэтому ничего не откладывается до выполнения,
если в модуле объявлен несуществующий тип.
история с "откладыванием" не катит в данном случае
Msg 2715, Level 16, State 3, Procedure usp_test2, Line 1
Column, parameter, or variable #1: Cannot find data type MyTableType.

Parameter or variable '@t' has an invalid data type.
11 авг 16, 10:03    [19527873]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировка ресурса, как быть  [new]
Denis Reznik
Member

Откуда: Киев
Сообщений: 156
o-o
Denis Reznik
А что в сиквеле валится на компиляции? :) Запрос, который не смог прожевать оптимизатор?

валится вот на таком:
if object_id('dbo.t') is not null drop table dbo.t;
create table dbo.t(id int);
insert dbo.t(id) values(1);
go
begin try
	alter table dbo.t add col int;
	update dbo.t
	set col= 2
	where id = 1
end try
begin catch
	print 'catch block'
end catch

блок catch не ловит, потому что это ошибка компиляции.
и это документированное поведение


Блок catch не ловит, потому что запрос не доходит до стадии выполнения. Он валится на стадии алгебраизации, а именно на резолве имён. Но я понял что вы подразумеваете под компиляцией.

o-o
Denis Reznik
пропущено...
У запостившего в конект есть конкретный сценарий репро, который видно на коннекте и который я приводил выше. И вот он конкретно пофикшен, не пойму почему вы спорите.

детский сад какой-то.
у него дано четкое словесное описание проблемы.
и одно репро.
что, 10 репро надо постить?


Хорошо, давайте разберём что пишет товарищ в описании:

When you try to create (in a single transaction) user-defined data type and any object that uses this type in a table variable a deadlock occurs.
This scenario occurs when I want to change user-defined data type. I have to drop all objects depending on the type and recreate them with the type. All happens in a single transaction.

Поняли что он хочет? Объясняю, он меняет тип и ему нужно пересоздать все объекты, которые на него ссылаются. Ему не нужно объявлять в этой-же транзакции переменную типа и использовать её. Ловите разницу?

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


На заборе тоже много чего написано. А по факту - баг пофикшен. В коментариях народ кричит что не пофикшен и кидают свои репро в закрытый баг на не совсем их сценарий. А по хорошему надо новый баг завести, потому что а) Сценарий похожий, но другой; б) Что-то мне подсказывает что новый баг получит больше внимания от Майкрософт чем фидбек к закрытом багу (больше чем год после закрытия).
11 авг 16, 12:21    [19528733]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировка ресурса, как быть  [new]
o-o
Guest
Denis Reznik
Блок catch не ловит, потому что запрос не доходит до стадии выполнения. Он валится на стадии алгебраизации, а именно на резолве имён. Но я понял что вы подразумеваете под компиляцией.

я подразумеваю под компиляцией именно компиляцию, т.е. грубо говоря, построение плана,
получение набора инструкций к выполнению.
это то, что происходит после парсинга, но еще до выполнения,
это не есть "процесс сохранения текста" модуля, но это и еще не runtime.
а что же тогда компиляция в вашем понимании?
это когда набили текст модуля и нажали F5 в студии, пытаясь текст в базе сохранить?
11 авг 16, 12:39    [19528844]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировка ресурса, как быть  [new]
o-o
Guest
Denis Reznik
Хорошо, давайте разберём что пишет товарищ в описании:

When you try to create (in a single transaction) user-defined data type and any object that uses this type in a table variable a deadlock occurs.
This scenario occurs when I want to change user-defined data type. I have to drop all objects depending on the type and recreate them with the type. All happens in a single transaction.

в моем посте 19524393 вот эта часть его фразы и вынесена.
мне неинтересна вторая часть, где он обосновывает, зачем ему это надо.
поэтому я продолжу обсуждать первую.
вы намекаете, что я выпускаю из этой фразы ОБъЕКТ,
заюзавший тип?
any object that uses this type in a table variable
т.е. он ограничился лишь модулями, не упомянул о просто о куске кода,
который в той же транзакции пытается заюзать только что объявленный тип?
а дальше своего носа MS-овцам видеть запретили?
ну, радуйтесь с ними вместе, формально да, речь только о модулях.
а что дедлок тот же самый, причина все та же и просто костыль это всегда проще,
это в порядке вещей.
я при своем мнении останусь, а вы свое если кому еще хотите пропихнуть, то вон пожалуйста,
почему не в той же самой теме на коннекте,
там как раз атакующих больше, чем защитников
11 авг 16, 12:51    [19528907]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировка ресурса, как быть  [new]
o-o
Guest
По поводу заведения новой темы на коннекте соглашусь,
хорошо бы еще на нее оставить ссылку в старой теме,
я даже обычно иду до конца и не сдаюсь,
но что-то мне подсказывает, фиксить не будут,
так что на этот раз я пас.
11 авг 16, 13:07    [19529003]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить