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

Откуда: From Russia
Сообщений: 146
Вопрос по конструкции
insert into TableA
(
    Field1,
    Field2,
    Field3
)
select b.Field1, b.Field2, b.Field3
from TableB b
left join TableC1 on ....
left join TableC2 on ....
left join TableC3 on ....

Возник deadlock при параллельном вызове данного запроса. В логах сиквела видно, что оба процесса дошли до вызова insert и зависли. Вот keylocks:
автор
06/01/2012 17:49:55,spid22s,Unknown,keylock hobtid=72057594048086016 dbid=5 objectname=TableA indexname=PK_TableA id=lock23d00c880 mode=X associatedObjectId=72057594048086016
06/01/2012 17:49:55,spid22s,Unknown,keylock hobtid=72057594048086016 dbid=5 objectname=TableA indexname=PK_TableA id=lock15f0d5680 mode=X associatedObjectId=72057594048086016

Я не понимаю, каким образом в принципе здесь может возникнуть deadlock на инсерте в одну таблицу? Может дело в том, что при инсерте эксклюзивно лочатся еще и таблицы, из которых идет выборка select'ом?
SQL Server 2008 R2, БД без isolated snapshot.
7 июн 12, 17:44    [12683790]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по insert into MyTable select  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Разобраться в причинах дедлока без его графа - невозможно. То, что Вы привели - это блокировки одного процесса.
7 июн 12, 17:48    [12683811]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по insert into MyTable select  [new]
Kudep
Member

Откуда: From Russia
Сообщений: 146
pkarklin
Разобраться в причинах дедлока без его графа - невозможно. То, что Вы привели - это блокировки одного процесса.

Еще раз повторюсь, приходят параллельно запросы от двух разных процессов из двух сессий. Это видно по логам.
7 июн 12, 18:25    [12683995]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по insert into MyTable select  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Kudep
Возник deadlock при параллельном вызове данного запроса. В логах сиквела видно, что оба процесса дошли до вызова insert и зависли.
...
Я не понимаю, каким образом в принципе здесь может возникнуть deadlock на инсерте в одну таблицу?
Дедлоки разруливаются сервером каждые несколько секунд, после чего одна из транзакций прибивается с соответствующим сообщением об ошибке. Вы такое сообщение получили или просто слово красивое приплели для описания вашей ситуации?

Сообщение было отредактировано: 7 июн 12, 18:29
7 июн 12, 18:28    [12684007]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по insert into MyTable select  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Kudep,

В приведенно логе - одна сессия. И, Вы, не путаете лок, с дедлоком, который самостоятельно разрешается сервером?
7 июн 12, 18:36    [12684035]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по insert into MyTable select  [new]
Kudep
Member

Откуда: From Russia
Сообщений: 146
pkarklin
Kudep,

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

В приведенном логе нет инфы о кол-ве сессий. Я так понял, вы смотрите на spid22s. Это ID репортера, которые обнаружил и прибил один из процессов. Для каждого процесса имеется свой spid (выделены зеленым). Вот логи по данному случаю целиком:

06/01/2012 17:49:55,spid22s,Unknown,waiter id=process4a9b88 mode=X requestType=wait
06/01/2012 17:49:55,spid22s,Unknown,waiter-list
06/01/2012 17:49:55,spid22s,Unknown,owner id=process4c3b88 mode=X
06/01/2012 17:49:55,spid22s,Unknown,owner-list
06/01/2012 17:49:55,spid22s,Unknown,keylock hobtid=72057594048086016 dbid=5 objectname=TableA indexname=PK_TableA id=lock23d00c880 mode=X associatedObjectId=72057594048086016
06/01/2012 17:49:55,spid22s,Unknown,waiter id=process4c3b88 mode=X requestType=wait
06/01/2012 17:49:55,spid22s,Unknown,waiter-list
06/01/2012 17:49:55,spid22s,Unknown,owner id=process4a9b88 mode=X
06/01/2012 17:49:55,spid22s,Unknown,owner-list
06/01/2012 17:49:55,spid22s,Unknown,keylock hobtid=72057594048086016 dbid=5 objectname=TableA indexname=PK_TableA id=lock15f0d5680 mode=X associatedObjectId=72057594048086016
06/01/2012 17:49:55,spid22s,Unknown,resource-list
06/01/2012 17:49:55,spid22s,Unknown,Proc [Database Id = 5 Object Id = 861246123]
06/01/2012 17:49:55,spid22s,Unknown, ===== stack trace вплоть до инсерта=====
06/01/2012 17:49:55,spid22s,Unknown,insert into TableA
06/01/2012 17:49:55,spid22s,Unknown,frame procname=MyStoredProcName line=51 stmtstart=3710 stmtend=7090 sqlhandle=0x03000500ab925533cf663c015aa000000100000000000000
06/01/2012 17:49:55,spid22s,Unknown,executionStack
06/01/2012 17:49:55,spid22s,Unknown,process id=process4a9b88 taskpriority=0 logused=17160 waitresource=KEY: 5:72057594048086016 (2fe0985a7b83) waittime=2910 ownerId=770967530 transactionname=INSERT lasttranstarted=2012-06-01T17:49:52.833 XDES=0xbaabb950 lockMode=X schedulerid=2 kpid=2616 status=suspended spid=77 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-06-01T17:49:52.830 lastbatchcompleted=2012-06-01T17:49:52.830 clientapp=.Net SqlClient Data Provider hostname=MyServerName hostpid=7812 loginname=MyLoginName isolationlevel=read committed (2) xactid=770967530 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
06/01/2012 17:49:55,spid22s,Unknown,Proc [Database Id = 5 Object Id = 861246123]
06/01/2012 17:49:55,spid22s,Unknown,===== stack trace вплоть до инсерта=====
06/01/2012 17:49:55,spid22s,Unknown,insert into TableA
06/01/2012 17:49:55,spid22s,Unknown,frame procname=MyStoredProcName line=51 stmtstart=3710 stmtend=7090 sqlhandle=0x03000500ab925533cf663c015aa000000100000000000000
06/01/2012 17:49:55,spid22s,Unknown,executionStack
06/01/2012 17:49:55,spid22s,Unknown,process id=process4c3b88 taskpriority=0 logused=6708 waitresource=KEY: 5:72057594048086016 (ebeb807168ba) waittime=2910 ownerId=770967539 transactionname=INSERT lasttranstarted=2012-06-01T17:49:52.833 XDES=0xea397950 lockMode=X schedulerid=3 kpid=4064 status=suspended spid=96 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-06-01T17:49:52.833 lastbatchcompleted=2012-06-01T17:49:52.833 clientapp=.Net SqlClient Data Provider hostname=MyServerName hostpid=7812 loginname=MyLoginName isolationlevel=read committed (2) xactid=770967539 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
06/01/2012 17:49:55,spid22s,Unknown,process-list
06/01/2012 17:49:55,spid22s,Unknown,deadlock victim=process4c3b88
06/01/2012 17:49:55,spid22s,Unknown,deadlock-list
7 июн 12, 19:10    [12684149]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по insert into MyTable select  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Приведите структуру таблицы TableA. Это весь запрос? Просто вставка без всяких проверок на NOT EXISTS?
7 июн 12, 21:11    [12684392]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по insert into MyTable select  [new]
Kudep
Member

Откуда: From Russia
Сообщений: 146
pkarklin
Приведите структуру таблицы TableA. Это весь запрос? Просто вставка без всяких проверок на NOT EXISTS?

CREATE TABLE TableA(
	[Field1] [int] NOT NULL,
	[Field2] [nvarchar](100) NOT NULL,
	[Field3] [nvarchar](255) NULL,
	[Field4] [nvarchar](255) NULL,
	...,
 CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED 
(
	[Field1] ASC,
	[Field2] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [FG_Data_01]
) ON [FG_Data_01]

Foreign keys отсутствуют.
Primary CLUSTERED состоит из двух полей.
По запросу вставки, да, это весь запрос. Много разных полей вытаскивается в подзапросе, затем вставляется в TableA.
Может кто подскажет хоть, в какую сторону копать...
8 июн 12, 08:32    [12685125]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по insert into MyTable select  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Я бы добавил в запрос проверку на NOT EXISTS по полям первичного ключа и поднял бы TIL до SERIALIZABLE.
8 июн 12, 08:49    [12685188]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по insert into MyTable select  [new]
Дмитрий_sql
Member

Откуда:
Сообщений: 35
Kudep, а триггеров на инсерт нет?
8 июн 12, 10:15    [12685698]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по insert into MyTable select  [new]
Дмитрий_sql
Member

Откуда:
Сообщений: 35
Может дедлок происходит из-за тяжелого составного индекса?
Попробуй создать таблицу типа

CREATE TABLE TableA(
        id int not null identity(1,1) primary key,
	[Field1] [int] NOT NULL,
	[Field2] [nvarchar](100) NOT NULL,
	[Field3] [nvarchar](255) NULL,
	[Field4] [nvarchar](255) NULL,
	...,
) on primary

go
CREATE  unique NONCLUSTERED  INDEX [IX_TableA#id] ON [dbo].[esc_Client] 
(
	[Field1] ASC,
	[Field2] ASC
)
ON [FG_Data_01]


В результате примари кей меньше, и перед вставкой проверяйте уникальность.
8 июн 12, 10:26    [12685776]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по insert into MyTable select  [new]
Дмитрий_sql
Member

Откуда:
Сообщений: 35
Опечатался :-)
Дмитрий_sql
Может дедлок происходит из-за тяжелого составного индекса?
Попробуй создать таблицу типа

CREATE TABLE TableA(
        id int not null identity(1,1) primary key,
	[Field1] [int] NOT NULL,
	[Field2] [nvarchar](100) NOT NULL,
	[Field3] [nvarchar](255) NULL,
	[Field4] [nvarchar](255) NULL,
	...,
) on primary

go
CREATE  unique NONCLUSTERED  INDEX [IX_TableA#id] ON [dbo].[TableA] 
(
	[Field1] ASC,
	[Field2] ASC
)
ON [FG_Data_01]


В результате примари кей меньше, и перед вставкой проверяйте уникальность.
8 июн 12, 10:27    [12685781]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по insert into MyTable select  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
Дмитрий_sql
В результате примари кей меньше, и перед вставкой проверяйте уникальность.


а это-то зачем? т.е. был один индекс, а теперь 2?
пущай сиквел ещё одно Б-дерево колбасит?
8 июн 12, 10:41    [12685880]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по insert into MyTable select  [new]
Kudep
Member

Откуда: From Russia
Сообщений: 146
Триггеров нет.
А что касается кластерного индекса на ID, то тут есть свои нюансы. Записи будут отсортированы на диске по порядку согласно ID. И каждый новый инсерт будет пытаться вставить новую запись в одно и то же место. В моем случае, когда кластерный индекс состоит из двух бизнес-полей, то вставка новой записи будет производитсья в разные страницы БД. Так как очередной ключ ([Field1], [Field2]) не будет идти последним по порядку в отсортированном массиве ключей.

Всё-таки, я думаю, проблема связана со сложным подзапросов внутри инсерта. Я не понимаю, как это приводит к взаимным блокировкам, ведь подзапрос select не обращается к таблице TableA. Но тем не менее попробовал вынести этот подзапрос наверх, чтобы данные сохранялись сначала во временную табличку, а потом быстро копировались из временной в TableA.
8 июн 12, 13:40    [12687560]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по insert into MyTable select  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Kudep
А что касается кластерного индекса на ID, то тут есть свои нюансы. Записи будут отсортированы на диске по порядку согласно ID. И каждый новый инсерт будет пытаться вставить новую запись в одно и то же место. В моем случае, когда кластерный индекс состоит из двух бизнес-полей, то вставка новой записи будет производитсья в разные страницы БД.
Вау. А сколько тысяч записей в секунду у вас вставляется, что вас начала заботить скорость вставки в последовательный ключ?
8 июн 12, 13:52    [12687684]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по insert into MyTable select  [new]
Kudep
Member

Откуда: From Russia
Сообщений: 146
Гавриленко Сергей Алексеевич
Kudep
А что касается кластерного индекса на ID, то тут есть свои нюансы. Записи будут отсортированы на диске по порядку согласно ID. И каждый новый инсерт будет пытаться вставить новую запись в одно и то же место. В моем случае, когда кластерный индекс состоит из двух бизнес-полей, то вставка новой записи будет производитсья в разные страницы БД.
Вау. А сколько тысяч записей в секунду у вас вставляется, что вас начала заботить скорость вставки в последовательный ключ?

Я понял. По скорости разница минимальна. А как насчет блокировок? Если Clustered PK совпадает с Identity полем (например ID), то в этом случае, когда в очередной раз придет одновременно два запроса на вставку, то они дополнитеьлно будут упираться в блокируемый общий ресурс в виде пейджа. Разве не стоит учитывать этот момент?
8 июн 12, 14:51    [12688242]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по insert into MyTable select  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Kudep
Я понял. По скорости разница минимальна. А как насчет блокировок? Если Clustered PK совпадает с Identity полем (например ID), то в этом случае, когда в очередной раз придет одновременно два запроса на вставку, то они дополнитеьлно будут упираться в блокируемый общий ресурс в виде пейджа. Разве не стоит учитывать этот момент?
При вставке на страницу не накладываются блокировки. Только латчи.
8 июн 12, 14:53    [12688274]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по insert into MyTable select  [new]
Kudep
Member

Откуда: From Russia
Сообщений: 146
Гавриленко Сергей Алексеевич
При вставке на страницу не накладываются блокировки. Только латчи.

А у вас нет идей, как вообще в принципе на двух инсертах может возникать deadlock на одном и том же ресурсе (PK)?
Я вот только эту статью нашел про параллельные инсерты, интересно описано. Но там речь идет об инсертах в таблицу с foreign keys. Так что это не мой случай.
8 июн 12, 16:51    [12689375]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить