开发者

SQL Server Deadlock Problem - How to Resolve?

开发者 https://www.devze.com 2023-01-05 01:27 出处：网络
We have a SQL Job (2005) that from time to time will fail due a deadlock. The error is as follows: Transaction (Process ID 52) was deadlocked on thread | communication buffer resources with another p
相关专题：buffercommunicationmultithreadingsql-server-2005

We have a SQL Job (2005) that from time to time will fail due a deadlock. The error is as follows:

Transaction (Process ID 52) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.

This is an overnight job which effectively loads a reporting database and performs various corrections to the data. There are no other DML statements running simultaneously. The error occurs about once a week on differing days and at different points in the data load process. So I am presuming it is not due to DML statements.

Turning on Trace Flag 1222 provides information that is next to useless (log records below). Most of the articles that I have read are related to transactions and queries and therefore locks related to data pages/tables but not many related to memory/threads/buffer resources.

I ran PerfMon with all counters that seemed relevant (Network related, Available threads, Available Work Items, Log Buffer Waits as well as the usual ones such as Processor & memory related) but there was nothing immediately obvious.

06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7862208
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process5caf63e8
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process55a38118
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process4e95a2f8
06/21/2010 08:17:30,spid5s,Unknown,waiter-list
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process4e95a6b8
06/21/2010 08:17:30,spid5s,Unknown,owner event=e_waitNone type=producer id=process78622f8
06/21/2010 08:17:30,spid5s,Unknown,owner-list
06/21/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152d80 nodeId=76
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process5caf64d8
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process4e95a3e8
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process55a38208
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process78622f8
06/21/2010 08:17:30,spid5s,Unknown,waiter-list
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process4e95a6b8
06/21/2010 08:17:30,spid5s,Unknown,owner event=e_waitNone type=producer id=process55a382f8
06/21/2010 08:17:30,spid5s,Unknown,owner-list
06/21/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152b80 nodeId=79
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process4e95a4d8
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process78623e8
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process5caf65c8
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process55a382f8
06/21/2010 08:17:30,spid5s,Unknown,waiter-list
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process4e95a6b8
06/21/2010 08:17:30,spid5s,Unknown,owner event=e_waitNone type=producer id=process78624d8
06/21/2010 08:17:30,spid5s,Unknown,owner-list
06/21/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152980 nodeId=81
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process5caf6208
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7ffe28
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7ebd38
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process4e95a118
06/21/2010 08:17:30,spid5s,Unknown,waiter-list
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process4e95a6b8
06/21/2010 08:17:30,spid5s,Unknown,owner event=e_waitNone type=producer id=process55a38118
06/21/2010 08:17:30,spid5s,Unknown,owner-list
06/21/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152380 nodeId=72
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7fea78
06/21/2010 08:17:30,spid5s,Unknown,waiter-list
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process4e95a6b8
06/21/2010 08:17:30,spid5s,Unknown,owner event=e_waitNone type=producer id=processd75d38
06/21/2010 08:17:30,spid5s,Unknown,owner-list
06/21/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152780 nodeId=2
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7ebf18
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7f9f18
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7fed48
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=processd75d38
06/21/2010 08:17:30,spid5s,Unknown,waiter-list
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process4e95a6b8
06/21/2010 08:17:30,spid5s,Unknown,owner event=e_waitNone type=producer id=processd74e38
06/21/2010 08:17:30,spid5s,Unknown,owner-list
06/21/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152200 nodeId=59
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=processd74e38
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7f9d38
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7ff2e8
06/21/2010 08:17:30,spid5s,Unknown,waiter-list
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process4e95a6b8
06/21/2010 08:17:30,spid5s,Unknown,owner event=e_waitNone type=producer id=processd754c8
06/21/2010 08:17:30,spid5s,Unknown,owner-list
06/21/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152e00 nodeId=63
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7ffa68
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=processd754c8
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7f8898
06/21/2010 08:17:30,spid5s,Unknown,waiter-list
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process4e95a6b8
06/21/2010 08:17:30,spid5s,Unknown,owner event=e_waitNone type=producer id=process5caf6208
06/21/2010 08:17:30,spid5s,Unknown,owner-list
06/21/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152100 nodeId=68
06/21/2010 08:17:30,spid5s,Unknown,waiter id=process55a384d8
06/21/2010 08:17:30,spid5s,Unknown,waiter-list
06/21/2010 08:17:30,spid5s,Unknown,owner id=process7fea78
06/21/2010 08:17:30,spid5s,Unknown,owner id=process7fed48
06/21/2010 08:17:30,spid5s,Unknown,owner id=process7ff2e8
06/21/2010 08:17:30,spid5s,Unknown,owner id=process7ffa68
06/21/2010 08:17:30,spid5s,Unknown,owner id=process7ffe28
06/21/2010 08:17:30,spid5s,Unknown,owner id=process55a38118
06/21/2010 08:17:30,spid5s,Unknown,owner id=process55a38208
06/21/2010 08:17:30,spid5s,Unknown,owner id=process55a382f8
06/21/2010 08:17:30,spid5s,Unknown,owner id=process55a383e8
06/21/2010 08:17:30,spid5s,Unknown,owner-list
06/21/2010 08:17:30,spid5s,Unknown,threadpool id=schedulerce2040
06/21/2010 08:17:30,spid5s,Unknown,waiter id=process4e95a6b8
06/21/2010 08:17:30,spid5s,Unknown,waiter-list
06/21/2010 08:17:30,spid5s,Unknown,owner id=processd74e38
06/21/2010 08:17:30,spid5s,Unknown,owner id=processd754c8
06/21/2010 08:17:30,spid5s,Unknown,owner id=processd75d38
06/21/2010 08:17:30,spid5s,Unknown,owner id=process4e95a118
06/21/2010 08:17:30,spid5s,Unknown,owner id=process4e95a2f8
06/21/2010 08:17:30,spid5s,Unknown,owner id=process4e95a3e8
06/21/2010 08:17:30,spid5s,Unknown,owner id=process4e95a4d8
06/21/2010 08:17:30,spid5s,Unknown,owner id=process4e95a5c8
06/21/2010 08:17:30,spid5s,Unknown,owner-list
06/21/2010 08:17:30,spid5s,Unknown,threadpool id=schedulerd78040
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process4e95a5c8
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process55a383e8
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process78624d8
06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process5caf66b8
06/21/2010 08:17:30,spid5s,Unknown,waiter-list
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process4e95a6b8
06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process5caf67a8
06/21/2010 08:17:30,spid5s,Unknown,owner-list
06/21/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152f00 nodeId=84
06/21/2010 08:17:30,spid5s,Unknown,waiter id=process5caf67a8
06/21/2010 08:17:30,spid5s,Unknown,waiter-list
06/21/2010 08:17:30,spid5s,Unknown,owner id=process7f8898
06/21/2010 08:17:30,spid5s,Unknown,owner id=process7f9d38
06/21/2010 08:17:30,spid5s,Unknown,owner id=process7f9f18
06/21/2010 08:17:30,spid5s,Unknown,owner id=process5caf6208
06/21/2010 08:17:30,spid5s,Unknown,owner id=process5caf63e8
06/21/2010 08:17:30,spid5s,Unknown,owner id=process5caf64d8
06/21/2010 08:17:30,spid5s,Unknown,owner id=process5caf65c8
06/21/2010 08:17:30,spid5s,Unknown,owner id=process5caf66b8
06/21/2010 08:17:30,spid5s,Unknown,owner-list
06/21/2010 08:17:30,spid5s,Unknown,threadpool id=schedulerbd0040
06/21/2010 08:17:30,spid5s,Unknown,resource-list
06/21/2010 08:17:30,spid5s,Unknown,process id=process5caf67a8 schedulerid=2 kpid=0
06/21/2010 08:17:30,spid5s,Unknown,inputbuf
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,executionStack
06/21/2010 08:17:30,spid5s,Unknown,process id=process5caf66b8 taskpriority=0 logused=20084 waittime=15 schedulerid=2 kpid=5544 status=suspended spid=52 sbid=0 ecid=30 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
06/21/2010 08:17:30,spid5s,Unknown,inputbuf
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,executionStack
06/21/2010 08:17:30,spid5s,Unknown,process id=process5caf65c8 taskpriority=0 logused=20081 waittime=31 schedulerid=2 kpid=6576 status=suspended spid=52 sbid=0 ecid=25 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
06/21/2010 08:17:30,spid5s,Unknown,inputbuf
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,executionStack
06/21/2010 08:17:30,spid5s,Unknown,process id=process5caf64d8 taskpriority=0 logused=20079 waittime=31 schedulerid=2 kpid=4752 status=suspended spid=52 sbid=0 ecid=21 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
06/21/2010 08:17:30,spid5s,Unknown,inputbuf
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,executionStack
06/21/2010 08:17:30,spid5s,Unknown,process id=process5caf63e8 taskpriority=0 logused=20076 waittime=31 schedulerid=2 kpid=7312 status=suspended spid=52 sbid=0 ecid=19 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
06/21/2010 08:17:30,spid5s,Unknown,inputbuf
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,executionStack
06/21/2010 08:17:30,spid5s,Unknown,process id=process5caf6208 taskpriority=0 logused=20072 waittime=31 schedulerid=2 kpid=1740 status=suspended spid=52 sbid=0 ecid=13 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
06/21/2010 08:17:30,spid5s,Unknown,process id=process55a384d8 schedulerid=3 kpid=0
06/21/2010 08:17:30,spid5s,Unknown,inputbuf
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,executionStack
06/21/2010 08:17:30,spid5s,Unknown,process id=process55a383e8 taskpriority=0 logused=20084 schedulerid=3 kpid=6868 status=suspended spid=52 sbid=0 ecid=31 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
06/21/2010 08:17:30,spid5s,Unknown,inputbuf
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,executionStack
06/21/2010 08:17:30,spid5s,Unknown,process id=process55a382f8 taskpriority=0 logused=20081 waittime=31 schedulerid=3 kpid=6696 status=suspended spid=52 sbid=0 ecid=26 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
06/21/2010 08:17:30,spid5s,Unknown,inputbuf
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,executionStack
06/21/2010 08:17:30,spid5s,Unknown,process id=process55a38208 taskpriority=0 logused=20079 waittime=31 schedulerid=3 kpid=7180 status=suspended spid=52 sbid=0 ecid=23 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
06/21/2010 08:17:30,spid5s,Unknown,inputbuf
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,executionStack
06/21/2010 08:17:30,spid5s,Unknown,process id=process55a38118 taskpriority=0 logused=20076 waittime=31 schedulerid=3 kpid=2640 status=suspended spid=52 sbid=0 ecid=18 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
06/21/2010 08:17:30,spid5s,Unknown,process id=process4e95a6b8 schedulerid=4 kpid=0
06/21/2010 08:17:30,spid5s,Unknown,inputbuf
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmte开发者_如何学Cnd=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,executionStack
06/21/2010 08:17:30,spid5s,Unknown,process id=process4e95a5c8 taskpriority=0 logused=20084 waittime=15 schedulerid=4 kpid=2508 status=suspended spid=52 sbid=0 ecid=32 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
06/21/2010 08:17:30,spid5s,Unknown,inputbuf
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,executionStack
06/21/2010 08:17:30,spid5s,Unknown,process id=process4e95a4d8 taskpriority=0 logused=20081 waittime=31 schedulerid=4 kpid=6328 status=suspended spid=52 sbid=0 ecid=27 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
06/21/2010 08:17:30,spid5s,Unknown,inputbuf
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown

<Repetitive (seemingly) chunks removed in order to be able to post>

06/21/2010 08:17:30,spid5s,Unknown,executionStack
06/21/2010 08:17:30,spid5s,Unknown,process id=process7f9f18 taskpriority=0 logused=20059 waittime=78 schedulerid=2 kpid=6284 status=suspended spid=52 sbid=0 ecid=13 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
06/21/2010 08:17:30,spid5s,Unknown,inputbuf
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,executionStack
06/21/2010 08:17:30,spid5s,Unknown,process id=process7f9d38 taskpriority=0 logused=20063 waittime=62 schedulerid=2 kpid=4760 status=suspended spid=52 sbid=0 ecid=7 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
06/21/2010 08:17:30,spid5s,Unknown,inputbuf
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,executionStack
06/21/2010 08:17:30,spid5s,Unknown,process id=process7f8898 taskpriority=0 logused=20068 waittime=46 schedulerid=2 kpid=6772 status=suspended spid=52 sbid=0 ecid=12 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
06/21/2010 08:17:30,spid5s,Unknown,inputbuf
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,executionStack
06/21/2010 08:17:30,spid5s,Unknown,process id=process7ebf18 taskpriority=0 logused=20059 waittime=78 schedulerid=1 kpid=5052 status=suspended spid=52 sbid=0 ecid=14 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
06/21/2010 08:17:30,spid5s,Unknown,inputbuf
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,unknown
06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000
06/21/2010 08:17:30,spid5s,Unknown,executionStack
06/21/2010 08:17:30,spid5s,Unknown,process id=process7ebd38 taskpriority=0 logused=20072 waittime=31 schedulerid=1 kpid=6208 status=suspended spid=52 sbid=0 ecid=15 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
06/21/2010 08:17:30,spid5s,Unknown,process-list
06/21/2010 08:17:30,spid5s,Unknown,deadlock victim=process7fea78
06/21/2010 08:17:30,spid5s,Unknown,deadlock-list

Could this job block itself? May be it runs asynchronous tasks and something is going wrong.

If not, that should be conflict with another process. Possibly you could set for your database single user mode before launch updates

Generally this kind of deadlock comes from a transaction using parallelism. SQL Server divides the query over several threads and then they deadlock on one another. An immediate fix is to add OPTION (MAXDOP 1) at the end of the query that is multithreaded. However, it's better to optimize the query and/or add appropriate indexes

0

上一篇:

:下一篇

更多 问答 相关资讯：

精彩评论

暂无评论...
登录 注册
Qedev.com
验证码 换一张
取 消

    关注公众号

    热门标签

    图文推荐

    Delphi - Custom drawing a message list

    Delphi - Custom drawing a message list
    C++ header-only include pattern

    C++ header-only include pattern
    IE7 Margin Collapses Into Padding

    IE7 Margin Collapses Into Padding
    in CoffeeScript, how can I use a variable as a key in a hash?

    in CoffeeScript, how can I use a variable as a key in a hash?
    Interactive visualization of a graph in python [closed]

    Interactive visualization of a graph in python [closed]
    How to customise PHP MYSQL tables?

    How to customise PHP MYSQL tables?
    High quality, simple random password generator

    High quality, simple random password generator
    Image Recognition ApI in android

    Image Recognition ApI in android

    开发者开发者网给大家分享系统运维,大数据运维,云计算,编程开发技巧,路由交换,运维和开发相关的资讯及技术文章，同时StackOverflow中文社区，知识经验交流分享。

    法律声明：本站内容均为网友上传，网站举办方负责审核和监督，如存在版权或非法内容，欢迎举报，我们将尽快予以删除。邮箱：devze@qq.com

    Copyright © 2018-2020 开发者. All rights reserved. Powered by 开发者ICP备案号: 京ICP备10032868号-9