Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 sp_send_dbmail deadlock  [new]
qwsqwsqwsq
Guest
Есть процедура exec qmx.ProcessQmxRequest @process_request;, Использует dmail для рассылки писем. Одновременно может быть несколько вызовов. Возникает deadlock. Как побороть. Граф дедлока мне ясен, но вот повлиять то я на кишки dmial не смогу. Уровень изоляции транзакции, открываемой в процедуре exec qmx.ProcessQmxRequest @process_request; read committed.

<deadlock-list>
 <deadlock victim="process1bb744188">
  <process-list>
   <process id="process1bb744188" taskpriority="0" logused="145892" waitresource="KEY: 4:72057594044350464 (0d86a26bb5f1)" waittime="9436" ownerId="22589043" transactionname="user_transaction" lasttranstarted="2014-06-27T15:01:55.150" XDES="0x18406ebd0" lockMode="S" schedulerid="1" kpid="8160" status="background" spid="33" sbid="0" ecid="0" priority="0" trancount="2">
    <executionStack>
     <frame procname="msdb.dbo.sp_send_dbmail" line="367" stmtstart="27482" stmtend="27670" sqlhandle="0x03000400e38d697018ceef0053a3000001000000000000000000000000000000000000000000000000000000">
IF EXISTS(SELECT * FROM sysmail_attachments_transfer WHERE uid = @temp_table_uid)     </frame>
     <frame procname="mydb.qmx.ProcessQmxRequest" line="84" stmtstart="4556" stmtend="5040" sqlhandle="0x03000700f975ce7d0bf2e90057a3000001000000000000000000000000000000000000000000000000000000">
exec msdb.dbo.sp_send_dbmail
				@profile_name = @sql_mail_profile,
				@from_address = @from_email,
				@recipients = @to_email,
				@subject = @mail_subject,
				@body_format = 'HTML',
				@file_attachments = @report_file_name;     </frame>
     <frame procname="mydb.qmx.ProcessQmxRequestQueue" line="44" stmtstart="1908" stmtend="2002" sqlhandle="0x03000700efb9f92702dff30057a3000001000000000000000000000000000000000000000000000000000000">
exec qmx.ProcessQmxRequest @process_request;     </frame>
    </executionStack>
    <inputbuf>
    </inputbuf>
   </process>
   <process id="process19ca2b498" taskpriority="0" logused="308952" waitresource="KEY: 4:72057594044350464 (14fee7739441)" waittime="5007" ownerId="22588784" transactionname="user_transaction" lasttranstarted="2014-06-27T15:01:47.140" XDES="0x18532f760" lockMode="S" schedulerid="1" kpid="7444" status="background" spid="12" sbid="0" ecid="0" priority="0" trancount="3">
    <executionStack>
     <frame procname="msdb.dbo.sp_send_dbmail" line="462" stmtstart="32734" stmtend="33178" sqlhandle="0x03000400e38d697018ceef0053a3000001000000000000000000000000000000000000000000000000000000">
INSERT INTO sysmail_attachments(mailitem_id, filename, filesize, attachment)
        SELECT @mailitem_id, filename, filesize, attachment
        FROM sysmail_attachments_transfer
        WHERE uid = @temp_table_uid     </frame>
     <frame procname="mydb.qmx.ProcessQmxRequest" line="84" stmtstart="4556" stmtend="5040" sqlhandle="0x03000700f975ce7d0bf2e90057a3000001000000000000000000000000000000000000000000000000000000">
exec msdb.dbo.sp_send_dbmail
				@profile_name = @sql_mail_profile,
				@from_address = @from_email,
				@recipients = @to_email,
				@subject = @mail_subject,
				@body_format = 'HTML',
				@file_attachments = @report_file_name;     </frame>
     <frame procname="mydb.qmx.ProcessQmxRequestQueue" line="44" stmtstart="1908" stmtend="2002" sqlhandle="0x03000700efb9f92702dff30057a3000001000000000000000000000000000000000000000000000000000000">
exec qmx.ProcessQmxRequest @process_request;     </frame>
    </executionStack>
    <inputbuf>
    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594044350464" dbid="4" objectname="msdb.dbo.sysmail_attachments_transfer" indexname="PK__sysmail___78E6FD33C37F3600" id="lock1a537c900" mode="X" associatedObjectId="72057594044350464">
    <owner-list>
     <owner id="process19ca2b498" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process1bb744188" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594044350464" dbid="4" objectname="msdb.dbo.sysmail_attachments_transfer" indexname="PK__sysmail___78E6FD33C37F3600" id="lock19c873900" mode="X" associatedObjectId="72057594044350464">
    <owner-list>
     <owner id="process1bb744188" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process19ca2b498" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>
27 июн 14, 15:32    [16229718]     Ответить | Цитировать Сообщить модератору
 Re: sp_send_dbmail deadlock  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
Можно индекс сделать
create index IX_smat__uid on dbo.sysmail_attachments_transfer (uid) include (filename, filesize, attachment);

А можно отлавливать dedlock и повторять транзакцию.
27 июн 14, 16:47    [16230327]     Ответить | Цитировать Сообщить модератору
 Re: sp_send_dbmail deadlock  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
одну очередь для отправки почты асинхронно
27 июн 14, 16:49    [16230339]     Ответить | Цитировать Сообщить модератору
 Re: sp_send_dbmail deadlock  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
а почему не сделать собственный семафор перед вызовом send_DBMail?
27 июн 14, 17:00    [16230425]     Ответить | Цитировать Сообщить модератору
 Re: sp_send_dbmail deadlock  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
А не хотите через SSIS почту слать
27 июн 14, 17:38    [16230612]     Ответить | Цитировать Сообщить модератору
 Re: sp_send_dbmail deadlock  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
автор
Одновременно может быть несколько вызовов.

Что-то оно не так вызывает, как надо. Используйте единый обработчик, письма ставьте в очередь хоть брокером, хоть в свою.
30 июн 14, 10:48    [16237178]     Ответить | Цитировать Сообщить модератору
 Re: sp_send_dbmail deadlock  [new]
fwefewfwef
Guest
Владислав Колосов
автор
Одновременно может быть несколько вызовов.

Что-то оно не так вызывает, как надо. Используйте единый обработчик, письма ставьте в очередь хоть брокером, хоть в свою.


Письма в очереди, поставлено 10 ридеров, могу конечно уменьшить для 1.
30 июн 14, 11:35    [16237472]     Ответить | Цитировать Сообщить модератору
 Re: sp_send_dbmail deadlock  [new]
jkhhiuhi
Guest
invm
Можно индекс сделать
create index IX_smat__uid on dbo.sysmail_attachments_transfer (uid) include (filename, filesize, attachment);

А можно отлавливать dedlock и повторять транзакцию.


совет с индексом помог - спасибо.

я верно понял механику?

при выполнении 2-х вызовов sp_send_mail было заблокированы какие-то строки, каждый заблокировал для своего uid, ниже по тексту sp_send_mail делаются два запроса с предикатом where uid = @..., а так как индекса нету на uid, то выполняется fullscan, каждый натыкается на заблокированную другим строку и ждет S-блокировку - deadlock. верно?
30 июн 14, 12:09    [16237762]     Ответить | Цитировать Сообщить модератору
 Re: sp_send_dbmail deadlock  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
jkhhiuhi
при выполнении 2-х вызовов sp_send_mail было заблокированы какие-то строки, каждый заблокировал для своего uid, ниже по тексту sp_send_mail делаются два запроса с предикатом where uid = @..., а так как индекса нету на uid, то выполняется fullscan, каждый натыкается на заблокированную другим строку и ждет S-блокировку - deadlock. верно?
Да.

Но индекс не самое лучшее решение с точки зрения надежности. И применим, если вам действительно необходимо параллелить вызовы sp_send_dbmail. Если нет, то наиболее универсальным будет сериализовать эти вызовы в вашей процедуре примерно так:
exec @result = sp_getapplock N'mydb.qmx.ProcessQmxRequest(sp_send_dbmail)', N'Exclusive', N'Transaction';
if @result ...

exec sp_send_dbmail ...

exec @result = sp_releaseapplock N'mydb.qmx.ProcessQmxRequest(sp_send_dbmail)', 'Transaction';
if @result ...
30 июн 14, 12:32    [16237889]     Ответить | Цитировать Сообщить модератору
 Re: sp_send_dbmail deadlock  [new]
Glory
Member

Откуда:
Сообщений: 104760
А выполнение sp_send_dbmail в транзакции обусловлено постановкой задачи ?
30 июн 14, 12:48    [16238003]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить