Deadlocks

Question

Post reply

Deadlocks

sqln00b

Newbie

Points: 8
More actions
June 29, 2010 at 12:56 pm

#226270

Greetings,
I'll try to explain my problem,
We have 3 servers all with the same type service. The task of this service is to create a mailbox in exchange and after that to update a table in a database. All use the same database and the same query. Lately we are experience a large number of deadlocks (page lock).
I'll show you the deadlock log:
2010-06-29 12:04:51.69 spid14s deadlock-list
2010-06-29 12:04:51.69 spid14s deadlock victim=process2c205c8
2010-06-29 12:04:51.69 spid14s process-list
2010-06-29 12:04:51.69 spid14s process id=processa1a988 taskpriority=0 logused=0 waitresource=PAGE: 6:1:471 waittime=2609 ownerId=46335 transactionname=UPDATE lasttranstarted=2010-06-29T12:04:49.093 XDES=0x4371e78 lockMode=U schedulerid=1 kpid=5200 status=suspended spid=169 sbid=0 ecid=1 priority=0 transcount=0 lastbatchstarted=2010-06-29T12:04:49.093 lastbatchcompleted=2010-06-29T12:04:49.093 clientapp=.Net SqlClient Data Provider hostname=server03 hostpid=11064 isolationlevel=read committed (2) xactid=46335 currentdb=6 lockTimeout=4294967295 clientoption1=536870944 clientoption2=128024
2010-06-29 12:04:51.69 spid14s executionStack
2010-06-29 12:04:51.69 spid14s frame procname=db01.dbo.UserPool_Release line=11 stmtstart=608 stmtend=944 sqlhandle=0x030006007974e51df2410601c49b00000100000000000000
2010-06-29 12:04:51.69 spid14s update UsersPOOL set available = 1,
2010-06-29 12:04:51.69 spid14s CreatingEndDate = GETDATE(),
2010-06-29 12:04:51.69 spid14s MailCreatingTime = GETDATE(),
2010-06-29 12:04:51.69 spid14s NomeSVR = @NomeSVR
2010-06-29 12:04:51.69 spid14s where UsersPOOL.EntityID = @EntityID
2010-06-29 12:04:51.69 spid14s inputbuf
2010-06-29 12:04:51.69 spid14s process id=processd544d8 taskpriority=0 logused=0 waitresource=PAGE: 6:1:533 waittime=2609 ownerId=46338 transactionname=UPDATE lasttranstarted=2010-06-29T12:04:49.093 XDES=0x67d1e00 lockMode=U schedulerid=4 kpid=6364 status=suspended spid=156 sbid=0 ecid=4 priority=0 transcount=0 lastbatchstarted=2010-06-29T12:04:49.093 lastbatchcompleted=2010-06-29T12:04:49.093 clientapp=.Net SqlClient Data Provider hostname=server03 hostpid=11064 isolationlevel=read committed (2) xactid=46338 currentdb=6 lockTimeout=4294967295 clientoption1=536870944 clientoption2=128024
2010-06-29 12:04:51.69 spid14s executionStack
2010-06-29 12:04:51.69 spid14s frame procname=db01.dbo.UserPool_Release line=11 stmtstart=608 stmtend=944 sqlhandle=0x030006007974e51df2410601c49b00000100000000000000
2010-06-29 12:04:51.69 spid14s update UsersPOOL set available = 1,
2010-06-29 12:04:51.69 spid14s CreatingEndDate = GETDATE(),
2010-06-29 12:04:51.69 spid14s MailCreatingTime = GETDATE(),
2010-06-29 12:04:51.69 spid14s NomeSVR = @NomeSVR
2010-06-29 12:04:51.69 spid14s where UsersPOOL.EntityID = @EntityID
2010-06-29 12:04:51.69 spid14s inputbuf
2010-06-29 12:04:51.69 spid14s process id=processdeac58 taskpriority=0 logused=20004 waittime=2593 schedulerid=5 kpid=5228 status=suspended spid=169 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2010-06-29T12:04:49.093 lastbatchcompleted=2010-06-29T12:04:49.093 clientapp=.Net SqlClient Data Provider hostname=server03 hostpid=11064 loginname=_obs isolationlevel=read committed (2) xactid=46335 currentdb=6 lockTimeout=4294967295 clientoption1=536870944 clientoption2=128024
2010-06-29 12:04:51.69 spid14s executionStack
2010-06-29 12:04:51.69 spid14s frame procname=db01.dbo.UserPool_Release line=11 stmtstart=608 stmtend=944 sqlhandle=0x030006007974e51df2410601c49b00000100000000000000
2010-06-29 12:04:51.69 spid14s update UsersPOOL set available = 1,
2010-06-29 12:04:51.69 spid14s CreatingEndDate = GETDATE(),
2010-06-29 12:04:51.69 spid14s MailCreatingTime = GETDATE(),
2010-06-29 12:04:51.69 spid14s NomeSVR = @NomeSVR
2010-06-29 12:04:51.69 spid14s where UsersPOOL.EntityID = @EntityID
2010-06-29 12:04:51.69 spid14s inputbuf
2010-06-29 12:04:51.69 spid14s Proc [Database Id = 6 Object Id = 501576825]
2010-06-29 12:04:51.69 spid14s process id=processdeb5b8 taskpriority=0 logused=0 waitresource=PAGE: 6:1:533 waittime=2609 ownerId=46338 transactionname=UPDATE lasttranstarted=2010-06-29T12:04:49.093 XDES=0x110ca768 lockMode=U schedulerid=5 kpid=6080 status=suspended spid=156 sbid=0 ecid=7 priority=0 transcount=0 lastbatchstarted=2010-06-29T12:04:49.093 lastbatchcompleted=2010-06-29T12:04:49.093 clientapp=.Net SqlClient Data Provider hostname=server03 hostpid=11064 isolationlevel=read committed (2) xactid=46338 currentdb=6 lockTimeout=4294967295 clientoption1=536870944 clientoption2=128024
2010-06-29 12:04:51.69 spid14s executionStack
2010-06-29 12:04:51.69 spid14s frame procname=db01.dbo.UserPool_Release line=11 stmtstart=608 stmtend=944 sqlhandle=0x030006007974e51df2410601c49b00000100000000000000
2010-06-29 12:04:51.69 spid14s update UsersPOOL set available = 1,
2010-06-29 12:04:51.69 spid14s CreatingEndDate = GETDATE(),
2010-06-29 12:04:51.69 spid14s MailCreatingTime = GETDATE(),
2010-06-29 12:04:51.69 spid14s NomeSVR = @NomeSVR
2010-06-29 12:04:51.69 spid14s where UsersPOOL.EntityID = @EntityID
2010-06-29 12:04:51.69 spid14s inputbuf
2010-06-29 12:04:51.69 spid14s process id=process2c205c8 taskpriority=0 logused=0 waitresource=PAGE: 6:1:471 waittime=2609 ownerId=46335 transactionname=UPDATE lasttranstarted=2010-06-29T12:04:49.093 XDES=0x4344568 lockMode=U schedulerid=6 kpid=6140 status=suspended spid=169 sbid=0 ecid=2 priority=0 transcount=0 lastbatchstarted=2010-06-29T12:04:49.093 lastbatchcompleted=2010-06-29T12:04:49.093 clientapp=.Net SqlClient Data Provider hostname=server03 hostpid=11064 isolationlevel=read committed (2) xactid=46335 currentdb=6 lockTimeout=4294967295 clientoption1=536870944 clientoption2=128024
2010-06-29 12:04:51.69 spid14s executionStack
2010-06-29 12:04:51.69 spid14s frame procname=db01.dbo.UserPool_Release line=11 stmtstart=608 stmtend=944 sqlhandle=0x030006007974e51df2410601c49b00000100000000000000
2010-06-29 12:04:51.69 spid14s update UsersPOOL set available = 1,
2010-06-29 12:04:51.69 spid14s CreatingEndDate = GETDATE(),
2010-06-29 12:04:51.69 spid14s MailCreatingTime = GETDATE(),
2010-06-29 12:04:51.69 spid14s NomeSVR = @NomeSVR
2010-06-29 12:04:51.69 spid14s where UsersPOOL.EntityID = @EntityID
2010-06-29 12:04:51.69 spid14s inputbuf
2010-06-29 12:04:51.69 spid14s resource-list
2010-06-29 12:04:51.69 spid14s pagelock fileid=1 pageid=471 dbid=6 objectname=db01.dbo.UsersPOOL id=locke6e8bc0 mode=U associatedObjectId=72057594038714368
2010-06-29 12:04:51.69 spid14s owner-list
2010-06-29 12:04:51.69 spid14s owner id=processd544d8 mode=U
2010-06-29 12:04:51.69 spid14s waiter-list
2010-06-29 12:04:51.69 spid14s waiter id=processa1a988 mode=U requestType=wait
2010-06-29 12:04:51.69 spid14s waiter id=process2c205c8 mode=U requestType=wait
2010-06-29 12:04:51.69 spid14s exchangeEvent id=port44a8400 nodeId=4
2010-06-29 12:04:51.69 spid14s owner-list
2010-06-29 12:04:51.69 spid14s owner event=e_waitNone type=producer id=process2c205c8
2010-06-29 12:04:51.69 spid14s owner event=e_waitNone type=producer id=processa1a988
2010-06-29 12:04:51.69 spid14s waiter-list
2010-06-29 12:04:51.69 spid14s waiter event=e_waitPipeGetRow type=consumer id=processdeac58
2010-06-29 12:04:51.70 spid14s pagelock fileid=1 pageid=533 dbid=6 objectname=db01.dbo.UsersPOOL id=locke8e2440 mode=U associatedObjectId=72057594038714368
2010-06-29 12:04:51.70 spid14s owner-list
2010-06-29 12:04:51.70 spid14s owner id=processdeac58 mode=U
2010-06-29 12:04:51.70 spid14s waiter-list
2010-06-29 12:04:51.70 spid14s waiter id=processdeb5b8 mode=U requestType=wait
2010-06-29 12:04:51.70 spid14s waiter id=processd544d8 mode=U requestType=wait
Any help or advice would be appreciated, i'm a n00b in sql :hehe:

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

sqln00b Newbie Points: 8 More actions · Answer 1

I want to add that deadlocks are not occuring with only 1 service active