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

Откуда:
Сообщений: 27
Добрый день, подскажите пожалуйста как решить проблему. Имеется MS SQL Server 2008. Несколько приложение через ADO подключаются к одной базе. Все приложения одновременно в большом количестве делают INSERT и SELECT из одной таблицы. На один из INSERT происходит deadlock. Ado возвращает ошибку "1205".
10 мар 15, 13:41    [17364399]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
следите за порядком наложения блокировок...укорачивайте транзакции, повышайте уровень изоляции...
решений несколько - решать вам...для начала, начните с чтения графа
10 мар 15, 13:43    [17364416]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
SergunchikSK
Member

Откуда:
Сообщений: 27
Knyazev Alexey,

Транзакции у меня короткие в одном случает делается просто выборка одной строчки, а в другом простой INSERT. Данные приложения одинаковы, просто запускаются на разных машинах.
10 мар 15, 13:55    [17364505]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
Glory
Member

Откуда:
Сообщений: 104760
SergunchikSK
Транзакции у меня короткие в одном случает делается просто выборка одной строчки, а в другом простой INSERT.

Все так говорят. А граф потом показывает все по-другому.
10 мар 15, 13:57    [17364521]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
хмхмхм
Guest
SergunchikSK,

покажите для начала deadlock граф.
10 мар 15, 13:58    [17364527]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
SergunchikSK
Member

Откуда:
Сообщений: 27
хмхмхм,

<deadlock-list>
 <deadlock victim="process428b468">
  <process-list>
   <process id="process428b468" taskpriority="10" logused="636" waitresource="KEY: 7:72057594072399872 (6d862fc073ad)" waittime="3503" ownerId="181117" transactionname="INSERT" lasttranstarted="2015-03-10T14:11:13.250" XDES="0x44bf750" lockMode="S" schedulerid="2" kpid="8832" status="suspended" spid="52" sbid="0" ecid="0" priority="-10" trancount="2" lastbatchstarted="2015-03-10T14:11:13.250" lastbatchcompleted="2015-03-10T14:11:13.250" lastattention="1900-01-01T00:00:00.250" clientapp="Proga1" hostname="COMP2" hostpid="3068" loginname="sa" isolationlevel="read committed (2)" xactid="181117" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
    <executionStack>
     <frame procname="Vega.dbo.InsertNameIntoEventsProtocol" line="15" stmtstart="1002" stmtend="1172" sqlhandle="0x03000700958e81767c47b50057a40000000000000000000000000000000000000000000000000000">
SET @iCountRecord = (SELECT COUNT([EVENTS_PROTOCOL].[action]) FROM [EVENTS_PROTOCOL])     </frame>
     <frame procname="adhoc" line="1" stmtstart="234" sqlhandle="0x02000000423e2f0d9781f2d895073d29a90858dd7415c88800000000000000000000000000000000">
INSERT INTO [EVENTS_PROTOCOL]([event_guid],[status],[user_guid],[type],[guid],[action],[dtime],[params],[comp_name]) values(@1,@2,NULL,@3,@4,@5,CONVERT([datetime],@6,0),@7,@8)     </frame>
     <frame procname="adhoc" line="1" stmtstart="30" sqlhandle="0x020000007534df2086274823f0dfe0eafa12e0e05d98842700000000000000000000000000000000">
INSERT INTO [EVENTS_PROTOCOL] ([event_guid], [status], [user_guid], [type], [guid], [action], [dtime], [params], [comp_name]) VALUES (&apos;{C4893F6B-0730-40BD-BDEF-D464A99AA970}&apos;, 0, NULL, &apos;ZONE_OPS&apos;, &apos;{B7A6F8C9-63D2-45D4-B824-95C4288BE769}&apos;, 103, CAST(&apos;2015-10-03 14:11:12.883&apos; AS datetime), &apos;&lt;?xml version=&quot;1.0&quot;?&gt;&lt;MsgParameters&gt;&lt;Parameter Index=&quot;-2&quot; Type=&quot;3&quot; Value=&quot;&quot;/&gt;&lt;Parameter Index=&quot;-1&quot; Type=&quot;5&quot; Value=&quot;4676015363915728918&quot;/&gt;&lt;/MsgParameters&gt;&apos;, &apos;comp2&apos;)     </frame>
    </executionStack>
    <inputbuf>
SET NOCOUNT ON INSERT INTO [EVENTS_PROTOCOL] ([event_guid], [status], [user_guid], [type], [guid], [action], [dtime], [params], [comp_name]) VALUES (&apos;{C4893F6B-0730-40BD-BDEF-D464A99AA970}&apos;, 0, NULL, &apos;ZONE_OPS&apos;, &apos;{B7A6F8C9-63D2-45D4-B824-95C4288BE769}&apos;, 103, CAST(&apos;2015-10-03 14:11:12.883&apos; AS datetime), &apos;&lt;?xml version=&quot;1.0&quot;?&gt;&lt;MsgParameters&gt;&lt;Parameter Index=&quot;-2&quot; Type=&quot;3&quot; Value=&quot;&quot;/&gt;&lt;Parameter Index=&quot;-1&quot; Type=&quot;5&quot; Value=&quot;4676015363915728918&quot;/&gt;&lt;/MsgParameters&gt;&apos;, &apos;a-tochilin&apos;)    </inputbuf>
   </process>
   <process id="process4281468" taskpriority="10" logused="636" waitresource="KEY: 7:72057594072399872 (39450958143d)" waittime="3503" ownerId="181114" transactionname="INSERT" lasttranstarted="2015-03-10T14:11:13.250" XDES="0x1ace6290" lockMode="S" schedulerid="1" kpid="4016" status="suspended" spid="66" sbid="0" ecid="0" priority="-10" trancount="2" lastbatchstarted="2015-03-10T14:11:13.250" lastbatchcompleted="2015-03-10T14:11:13.250" lastattention="1900-01-01T00:00:00.250" clientapp="АРМ ПЦО «Эгида-3»" hostname="COMP" hostpid="4000" loginname="sa" isolationlevel="read committed (2)" xactid="181114" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
    <executionStack>
     <frame procname="Vega.dbo.InsertNameIntoEventsProtocol" line="15" stmtstart="1002" stmtend="1172" sqlhandle="0x03000700958e81767c47b50057a40000000000000000000000000000000000000000000000000000">
SET @iCountRecord = (SELECT COUNT([EVENTS_PROTOCOL].[action]) FROM [EVENTS_PROTOCOL])     </frame>
     <frame procname="adhoc" line="1" stmtstart="234" sqlhandle="0x02000000423e2f0d9781f2d895073d29a90858dd7415c88800000000000000000000000000000000">
INSERT INTO [EVENTS_PROTOCOL]([event_guid],[status],[user_guid],[type],[guid],[action],[dtime],[params],[comp_name]) values(@1,@2,NULL,@3,@4,@5,CONVERT([datetime],@6,0),@7,@8)     </frame>
     <frame procname="adhoc" line="1" stmtstart="30" sqlhandle="0x0200000080e7a92b316a81d8b2444c456efa8da357f74b6f00000000000000000000000000000000">
INSERT INTO [EVENTS_PROTOCOL] ([event_guid], [status], [user_guid], [type], [guid], [action], [dtime], [params], [comp_name]) VALUES (&apos;{60C2BEDD-4D04-49FA-8FF4-74B4C97D74F5}&apos;, 0, NULL, &apos;ZONE_OPS&apos;, &apos;{57556F4A-D882-471B-B760-97A4E46C1B92}&apos;, 103, CAST(&apos;2015-10-03 14:11:13.246&apos; AS datetime), &apos;&lt;?xml version=&quot;1.0&quot;?&gt;&lt;MsgParameters&gt;&lt;Parameter Index=&quot;-2&quot; Type=&quot;3&quot; Value=&quot;&quot;/&gt;&lt;Parameter Index=&quot;-1&quot; Type=&quot;5&quot; Value=&quot;4676015363916306352&quot;/&gt;&lt;/MsgParameters&gt;&apos;, &apos;comp&apos;)     </frame>
    </executionStack>
    <inputbuf>
SET NOCOUNT ON INSERT INTO [EVENTS_PROTOCOL] ([event_guid], [status], [user_guid], [type], [guid], [action], [dtime], [params], [comp_name]) VALUES (&apos;{60C2BEDD-4D04-49FA-8FF4-74B4C97D74F5}&apos;, 0, NULL, &apos;ZONE_OPS&apos;, &apos;{57556F4A-D882-471B-B760-97A4E46C1B92}&apos;, 103, CAST(&apos;2015-10-03 14:11:13.246&apos; AS datetime), &apos;&lt;?xml version=&quot;1.0&quot;?&gt;&lt;MsgParameters&gt;&lt;Parameter Index=&quot;-2&quot; Type=&quot;3&quot; Value=&quot;&quot;/&gt;&lt;Parameter Index=&quot;-1&quot; Type=&quot;5&quot; Value=&quot;4676015363916306352&quot;/&gt;&lt;/MsgParameters&gt;&apos;, &apos;comp&apos;)    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594072399872" dbid="7" objectname="Vega.dbo.EVENTS_PROTOCOL" indexname="1" id="lock13a54400" mode="X" associatedObjectId="72057594072399872">
    <owner-list>
     <owner id="process4281468" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process428b468" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594072399872" dbid="7" objectname="Vega.dbo.EVENTS_PROTOCOL" indexname="1" id="lock13a69e80" mode="X" associatedObjectId="72057594072399872">
    <owner-list>
     <owner id="process428b468" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process4281468" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>
10 мар 15, 14:20    [17364736]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
Konst_One
Member

Откуда:
Сообщений: 11538
скрипт таблички своей выложите
у вас там поле action в индексе или как?
10 мар 15, 14:31    [17364832]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
SergunchikSK
Member

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

CREATE TABLE [dbo].[EVENTS_PROTOCOL](
[event_guid] [uniqueidentifier] NOT NULL,
[status] [int] NULL,
[type] [nvarchar](150) NULL,
[guid] [uniqueidentifier] NULL,
[action] [int] NULL,
[dtime] [datetime] NULL,
[params] [nvarchar](max) NULL,
[user_guid] [uniqueidentifier] NULL,
[comp_name] [nvarchar](150) NULL,
CONSTRAINT [PK_EVENTS_PROTOCOL] PRIMARY KEY CLUSTERED
(
[event_guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
[/SRC]

Да еще забыл сказать на таблице висит тригер 
[src]
ALTER TRIGGER [dbo].[InsertNameIntoEventsProtocol] ON [dbo].[EVENTS_PROTOCOL]
FOR INSERT
AS
SET NOCOUNT ON
SET DEADLOCK_PRIORITY -10
DECLARE @error_code int = 0
DECLARE @iCountRecord int = 0
DECLARE @iCountMAX int = 1000
DECLARE @Temp TABLE (event_guid uniqueidentifier,
status int, type nvarchar(150), guid uniqueidentifier,
action int, dtime datetime, params nvarchar(max), user_guid uniqueidentifier, comp_name nvarchar(150))
DECLARE @AllTran VARCHAR(8) ='ALLTRAN'
begin TRANSACTION @AllTran
SET @iCountRecord = (SELECT COUNT([EVENTS_PROTOCOL].[action]) FROM [EVENTS_PROTOCOL])
IF @iCountRecord > @iCountMAX
begin
SET @error_code = 0
DELETE FROM  @Temp
insert into @Temp select top 500 * from [EVENTS_PROTOCOL] order by [EVENTS_PROTOCOL].[dtime]
INSERT INTO [HISTORY_PROTOCOL] select * from @Temp
IF @@ERROR <> 0
SET @error_code = -1
DELETE FROM [EVENTS_PROTOCOL] where  [EVENTS_PROTOCOL].[event_guid] in (select event_guid from @Temp)
IF @@ERROR <> 0
SET @error_code = -1
end
select top 1 [EVENTS_PROTOCOL].[action] from EVENTS_PROTOCOL where convert(date, GETDATE()) <> convert(date, EVENTS_PROTOCOL.dtime)
if @@ROWCOUNT > 0
begin
DELETE FROM  @Temp
insert into @Temp select * from EVENTS_PROTOCOL where convert(date, GETDATE()) <> convert(date, EVENTS_PROTOCOL.dtime)
IF @@ERROR <> 0
SET @error_code = -1
insert into HISTORY_PROTOCOL select * from  @Temp
IF @@ERROR <> 0
SET @error_code = -1
DELETE FROM [EVENTS_PROTOCOL] where  [EVENTS_PROTOCOL].[event_guid] in (select event_guid from @Temp)
IF @@ERROR <> 0
SET @error_code = -1
end
IF @error_code <> 0
ROLLBACK TRANSACTION  @AllTran
else
COMMIT TRANSACTION  @AllTran
10 мар 15, 14:35    [17364855]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
SergunchikSK,

виновник торжества в триггере

SET @iCountRecord = (SELECT COUNT([EVENTS_PROTOCOL].[action]) FROM [EVENTS_PROTOCOL])


пересмотрите свой метод архивирования записей (джобом или partitioning рассмотрите со скользящим окном...). или втулите туда костыль

SET @iCountRecord = (SELECT COUNT([EVENTS_PROTOCOL].[action]) FROM [EVENTS_PROTOCOL] with(readpast))
10 мар 15, 14:43    [17364897]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
SergunchikSK
Member

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

джобом не могу, так как в Express нет их
10 мар 15, 14:46    [17364918]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
SergunchikSK
churupaha,

джобом не могу, так как в Express нет их


зато есть sqlcmd и виндовый шедулер.
10 мар 15, 14:48    [17364931]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
хмхмхм
Guest
SergunchikSK
churupaha,

джобом не могу, так как в Express нет их


На сервере есть хорошая компонента Task Scheduler, пишется скрипт, который запускается из bat и вот вам готовый SQL Job Agent.
Ваш метод удаления в триггере мне кажется совершенно неправильным.
10 мар 15, 14:48    [17364937]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
+

вместо with(readpast) судя по задаче можно и with(nolock)
10 мар 15, 14:49    [17364945]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
SergunchikSK
Member

Откуда:
Сообщений: 27
хмхмхм,

Мне он самому не нравился. Сделал костыль вместо джоба
10 мар 15, 14:58    [17365033]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
SergunchikSK
костыль вместо джоба


+ хороший стук наружу выйдет

Картинка с другого сайта.
10 мар 15, 15:01    [17365049]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
SergunchikSK
Member

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

Task Scheduler тоже не нравиться
10 мар 15, 15:02    [17365057]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
Konst_One
Member

Откуда:
Сообщений: 11538
а зачем вам под транзакцией кол-во считать?

...
begin TRANSACTION @AllTran
SET @iCountRecord = (SELECT COUNT([EVENTS_PROTOCOL].[action]) FROM [EVENTS_PROTOCOL])
IF @iCountRecord > @iCountMAX
...
10 мар 15, 15:07    [17365088]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
Glory
Member

Откуда:
Сообщений: 104760
А зачем вообще транзакция в триггере ???
10 мар 15, 15:10    [17365112]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
SergunchikSK,

а с какой целью записи архивируются? у вас там express...

+ может хватит и такого

1) выпилюем триггер
2) строим индекс по dtime
3) приложение в вашу таблицу событий ходит так select top(N) ... from t order by dtime desc. прочитает чуть-чуть страниц индексов справа и чуть-чуть лукапов в основную таблицу...
4) если приспичило иногда руками выгружаем все в csv... (или всеже шедулером по расписанию)
10 мар 15, 15:13    [17365129]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
SergunchikSK
Member

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

Так так необходимо сбрасывать из этой таблицы данные в другую, превращающие определенное количество строк
10 мар 15, 15:13    [17365135]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
хмхмхм
Guest
SergunchikSK
хмхмхм,

Мне он самому не нравился. Сделал костыль вместо джоба


У вас и в триггере что-то непонятное:

1. Зачем делать Select из той же таблицы в транзакции? Почему бы его не вывести за транзакцию?
2. Вот это:

insert into @Temp
select top 500
*
from
[EVENTS_PROTOCOL]
order by
[EVENTS_PROTOCOL].[dtime]

insert into [HISTORY_PROTOCOL]
select
*
from
@Temp



логичнее сделать в триггере на delete

3. select top 1
[EVENTS_PROTOCOL].[action]
from
EVENTS_PROTOCOL
where
convert(date, getdate()) <> convert(date, EVENTS_PROTOCOL.dtime)


Условие с конвертацией за гранью добра и зла. Надо переделать, чтобы был index seek

4. Все бы хорошо переделать в разовое выполнение, а не в триггере. Но уж если хочется мучить сервер каждый раз, то надо бы в начале процедуры проверять условия и делать return до транзакции и вставок\удалений.
10 мар 15, 15:16    [17365152]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
SergunchikSK
Добрый день, подскажите пожалуйста как решить проблему. Имеется MS SQL Server 2008. Несколько приложение через ADO подключаются к одной базе. Все приложения одновременно в большом количестве делают INSERT и SELECT из одной таблицы. На один из INSERT происходит deadlock. Ado возвращает ошибку "1205".


Вообще есть рекомендация перейти на Read Committed Snashot, с оговоркой, что это не даёт 100% гарантии избавиться от Deadlock, а лишь снизит их интенсивность. https://technet.microsoft.com/en-us/library/ms191242(v=sql.105).aspx

Ваше приложение должно исходить из того, что в процессе работы оно может получить deadlock.
10 мар 15, 15:19    [17365172]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
http://www.mssqltips.com/sqlservertip/3188/implementing-sql-server-transaction-retry-logic-for-failed-transactions/
10 мар 15, 15:21    [17365193]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
Glory
Member

Откуда:
Сообщений: 104760
хмхмхм
У вас и в триггере что-то непонятное:

Во-во
Архивирование делается простым
DELETE OUTPUT
И не в триггере, а после команды INSERT
10 мар 15, 15:26    [17365243]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с deadlock  [new]
хмхмхм
Guest
SergunchikSK
Konst_One,

Так так необходимо сбрасывать из этой таблицы данные в другую, превращающие определенное количество строк


Так зачем в транзакции то кол-во получать?
10 мар 15, 15:28    [17365255]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить