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

Откуда: Ростов-на-Дону
Сообщений: 64
Здравствуйте.
На днях вылезло несколько Deadlock'ов. С помощью профайлера получил xml графом Deadlock'a, но не могу четко понять информацию отображенную в этом XML. Как перевести на нормальный язык это xml.

помогите разобраться с xml графом Deadlock'a :

<deadlock-list>
<deadlock victim="process88af28">
<process-list>
<process id="process88af28" taskpriority="0" logused="848" waitresource="OBJECT: 6:373576369:0 " waittime="1328" ownerId="12172558" transactionname="implicit_transaction" lasttranstarted="2009-12-14T17:10:21.150" XDES="0xffffffffb6affb78" lockMode="IS" schedulerid="1" kpid="6140" status="suspended" spid="57" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2009-12-14T17:10:21.210" lastbatchcompleted="2009-12-14T17:10:21.210" clientapp=" Программа "Сова"" hostname="IRINAK" hostpid="3448" loginname="veld2" isolationlevel="read committed (2)" xactid="12172558" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
<executionStack>
<frame procname="veld2.dbo.spKKSDCORE" line="26" stmtstart="1228" stmtend="1850" sqlhandle="0x030006005926857343481601b19c00000100000000000000">
SELECT f.*
INTO #fotsdcor
FROM fotsd f
LEFT JOIN #tmp2cor t ON f.kp_id=t.kp_id AND f.skl_id=t.skl_id AND f.uz_id=t.uz_id
ORDER BY f.dat DESC
--*) построение #fotsdcor

--*( получить курсор различных дат, с которых надо менять

--*( добавить в fotsd строку с нулевым количеством для дат, которых не было </frame>
<frame procname="veld2.dbo.spFOTSDCOM" line="6" stmtstart="224" stmtend="318" sqlhandle="0x03000600611d0b443df4c800d29b00000100000000000000">
EXEC spKKSDCORE @is_OK output , @soob output </frame>
<frame procname="veld2.dbo.TR_UZ1_3_U" line="305" stmtstart="20718" stmtend="20756" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
EXEC spFOTSDCOM </frame>
<frame procname="adhoc" line="1" stmtstart="70" sqlhandle="0x0200000061298413a69705a029071fe75716fa7e6cfb3e24">
UPDATE dbo.Uz1 SET datdok=@P1,datopl=@P2 WHERE uz1_id=@P3 </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P1 datetime,@P2 datetime,@P3 int)UPDATE dbo.Uz1 SET datdok=@P1,datopl=@P2 WHERE uz1_id=@P3 </inputbuf>
</process>
<process id="process88b3d8" taskpriority="0" logused="20017" waittime="875" schedulerid="1" kpid="4008" status="suspended" spid="53" sbid="0" ecid="7" priority="0" transcount="0" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова"" hostname="NIKORA" hostpid="3836" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
<executionStack>
<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
IF EXISTS -- проверка неотрицательности сальдо
(
SELECT
CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
FROM uz1
INNER JOIN uz ON uz.uz_id = uz1.uz_id
LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
) </frame>
<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
EXEC spKKVR_Saldo </frame>
<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5 </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="process919108" taskpriority="0" logused="20017" waittime="875" schedulerid="2" kpid="2352" status="suspended" spid="53" sbid="0" ecid="8" priority="0" transcount="0" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова"" hostname="NIKORA" hostpid="3836" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
<executionStack>
<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
IF EXISTS -- проверка неотрицательности сальдо
(
SELECT
CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
FROM uz1
INNER JOIN uz ON uz.uz_id = uz1.uz_id
LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
) </frame>
<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
EXEC spKKVR_Saldo </frame>
<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5 </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="process919a68" taskpriority="0" logused="20008" waittime="890" schedulerid="2" kpid="1348" status="suspended" spid="53" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова"" hostname="NIKORA" hostpid="3836" loginname="veld2" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
<executionStack>
<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
IF EXISTS -- проверка неотрицательности сальдо
(
SELECT
CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
FROM uz1
INNER JOIN uz ON uz.uz_id = uz1.uz_id
LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
) </frame>
<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
EXEC spKKVR_Saldo </frame>
<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5 </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P1 numeric(9,0),@P2 numeric(9,2),@P3 numeric(18,8),@P4 int,@P5 int)UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5 </inputbuf>
</process>
<process id="process91e5c8" taskpriority="0" logused="20017" waittime="875" schedulerid="3" kpid="3608" status="suspended" spid="53" sbid="0" ecid="2" priority="0" transcount="0" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова"" hostname="NIKORA" hostpid="3836" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
<executionStack>
<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
IF EXISTS -- проверка неотрицательности сальдо
(
SELECT
CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
FROM uz1
INNER JOIN uz ON uz.uz_id = uz1.uz_id
LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
) </frame>
<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
EXEC spKKVR_Saldo </frame>
<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5 </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="processbc45c8" taskpriority="0" logused="20017" waittime="875" schedulerid="4" kpid="3308" status="suspended" spid="53" sbid="0" ecid="1" priority="0" transcount="0" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова"" hostname="NIKORA" hostpid="3836" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
<executionStack>
<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
IF EXISTS -- проверка неотрицательности сальдо
(
SELECT
CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
FROM uz1
INNER JOIN uz ON uz.uz_id = uz1.uz_id
LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
) </frame>
<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
EXEC spKKVR_Saldo </frame>
<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5 </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="processc5a7a8" taskpriority="0" logused="20017" waittime="875" schedulerid="5" kpid="2696" status="suspended" spid="53" sbid="0" ecid="6" priority="0" transcount="0" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова"" hostname="NIKORA" hostpid="3836" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
<executionStack>
<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
IF EXISTS -- проверка неотрицательности сальдо
(
SELECT
CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
FROM uz1
INNER JOIN uz ON uz.uz_id = uz1.uz_id
LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
) </frame>
<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
EXEC spKKVR_Saldo </frame>
<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5 </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="processe70898" taskpriority="0" logused="20017" waittime="875" schedulerid="6" kpid="1736" status="suspended" spid="53" sbid="0" ecid="3" priority="0" transcount="0" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова"" hostname="NIKORA" hostpid="3836" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
<executionStack>
<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
IF EXISTS -- проверка неотрицательности сальдо
(
SELECT
CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
FROM uz1
INNER JOIN uz ON uz.uz_id = uz1.uz_id
LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
) </frame>
<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
EXEC spKKVR_Saldo </frame>
<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5 </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="processe70f28" taskpriority="0" logused="2008" waitresource="PAGE: 6:1:25725" waittime="875" ownerId="12169203" transactionname="implicit_transaction" lasttranstarted="2009-12-14T17:10:17.170" XDES="0xffffffffba1a8d80" lockMode="S" schedulerid="6" kpid="3252" status="suspended" spid="53" sbid="0" ecid="15" priority="0" transcount="0" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова"" hostname="NIKORA" hostpid="3836" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
<executionStack>
<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
IF EXISTS -- проверка неотрицательности сальдо
(
SELECT
CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
FROM uz1
INNER JOIN uz ON uz.uz_id = uz1.uz_id
LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
) </frame>
<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
EXEC spKKVR_Saldo </frame>
<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5 </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="processe87978" taskpriority="0" logused="20017" waittime="875" schedulerid="7" kpid="1296" status="suspended" spid="53" sbid="0" ecid="4" priority="0" transcount="0" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова"" hostname="NIKORA" hostpid="3836" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
<executionStack>
<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
IF EXISTS -- проверка неотрицательности сальдо
(
SELECT
CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
FROM uz1
INNER JOIN uz ON uz.uz_id = uz1.uz_id
LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
) </frame>
<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
EXEC spKKVR_Saldo </frame>
<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5 </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="processf9cc58" taskpriority="0" logused="20017" waittime="875" schedulerid="8" kpid="5728" status="suspended" spid="53" sbid="0" ecid="5" priority="0" transcount="0" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова"" hostname="NIKORA" hostpid="3836" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
<executionStack>
<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
IF EXISTS -- проверка неотрицательности сальдо
(
SELECT
CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
FROM uz1
INNER JOIN uz ON uz.uz_id = uz1.uz_id
LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
) </frame>
<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
EXEC spKKVR_Saldo </frame>
<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5 </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="25725" dbid="6" objectname="veld2.dbo.UZ1" id="lock367fa100" mode="IX" associatedObjectId="72057594161922048">
<owner-list>
<owner id="process88af28" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="processe70f28" mode="S" requestType="wait"/>
</waiter-list>
</pagelock>
<exchangeEvent id="portffffffff80198900" nodeId="17">
<owner-list>
<owner event="e_waitNone" type="producer" id="processe70f28"/>
</owner-list>
<waiter-list>
<waiter event="e_waitPipeGetRow" type="consumer" id="processe87978"/>
<waiter event="e_waitPipeGetRow" type="consumer" id="processbc45c8"/>
<waiter event="e_waitPipeGetRow" type="consumer" id="process91e5c8"/>
<waiter event="e_waitPipeGetRow" type="consumer" id="processf9cc58"/>
<waiter event="e_waitPipeGetRow" type="consumer" id="processe70898"/>
<waiter event="e_waitPipeGetRow" type="consumer" id="processc5a7a8"/>
<waiter event="e_waitPipeGetRow" type="consumer" id="process919108"/>
<waiter event="e_waitPipeGetRow" type="consumer" id="process88b3d8"/>
</waiter-list>
</exchangeEvent>
<exchangeEvent id="portffffffff80198680" nodeId="8">
<owner-list>
<owner event="e_waitNone" type="producer" id="process919108"/>
<owner event="e_waitNone" type="producer" id="processe87978"/>
<owner event="e_waitNone" type="producer" id="processbc45c8"/>
<owner event="e_waitNone" type="producer" id="process91e5c8"/>
<owner event="e_waitNone" type="producer" id="processe70898"/>
<owner event="e_waitNone" type="producer" id="processf9cc58"/>
<owner event="e_waitNone" type="producer" id="processc5a7a8"/>
<owner event="e_waitNone" type="producer" id="process88b3d8"/>
</owner-list>
<waiter-list>
<waiter event="e_waitPipeGetRow" type="consumer" id="process919a68"/>
</waiter-list>
</exchangeEvent>
<objectlock lockPartition="0" objid="373576369" subresource="FULL" dbid="6" objectname="veld2.dbo.FOTSD" id="lockffffffffad30c380" mode="X" associatedObjectId="373576369">
<owner-list>
<owner id="process919a68" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process88af28" mode="IS" requestType="wait"/>
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
</deadlock-list>


буду очень благодарен за помощь в данном вопросе.
15 дек 09, 13:28    [8068744]     Ответить | Цитировать Сообщить модератору
 Re: помогите разобраться с xml графом Deadlock'a  [new]
Masygreen
Member

Откуда: Москва
Сообщений: 558
<deadlock-list>
	<deadlock victim="process88af28">
		<process-list>
			<process id="process88af28" taskpriority="0" logused="848" waitresource="OBJECT: 6:373576369:0 " waittime="1328" ownerId="12172558" transactionname="implicit_transaction" lasttranstarted="2009-12-14T17:10:21.150" XDES="0xffffffffb6affb78" lockMode="IS" schedulerid="1" kpid="6140" status="suspended" spid="57" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2009-12-14T17:10:21.210" lastbatchcompleted="2009-12-14T17:10:21.210" clientapp=" Программа "Сова="""" hostname="IRINAK" hostpid="3448" loginname="veld2" isolationlevel="read committed (2)" xactid="12172558" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
				<executionStack>
					<frame procname="veld2.dbo.spKKSDCORE" line="26" stmtstart="1228" stmtend="1850" sqlhandle="0x030006005926857343481601b19c00000100000000000000">
						SELECT f.*
						INTO #fotsdcor
						FROM fotsd f
						LEFT JOIN #tmp2cor t ON f.kp_id=t.kp_id AND f.skl_id=t.skl_id AND f.uz_id=t.uz_id
						ORDER BY f.dat DESC
						--*) построение #fotsdcor

						--*( получить курсор различных дат, с которых надо менять

						--*( добавить в fotsd строку с нулевым количеством для дат, которых не было
					</frame>
					<frame procname="veld2.dbo.spFOTSDCOM" line="6" stmtstart="224" stmtend="318" sqlhandle="0x03000600611d0b443df4c800d29b00000100000000000000">
						EXEC spKKSDCORE @is_OK output , @soob output
					</frame>
					<frame procname="veld2.dbo.TR_UZ1_3_U" line="305" stmtstart="20718" stmtend="20756" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
						EXEC spFOTSDCOM
					</frame>
					<frame procname="adhoc" line="1" stmtstart="70" sqlhandle="0x0200000061298413a69705a029071fe75716fa7e6cfb3e24">
						UPDATE dbo.Uz1 SET datdok=@P1,datopl=@P2 WHERE uz1_id=@P3
					</frame>
					<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
						unknown
					</frame>
				</executionStack>
				<inputbuf>
					(@P1 datetime,@P2 datetime,@P3 int)UPDATE dbo.Uz1 SET datdok=@P1,datopl=@P2 WHERE uz1_id=@P3
				</inputbuf>
			</process>
			<process id="process88b3d8" taskpriority="0" logused="20017" waittime="875" schedulerid="1" kpid="4008" status="suspended" spid="53" sbid="0" ecid="7" priority="0" transcount="0" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова="""" hostname="NIKORA" hostpid="3836" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
				<executionStack>
					<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
						IF EXISTS -- проверка неотрицательности сальдо
						(
						SELECT
						CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
						FROM uz1
						INNER JOIN uz ON uz.uz_id = uz1.uz_id
						LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
						WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
						AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
						-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
						AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
						GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
						HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
)
							
					</frame>
					<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
						EXEC spKKVR_Saldo
					</frame>
					<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
						UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5
					</frame>
					<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
						unknown
					</frame>
				</executionStack>
				<inputbuf>
				</inputbuf>
			</process>
			<process id="process919108" taskpriority="0" logused="20017" waittime="875" schedulerid="2" kpid="2352" status="suspended" spid="53" sbid="0" ecid="8" priority="0" transcount="0" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова="""" hostname="NIKORA" hostpid="3836" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
				<executionStack>
					<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
						IF EXISTS -- проверка неотрицательности сальдо
						(
						SELECT
						CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
						FROM uz1
						INNER JOIN uz ON uz.uz_id = uz1.uz_id
						LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
						WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
						AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
						-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
						AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
						GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
						HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
)
							
					</frame>
					<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
						EXEC spKKVR_Saldo
					</frame>
					<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
						UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5
					</frame>
					<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
						unknown
					</frame>
				</executionStack>
				<inputbuf>
				</inputbuf>
			</process>
			<process id="process919a68" taskpriority="0" logused="20008" waittime="890" schedulerid="2" kpid="1348" status="suspended" spid="53" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова="""" hostname="NIKORA" hostpid="3836" loginname="veld2" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
				<executionStack>
					<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
						IF EXISTS -- проверка неотрицательности сальдо
						(
						SELECT
						CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
						FROM uz1
						INNER JOIN uz ON uz.uz_id = uz1.uz_id
						LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
						WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
						AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
						-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
						AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
						GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
						HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
)
							
					</frame>
					<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
						EXEC spKKVR_Saldo
					</frame>
					<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
						UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5
					</frame>
					<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
						unknown
					</frame>
				</executionStack>
				<inputbuf>
					(@P1 numeric(9,0),@P2 numeric(9,2),@P3 numeric(18,8),@P4 int,@P5 int)UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5
				</inputbuf>
			</process>
			<process id="process91e5c8" taskpriority="0" logused="20017" waittime="875" schedulerid="3" kpid="3608" status="suspended" spid="53" sbid="0" ecid="2" priority="0" transcount="0" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова="""" hostname="NIKORA" hostpid="3836" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
				<executionStack>
					<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
						IF EXISTS -- проверка неотрицательности сальдо
						(
						SELECT
						CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
						FROM uz1
						INNER JOIN uz ON uz.uz_id = uz1.uz_id
						LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
						WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
						AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
						-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
						AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
						GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
						HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
)
							
					</frame>
					<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
						EXEC spKKVR_Saldo
					</frame>
					<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
						UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5
					</frame>
					<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
						unknown
					</frame>
				</executionStack>
				<inputbuf>
				</inputbuf>
			</process>
			<process id="processbc45c8" taskpriority="0" logused="20017" waittime="875" schedulerid="4" kpid="3308" status="suspended" spid="53" sbid="0" ecid="1" priority="0" transcount="0" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова="""" hostname="NIKORA" hostpid="3836" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
				<executionStack>
					<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
						IF EXISTS -- проверка неотрицательности сальдо
						(
						SELECT
						CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
						FROM uz1
						INNER JOIN uz ON uz.uz_id = uz1.uz_id
						LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
						WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
						AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
						-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
						AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
						GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
						HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
)
							
					</frame>
					<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
						EXEC spKKVR_Saldo
					</frame>
					<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
						UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5
					</frame>
					<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
						unknown
					</frame>
				</executionStack>
				<inputbuf>
				</inputbuf>
			</process>
			<process id="processc5a7a8" taskpriority="0" logused="20017" waittime="875" schedulerid="5" kpid="2696" status="suspended" spid="53" sbid="0" ecid="6" priority="0" transcount="0" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова="""" hostname="NIKORA" hostpid="3836" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
				<executionStack>
					<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
						IF EXISTS -- проверка неотрицательности сальдо
						(
						SELECT
						CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
						FROM uz1
						INNER JOIN uz ON uz.uz_id = uz1.uz_id
						LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
						WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
						AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
						-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
						AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
						GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
						HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
)
							
					</frame>
					<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
						EXEC spKKVR_Saldo
					</frame>
					<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
						UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5
					</frame>
					<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
						unknown
					</frame>
				</executionStack>
				<inputbuf>
				</inputbuf>
			</process>
			<process id="processe70898" taskpriority="0" logused="20017" waittime="875" schedulerid="6" kpid="1736" status="suspended" spid="53" sbid="0" ecid="3" priority="0" transcount="0" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова="""" hostname="NIKORA" hostpid="3836" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
				<executionStack>
					<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
						IF EXISTS -- проверка неотрицательности сальдо
						(
						SELECT
						CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
						FROM uz1
						INNER JOIN uz ON uz.uz_id = uz1.uz_id
						LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
						WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
						AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
						-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
						AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
						GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
						HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
)
							
					</frame>
					<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
						EXEC spKKVR_Saldo
					</frame>
					<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
						UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5
					</frame>
					<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
						unknown
					</frame>
				</executionStack>
				<inputbuf>
				</inputbuf>
			</process>
			<process id="processe70f28" taskpriority="0" logused="2008" waitresource="PAGE: 6:1:25725" waittime="875" ownerId="12169203" transactionname="implicit_transaction" lasttranstarted="2009-12-14T17:10:17.170" XDES="0xffffffffba1a8d80" lockMode="S" schedulerid="6" kpid="3252" status="suspended" spid="53" sbid="0" ecid="15" priority="0" transcount="0" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова="""" hostname="NIKORA" hostpid="3836" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
				<executionStack>
					<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
						IF EXISTS -- проверка неотрицательности сальдо
						(
						SELECT
						CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
						FROM uz1
						INNER JOIN uz ON uz.uz_id = uz1.uz_id
						LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
						WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
						AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
						-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
						AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
						GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
						HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
)
							
					</frame>
					<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
						EXEC spKKVR_Saldo
					</frame>
					<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
						UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5
					</frame>
					<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
						unknown
					</frame>
				</executionStack>
				<inputbuf>
				</inputbuf>
			</process>
			<process id="processe87978" taskpriority="0" logused="20017" waittime="875" schedulerid="7" kpid="1296" status="suspended" spid="53" sbid="0" ecid="4" priority="0" transcount="0" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова="""" hostname="NIKORA" hostpid="3836" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
				<executionStack>
					<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
						IF EXISTS -- проверка неотрицательности сальдо
						(
						SELECT
						CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
						FROM uz1
						INNER JOIN uz ON uz.uz_id = uz1.uz_id
						LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
						WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
						AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
						-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
						AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
						GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
						HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
)
							
					</frame>
					<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
						EXEC spKKVR_Saldo
					</frame>
					<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
						UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5
					</frame>
					<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
						unknown
					</frame>
				</executionStack>
				<inputbuf>
				</inputbuf>
			</process>
			<process id="processf9cc58" taskpriority="0" logused="20017" waittime="875" schedulerid="8" kpid="5728" status="suspended" spid="53" sbid="0" ecid="5" priority="0" transcount="0" lastbatchstarted="2009-12-14T17:10:20.380" lastbatchcompleted="2009-12-14T17:10:20.380" clientapp=" Программа "Сова="""" hostname="NIKORA" hostpid="3836" isolationlevel="read committed (2)" xactid="12169203" currentdb="6" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128058">
				<executionStack>
					<frame procname="veld2.dbo.spKKVR_Saldo" line="1" stmtstart="134" stmtend="1612" sqlhandle="0x03000600b982ee5ab65c1f014f9c00000100000000000000">
						IF EXISTS -- проверка неотрицательности сальдо
						(
						SELECT
						CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
						FROM uz1
						INNER JOIN uz ON uz.uz_id = uz1.uz_id
						LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
						WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
						AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
						-- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то
						AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
						GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
						HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
)
							
					</frame>
					<frame procname="veld2.dbo.TR_UZ1_3_U" line="332" stmtstart="22748" stmtend="22792" sqlhandle="0x03000600df930f28a5d8d200d49c00000000000000000000">
						EXEC spKKVR_Saldo
					</frame>
					<frame procname="adhoc" line="1" stmtstart="138" sqlhandle="0x0200000098aa01084066d32bcee5a0738f7c070b274bd104">
						UPDATE dbo.Uz1 SET kolvo1=@P1,kolvo2=@P2,cena1=@P3,valuta_id=@P4 WHERE uz1_id=@P5
					</frame>
					<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
						unknown
					</frame>
				</executionStack>
				<inputbuf>
				</inputbuf>
			</process>
		</process-list>
		<resource-list>
			<pagelock fileid="1" pageid="25725" dbid="6" objectname="veld2.dbo.UZ1" id="lock367fa100" mode="IX" associatedObjectId="72057594161922048">
				<owner-list>
					<owner id="process88af28" mode="IX"/>
				</owner-list>
				<waiter-list>
					<waiter id="processe70f28" mode="S" requestType="wait"/>
				</waiter-list>
			</pagelock>
			<exchangeEvent id="portffffffff80198900" nodeId="17">
				<owner-list>
					<owner event="e_waitNone" type="producer" id="processe70f28"/>
				</owner-list>
				<waiter-list>
					<waiter event="e_waitPipeGetRow" type="consumer" id="processe87978"/>
					<waiter event="e_waitPipeGetRow" type="consumer" id="processbc45c8"/>
					<waiter event="e_waitPipeGetRow" type="consumer" id="process91e5c8"/>
					<waiter event="e_waitPipeGetRow" type="consumer" id="processf9cc58"/>
					<waiter event="e_waitPipeGetRow" type="consumer" id="processe70898"/>
					<waiter event="e_waitPipeGetRow" type="consumer" id="processc5a7a8"/>
					<waiter event="e_waitPipeGetRow" type="consumer" id="process919108"/>
					<waiter event="e_waitPipeGetRow" type="consumer" id="process88b3d8"/>
				</waiter-list>
			</exchangeEvent>
			<exchangeEvent id="portffffffff80198680" nodeId="8">
				<owner-list>
					<owner event="e_waitNone" type="producer" id="process919108"/>
					<owner event="e_waitNone" type="producer" id="processe87978"/>
					<owner event="e_waitNone" type="producer" id="processbc45c8"/>
					<owner event="e_waitNone" type="producer" id="process91e5c8"/>
					<owner event="e_waitNone" type="producer" id="processe70898"/>
					<owner event="e_waitNone" type="producer" id="processf9cc58"/>
					<owner event="e_waitNone" type="producer" id="processc5a7a8"/>
					<owner event="e_waitNone" type="producer" id="process88b3d8"/>
				</owner-list>
				<waiter-list>
					<waiter event="e_waitPipeGetRow" type="consumer" id="process919a68"/>
				</waiter-list>
			</exchangeEvent>
			<objectlock lockPartition="0" objid="373576369" subresource="FULL" dbid="6" objectname="veld2.dbo.FOTSD" id="lockffffffffad30c380" mode="X" associatedObjectId="373576369">
				<owner-list>
					<owner id="process919a68" mode="X"/>
				</owner-list>
				<waiter-list>
					<waiter id="process88af28" mode="IS" requestType="wait"/>
				</waiter-list>
			</objectlock>
		</resource-list>
	</deadlock>
</deadlock-list>

15 дек 09, 13:44    [8068852]     Ответить | Цитировать Сообщить модератору
 Re: помогите разобраться с xml графом Deadlock'a  [new]
Masygreen
Member

Откуда: Москва
Сообщений: 558
Ctrl+c
ctrl+v
в визуалку :)
15 дек 09, 13:45    [8068856]     Ответить | Цитировать Сообщить модератору
 Re: помогите разобраться с xml графом Deadlock'a  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
Masygreen
спасибо за ответ, но я не очень понял, что значит в "визуалку"?
15 дек 09, 13:53    [8068939]     Ответить | Цитировать Сообщить модератору
 Re: помогите разобраться с xml графом Deadlock'a  [new]
EvAlex
Member

Откуда: Israel
Сообщений: 995
spKKVR_Saldo - source надо
15 дек 09, 14:07    [8069045]     Ответить | Цитировать Сообщить модератору
 Re: помогите разобраться с xml графом Deadlock'a  [new]
Masygreen
Member

Откуда: Москва
Сообщений: 558
Microsoft Visual Studio 2005 конечно ..
оно само код форматирует - защита от удодов прогеров любящих в одну строчку без отступов куяритть говнокод ....
15 дек 09, 14:12    [8069080]     Ответить | Цитировать Сообщить модератору
 Re: помогите разобраться с xml графом Deadlock'a  [new]
Masygreen
Member

Откуда: Москва
Сообщений: 558
собсна этот текст в любой редактор держущий xml сунуть .. все + - будут доступны
15 дек 09, 14:13    [8069090]     Ответить | Цитировать Сообщить модератору
 Re: помогите разобраться с xml графом Deadlock'a  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
EvAlex

ALTER PROCEDURE [dbo].[spKKVR_Saldo] AS
BEGIN
IF EXISTS -- проверка неотрицательности сальдо
(
	SELECT 
		CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
	FROM uz1
		INNER JOIN uz ON uz.uz_id = uz1.uz_id
		LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
	WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
	  AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
	  -- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то 
	  AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
	GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
	HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
)
 BEGIN

	  DECLARE @soob VARCHAR(200)
      --SET @soob = 'Кол-во проданного меньше, чем кол-во, возвращенное и реализованное: код прих.='+CONVERT(CHAR(10),@nKp_id)  
	  SET @soob = 'Кол-во проданного меньше, чем кол-во, возвращенное и реализованное. Код товара = '+
       (SELECT TOP 1 
		-- CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END AS uz1_id
        STR(slkp.nmatr_id)
	FROM uz1
		INNER JOIN uz ON uz.uz_id = uz1.uz_id
        INNER JOIN slkp ON uz1.kp_id = slkp.kp_id 
		LEFT JOIN stdok ON uz1.stdok1_id = stdok.stdok_id
	WHERE uz.typeuz_id in (20,23,5,30) -- +Продажа -Возврат -Инфа от реализатора
	  AND (uz.typeuz_id <> 20 OR stdok.flrezervr=0) -- если продажа, то
	  -- AND (uz.typeuz_id <> 23 OR uz1.fixdok1=1) -- если возвр.от пок, то 
	  -- AND (CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END) IN (SELECT DISTINCT uz1_id from #change_list)
	GROUP BY CASE WHEN uz.typeuz_id IN (20,30) then uz1_id ELSE uz1p_id END
            ,slkp.nmatr_id     
	HAVING ISNULL(SUM(CASE WHEN uz.typeuz_id IN (20,30) THEN kolvo1 ELSE -kolvo1 END),0) < 0
)
     
      RAISERROR (@soob, 16, 10) 
      ROLLBACK TRAN
      RETURN
 END

END
15 дек 09, 14:16    [8069111]     Ответить | Цитировать Сообщить модератору
 Re: помогите разобраться с xml графом Deadlock'a  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
Masygreen
Не понял. вы мне предлагаете засунуть xml текст в редактор, чтобы появились + и - ?.... как мне это поможет в разборе xml? (я правда и так пользуюсь редактором который отображает нормально xml с + и -)
15 дек 09, 14:20    [8069170]     Ответить | Цитировать Сообщить модератору
 Re: помогите разобраться с xml графом Deadlock'a  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Два блока:
process-list - список процессов попавших в deadlock.
resource-list - блокированные ресурсы

В executionStack стек вызовов, читаем снизу вверх.
Например здесь первый процесс: veld2.dbo.TR_UZ1_3_U -> veld2.dbo.spFOTSDCOM -> veld2.dbo.spKKSDCORE -> SELECT
UPDATE dbo.Uz1 SET datdok=@P1,datopl=@P2 WHERE uz1_id=@P3
-- -->
EXEC spFOTSDCOM
-- -->
EXEC spKKSDCORE @is_OK output , @soob output
-- -->
SELECT f.*
INTO #fotsdcor
FROM fotsd f
LEFT JOIN #tmp2cor t ON f.kp_id=t.kp_id AND f.skl_id=t.skl_id AND f.uz_id=t.uz_id
ORDER BY f.dat DESC
Даже подписаны номера строк.

Блокированы: станица из veld2.dbo.UZ1 процессом process88af28 и полностью veld2.dbo.FOTSD процессом process919a68.
И кажись самое главное: exchangeEvent

Судя по всему у вас вышло такое ошибко:
Server: Msg 8650, Level 13, State 1, Line 1 Intra-query parallelism caused your server command (process ID #53) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).
Откопал отседова.

IMXO.
15 дек 09, 15:40    [8069849]     Ответить | Цитировать Сообщить модератору
 Re: помогите разобраться с xml графом Deadlock'a  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
кто нибудь?
15 дек 09, 15:40    [8069857]     Ответить | Цитировать Сообщить модератору
 Re: помогите разобраться с xml графом Deadlock'a  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
Mnior
пасибо :) ща посмотрю
15 дек 09, 15:42    [8069873]     Ответить | Цитировать Сообщить модератору
 Re: помогите разобраться с xml графом Deadlock'a  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
Mnior
вы можете истолковать мне xchangeEvent ?
15 дек 09, 15:46    [8069917]     Ответить | Цитировать Сообщить модератору
 Re: помогите разобраться с xml графом Deadlock'a  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Ваш случай
-------------------------------------
Jedem Das Seine
15 дек 09, 16:35    [8070376]     Ответить | Цитировать Сообщить модератору
 Re: помогите разобраться с xml графом Deadlock'a  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
Maxx
Спасибо. я уже прочитал. Mnior так же дал эту ссылочку.
15 дек 09, 16:41    [8070441]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить